|
<< Click to Display Table of Contents >> OUTER JOIN |
  
|
|
<< Click to Display Table of Contents >> OUTER JOIN |
|
Joins two tables based on column values common between the two, including non-matches.
SELECT column_list
FROM table_reference
LEFT | RIGHT | FULL [OUTER] JOIN table_reference
ON predicate
[LEFT | RIGHT | FULL [OUTER] JOIN table_reference
ON predicate...]
Use an OUTER JOIN to join two tables, a source and joining table, that have one or more columns in common. One or more columns from each table are compared in the ON clause for equal values. The primary difference between inner and outer joins is that, in outer joins rows from the source table that do not have a match in the joining table are not excluded from the result set. Columns from the joining table for rows in the source table without matches have NULL values.
In the statement below, the CUSTOMER and ORDERS tables are joined based on values in the CUSTNO column, which each table contains. For rows from CUSTOMER that do not have a matching value between CUSTOMER.CUSTNO and ORDERS.CUSTNO, the columns from ORDERS contain NULL values.
SELECT *
FROM Customer C
LEFT OUTER JOIN Orders O
ON (C.CustNo = O.CustNo)
The LEFT modifier causes all rows from the table on the left of the OUTER JOIN operator to be included in the result set, with or without matches in the table to the right. If there is no matching row from the table on the right, its columns contain NULL values. The RIGHT modifier causes all rows from the table on the right of the OUTER JOIN operator to be included in the result set, with or without matches. If there is no matching row from the table on the left, its columns contain NULL values. The FULL modifier causes all rows from the all tables specified in the FROM clause to be included in the result set, with or without matches. If there is no matching row from one of the tables, its columns contain NULL values.
More than one table may be joined with an INNER JOIN. One use of the INNER JOIN operator and corresponding ON clause is required for each each set of two tables joined. One column comparison predicate in an ON clause is required for each column compared to join each two tables. The statement below joins the CUSTOMER table to ORDERS, and then ORDERS to ITEMS. In this case, the joining table ORDERS acts as a source table for the joining table ITEMS.
SELECT *
FROM Customer C
FULL OUTER JOIN Orders O
ON (C.CustNo = O.CustNo)
FULL OUTER JOIN items I
ON (O.OrderNo = I.OrderNo)
Tables may also be joined using expressions to produce a single value for the join comparison predicate. Here, the ID1 and ID2 columns in JOINING are separately compared with two values produced by the SUBSTRING function using the single column ID in SOURCE.
SELECT *
FROM Source S
RIGHT OUTER JOIN Joining J
ON (SUBSTRING(S.ID FROM 1 FOR 2) = J.ID1) AND
(SUBSTRING(S.ID FROM 3 FOR 1) = J.ID2)
An ORDER BY clause in OUTER JOIN statements can use columns from any table specified in the FROM clause to sort the result set.
