About Multiple Join Operations

A SQL SELECT statement can contain multiple JOIN operations. Each JOIN operation is defined using one of the JOIN types supported by NuoDB. A join predicate is defined for each JOIN operation.

The first example below shows a SQL SELECT statement with one JOIN operator. The second example builds on this first example by adding a second JOIN operator.

For example, suppose you want to select players from the TEST.PLAYERS table who played for a specific team in the TEST.TEAMS table. The following statement specifies an INNER JOIN to select from these two tables. The join predicate specifies that rows in the TEST.TEAMS table will be matched to rows in the TEST.PLAYERS table using the team_id column value. The TEST.TEAMS table predicate is defined by a WHERE clause, which specifies the team_id value to be selected.

Suppose you want to know if 2011 was the rookie year for any of the players selected in the previous example. This information is not available from the TEST.PLAYERS table, but it is available from the HOCKEY.PLAYERS table. A JOIN operation to the HOCKEY.PLAYERS table can be added to the previous example to obtain this additional information for each player selected. This second JOIN operator uses a join predicate that specifies that rows from the TEST.PLAYERS table will be matched to rows in the HOCKEY.PLAYERS table based on having the same playerid column value.

There are a couple of options, depending on the type of JOIN used, for deriving this rookie year information for these selected players. Each of these JOIN types returns the same result set.

INNER JOIN:

This JOIN type would be used if all of the players that were previously selected also had a matching row in the HOCKEY.PLAYERS table. For every player that is selected, we would test whether or not 2011 was their rookie year. The original SELECT statement is modified as follows:

LEFT OUTER JOIN:

This JOIN type would be used if some of the players that were previously selected did not have a matching row in the HOCKEY.PLAYERS table. This can be illustrated by adding a table predicate to the HOCKEY.PLAYERS table that filters rows such that only players who were rookie players in 2011 will be included in the JOIN operation for that table. Using a LEFT OUTER JOIN type for the JOIN operation on TEST.PLAYERS with this subset of the HOCKEY.PLAYERS table, only those players that were rookie players in 2011 will find a match. Because of the LEFT OUTER JOIN, all players previously select from the TEST.PLAYERS table will be selected, regardless of finding a match. The column(s) from the HOCKEY.PLAYERS table can be tested for NULL values to find rows that do not have a match. The NULL values for columns from the HOCKEY.PLAYERS table indicate that the player was not a rookie player in 2011. The original SELECT statement is modified as follows: