Course contentsShow
← Data Science
Lesson 937 of 2,14524. SQL JoinsFree lesson

Identifying Matched vs Unmatched Rows

Using NULL checks to distinguish rows that matched from those that exist in only one table.

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.