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

Handling NULL Values in LEFT JOINs

How unmatched rows produce NULLs in right-table columns and techniques for working with these missing values.

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.