NULL Handling in Joins and Aggregates in SQL
Joins and aggregate functions each interact with NULL in ways that are consistent with NULL semantics but non-obvious in combination. The failures they produce are silent: the query runs, returns results, and the results are wrong.
A LEFT JOIN is designed to keep every row from the left table, even when there is no match on the right. The problem is that the NULLs it introduces can silently break the next thing you do with the result.
You're analyzing customer activity. You run a LEFT JOIN from customers to orders so that customers with no orders still appear in the output. Then you add a WHERE filter to see only completed orders. The LEFT JOIN gives you every customer, matched or not. The WHERE clause then removes all the unmatched customers, because orders.status = 'complete' is not true for NULL — and NULL doesn't fail the comparison exactly, it produces NULL, which WHERE treats as false. You get back only customers who have orders. No error. No warning. Just the wrong result.
SELECT customers.id, orders.status
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.status = 'complete'This returns only customers with a completed order. Customers with no orders — the ones the LEFT JOIN was specifically keeping — are silently dropped. The fix is to move the filter into the JOIN predicate, so it applies only to the matching logic and doesn't eliminate unmatched rows:
SELECT customers.id, orders.status
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id
AND orders.status = 'complete'Now unmatched customers still appear, with NULL in the status column.
The second place introduced NULLs cause trouble is in aggregate functions. SUM, AVG, MIN, MAX, and COUNT(column) all skip NULL values. This is usually what you want, but it becomes a subtle bug when you're counting or summing across a LEFT JOIN.
For customers with no orders, o.id is NULL for every row in their group. COUNT(o.id) returns zero because it counts non-NULL values and finds none. This is correct for counting orders. But SUM(o.total_amount) for the same customers returns NULL, not zero — SUM of nothing is NULL. Whether that NULL should become zero with COALESCE is a judgment call. A customer with zero revenue is not the same as a customer with no data.
One more behavior worth knowing: GROUP BY treats NULL as a distinct value. If a column used in GROUP BY contains NULLs, all NULL rows are grouped into a single output row labeled NULL. This is easy to overlook when scanning results.
The one thing that trips people up: a WHERE filter on a joined column silently defeats a LEFT JOIN.
Any filter on a column from the right-side table will eliminate unmatched rows, because that column is NULL for those rows, and NULL fails every comparison. If you need to filter on a right-side column while preserving unmatched rows, the condition belongs in the ON clause, not the WHERE clause.
You run a LEFT JOIN from customers to orders. You then add WHERE orders.id IS NOT NULL. What happens to customers with no orders?
9 NULL Handling in Joins and Aggregates practice problems
Write a query to return the total number of sessions and the number of completed sessions as a single row.
Write a query to return the name and email of every customer with no order history.
Write a query to return each customer alongside the status of each order they have placed, or a placeholder for customers with no order history.
Write a query to return each customer alongside any cancelled order they have placed.
Write a query to return the total number of customer-order pairings and the number of actual order records as a single row.
Write a query to return the total order value from these customers as a single figure.
Write a query to return each user alongside any completed session they have.
Write a query to return each product alongside the ID of any qualifying order line item.
Write a query to return each employee alongside the title of any matching job history entry.
These problems are part of the NULL Handling in Joins and Aggregates lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.