SQL Fundamentals: Joins, NULL, Aggregate Functions, and Subqueries

Part 8 of 18 · ~3 min

COALESCE, NULLIF, and Handling NULL in Expressions

The report has a cosmetic problem now: a blank NULL cell next to Dave's name reads as a broken slide to anyone in the meeting who doesn't already know the backstory, and someone's going to interrupt to ask about it. COALESCE() takes any number of arguments and walks them left to right, handing back the first one that isn't NULL — feed it two arguments and you've got a one-line stand-in value generator:

SELECT name, COALESCE(salary, 0) AS salary_for_display FROM Employees;
-- Dave's cell now reads 0 instead of blank — purely cosmetic, nothing about the underlying data changed

Notice the "purely cosmetic" qualifier, because that's exactly the boundary that's easy to cross without noticing. COALESCE doesn't know or care whether you're using it to make a slide look tidy or feeding its output straight into math — and the moment it's the latter, wrapping the column changes what the surrounding calculation actually measures. Watch what happens if that same 0-filling gets applied before an AVG() instead of after one, at display time:

SELECT dept, AVG(salary) AS honest_avg, AVG(COALESCE(salary, 0)) AS zero_filled_avg
FROM Employees WHERE dept = 'IT' GROUP BY dept;
-- honest_avg: 70000.0  (Dave's row doesn't count toward the average at all)
-- zero_filled_avg: 35000.0  (Dave's row counts, contributing exactly $0)

Those are two different, both-defensible questions with two different, both-correct answers, and COALESCE is the thing that decides which one you asked. "What does the average known salary look like" and "what does the average salary look like if we treat unentered payroll as zero for now" are not the same question, and swapping one for the other inside an AVG() changes your slide's headline number by exactly the amount Dave's real salary turns out to be. Know which question the meeting actually wants answered before you reach for COALESCE inside an aggregate — outside one, for display, it's almost always the right move.

NULLIF() is built the other way around from COALESCE(): it takes exactly two arguments, compares them, and hands back NULL the moment they match — otherwise it just passes the first one through untouched. Where that earns its keep is defusing a division right before it happens, and Finance's own zero headcount gives us a live denominator to defuse rather than a made-up one. Say the report wants to show average discretionary bonus per head for every department:

SELECT d.dept,
       COUNT(e.id) AS headcount,
       COALESCE(SUM(e.salary), 0) * 1.0 / NULLIF(COUNT(e.id), 0) AS avg_per_head
FROM Departments d
LEFT JOIN Employees e ON d.dept = e.dept
GROUP BY d.dept;
-- Finance | 0 | NULL   ← guarded: NULLIF(0, 0) turns the divisor into NULL, and NULL propagates cleanly
-- HR      | 1 | 55000.0

Without the guard, Finance's headcount reaches the division as a literal 0, and NULLIF(COUNT(e.id), 0) is what stands between that 0 and the arithmetic — worth flagging honestly here, rather than overselling it, that SQLite specifically will just hand back NULL for a bare x / 0 on its own, with no error and no guard needed. Not every engine is that forgiving: Postgres and SQL Server both raise a real division-by-zero error on an unguarded 0 denominator, which would take the whole report query down mid-run instead of quietly showing a blank cell for Finance. NULLIF is what makes the query portable and predictable regardless of which of those two behaviors your engine happens to have — worth writing defensively even on an engine, like this one, that wouldn't currently punish you for skipping it.