RIGHT OUTER Join Operations
The result set of a RIGHT OUTER join contains all rows from both tables that meet the WHERE clause criteria, the same as an INNER join result set.
In addition, any rows from the right table that do not have a matching row that exists in the left table is also included in the result set.
The columns being selected from the left side table will return NULL values.
Using the tables created in Join Operations Supported by NuoDB, we can use a RIGHT OUTER join to select all the players with names starting with J who played in 2011 and the players who played in the Eastern Conference in 2011.
Use a RIGHT OUTER join with the TEAMS table to select all the rows common to both the tables and all the remaining rows from the PLAYERS table.
Some columns selected from the TEAMS table are NULL.
This is because there are no rows in the TEAMS table for the corresponding TEAM_ID in the PLAYERS table.
Since it is a RIGHT OUTER join, it will return all rows from the right table, the PLAYERS table, that have PLAYER_NAME starting with J.
SELECT t.*,p.* FROM teams t
RIGHT OUTER JOIN players p ON p.team_id = t.team_id
WHERE p.player_name like 'J%'
ORDER BY p.team_id, p.playerid; TEAM_ID TEAM_NAME WINS LOSSES DIVISION_ID PLAYERID PLAYER_NAME YEAR TEAM_ID POSITION
-------- ------------------- ------ ------- ------------ --------- ----------------------- ----- -------- ---------
<null> <null> <null> <null> <null> drouije01 Jeff Drouin-Deslauriers 2011 AND G
<null> <null> <null> <null> <null> hillejo01 Jonas Hiller 2011 AND G
BUF Buffalo Sabres 39 32 NE enrotjh01 Jhonas Enroth 2011 BUF G
CAR Carolina Hurricanes 33 33 SE peterju01 Justin Peters 2011 CAR G
<null> <null> <null> <null> <null> gigueje01 Jean-Sebastien Giguere 2011 COL G
<null> <null> <null> <null> <null> howarji01 Jimmy Howard 2011 DET G
<null> <null> <null> <null> <null> macdojo01 Joey MacDonald 2011 DET GA RIGHT OUTER join can also be used to return a result set that has all the rows in the right table that do not exist in the left table by testing in the WHERE clause the value of a NOT NULL column in the left table having a NULL value.
This result is the same as using a WHERE NOT EXISTS subquery.
For example, to select all players who played in 2011 but did not participate in the Eastern Conference, use the RIGHT OUTER join with a WHERE clause.
The columns returned from the TEAMS table after the RIGHT OUTER join will be NULL.
Since the TEAM_ID column is a NOT NULL column, we can use that column to test whether a row in the TEAMS table exists for each team.
SELECT t.*, p.* FROM teams t
RIGHT OUTER JOIN players p ON t.team_id = p.team_id
WHERE t.team_id is NULL
ORDER BY p.team_id, p.playerid; TEAM_ID TEAM_NAME WINS LOSSES DIVISION_ID PLAYERID PLAYER_NAME YEAR TEAM_ID POSITION
-------- ---------- ------ ------- ------------ --------- ----------------------- ----- -------- ---------
<null> <null> <null> <null> <null> drouije01 Jeff Drouin-Deslauriers 2011 AND G
<null> <null> <null> <null> <null> ellisda01 Dan Ellis 2011 AND G
<null> <null> <null> <null> <null> hillejo01 Jonas Hiller 2011 AND G
<null> <null> <null> <null> <null> tarkkii01 Iiro Tarkki 2011 AND G
<null> <null> <null> <null> <null> irvinle01 Leland Irving 2011 CAL G
<null> <null> <null> <null> <null> karlshe01 Henrik Karlsson 2011 CAL G
<null> <null> <null> <null> <null> kiprumi01 Miikka Kiprusoff 2011 CAL G
<null> <null> <null> <null> <null> hunwish01 Shawn Hunwick 2011 CBS G
<null> <null> <null> <null> <null> masonst01 Steve Mason 2011 CBS G
<null> <null> <null> <null> <null> sanfocu01 Curtis Sanford 2011 CBS G
<null> <null> <null> <null> <null> yorkal01 Allen York 2011 CBS G
<null> <null> <null> <null> <null> crawfco01 Corey Crawford 2011 CHI G
<null> <null> <null> <null> <null> emeryra01 Ray Emery 2011 CHI G
<null> <null> <null> <null> <null> gigueje01 Jean-Sebastien Giguere 2011 COL G
<null> <null> <null> <null> <null> varlasi01 Semyon Varlamov 2011 COL G
<null> <null> <null> <null> <null> bachmri01 Richard Bachman 2011 DAL G
<null> <null> <null> <null> <null> lehtoka01 Kari Lehtonen 2011 DAL G
<null> <null> <null> <null> <null> raycran01 Andrew Raycroft 2011 DAL G
<null> <null> <null> <null> <null> conklty01 Ty Conklin 2011 DET G
<null> <null> <null> <null> <null> howarji01 Jimmy Howard 2011 DET G
<null> <null> <null> <null> <null> macdojo01 Joey MacDonald 2011 DET GThis result is the same as using WHERE NOT EXISTS, except the columns from the left table are not selected.
SELECT p.* FROM players p
WHERE NOT EXISTS (SELECT 'x' FROM teams t
WHERE p.team_id = t.team_id)
ORDER BY p.team_id, p.playerid; PLAYERID PLAYER_NAME YEAR TEAM_ID POSITION
--------- ----------------------- ----- -------- ---------
drouije01 Jeff Drouin-Deslauriers 2011 AND G
ellisda01 Dan Ellis 2011 AND G
hillejo01 Jonas Hiller 2011 AND G
tarkkii01 Iiro Tarkki 2011 AND G
irvinle01 Leland Irving 2011 CAL G
karlshe01 Henrik Karlsson 2011 CAL G
kiprumi01 Miikka Kiprusoff 2011 CAL G
hunwish01 Shawn Hunwick 2011 CBS G
masonst01 Steve Mason 2011 CBS G
sanfocu01 Curtis Sanford 2011 CBS G
yorkal01 Allen York 2011 CBS G
crawfco01 Corey Crawford 2011 CHI G
emeryra01 Ray Emery 2011 CHI G
gigueje01 Jean-Sebastien Giguere 2011 COL G
varlasi01 Semyon Varlamov 2011 COL G
bachmri01 Richard Bachman 2011 DAL G
lehtoka01 Kari Lehtonen 2011 DAL G
raycran01 Andrew Raycroft 2011 DAL G
conklty01 Ty Conklin 2011 DET G
howarji01 Jimmy Howard 2011 DET G
macdojo01 Joey MacDonald 2011 DET G