SQL – FULL JOIN
Combine all rows from both tables using FULL OUTER JOIN, showing NULLs where there is no match on either side.
Table of Contents
FULL OUTER JOIN is the most inclusive join — it returns every row from both tables whether or not a match exists. Where the left table has no match, right columns are NULL. Where the right has no match, left columns are NULL. It is the union of a LEFT JOIN and a RIGHT JOIN.
SQLite does not natively support FULL OUTER JOIN, but you can emulate it perfectly using UNION ALL as shown below. This is a great example of problem-solving around database limitations.
Emulating FULL OUTER JOIN in SQLite
-- FULL JOIN = LEFT JOIN UNION ALL anti-join RIGHT
SELECT * FROM a LEFT JOIN b ON a.id = b.id
UNION ALL
SELECT * FROM a RIGHT JOIN b ON a.id = b.id
WHERE a.id IS NULL;
Try It Yourself — Interactive SQL Editor
Edit the query below and click Run Query ▶ to see live results powered by SQLite running directly in your browser.
Key Points
- FULL JOIN returns every row from both tables
- NULLs fill columns where no match exists on either side
- SQLite emulates it with UNION of LEFT and RIGHT joins
- Useful for finding all discrepancies between two datasets
Pro Tip from CodesCompiler: The best way to learn SQL is to break things intentionally — modify the query above, change the WHERE conditions, try different columns. Every error teaches you something the docs cannot.
In the next lesson, we continue exploring SQL’s powerful feature set to build your database mastery.