Join Operations Supported by NuoDB

Some queries require information that can only be obtained by accessing data across multiple tables. This can be accomplished by using a JOIN statement in the FROM clause. A JOIN operation combines rows from multiple tables using one or more matching column values from each table specified in the JOIN statement. These matching columns are defined by a join predicate. A table can also be joined to itself in this same way.

NuoDB supports several types of joins.

The examples for these JOIN types use a new schema named TEST. This schema is created from tables defined in the SQL QuickStart HOCKEY schema (see Running the SQL QuickStart).

The following creates a new table named TEAMS in this TEST schema. This is a subset of the HOCKEY.TEAMS table. It includes only those teams that played in the Eastern Conference in the year 2011.

USE test;
CREATE TABLE test.teams (team_id string, team_name string, wins integer, losses integer, division_id string);
INSERT INTO test.teams (SELECT teamid, name, wins, losses, divisionid
                          FROM hockey.teams
                           WHERE year = 2011 AND conferenceid = 'EC');
SELECT * FROM teams;
 TEAM_ID       TEAM_NAME      WINS  LOSSES  DIVISION_ID
 -------- ------------------- ----- ------- ------------
   BOS    Boston Bruins        49     29         NE
   BUF    Buffalo Sabres       39     32         NE
   CAR    Carolina Hurricanes  33     33         SE
   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

The following creates a new table named PLAYERS in this TEST schema. This is a subset of the HOCKEY.PLAYERS table. This new table includes players who played the goalie position in the year 2011 for a TEAM whose TEAM_ID starts with A, B, C or D. Note that some of these goalie players may have played for teams that are not included in the TEST.TEAMS table.

CREATE TABLE test.players (playerid string, player_name string, year integer, team_id string, position string);
INSERT INTO test.players (select p.playerid, p.firstname||' '||p.lastname, s.year, s.teamid, s.position
                            FROM hockey.players p, hockey.scoring s
                             WHERE p.playerid = s.playerid
                              AND s.year = 2011 and s.position = 'G' and s.stint = 1 and s.teamid < 'E');
SELECT * FROM test.players ORDER BY team_id;
 PLAYERID         PLAYER_NAME       YEAR  TEAM_ID  POSITION
 ---------- ----------------------- ----- -------- ---------
 bachmri01  Richard Bachman         2011    DAL        G
 bouchbr01  Brian Boucher           2011    CAR        G
 conklty01  Ty Conklin              2011    DET        G
 crawfco01  Corey Crawford          2011    CHI        G
 drouije01  Jeff Drouin-Deslauriers 2011    AND        G
 ellisda01  Dan Ellis               2011    AND        G
 emeryra01  Ray Emery               2011    CHI        G
 enrotjh01  Jhonas Enroth           2011    BUF        G
 gigueje01  Jean-Sebastien Giguere  2011    COL        G
 hillejo01  Jonas Hiller            2011    AND        G
 howarji01  Jimmy Howard            2011    DET        G
 hunwish01  Shawn Hunwick           2011    CBS        G
 irvinle01  Leland Irving           2011    CAL        G
 karlshe01  Henrik Karlsson         2011    CAL        G
 khudoan01  Anton Khudobin          2011    BOS        G
 kiprumi01  Miikka Kiprusoff        2011    CAL        G
 lehtoka01  Kari Lehtonen           2011    DAL        G
 macdojo01  Joey MacDonald          2011    DET        G
 macindr01  Drew MacIntyre          2011    BUF        G
 masonst01  Steve Mason             2011    CBS        G
 millery01  Ryan Miller             2011    BUF        G
 murphmi02  Mike Murphy             2011    CAR        G
 peterju01  Justin Peters           2011    CAR        G
 rasktu01   Tuukka Rask             2011    BOS        G
 raycran01  Andrew Raycroft         2011    DAL        G
 sanfocu01  Curtis Sanford          2011    CBS        G
 tarkkii01  Iiro Tarkki             2011    AND        G
 thomati01  Tim Thomas              2011    BOS        G
 turcoma01  Marty Turco             2011    BOS        G
 varlasi01  Semyon Varlamov         2011    COL        G
 wardca01   Cam Ward                2011    CAR        G
 yorkal01   Allen York              2011    CBS        G