Identifying Matched vs Unmatched Rows
You'll learn how to use NULL checks to tell which rows found a match across both tables and which exist in only one table after a FULL OUTER JOIN.
The Challenge with FULL OUTER JOINs
When you perform a FULL OUTER JOIN, you get all rows from both tables—whether they matched or not. But how do you tell them apart in your results?
The answer lies in checking for NULLs in columns from each side of the join.
How NULL Reveals the Story
Remember: when a row from one table has no match in the other, the FULL OUTER JOIN fills all columns from the missing side with NULL.
- Matched rows: Both sides have real values (no NULLs in key columns)
- Left-only rows: Right-side columns are NULL
- Right-only rows: Left-side columns are NULL
Practical Pattern
SELECT
customers.customer_id,
orders.order_id,
CASE
WHEN orders.order_id IS NULL THEN 'Customer with no orders'
WHEN customers.customer_id IS NULL THEN 'Order with no customer'
ELSE 'Matched'
END AS match_status
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;
This pattern lets you classify every row in your result set.
Think of It Like a Party Guest List
Imagine merging two lists: invited guests and attendees. After a FULL OUTER JOIN, you can identify who was invited and came (matched), who was invited but didn't show (NULL in attendee columns), and who showed up uninvited (NULL in invitation columns).
Key Takeaway: Use IS NULL checks on columns from each table to distinguish matched rows from those existing in only one table—your decoder for understanding FULL OUTER JOIN results.