Handling NULL Values in LEFT JOINs
You'll learn how LEFT JOINs create NULL values for unmatched rows and practical techniques for working with this missing data.
Why NULL Values Appear in LEFT JOINs
Remember that a LEFT JOIN returns all rows from the left table, even when there's no matching row in the right table. When no match exists, SQL fills the right-table columns with NULL values. This is fundamentally different from an INNER JOIN, which excludes unmatched rows entirely.
Think of it like a guest list where you're noting everyone's plus-one. Some guests come alone (no match), so their plus-one column stays blank (NULL).
Techniques for Working with These NULLs
1. Identifying Unmatched Rows
You can use IS NULL in your WHERE clause to find rows that had no match:
SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_id IS NULL;
This finds customers who have never placed an order.
2. Providing Default Values
Use COALESCE or similar functions to replace NULLs with meaningful defaults (though COALESCE hasn't been formally introduced yet, the concept is important):
SELECT customers.name, orders.total_amount
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
Here, total_amount will be NULL for customers without orders.
3. Counting with Care
Remember that COUNT(column_name) ignores NULLs, while COUNT(*) counts all rows—crucial when analyzing LEFT JOIN results.
Key Takeaway: LEFT JOINs produce NULLs in right-table columns when no match exists; you can identify, filter, or handle these NULLs to answer questions like "who has no activity?" or provide sensible defaults for missing data.