Lesson Beginner

FULL OUTER JOIN

FULL OUTER JOIN keeps every row from both tables.
Where there's a match on the join key, columns from both sides come through. Where there isn't, the missing side is filled with NULL. It's the join for reconciliation: "what's in A but not B, and what's in B but not A, in one query".

Syntax #

sql

SELECT
  COALESCE(u.id, o.user_id) AS user_id,
  u.email,
  o.id AS order_id
FROM users AS u
FULL OUTER JOIN orders AS o
  ON o.user_id = u.id;

Some dialects accept FULL JOIN as shorthand for FULL OUTER JOIN; they're the same operator.

The Reconciliation Pattern #

Use FULL OUTER JOIN when you want to spot differences between two sources of truth:

Users who have never placed an order and orders whose user_id doesn't match any user.
Orders in the warehouse that are missing from the billing system and the reverse.
Expected dataset vs actual dataset, to find both drops and additions.

Filter for IS NULL on either side to isolate the orphans.

sql

-- Orphans on either side: unmatched users OR unmatched orders
SELECT
  u.id AS user_id,
  u.email,
  o.id AS order_id,
  o.user_id AS order_user_id
FROM users AS u
FULL OUTER JOIN orders AS o
  ON o.user_id = u.id
WHERE u.id IS NULL   -- order with no matching user
   OR o.id IS NULL;  -- user with no orders

Warning

Don't reference u.id in SELECT alone when u can be unmatched; it'll be NULL.
Use COALESCE(u.id, o.user_id) AS user_id when you want a single non-null key column regardless of which side matched. This is the #1 formatting bug in FULL OUTER queries.

Try It #

Reconcile users and orders in one pass. The seeded data has users with no orders and an order whose user_id doesn't match any user, so you should see both.

Loading SQL editor...

Practice #

Return only the orphans: users who have placed no orders, and orders whose user_id doesn't exist in users. Columns: user_id (use COALESCE so it's never NULL), email (NULL for orphan orders), order_id (NULL for users with no orders). No ordering requirement.

Loading SQL editor...

Mistakes to Watch For #

Referencing only one side's key column in SELECT when that side can be unmatched; use COALESCE(a.key, b.key).
Putting a filter in WHERE that isn't NULL-safe. It can silently drop unmatched rows and turn your FULL OUTER into an inner join.
Using FULL OUTER when you really only need LEFT — "keep everything on the left" is cheaper and simpler.
Underestimating output size. With unique keys the result is at most rows(a) + rows(b), but duplicate keys fan out (every match pair produces a row) and can exceed it. Reconciliation outputs can get large either way.

Knowledge check #

3 questions

0 / 3 answered

After A FULL OUTER JOIN B ON ..., how do you find rows that exist in A but not B?
A FULL OUTER JOIN of two 1M-row tables with no matches at all returns:
Which engine does not support FULL OUTER JOIN?

Next Step #

Continue to CROSS JOIN: every combination of rows, no join key at all.