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

NULL in Joins, Aggregates, GROUP BY, and Sorting

NULL behaves differently — sometimes surprisingly so — depending on which clause it shows up in:

• In a join condition: ON e1.dept = e2.dept never matches a row where either side is NULL, for the same three-valued-logic reason as above. Two NULLs are never considered equal in a join, even to each other.
• In GROUP BY: all NULL values in the grouping column are grouped together into a single group, as a special case — this is one of the few places SQL treats NULLs as "the same as each other," which feels inconsistent with NULL = NULL being UNKNOWN elsewhere, but it's how every major engine implements grouping.
• In aggregate functions (SUM, AVG, COUNT(column), MIN, MAX): NULL values are excluded from the calculation entirely, not treated as zero.
• In ORDER BY: NULLs sort either first or last depending on the engine (Postgres puts them last by default for ASC, SQLite and MySQL put them first) — if the order of NULL rows matters to your query, don't rely on the default; use ORDER BY salary IS NULL, salary (or the engine's NULLS FIRST/NULLS LAST syntax, where supported) to make it explicit.

SELECT dept, AVG(salary) FROM Employees GROUP BY dept;
-- IT: Carol=70000, Dave=NULL → AVG = 70000 (not 35000 — Dave's NULL is excluded, not averaged in as 0)

SELECT COUNT(*), COUNT(salary) FROM Employees;
-- COUNT(*) = 5 (every row, regardless of contents)
-- COUNT(salary) = 4 (skips Dave's NULL — COUNT(column) never counts NULLs in that column)

COUNT(*) vs. COUNT(column) is one of the most common sources of an off-by-however-many-NULLs bug in real reporting queries — always ask which one a requirement actually means before writing it.