NULL Propagation in Complex Queries in SQL
NULL propagates through a multi-step query in ways that compound across operations. A NULL introduced in one layer of a complex query can travel through subsequent joins, window functions, and aggregations, arriving in the final output at a location that gives no indication of where it originated. The challenge in complex queries is tracing how NULL moves across multiple operations that each handle it differently, from the layer where it was introduced to the output column where it appears.
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.
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_idAny 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_compensationWindow functions and NULL
Window functions operate on the rows as they exist in the partition, including rows where columns are NULL. A running total computed over a partition with NULL revenue rows carries the NULL forward. Adding NULL to a running sum produces NULL, and that NULL becomes the input to the next row's accumulation. Every row after the first NULL in the partition shows a NULL running total, not just the NULL row itself.
The fix: resolve NULLs in the source column before the window function sees them, typically in a CTE that handles NULL substitution before the window layer runs.
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.
10 NULL Propagation in Complex Queries 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.
These problems are part of the NULL Propagation in Complex Queries 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.