FULL OUTER JOIN
FULL OUTER JOIN keeps all rows from both tables, matching where it can and using NULL elsewhere.
In this page:
Syntax
SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.key = table2.key;FULL OUTER JOIN
It combines LEFT and RIGHT JOIN results: matched rows appear once, unmatched left rows have NULL right columns and unmatched right rows have NULL left columns.
It is useful for reconciling two data sets. PostgreSQL supports it directly; older SQLite does not.
Note:
FULL OUTER JOIN is handy for finding differences between two tables.
Example: FULL OUTER JOIN
shop=# SELECT a.id AS in_a, b.id AS in_b
shop-# FROM table_a a FULL OUTER JOIN table_b b ON a.id = b.id
shop-# ORDER BY COALESCE(a.id, b.id);
in_a | in_b
------+------
1 |
2 | 2
3 | 3
| 4⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Expecting it to be the same as CROSS JOIN
- Forgetting NULLs on both sides
- Using it when LEFT JOIN is enough
Chapter Summary
- Keeps unmatched rows from both sides
- Missing side is NULL
- Good for reconciliation
- PostgreSQL supports it natively
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: