PrismitaLEARN · PRACTICE · GROW

What is a FULL OUTER JOIN?

SQL Server Joins Basic 1–2 Yrs2–3 Yrs SQL Query 1 min read

Quick Answer

A FULL OUTER JOIN returns all matched rows, plus unmatched rows from both sides (with NULLs on the missing side). It's essentially LEFT JOIN ∪ RIGHT JOIN. The common use is reconciliation — comparing two datasets to find rows that exist only in one.

A full outer join keeps everything — the matched rows plus the unmatched rows from both tables, with nulls filling in the gaps. It's a left join and a right join combined. Where I actually use it is reconciliation: I've got the same data in two systems and I want to find rows that are in one but not the other, or where the values disagree. I coalesce the key because it can be null on either side, and filter on a.Key IS NULL OR b.Key IS NULL OR the values differ.

  • SELECT COALESCE(a.Id, b.Id) AS Id, a.Amount AS SysA, b.Amount AS SysB FROM SystemA a FULL OUTER JOIN SystemB b ON a.Id = b.Id WHERE a.Id IS NULL OR b.Id IS NULL OR a.Amount <> b.Amount;
  • COALESCE the join key because either side can be NULL.
  • Filter WHERE a.Key IS NULL → only in B; WHERE b.Key IS NULL → only in A.
  • Less common than inner/left joins; if an interviewer asks, they usually want the reconciliation scenario.
All SQL Server questions More on Joins