MySQL NULL Handling
debt(d5/e1/b3/t7)
Closest to 'specialist tool catches it' (d5). The detection_hints list semgrep as the tool, with an automated pattern match for `WHERE column = NULL` or `WHERE column != NULL`. Semgrep is a specialist SAST tool rather than a default linter or compiler, so d5 fits exactly. The bug is silent at runtime (returns zero rows) making it invisible without tooling.
Closest to 'one-line patch or single-call swap' (e1). The quick_fix is a direct substitution: replace `= NULL` with `IS NULL` or wrap in `COALESCE()`. This is a mechanical, localised text change with no structural refactoring required.
Closest to 'localised tax' (b3). The issue applies across web and cli contexts, but each occurrence is a self-contained query fix. It doesn't shape architectural decisions or impose a persistent productivity tax across many work streams — each fix is local to its query or DAL method.
Closest to 'serious trap (contradicts how a similar concept works elsewhere)' (t7). The misconception field states the canonical wrong belief: developers expect `NULL = NULL` to be true, as equality works everywhere else in programming. This directly contradicts universal equality semantics, making it a serious cognitive trap. It is documented and well-known but routinely tripped over because the intuition from other languages is strongly wrong here.
Also Known As
TL;DR
Explanation
NULL = NULL evaluates to NULL (not true) — use IS NULL and IS NOT NULL for comparisons. NULL in aggregate functions is ignored by COUNT(column) but counted by COUNT(*). NULL values sort last in ORDER BY ASC (MySQL-specific — other databases differ). COALESCE(val, default) returns the first non-NULL value. IFNULL(val, default) is MySQL-specific. Columns with frequent NULL values have reduced index efficiency.
Watch Out
Common Misconception
Why It Matters
Common Mistakes
- Writing WHERE column = NULL instead of WHERE column IS NULL — matches zero rows.
- Expecting COUNT(column) to count NULL rows — it doesn't; use COUNT(*) instead.
- Sorting by a nullable column and not accounting for NULLs appearing last (ASC) or first (DESC).
Avoid When
- Do not use nullable columns for data that is always required — use NOT NULL with a sensible DEFAULT instead.
- Do not rely on IFNULL() for portability — use COALESCE() which is SQL standard.
When To Use
- Use IS NULL / IS NOT NULL for all NULL comparisons — never use = NULL.
- Use COALESCE(column, default) to substitute a fallback for NULL values in SELECT.
Code Examples
-- Wrong: WHERE column = NULL always returns 0 rows
SELECT * FROM users WHERE deleted_at = NULL; -- always empty
-- Wrong: COUNT(nullable_column) skips NULLs
SELECT COUNT(deleted_at) FROM users; -- counts only non-NULL deleted_at-- Correct NULL comparison
SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM users WHERE deleted_at IS NOT NULL;
-- COALESCE for default values
SELECT id, COALESCE(display_name, email) AS name FROM users;
-- COUNT distinction
SELECT COUNT(*) as total, COUNT(email) as with_email FROM users;