SQL Reference ยท Joins and Subqueries
SQL FULL OUTER JOIN
Reference for SQL FULL OUTER JOIN: keep unmatched rows from both tables, compare two lists, support in SQLite 3.39+ and the MySQL workaround with UNION.
FULL OUTER JOIN (usually written FULL JOIN) returns every row from both tables. Matching rows are paired; left rows without a partner get NULL in the right columns, and right rows without a partner get NULL in the left columns.
It is the join for comparing two collections: what is in A only, in B only, and in both. Typical uses are reconciling two data sources and finding differences between a table and its backup.
SELECT a.id AS a_id, b.id AS b_id FROM a FULL JOIN b ON b.id = a.id;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
SELECT columns FROM table_a FULL [OUTER] JOIN table_b ON condition;
| Part | Description |
|---|---|
FULL [OUTER] JOIN |
Keeps unmatched rows from both sides. OUTER is optional. |
ON condition |
Decides which rows pair up. |
Returns: matched pairs, plus left rows with NULL right columns, plus right rows with NULL left columns. Use COALESCE to show the key from whichever side has it.
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite | FULL JOIN and RIGHT JOIN from version 3.39 (2022). Older versions need the UNION workaround. |
| PostgreSQL, SQL Server, Oracle | Supported. PostgreSQL requires merge-joinable or hash-joinable conditions. |
| MySQL | Not supported. Emulate it with a LEFT JOIN unioned with the unmatched rows of a RIGHT JOIN. |
-- MySQL workaround SELECT a.id, b.id FROM a LEFT JOIN b ON b.id = a.id UNION SELECT a.id, b.id FROM a RIGHT JOIN b ON b.id = a.id;
Usage notes
- Rows are never lost: every row from either table appears at least once.
- Show the key with
COALESCE, since either side's key may beNULL. - Differences only: add
WHERE a.key IS NULL OR b.key IS NULL, as the second example does. - Filters in
WHEREon either table can silently turn the result into a left, right or inner join; put match conditions inON. - Rarely needed in everyday application queries; reconciliation and reporting are the typical uses.
UNIONworkaround:UNIONremoves duplicates; when rows can legitimately repeat, useUNION ALLwith an anti-join on the second half.- Duplicates multiply as in any join when keys repeat.
Related
LEFT JOIN,RIGHT JOIN,INNER JOIN,UNION,COALESCE()- Lessons: Right and full joins, Joins