FULL OUTER JOIN
FULL OUTER JOIN returns all rows from BOTH tables. Unmatched rows from each side get NULLs for the other side's columns. Use it when you want to see the complete picture - every record from both tables, whether they match or not.
FULL OUTER JOIN Key Points
- All rows from left table + all rows from right table
- Matching rows are combined; unmatched rows get NULLs
- Supported in PostgreSQL and SQL Server - NOT in MySQL
- MySQL workaround: UNION of LEFT JOIN and RIGHT JOIN
- Useful for finding unmatched rows on both sides (full outer anti-join)
FULL OUTER JOIN Examples
-- PostgreSQL: all users and all orders (even unmatched)
SELECT
u.name AS user_name,
o.id AS order_id,
o.status
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
-- All users on left (with NULLs for no orders)
-- All orders on right (with NULLs if user deleted)
-- Find ALL unmatched rows from both tables
SELECT
u.name AS user_name,
o.id AS order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id
WHERE u.id IS NULL OR o.id IS NULL;
-- Users with no orders AND orders with no users
-- MySQL workaround (UNION of LEFT + RIGHT):
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 users u RIGHT JOIN orders o ON u.id = o.user_id;Quick Quiz
Tip
Tip
Practice FULL OUTER JOIN in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Common Mistake
Warning
A common mistake with FULL OUTER JOIN is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of FULL OUTER JOIN from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- FULL OUTER JOIN returns all rows from BOTH tables.
- All rows from left table + all rows from right table
- Matching rows are combined; unmatched rows get NULLs
- Supported in PostgreSQL and SQL Server - NOT in MySQL