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

Part 7 of 18 · ~3 min

NULL in Joins, Aggregates, GROUP BY, and Sorting

NULL doesn't behave identically everywhere it shows up — the same value acts differently depending on which part of a query it lands in, and a few of those differences genuinely surprise people:

• Sitting in a join condition: nothing changes from what you already learned in chapter two — e1.dept = e2.dept can't come back TRUE when either side is NULL, so a NULL on either side of a join key guarantees that row finds no partner through it. There's no special-casing here at all, just ordinary = behaving exactly the way it always does.
• Sitting in a GROUP BY column, though, the rules genuinely bend: every row whose grouping value is NULL gets swept into one shared bucket together, as though NULL suddenly equals NULL for this one purpose. It's a strange exception to carry around, given that NULL = NULL is UNKNOWN in literally every other context in this reference — but it's not a quirk of any one engine. Every mainstream database groups NULLs this way, without an option to turn it off.
• Sitting inside an aggregate — SUM, AVG, COUNT(column), MIN, MAX, all of them — a NULL is simply skipped, as if that row had never been fed into the calculation in the first place. Not counted as zero, not counted as anything: absent from the math entirely.
• Sitting in an ORDER BY, where it lands is the one people guess wrong most often, because the true rule has two moving parts and it's tempting to remember only one of them: which engine you're on, and which direction you sorted. SQLite, MySQL, and SQL Server all treat NULL as the smallest thing that can exist in a column — ascending puts it first, descending flips it to last. Postgres and Oracle go the other way, treating NULL as larger than any real value, so it's last ascending and first descending. Reducing any of that to a flat "this engine puts NULLs first" only holds for the ascending case, and quietly stops being true the moment somebody adds DESC. When it actually matters where a NULL row ends up, don't trust memory — write ORDER BY salary IS NULL, salary (that expression is 0 for a real number and 1 for NULL, so it sorts exactly where you told it to) or reach for the engine's native NULLS FIRST / NULLS LAST, both supported on SQLite, Postgres, and Oracle.

Apply the aggregate and grouping rules to your own IT department and the averages chapter has already half-written itself:

SELECT dept, AVG(salary), COUNT(*) AS headcount, COUNT(salary) AS with_salary_on_file
FROM Employees GROUP BY dept;
-- IT | 70000.0 | 2 | 1

IT's average comes back $70,000, not $35,000. If AVG treated Dave's missing salary as $0, it would drag the department average down by half — instead it's simply left out of both the sum and the count, so the average is Carol's number, alone, because Carol is the only IT employee with a number to average. COUNT(*) still correctly reports two people in IT; COUNT(salary) reports that only one of them has a salary on file. Both are true and neither one is the other, which is exactly the kind of thing a headcount-and-pay slide has to get right or nobody catches it.