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 SEThe 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