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.

teams players left outer join 1

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.

teams players left outer join not exist 1

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