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

Common Mistakes Worth Remembering

• Using WHERE salary = NULL instead of IS NULL — it silently returns nothing, for every row, with no error.
• Assuming AVG()/SUM() treat NULL as zero — they don't; they exclude it from the calculation entirely, which changes the denominator for AVG().
• Using NOT IN against a subquery that could contain a NULL — the entire result silently becomes empty. Prefer NOT EXISTS.
• Reaching for INNER JOIN when unmatched rows actually need to be preserved (LEFT JOIN), or the reverse — accidentally dropping rows a report was supposed to include.
• Counting with COUNT(*) when the intent was "how many have a value here" (COUNT(column)), or vice versa — an easy off-by-however-many-NULLs bug.
• Forgetting that NOT (condition involving NULL) is not the logical complement of condition — both can exclude the same row.
• Wrapping a column in a function in WHERE (WHERE YEAR(order_date) = 2024) — this usually prevents the database from using an index on that column. See Understanding SQL Indexes and Query Performance for exactly why.
• Writing an implicit comma-join in FROM and forgetting the WHERE condition that was supposed to relate the tables, silently producing a Cartesian product.

Run every one of these exact queries against a live database in the code lab — seeing Dave's NULL propagate through a JOIN, an AVG(), and a NOT IN firsthand is worth more than reading about it.