SQL FULL OUTER JOIN
intermediatejoins
2 min read
What is FULL OUTER JOIN?
FULL OUTER JOIN returns all rows from both tables. Where there is a match, columns are filled from both tables. Where there is no match, the missing side's columns are NULL.
Syntax
SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.col = table2.col;When to Use
- Finding mismatches between two tables
- Reconciliation reports where you need to see all data from both sides
- Data migration validation
Key Points
- All Rows — Rows from both tables appear, whether they have matches or not.
- NULLs on Both Sides — Unmatched left rows have NULLs in right columns and vice versa.
- Combines LEFT and RIGHT — A FULL OUTER JOIN is conceptually a LEFT JOIN plus a RIGHT JOIN with duplicates removed.
- Performance — Can be expensive on large tables.
- OUTER is Optional —
FULL JOINis equivalent toFULL OUTER JOIN.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
SQL FULL OUTER JOIN Challenge
Write a query that solve this task: show all students and all courses, even if unmatched.
Expected result
All students and all courses appear. Unmatched entries show NULL.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is FULL OUTER JOIN?
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
When should I use FULL OUTER JOIN?
Use it when you need to see all records from both tables, including those without matches. Common for data comparison and reconciliation.
Is FULL OUTER JOIN slow?
It can be slower than INNER JOIN or LEFT JOIN because it must retain unmatched rows from both sides.