FULL OUTER JOIN in SQL
FULL OUTER JOIN returns all rows from both tables, matching them where the join condition is true and filling unmatched positions on either side with NULL. It combines the preservation behavior of LEFT JOIN and RIGHT JOIN into a single operation.
Before this LEFT JOIN and RIGHT JOIN
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.
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.
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.
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.
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.
9 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.
These problems are part of the FULL OUTER JOIN lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.