Left outer join

The LEFT OUTER JOIN clause lists rows from the left table even if there are no matching rows on right table.

As in an inner join, the join condition of a left outer join can be any simple or compound search condition that does not contain a subquery reference.

Begin general-use programming interface information.
Example: To include rows from the PARTS table that have no matching values in the PRODUCTS table and to include prices that exceed $10.00, run this query:
SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT, PRICE
  FROM PARTS LEFT OUTER JOIN PRODUCTS
  ON PARTS.PROD#=PRODUCTS.PROD#
  AND PRODUCTS.PRICE>10.00;

The result table looks like the following example:

PART        SUPPLIER         PROD#     PRODUCT       PRICE
=======     ============     =====     =========     =====
WIRE        ACWF             10        GENERATOR     45.75
MAGNETS     BATEMAN          10        GENERATOR     45.75
OIL         WESTERN_CHEM     160       ---------     -----
BLADES      ACE_STEEL        205       SAW           18.90
PLASTIC     PLASTIK_CORP     30        ---------     -----

Because the PARTS table can have rows that are not matched by values in the joined columns and because the PRICE column is not in the PARTS table, rows in which the PRICE value does not exceed $10.00 are included in the result of the join, but the PRICE value is set to null.

In this result table, the row for PROD# 160 has null values on the right two columns because PROD# 160 does not match another product number. PROD# 30 has null values on the right two columns because the price of PROD# 30 is less than $10.00.

End general-use programming interface information.