What is a FULL OUTER JOIN?
You'll learn how FULL OUTER JOIN combines data from both tables, keeping every row even when there's no match.
The Most Inclusive Join
A FULL OUTER JOIN (also called FULL JOIN) returns all rows from both tables, regardless of whether there's a matching row in the other table. Think of it as combining a LEFT JOIN and RIGHT JOIN into one operation.
When rows match based on your join condition, they're combined normally. When a row from the left table has no match in the right table, you still get that row with NULLs for the right table's columns. Similarly, when a row from the right table has no match on the left, you get that row with NULLs for the left table's columns.
How It Works
Imagine two attendance lists: one from Monday, one from Friday. A FULL OUTER JOIN shows everyone who attended either day. If someone came Monday but not Friday, you see their name with blanks for Friday. If someone came Friday but not Monday, you see their name with blanks for Monday. If they came both days, you see complete information.
Basic Syntax
SELECT *
FROM table_a
FULL OUTER JOIN table_b
ON table_a.id = table_b.id;
This returns every row from table_a and every row from table_b. Matched rows appear combined; unmatched rows appear with NULLs filling in the missing side.
When to Use It
FULL OUTER JOIN is perfect for finding gaps in data—like customers who placed orders versus those who didn't, or products that were ordered versus inventory items never purchased. It gives you the complete picture from both perspectives.
Key Takeaway: FULL OUTER JOIN returns all rows from both tables, combining matches and filling unmatched rows with NULLs—the most comprehensive join type.