FULL OUTER JOIN in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this LEFT JOIN and RIGHT JOIN
What is FULL OUTER JOIN in SQL?
FULL OUTER JOIN keeps all rows from both tables, filling in NULL on whichever side has no match.
You're auditing product-category coverage. Some products have no category assigned. Some categories have no products. LEFT JOIN shows you products with their categories — and products missing a category — but drops categories with no products entirely. You'd need two separate queries to see both gaps. FULL OUTER JOIN shows everything in one result: matched pairs plus unmatched rows from both sides.
Think of it as LEFT JOIN and RIGHT JOIN combined. Every row from the left table appears. Every row from the right table appears. Where there's a match, both sides contribute their columns. Where there's no match, the missing side's columns come back as NULL.
How do you write a FULL OUTER JOIN in SQL?
Here's the full product-category coverage check:
SELECT p.name AS product_name, cat.name AS category_name
FROM products p
FULL OUTER JOIN categories cat ON p.category_id = cat.idProducts with a category: both columns populated. Products with no category: category_name is NULL. Categories with no products: product_name is NULL. One query, all three situations visible.
How do you find unmatched rows on both sides of a join?
You can filter to see just the unmatched rows from either side. Products with no category:
SELECT p.name AS product_name
FROM products p
FULL OUTER JOIN categories cat ON p.category_id = cat.id
WHERE cat.id IS NULLCategories with no products: flip the WHERE to WHERE p.id IS NULL.
SELECT c.name AS customer_name, o.id AS order_id, o.total_amount FROM customers c FULL OUTER JOIN orders o ON c.id = o.customer_id
Customers with orders appear normally. Customers with no orders appear with NULL in the order columns. Orders with no matching customer appear with NULL in the customer columns.
When should you use FULL OUTER JOIN instead of LEFT JOIN?
The one thing that trips people up: reaching for FULL OUTER JOIN when LEFT JOIN is what you actually need.
If you only care about unmatched rows on one side — say, customers with no orders — LEFT JOIN with a WHERE o.id IS NULL filter is cleaner and more efficient. FULL OUTER JOIN makes sense when you genuinely need to see gaps on both sides simultaneously. Auditing referential integrity, comparing two sets for overlap, finding orphaned records in either table — those are the real use cases.
Practice FULL OUTER JOIN in SQL
Brightlane's catalogue team needs a complete reconciliation of the product and category tables — both directions:
- Every product appears, including products whose category does not resolve (category name will be
NULL). - Every category appears, including categories with no products assigned (product name will be
NULL).
Write a query to return the product name and category name for every row in the combined view.
Assumptions:
- The
productstable contains every product in the catalogue. - The
categoriestable contains every defined category. - Some products have a
category_idthat does not resolve to any category; some categories have no products assigned.
Output:
- One row per matched product-category pair, plus one row per unmatched product (with
category_nameasNULL), plus one row per unmatched category (withproduct_nameasNULL).
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 FULL OUTER JOIN practice problems
Write a query to return the product name and category name for every row in the combined view.
Write a query to return the customer name, order ID, and order total for every row in the combined view.
Write a query to return the user ID and conversion amount for every row in the combined view.
Write a query to return the product name for every product whose category_id does not resolve to any row in categories.
Write a query to return the category name for every category that has no products currently assigned to it.
Write a query to return the names of every customer who appears in the customer table but has no matching order.
Write a query to return the session ID for every session with no associated event.
Write a query to return the product name and category name for every row in the reconciliation.
Write a query to return the session ID and event ID for every row in the audit view.
Start learning to practice all 9 FULL OUTER JOIN problems, with instant grading and mastery tracking.
Common questions about FULL OUTER JOIN
Does FULL OUTER JOIN need an ON clause?
Yes. Without one the statement does not parse, because a full outer join still has to know which rows count as a pair before it can tell you which rows had none. Every combination with no condition is a CROSS JOIN, which is a different thing entirely.
Is FULL OUTER JOIN the same as a LEFT JOIN combined with a RIGHT JOIN?
The rows come out the same, and the single join is easier to read and does the work once. The two-query version is worth knowing because it explains what a full outer join is actually doing: everything the left keeps, plus everything the right keeps.
Why is FULL OUTER JOIN uncommon in analytics?
Because most questions care about gaps on one side only, and a LEFT JOIN filtered for missing matches answers those more directly. It earns its place when you genuinely need to see unmatched rows from both tables at once, such as reconciling two systems against each other.
