Using a Left Outer Join

Go Up to Using Outer Joins

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.