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.
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.