SQL FULL JOIN: Combine All Rows from Both Tables (Examples)

SQL Tutorial · Chapter 23 of 32

The SQL FULL JOIN (also written FULL OUTER JOIN) returns every row from both tables. Where a row on either side has no match, the columns from the other table are NULL. It combines the behaviour of LEFT JOIN and RIGHT JOIN in one statement and is the standard way to reconcile two lists and see what is missing from each.

SQL FULL JOIN Syntax

SELECT columns
FROM table1
FULL OUTER JOIN table2
    ON table1.key = table2.key;

OUTER is optional. Because either side can be NULL, wrap key columns in COALESCE when you need a single non-NULL identifier.

Sample Tables

Two rows have no partner: customer 5 (no orders) and order 105 (customer 6 does not exist). A FULL JOIN shows both.

Example 1: Full Join of Customers and Orders

SELECT c.CustomerID, c.Name, o.OrderID, o.Product
FROM Customers c
FULL OUTER JOIN Orders o ON o.CustomerID = c.CustomerID
ORDER BY COALESCE(c.CustomerID, o.CustomerID), o.OrderID;

Seven rows: five matches, one customer-only row and one order-only row.

Example 2: Show Only the Mismatches

Keeping rows where either key is NULL gives a reconciliation report in one query.

SELECT c.Name, o.OrderID,
       CASE WHEN c.CustomerID IS NULL THEN 'Order without customer'
            ELSE 'Customer without orders' END AS Issue
FROM Customers c
FULL OUTER JOIN Orders o ON o.CustomerID = c.CustomerID
WHERE c.CustomerID IS NULL OR o.OrderID IS NULL;

Example 3: Totals That Reconcile

SELECT COALESCE(c.Name, 'Unknown customer') AS Customer,
       COUNT(o.OrderID) AS Orders,
       COALESCE(SUM(o.Amount), 0) AS Total
FROM Customers c
FULL OUTER JOIN Orders o ON o.CustomerID = c.CustomerID
GROUP BY COALESCE(c.Name, 'Unknown customer')
ORDER BY Total DESC;

The grand total is 2800, the same as SUM(Amount) on Orders alone: nothing was dropped and nothing was double counted.

Example 4: Emulating FULL JOIN in MySQL

MySQL has no FULL JOIN. Combine a LEFT JOIN and a RIGHT JOIN with UNION, which removes the duplicated matched rows.

SELECT c.Name, o.OrderID
FROM Customers c LEFT JOIN Orders o ON o.CustomerID = c.CustomerID
UNION
SELECT c.Name, o.OrderID
FROM Customers c RIGHT JOIN Orders o ON o.CustomerID = c.CustomerID;

Result: the same seven rows as Example 1. If the two SELECTs could legitimately produce identical rows, use UNION ALL on the LEFT JOIN plus a RIGHT JOIN filtered with WHERE c.CustomerID IS NULL instead.

Works in MySQL, SQL Server, PostgreSQL, SQLite

• SQL Server and PostgreSQL: FULL OUTER JOIN is fully supported.
• SQLite: supported from version 3.39; older versions need the UNION workaround.
• MySQL: not supported in any version; always use the UNION of a LEFT and a RIGHT JOIN.

Common Mistakes

• Selecting c.CustomerID alone as the key and getting NULL for orphan orders; use COALESCE across both sides.
• Adding a WHERE condition on one table and unintentionally removing the other side’s unmatched rows.
• Using UNION ALL in the MySQL emulation and doubling every matched row.
• Reaching for FULL JOIN when the question is really one-sided; LEFT JOIN is simpler and faster.

Practice Exercise

1. Count how many rows a FULL JOIN of Customers and Orders returns, and explain each extra row.
2. Return only customers without orders using a FULL JOIN.

-- 1: 7 rows = 5 matched + Emma Brown (no orders) + order 105 (no customer)
SELECT COUNT(*) FROM Customers c FULL OUTER JOIN Orders o ON o.CustomerID = c.CustomerID;

-- 2
SELECT c.Name FROM Customers c
FULL OUTER JOIN Orders o ON o.CustomerID = c.CustomerID
WHERE o.OrderID IS NULL AND c.CustomerID IS NOT NULL;
-- Emma Brown

Related Chapters

• SQL LEFT JOIN – one half of a FULL JOIN
• SQL RIGHT JOIN – the other half
• SQL UNION and UNION ALL – used to emulate FULL JOIN in MySQL
• SQL from Scratch: full course overview

FAQ

What is the difference between FULL JOIN and FULL OUTER JOIN?

None. OUTER is optional; both keywords produce the same result.

Does MySQL support FULL OUTER JOIN?

No. Emulate it by combining a LEFT JOIN and a RIGHT JOIN with UNION.

When should I use a FULL JOIN?

When you need to see unmatched rows from both tables at once, such as reconciling two systems, comparing budget with actuals, or auditing referential integrity.

Chapter 23 of 32 · SQL from Scratch: all 32 chapters