FULL OUTER JOIN
Include all rows from both tables
FULL OUTER JOIN returns all rows from both tables. Where there's no match, NULL values fill in the gaps.
Syntax
SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.col = table2.col;
Example
-- Get all users AND all orders (matched where possible)
SELECT
u.name,
o.id AS order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
-- Imagine we have an orphan order (user_id = 99, no matching user):
-- Alice | 101
-- Alice | 102
-- Bob | 103
-- Carol | NULL <-- User with no orders
-- NULL | 104 <-- Order with no user (orphan)SQLite Workaround
SQLite doesn't support FULL OUTER JOIN. Use UNION instead:
-- Simulate FULL OUTER JOIN in SQLite SELECT u.name, o.id FROM users u LEFT JOIN orders o ON u.id = o.user_id UNION SELECT u.name, o.id FROM orders o LEFT JOIN users u ON u.id = o.user_id WHERE u.id IS NULL;
When to use: Data reconciliation, finding orphaned records, comparing two datasets.