Using Outer Joins
Go Up to Joining Tables (Embedded SQL Guide)
Contents
Outer joins produce a results table that contains columns from every row in one table, and a subset of rows from another table. Actually, one type of outer join returns all rows from each table, but this type of join is used less frequently than other types. Outer join syntax is very similar to that of inner joins:
SELECT col [, col ...] | *
FROM <tablerefleft> {LEFT | RIGHT | FULL} [OUTER] JOIN
<tablerefright> [ON <searchcondition>]
[WHERE <searchcondition>];
Outer join syntax requires that you specify the type of join to perform. There are three possibilities:
- A left outer join retrieves all rows from the left table in a join, and retrieves any rows from the right table that match the search condition specified in the
ONclause. - A right outer join retrieves all rows from the right table in a join, and retrieves any rows from the left table that match the search condition specified in the
ONclause. - A full outer join retrieves all rows from both the left and right tables in a join regardless of the search condition specified in the
ONclause.
Outer joins are useful for comparing a subset of data to the background of all data from which it is retrieved. For example, when listing those countries which contain the sources of rivers, it may be interesting to see those countries which are not the sources of rivers as well.
Using a Left Outer Join
The left outer join is more commonly used than other types of outer joins. The following left outer join retrieves those countries that contain the sources of rivers, and identifies those countries that do not have NULL values in the R.RIVERS column:
EXEC SQL DECLARE RIVSOURCE CURSOR FOR SELECT C.COUNTRY, R.RIVER FROM COUNTRIES C LEFT JOIN RIVERS R ON R.SOURCE = C.COUNTRY ORDER BY C.COUNTRY;
The ON clause enables join search conditions to be expressed in the FROM clause. The search condition that follows the ON clause is the only place where retrieval of rows can be restricted based on columns appearing in the right table. The WHERE clause can be used to further restrict rows based solely on columns in the left (outer) table.
Using a Right Outer Join
A right outer join retrieves all rows from the right table in a join, and only those rows from the left table that match the search condition specified in the ON clause. The following right outer join retrieves a list of rivers and their countries of origin, but also reports those countries that are not the source of any river:
EXEC SQL DECLARE RIVSOURCE CURSOR FOR SELECT R.RIVER, C.COUNTRY FROM RIVERS.R RIGHT JOIN COUNTRIES C ON C.COUNTRY = R.SOURCE ORDER BY C.COUNTRY;
Most right outer joins can be rewritten as left outer joins by reversing the order in which tables are listed.
Using a Full Outer Join
A full outer join returns all selected columns that do not contain NULL values from each table in the FROM clause without regard to search conditions. It is useful to consolidate similar data from disparate tables.
For example, several tables in a database may contain city names. Assuming triggers have not been created that ensure that a city entered in one table is also entered in the others to which it also applies, one of the only ways to see a list of all cities in the database is to use full outer joins. The following example uses two full outer joins to retrieve the name of every city listed in three tables, COUNTRIES, CITIES, and NATIONAL_PARKS:
EXEC SQL DECLARE ALLCITIES CURSOR FOR SELECT DISTINCT CIT.CITY, COU.CAPITAL, N.PARKCITY FROM (CITIES CIT FULL JOIN COUNTRIES COU) FULL JOIN NATIONAL_PARKS N;
This example uses a nested full outer join to process all rows from the CITIES and COUNTRIES tables. The result table produced by that operation is then used as the left table of the full outer join with the NATIONAL_PARKS table. For more information about using nested joins, see Using Nested Joins.
In most databases where tables share similar or related information, triggers are usually created to ensure that all tables are updated with shared information. For more information about triggers, see the Data Definition Guide.
Sort/Merge Optimization for Outer Joins
The Sort/Merge option for the outer joins algorithm recognizes outer and inner streams of an outer join and matches an outer row with a null-valued inner row when there is no matching row in the inner stream.
For full outer joins, the outer and inner streams are swapped after producing matching and null-matched rows for the first stream. The first stream becomes the inner stream and what was the second stream becomes the outer stream. These rows are then left outer joined and only those rows in which the outer stream is matched with nulls are produced. The matching rows on the join terms are filtered out because they were produced before the two streams were swapped during the first pass.