SQL FULL OUTER JOIN: Keep All Rows From Both Tables

A FULL OUTER JOIN keeps every row from both tables. Rows that match are combined; rows without a partner on either side still appear, with NULLs for the missing side. It is the join for comparing two lists and seeing everything: matches, and mismatches in both directions.

The example tables#

customers

orders

Syntax#

SELECT columns
FROM table_a
FULL OUTER JOIN table_b
  ON table_a.column = table_b.column;

FULL JOIN is a shorter way to write the same thing.

Example#

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

name  | order_id | amount
------+----------+-------
Asha  | 101      | 250
Asha  | 102      | 120
Ravi  | 103      | 560
Meera | NULL     | NULL
John  | NULL     | NULL
NULL  | 104      | 90

The result contains three kinds of rows:

In other words, FULL OUTER JOIN = INNER JOIN + unmatched left rows + unmatched right rows.

Find mismatches on both sides#

Keep only rows where one side is missing. This is a classic reconciliation query, for example comparing a bank statement with your own records:

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

customer_id | name  | order_id | order_customer
------------+-------+----------+---------------
3           | Meera | NULL     | NULL
4           | John  | NULL     | NULL
NULL        | NULL  | 104      | 5

Summarising with a FULL JOIN#

Because unmatched rows have NULLs, combine COALESCE with aggregates to produce a clean report:

SELECT COALESCE(c.name, 'Unknown customer') AS customer,
       COALESCE(SUM(o.amount), 0) AS total
FROM customers c
FULL OUTER JOIN orders o
  ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total DESC;

customer         | total
-----------------+------
Ravi             | 560
Asha             | 370
Unknown customer | 90
Meera            | 0
John             | 0

Every customer and every order is accounted for, and the totals add up to all revenue (1,020).

MySQL: emulate FULL OUTER JOIN#

MySQL does not support FULL OUTER JOIN. Combine a LEFT JOIN and a RIGHT JOIN with UNION, which also removes the duplicate matched rows:

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
UNION
SELECT c.name, o.order_id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id;

PostgreSQL, SQL Server, Oracle and SQLite 3.39+ support FULL OUTER JOIN directly.

Common mistakes#

Practice#

Key takeaway#