NULL Propagation in Complex Queries in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this NULL Handling in Joins and Aggregates, Joining Multiple Tables, Aggregate Window Functions (SUM, AVG, COUNT OVER)
What is NULL Propagation in SQL?
NULL propagates through a multi-step query in ways that compound across operations. A NULL introduced in one layer can travel through joins, arithmetic, and window functions — arriving in the final output at a location that gives no indication of where it originated.
The most common source is a LEFT JOIN. When a left row has no match on the right side, every right-side column for that row becomes NULL. In a query with multiple LEFT JOINs, each join can introduce NULLs independently. Columns from the first joined table may be populated while columns from the second are NULL.
Why does one NULL column make a whole calculation NULL?
When those NULL columns feed into arithmetic expressions, the propagation continues silently:
-- NULL in bonus silently nullifies the entire expression
SELECT
employee_id,
base_salary + bonus AS total_compensation -- NULL where bonus IS NULL
FROM employees
LEFT JOIN bonuses b ON b.employee_id = employees.employee_idSELECT c.name, c.city, 'City: ' || c.city AS city_label FROM customers c ORDER BY c.city NULLS FIRST LIMIT 10
Any arithmetic that includes a NULL operand evaluates to NULL. The employee with no bonus entry doesn't get base_salary — they get NULL. Nothing in the query signals the problem. Fix it at the column level, before the arithmetic:
base_salary + COALESCE(bonus, 0) AS total_compensationHow do window functions treat NULL?
Window functions and NULL
Window aggregates skip NULLs, exactly as their grouped forms do. A running SUM over a column holding a NULL does not turn NULL: the missing row contributes nothing and the total carries on. That is the opposite of the arithmetic above, and it is why this case is the harder one to catch.
The trap is the denominator. A running AVG divides by the number of values it actually saw, not by the number of rows, so a partition of 10, NULL, 30 averages to 20 rather than the 13.33 you would get by treating the gap as a zero. COUNT(revenue) and COUNT(*) part company for the same reason. Both answers are defensible and the query looks identical either way, so the question is which one you meant.
LAG and LEAD are the window functions that do propagate. They hand back the neighbouring value as it stands, NULL included, so arithmetic against it goes NULL and takes the row after it down as well. Resolve the column before the window layer sees it when a gap should read as zero.
Do CTEs stop a NULL from propagating?
The one thing that trips people up
CTEs don't isolate NULL propagation. A NULL introduced in the first CTE travels unchanged into every subsequent CTE that references it. In a five-CTE query, a NULL from the first layer can appear in the final SELECT with none of the intermediate CTEs having touched it.
The correct practice is to fix NULL at the layer where it originates. Handle the LEFT JOIN output in the same CTE where the join happens — don't pass the NULL downstream and try to catch it later.
Diagnosing unexpected NULLs: run each CTE independently (comment out later ones, SELECT from the current one) and find the first layer where the NULL appears. The source operation at that layer determines the correct fix.
Practice NULL Propagation in SQL
Scenario: Brightlane's finance team is producing a customer lifetime value report and needs every customer included — those who have never placed an order should appear with 0 rather than as missing.
Task: Write a query to return each customer's id, name, and total order value, with total_order_value reported as 0 for customers who have placed no orders.
Assumptions:
- A customer's
total_order_valueis the combinedtotal_amountacross all of theirorders. - The result covers every customer.
- A customer with no
orderson record appears withtotal_order_valuereported as0.
Output:
- One row per customer.
- Columns in this order:
customer_id,customer_name,total_order_value.
Schema · ecommerce5 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 solution10 NULL Propagation practice problems
Write a query to return each customer's id, name, and total order value, with total_order_value reported as 0 for customers who have placed no orders.
Write a query to return each user's id and the total number of events they have generated across all of their sessions, with total_event_count reported as 0 for users with no events on record.
Write a query to return each employee's id, their department_id, their salary_amount (their current salary, reported as a missing value if no active salary is on record), and dept_salary_total — the combined active salary across employees in their department.
Write a query to return each department's id, name, and active-employee count, with the count reported as 0 for departments with no active employees.
Write a query to return each employee's id, name, salary_amount (their current salary, reported as a missing value if no active salary is on record), and dept_avg_salary (the average current salary across employees in their department).
Write a query to return each department's id, name, and avg_current_salary, with the average reported as 0 for departments with no current salary records.
Write a query to return each customer's id, name, and customer_status — set to 'Active' if they have placed at least one order and 'Inactive' otherwise.
Write a query to return each customer's id, name, and pending_order_id — the id of any pending order they have on record, reported as a missing value for customers with no pending order.
Write a query to return each department's id, name, and active_salaried_count — the count of employees in that department who are both active and have an active salary on record, reported as 0 for departments with none.
Write a query to return each user's id and their total_conversion_spend — the combined amount across all of their conversions, reported as a missing value for users with no conversions on record.
Start learning to practice all 10 NULL Propagation problems, with instant grading and mastery tracking.
Deeper guides on NULL Propagation
- which rows an aggregate leaves out
Most skip rows whose input is null. COUNT(*), array_agg and jsonb_agg do not, so two counts disagree.
Common questions about NULL Propagation
Does one NULL make a whole calculation NULL?
In arithmetic and in string concatenation with the double pipe, yes. Addition, subtraction, multiplication and concatenation all return NULL when either side is NULL, so a single missing bonus turns a total compensation column into nothing at all.
Where should you fix a NULL that a join introduced?
In the step that produced it. Handling it where the join happens means every later step reads a value you chose, rather than one that has travelled through three transformations before anyone noticed it.
How do you find where an unexpected NULL came from?
Run the chain one step at a time and stop at the first step whose output already contains it. The operation at that step is the source, and fixing it there is cheaper than adding guards to everything downstream.
