LEFT OUTER Join Operations
The result set of a LEFT OUTER join contains all rows from both tables that meet the WHERE clause criteria, same as an INNER join result set. In addition, any rows from the left table that do not have a matching row that exists in the right table will also be included in the result set. The columns being selected from the right side table will return NULL values.
Using the tables created in Join Operations Supported by NuoDB, if we want to see all players in both the BOS team and the WPG team, we would select those rows from the teams table which becomes the left table. Using a LEFT OUTER Join with the players table, we can select both rows from the teams table, along with any player rows that may exist. Note that the columns being selected from the players table for the WPG team row are NULL. This is because there are no player rows in the players table for team WPG. However, because this is an LEFT OUTER join, it will still return all rows in the LEFT table, the teams table.
SELECT t.*,p.* FROM teams t
LEFT OUTER JOIN players p ON p.team_id = t.team_id
WHERE t.team_id in ('BOS', 'WPG');
TEAM_ID TEAM_NAME WINS LOSSES DIVISION_ID PLAYERID PLAYER_NAME YEAR TEAM_ID POSITION
-------- ------------- ----- ------- ------------ ---------- -------------- ------ -------- ---------
BOS Boston Bruins 49 29 NE khudoan01 Anton Khudobin 2011 BOS G
BOS Boston Bruins 49 29 NE rasktu01 Tuukka Rask 2011 BOS G
BOS Boston Bruins 49 29 NE thomati01 Tim Thomas 2011 BOS G
BOS Boston Bruins 49 29 NE turcoma01 Marty Turco 2011 BOS G
WPG Winnipeg Jets 37 35 SE <null> <null> <null> <null> <null>A LEFT OUTER join can also be used to return a result set that contains all rows in the left table that do not exist in the right table by testing in the WHERE clause the value of a NOT NULL column in the right table having a NULL value. This is the same as using a WHERE NOT EXISTS subquery.
For example, select all of the teams that do not have players in the PLAYERS table. In other words, the columns being returned from the players table are NULL. Since the PLAYERID column is a NOT NULL column, we can use that column to test whether or not a row in the PLAYERS table exists for each team.
SELECT t.*,p.* FROM teams t
LEFT OUTER JOIN players p ON p.team_id = t.team_id
WHERE p.team_id is NULL;
TEAM_ID TEAM_NAME WINS LOSSES DIVISION_ID PLAYERID PLAYER_NAME YEAR TEAM_ID POSITION
-------- ------------------- ----- ------- ------------ ---------- ------------ ------ -------- ---------
FLO Florida Panthers 38 26 SE <null> <null> <null> <null> <null>
MTL Montreal Canadiens 31 35 NE <null> <null> <null> <null> <null>
NJD New Jersey Devils 48 28 AT <null> <null> <null> <null> <null>
NYI New York Islanders 34 37 AT <null> <null> <null> <null> <null>
NYR New York Rangers 51 24 AT <null> <null> <null> <null> <null>
OTT Ottawa Senators 41 31 NE <null> <null> <null> <null> <null>
PHI Philadelphia Flyers 47 26 AT <null> <null> <null> <null> <null>
PIT Pittsburgh Penguins 51 25 AT <null> <null> <null> <null> <null>
TBL Tampa Bay Lightning 38 36 SE <null> <null> <null> <null> <null>
TOR Toronto Maple Leafs 35 37 NE <null> <null> <null> <null> <null>
WAS Washington Capitals 42 32 SE <null> <null> <null> <null> <null>
WPG Winnipeg Jets 37 35 SE <null> <null> <null> <null> <null>This result is the same as using WHERE NOT EXISTS, except the columns from the right table are not selected.
SELECT t.* FROM teams t
WHERE NOT EXISTS (SELECT 'x' FROM players p
WHERE p.team_id = t.team_id);
TEAM_ID TEAM_NAME WINS LOSSES DIVISION_ID
-------- ------------------- ----- ------- ------------
FLO Florida Panthers 38 26 SE
MTL Montreal Canadiens 31 35 NE
NJD New Jersey Devils 48 28 AT
NYI New York Islanders 34 37 AT
NYR New York Rangers 51 24 AT
OTT Ottawa Senators 41 31 NE
PHI Philadelphia Flyers 47 26 AT
PIT Pittsburgh Penguins 51 25 AT
TBL Tampa Bay Lightning 38 36 SE
TOR Toronto Maple Leafs 35 37 NE
WAS Washington Capitals 42 32 SE
WPG Winnipeg Jets 37 35 SE