Full outer join

The FULL OUTER JOIN clause results in the inclusion of rows from two tables. If a value is missing when rows are joined, that value is null in the result table.

The join condition for a full outer join must be a search condition that compares two columns. The predicates of the search condition can be combined only with AND. Each predicate must have the form 'expression = expression'.

Begin general-use programming interface information.
Example 1: This query performs a full outer join of the PARTS and PRODUCTS tables: 
SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS FULL OUTER JOIN PRODUCTS
  ON PARTS.PROD# = PRODUCTS.PROD#;

The result table looks like this: 

PART         SUPPLIER       PROD#     PRODUCT
========     ============   =====     ===========
WIRE         ACWF           10        GENERATOR
MAGNETS      BATEMAN        10        GENERATOR
OIL          WESTERN_CHEM   160       -----------
BLADES       ACE_STEEL      205       SAW
PLASTIC      PLASTIK_CORP   30        RELAY
-------      ------------   -----     SCREWDRIVER
End general-use programming interface information.

Using COALESCE

This function can be particularly useful in full outer join operations because it returns the first nonnull value. For example, notice that the result in the example above is null for SCREWDRIVER, even though the PRODUCTS table contains a product number for SCREWDRIVER. If you select PRODUCTS.PROD# instead, PROD# is null for OIL. If you select both PRODUCTS.PROD# and PARTS.PROD#, the result contains two columns, and both columns contain some null values.

Begin general-use programming interface information.
Example 2: You can merge data from both columns into a single column, eliminating the null values, by using the COALESCE function. Consider this query with the same PARTS and PRODUCTS tables: 
SELECT PART, SUPPLIER,
  COALESCE(PARTS.PROD#, PRODUCTS.PROD#) AS PRODNUM, PRODUCT
  FROM PARTS FULL OUTER JOIN PRODUCTS
  ON PARTS.PROD# = PRODUCTS.PROD#;

This statement gives this result: 

PART        SUPPLIER         PRODNUM     PRODUCT
=======     ============     =======     ===========
WIRE        ACWF             10          GENERATOR
MAGNETS     BATEMAN          10          GENERATOR
OIL         WESTERN_CHEM     160         -----------
BLADES      ACE_STEEL        205         SAW
PLASTIC     PLASTIK_CORP     30          RELAY
-------     ------------     505         SCREWDRIVER

The AS clause AS PRODNUM provides a name for the result of the COALESCE function.

End general-use programming interface information.