Database: SELECT: Joins - Outer

Outer joins include all of the records from an inner join, plus some specified records that failed to join. Why would you want some records that failed to join?

Example

Problem. List all countries (edc_country), ed centers (edc_id) and courses (crs_id) that are currently being offered (ie, for which there are sections). in ascending order.

Solution.

SELECT edc_country, edc_id, crs_id
  FROM Ed_Center NATURAL JOIN Section
  ORDER BY edc_country, edc_id, crs_id;

This is plausible, but some ed centers might not be listed! This would happen if the there are no courses currently being taught at a location.

New Problem. Produce results as before, but include all ed centers, even if no courses are currently being taught.

An outer join causes all records (from either the left table, the right table, or both tables as speficied) to be used, and NULLs will be placed in all fields where there was no joined record.

Solution.

SELECT edc_country, edc_id, crs_id
  FROM Ed_Center NATURAL LEFT OUTER JOIN Section
  ORDER BY edc_country, edc_id, crs_id;

These results will show all ed centers. Those that aren't currently teaching courses will have a NULL for the course.

LEFT, RIGHT, or FULL

An outer join may include the unjoined records from the left table (LEFT), the right table (RIGHT), or both tables (FULL).

Syntax in FROM clause

FROM t1 NATURAL {LEFT | RIGHT | FULL} OUTER JOIN t2
FROM t1         {LEFT | RIGHT | FULL} OUTER JOIN t2 ON c1=c2
FROM t1         {LEFT | RIGHT | FULL} OUTER JOIN t2 USING (c,...)

Syntax in WHERE clause using non-standard operators

The SQL standard provides outer joins only in the FROM clause. Before the outer join standard, individual database systems chose their own syntax for indicating an outer join in the WHERE clause when comparing keys. For example, MS SQL Server expressed column comparisons for left, right, and full join as c1 *= c2, c1 =* c2, and c1 *=* c2. Oracle uses the following syntax c1 = c2 (+), c1 (+) = c2, and c1 (+) = c2 (+), confusingly putting the "(+)" on the opposite side of where the additional records will be generated. Avoid these old, non-standard operators - these database systems now also accept the standard outer join syntax in the FROM clause.