Using a Full Outer Join

Go Up to Using Outer Joins

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.

Note: 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.