Use a `LEFT JOIN` + unmatched rows from the other side via `UNION ALL`.
Section: Common join patterns
Emulate FULL OUTER JOIN where unsupported
sql
sql
SELECT a.id, b.id
FROM a
LEFT JOIN b ON b.id = a.id
UNION ALL
SELECT a.id, b.id
FROM b
LEFT JOIN a ON a.id = b.id
WHERE a.id IS NULL;Explanation
Useful in engines without native `FULL OUTER JOIN`, such as some MySQL versions and SQLite.
Learn the surrounding workflow
Compare similar commands or jump into common fixes when this command is part of a bigger troubleshooting path.
Related commands
Same sheet · prioritizing Common join patterns
Full outer join to compare two datasets
See items present in one side, the other side, or both.
Inner join active users with subscriptions
Only users who have a matching subscription row appear.
Left join to find users without subscriptions
Find unmatched left-side rows by checking for `NULL` on the right.