Full Outer Join

A FULL OUTER JOIN returns all rows from both tables - matching rows where a relationship exists, and filling in NULL for the columns of whichever table has no match, on either side.

Basic Syntax

SELECT c.first_name, o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

This returns every customer (even those with no orders) and every order (even any that somehow lack a matching customer), combining the effect of a LEFT JOIN and a RIGHT JOIN in one query.

Finding Unmatched Rows on Either Side

SELECT c.first_name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL OR o.order_id IS NULL;

Database Support

Not every database supports FULL OUTER JOIN directly - MySQL, for example, doesn't support it natively, and it's typically simulated with a LEFT JOIN combined with a UNION and a RIGHT JOIN.