INNER Join Operations

The result set of an INNER join contains rows from two or more tables that have matching values based on the defined join-predicate and that meet any WHERE clause criteria. In other words, rows with that matching value must exist in both tables. In the case of one table having duplicate row values, that row will be returned twice in the result set and both rows will be joined to the one row in the second table. Another way of writing an INNER join is to use comma separated table names in the FROM clause and including the join-predicate on the where clause, along with any additional WHERE clause statements.

teams players inner join 1

Using the tables created at Join Operations Supported by NuoDB, select all of the goalies for both the “BOS” team and the “BUF” team. Selecting only the rows required from the teams table, and using an INNER join on the players table, will only return players for those teams.

SELECT t.*,p.* FROM teams t
              INNER JOIN players p ON p.team_id = t.team_id
WHERE t.team_id in ('BOS','BUF');
 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
   BUF    Buffalo Sabres  39     32         NE      enrotjh01  Jhonas Enroth  2011    BUF        G
   BUF    Buffalo Sabres  39     32         NE      macindr01  Drew MacIntyre 2011    BUF        G
   BUF    Buffalo Sabres  39     32         NE      millery01  Ryan Miller    2011    BUF        G

If there are no players for a team, an INNER join will not return any rows. This is because an INNER join only returns rows that exist in both tables. The “WPG” team row only exists in the teams table. Using the join predicate of TEAM_ID, there are no rows in the players table that match.

SELECT t.*,p.* FROM teams t
              INNER JOIN players p ON p.team_id = t.team_id
WHERE t.team_id = 'WPG';
<< No Rows Returned >>

Another way of writing an INNER join is to use comma separated table names in the FROM clause and to include the join-predicate on the WHERE clause, along with any additional WHERE clause statements. As an example, the above SQL statement can be written as:

SELECT t.*,p.* FROM teams t, players p
 WHERE p.team_id = t.team_id AND t.team_id = 'WPG';