Finding Unmatched Rows with LEFT JOIN
You'll learn how to use LEFT JOIN with a NULL check to find rows in one table that have no match in another.
The Core Idea
Remember that LEFT JOIN returns all rows from the left table, even when there's no matching row in the right table. When there's no match, the columns from the right table are filled with NULL values. We can exploit this behavior to answer questions like "Which customers have never placed an order?" or "Which products have no reviews?"
The trick is simple: perform a LEFT JOIN, then add a WHERE clause checking if a column from the right table IS NULL.
How It Works
Think of it like checking attendance at a party. Your guest list (left table) shows everyone invited. The sign-in sheet (right table) shows who actually arrived. To find who didn't show up, you look for people on your guest list whose name doesn't appear on the sign-in sheet—those are your "unmatched" guests.
SELECT customers.customer_id, customers.name
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.customer_id IS NULL;
This query finds all customers with no orders. After the LEFT JOIN, any customer without orders will have NULL in all orders columns. The WHERE orders.customer_id IS NULL keeps only those unmatched rows.
Why This Matters
This pattern is incredibly useful for data quality checks, identifying gaps in relationships, and finding opportunities (customers to re-engage, products needing attention, etc.).
Key Takeaway: Combine LEFT JOIN with WHERE right_table_column IS NULL to find rows in the left table that have no corresponding match in the right table—a powerful pattern for spotting missing relationships.