NULL Handling in Joins and Aggregates in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this LEFT JOIN and RIGHT JOIN, Aggregate Functions (COUNT, SUM, AVG, MIN, MAX), COALESCE and NULLIF
Builds toward Join Fanout and Aggregate Correctness, NULL Propagation in Complex Queries
How do NULLs behave in SQL joins and aggregates?
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.
How do NULLs from a LEFT JOIN affect SUM and COUNT?
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.
SELECT c.name AS customer_name, COUNT(o.id) AS order_count FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.name
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.
How does GROUP BY treat NULL values?
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.
Why does a WHERE clause turn a LEFT JOIN into an INNER JOIN?
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?
Practice NULL Handling in Joins
Streamhub's engineering team is auditing session data quality.
Write a query to return the total number of sessions and the number of completed sessions as a single row.
Assumptions:
- The
sessionstable has one row per session with anended_atvalue. - Sessions still in progress have a missing
ended_at; completed sessions have a recordedended_at. - The total number of sessions covers every session record. The number of completed sessions covers only sessions with a recorded
ended_at.
Output:
- A single row with columns
total_sessionsandcompleted_sessions.
Schema · analytics5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 NULL Handling in Joins 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.
Start learning to practice all 9 NULL Handling in Joins problems, with instant grading and mastery tracking.
Common questions about NULL Handling in Joins
What is the difference between filtering in ON and filtering in WHERE?
ON decides which rows count as a match; WHERE decides which rows survive after the join has run. On a LEFT JOIN that difference is the whole ballgame, because a WHERE test on a right-side column removes the unmatched rows the join was written to keep.
Can you tell whether a NULL came from the data or from an unmatched join?
Not from the value alone, which is why it is worth deciding at the join. Test a column that is never NULL in the source table, usually its key: a NULL there means no match was found, while a NULL anywhere else may simply be missing data.
Should you replace every NULL a LEFT JOIN produces with zero?
Only when zero is the honest answer. A customer with no orders genuinely has a revenue of nothing, so zero reads well. But no data and a measured zero are different facts, and flattening one into the other hides the difference from whoever reads the report.
