SQL FULL OUTER JOIN: Reconciling Data and Faking It in MySQL

FULL OUTER JOIN is the join that throws nothing away. An INNER JOIN returns only matches, a LEFT JOIN protects the left table, a RIGHT JOIN protects the right one. A FULL OUTER JOIN keeps rows from both sides: when they match, the halves are stitched together; when they don't, the missing half is padded with NULL. That's exactly why it shines for reconciliation — when you need to find the differences between two sources, not their intersection.

In PostgreSQL it's a native operator. MySQL has no such thing at all, so you have to assemble it by hand. Let's walk through it on a users / orders schema.

What FULL OUTER JOIN actually returns

Say we have registered users and orders. Some orders were placed by guests (no user_id), and some users haven't bought anything yet.

-- PostgreSQL
SELECT
    u.id        AS user_id,
    u.email,
    o.id        AS order_id,
    o.amount
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id;

The result splits into three logical groups:

• Matches — the user has an order; both halves of the row are populated.
• Left only — a user with no orders: the o.* columns are NULL.
• Right only — an order with no user (a guest or a dangling user_id): the u.* columns are NULL.

The OUTER keyword is optional: FULL JOIN and FULL OUTER JOIN are identical. The join predicate still lives in ON, and it's symmetric — swapping the table order doesn't change the result set (only the column order).

Reconciliation: hunting for mismatches

The real practical payoff of FULL OUTER JOIN is surfacing whatever exists on only one side. The classic case: reconcile payments from a billing system against orders in our database and see the loose ends on both sides at once.

-- Orders with no payment AND payments with no order, in one query
SELECT
    o.id          AS order_id,
    o.amount      AS order_amount,
    p.id          AS payment_id,
    p.amount      AS payment_amount,
    CASE
        WHEN o.id IS NULL THEN 'payment without order'
        WHEN p.id IS NULL THEN 'order without payment'
        WHEN o.amount <> p.amount THEN 'amount mismatch'
        ELSE 'ok'
    END AS status
FROM orders o
FULL OUTER JOIN payments p ON p.order_id = o.id
WHERE o.id IS NULL
   OR p.id IS NULL
   OR o.amount <> p.amount;

The filter o.id IS NULL OR p.id IS NULL keeps only the unmatched rows — that's your discrepancy report. To find true orphans on either side, test for NULL on a column that can't itself be null, such as the primary key, rather than a field that might legitimately be NULL in the data.

Another common query is sizing up the damage:

SELECT
    count(*) FILTER (WHERE o.id IS NULL) AS orphan_payments,
    count(*) FILTER (WHERE p.id IS NULL) AS unpaid_orders
FROM orders o
FULL OUTER JOIN payments p ON p.order_id = o.id;

Emulating it in MySQL: LEFT UNION RIGHT

MySQL doesn't support FULL OUTER JOIN — the query fails with a syntax error. The standard trick is to combine a LEFT JOIN and a RIGHT JOIN with UNION.

-- MySQL: emulating FULL OUTER JOIN
SELECT u.id AS user_id, u.email, o.id AS order_id, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id

UNION

SELECT u.id AS user_id, u.email, o.id AS order_id, o.amount
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;

How it works: the LEFT JOIN gives every match plus users without orders; the RIGHT JOIN gives the same matches plus orders without users. UNION welds the two sets together and removes duplicates (the matched rows that landed in both halves).

• Use UNION, not UNION ALL: UNION is what dedupes the matched rows. UNION ALL would emit every match twice.
• Both halves must have the same columns in the same order.

There's a subtlety to UNION: it dedupes across every column at once. If your data contains fully identical "legitimate" duplicate rows, UNION will collapse those too. When that matters, write the second query as a LEFT JOIN that excludes already-found matches and use UNION ALL:

-- Alternative without implicit deduplication
SELECT u.id, u.email, o.id AS order_id, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id

UNION ALL

SELECT u.id, u.email, o.id AS order_id, o.amount
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id
WHERE u.id IS NULL;   -- only rows the LEFT half didn't already cover

Pitfalls

• NULLs in the join predicate. JOIN ... ON a.col = b.col never matches on NULL — NULL = NULL evaluates to UNKNOWN. Those rows land in the unmatched part of a FULL JOIN. If you want NULL to match NULL, use ON a.col IS NOT DISTINCT FROM b.col (PostgreSQL).
• Don't confuse "no match" with "the value is NULL." After a FULL JOIN a column can be NULL for two reasons: either there was no matching row, or the source value was genuinely NULL. The only reliable way to tell them apart is a non-nullable column — typically the primary key.
• WHERE vs ON. A predicate in ON controls what counts as a match; a predicate in WHERE runs after the join and quietly turns a FULL JOIN into an INNER one. Write WHERE o.status = 'paid' and every row where o.* IS NULL (users without orders) vanishes.
• ClickHouse. FULL JOIN is supported here, but unmatched columns default to type defaults (0, empty string) instead of NULL. To get honest NULLs, wrap columns in Nullable or set join_use_nulls = 1.

FULL OUTER JOIN is the "show me everything and highlight what's missing" tool. PostgreSQL gives it to you out of the box, MySQL needs LEFT plus RIGHT glued with UNION, and ClickHouse demands care around NULL. Keep the difference between "no match" and "value is NULL" in mind, and data reconciliation stops being painful.