You are here: SQL Development > About Join Operations Supported by NuoDB > About LEFT OUTER Join Operations

About 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 at About 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 first table that do not exist in the second table by testing in the WHERE clause the value of a NOT NULL column in the second table having a NULL value. This is the same as using a WHERE NOT EXISTS sub-query.

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 PLAYSERS 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 is the same as using WHERE NOT EXISTS, except that you cannot select the columns from the RIGHT table. This might be acceptable, since they would all contain NULL values.

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