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

Part 18 of 18 · ~3 min

Common Mistakes Worth Remembering

Every one of these showed up somewhere in the Monday report above, in one form or another:

• Reaching for = NULL when you mean IS NULL. There's no crash to warn you — the query runs, comes back empty every single time regardless of what's actually in the table, and nothing in the output hints that you asked the wrong question.
• Treating a missing value as a zero inside AVG() or SUM(). Both functions skip the row entirely instead, which for AVG() specifically means the denominator shrinks along with the numerator — the average of what's known, not the average with gaps padded to zero.
• Trusting NOT IN next to a subquery column that isn't guaranteed NULL-free. One stray unknown value in that list is enough to empty the whole result for every outer row, the way IT's roster silently zeroed out the Sales pay comparison the moment Dave's row got included. NOT EXISTS doesn't have this failure mode; make it the default rather than something to remember case by case.
• Picking INNER JOIN for a report that's supposed to show every row regardless of a match — or, less often but just as real, picking LEFT JOIN for a query that actually needed unmatched rows filtered out. Either direction quietly changes which rows make it onto the slide.
• Mixing up COUNT(*) and COUNT(column). They agree everywhere the column has no gaps and diverge exactly where it does — which is precisely the case a report can least afford to get wrong, since that's the case with something actually worth reporting on.
• Assuming a negated condition catches whatever the original condition missed. Once a NULL is involved, that assumption breaks specifically because negating UNKNOWN doesn't produce TRUE — both the original filter and its negation can, and did, exclude the exact same row.
• Building a CTE's aggregation from the table that's allowed to have gaps, instead of joining the complete table in first. It's the difference between a 0 you can stand behind and a NULL that only means the row was never in the running to begin with.
• Separating tables with a comma in FROM and letting the condition that was meant to relate them slip your mind. Nothing crashes — you just get every possible pairing back, a wrong answer wearing a plausible row count.

None of this sticks from reading alone nearly as well as it does from breaking it yourself. Pull up the code lab, paste in the schema from the top of this page, and run each of these bugs on purpose — swap IS NULL for = NULL and watch a filter go silent, put NULL in a NOT IN list and watch a whole result set vanish, wrap a filter in NOT and watch it not do what you expected. Three-valued logic stops being an abstract table of TRUE/FALSE/UNKNOWN the moment you've personally watched it eat a query's output.

And when you're ready to see these same two tables under genuinely different pressure, Understanding SQL Indexes and Query Performance picks up right where this leaves off — same Employees, same Departments, scaled to 2.4 million rows, where getting a query correct stops being the only problem and getting it fast joins the list.