FULL OUTER Join Operations
The result set of a FULL OUTER join contains all rows from both tables.
If a row from the right table do not have a matching row in the left table, the column in the left table will return NULL values.
If a row from the left table do not have a matching row in the right table, the column in the right table will return NULL values.
Using the tables created in Join Operations Supported by NuoDB, we can use a FULL OUTER join to select all goalies for team names starting with A, B, C, or D who played in 2011 and the teams that played in the Eastern Conference in 2011.
SELECT t.*,p.* FROM teams t
FULL OUTER JOIN players p ON p.team_id = t.team_id;TEAM_ID TEAM_NAME WINS LOSSES DIVISION_ID PLAYERID PLAYER_NAME YEAR TEAM_ID POSITION
-------- ------------------- ------ ------- ------------ --------- ----------------------- ------ -------- ---------
<null> <null> <null> <null> <null> bachmri01 Richard Bachman 2011 DAL G
CAR Carolina Hurricanes 33 33 SE bouchbr01 Brian Boucher 2011 CAR G
<null> <null> <null> <null> <null> conklty01 Ty Conklin 2011 DET G
<null> <null> <null> <null> <null> crawfco01 Corey Crawford 2011 CHI G
<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> emeryra01 Ray Emery 2011 CHI G
BUF Buffalo Sabres 39 32 NE enrotjh01 Jhonas Enroth 2011 BUF G
<null> <null> <null> <null> <null> gigueje01 Jean-Sebastien Giguere 2011 COL G
<null> <null> <null> <null> <null> hillejo01 Jonas Hiller 2011 AND G
<null> <null> <null> <null> <null> howarji01 Jimmy Howard 2011 DET G
<null> <null> <null> <null> <null> hunwish01 Shawn Hunwick 2011 CBS G
<null> <null> <null> <null> <null> irvinle01 Leland Irving 2011 CAL G
<null> <null> <null> <null> <null> karlshe01 Henrik Karlsson 2011 CAL G
BOS Boston Bruins 49 29 NE khudoan01 Anton Khudobin 2011 BOS G
<null> <null> <null> <null> <null> kiprumi01 Miikka Kiprusoff 2011 CAL G
<null> <null> <null> <null> <null> lehtoka01 Kari Lehtonen 2011 DAL G
<null> <null> <null> <null> <null> macdojo01 Joey MacDonald 2011 DET G
BUF Buffalo Sabres 39 32 NE macindr01 Drew MacIntyre 2011 BUF G
<null> <null> <null> <null> <null> masonst01 Steve Mason 2011 CBS G
BUF Buffalo Sabres 39 32 NE millery01 Ryan Miller 2011 BUF G
CAR Carolina Hurricanes 33 33 SE murphmi02 Mike Murphy 2011 CAR G
CAR Carolina Hurricanes 33 33 SE peterju01 Justin Peters 2011 CAR G
BOS Boston Bruins 49 29 NE rasktu01 Tuukka Rask 2011 BOS G
<null> <null> <null> <null> <null> raycran01 Andrew Raycroft 2011 DAL G
<null> <null> <null> <null> <null> sanfocu01 Curtis Sanford 2011 CBS G
<null> <null> <null> <null> <null> tarkkii01 Iiro Tarkki 2011 AND 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
<null> <null> <null> <null> <null> varlasi01 Semyon Varlamov 2011 COL G
CAR Carolina Hurricanes 33 33 SE wardca01 Cam Ward 2011 CAR G
<null> <null> <null> <null> <null> yorkal01 Allen York 2011 CBS G
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>