Outer Joins

A normal SELECT statement only selects rows that meet all conditions of the WHERE clause, including the join conditions. It is sometimes necessary to choose all data from one table and only choose rows from a second table if there is a row that satisfies the join condition - otherwise null values for columns from the second table are returned. This type of join is known as an Outer join.

The keyword OUTER is part of the SELECT statement and allows rows from a multi-table join to be returned when some rows from the first table do not have rows that join to the second table. Every row in the first table will be returned that satisfies the non-joining I conditions of the SELECT statement. This is useful when information is required from the first (or dominant) table whether or not a join is effected with the other (or subservient) tables.

Consider the following query:

SELECT a.one, b.two FROM a, b WHERE a.three = b.three;

Data will be selected only when the values of the field three are the same on both tables. To ensure that all records are selected from table a, regardless of whether matching rows exist in table b, the format would be:

SELECT a.one, b.two FROM a, OUTER b WHERE a.three b.three;

All records will be selected from table a and should they have a match on table b then that I: data will also be selected. A NULL value will exist for b.two where a match is not made.

This can be taken to deeper levels by the use of statements such as:

SELECT a.one, b.two, c.three FROM a, OUTER (b, c) WHERE select-criteria;

Here, all records will be selected from table a and associated returns will be obtained from tables band c only when they have a match with table a. V - This can be extended so that a value is only returned from the third table when a match is obtained between all three tables:

SELECT a.one, b.two, c.three FROM a, OUTER (b, OUTER c) WHERE select-criteria;