{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"null","title":"NULL, and why your counts are wrong","url":"https://addaly.com/learn/data-and-sql/null","position":24,"of":66,"minutes":8,"answer":"WHERE keeps only rows where the test is true, and every comparison with NULL is unknown.","keyIdea":"WHERE keeps only rows where the test is true, and every comparison with NULL is unknown."},"body":{"format":"markdown","text":"## NULL means unknown\n\nIt does not mean zero. It does not mean empty string. It means the database has no value here, and it will not pretend otherwise.\n\nThat produces three-valued logic. A comparison can be true, false, or **unknown**. And there is one rule that explains almost every NULL surprise you will ever hit:\n\n**WHERE keeps a row only when the condition is true. Not false. Not unknown.**\n\n## The comparisons that never match\n\n```sql\nSELECT * FROM students WHERE phone = NULL;   -- always zero rows\n```\n\nNot an error. Zero rows, even for the students whose phone is NULL. \"Unknown equals unknown\" is unknown, not true. The correct form is a different operator entirely:\n\n```sql\nSELECT * FROM students WHERE phone IS NULL;\nSELECT * FROM students WHERE phone IS NOT NULL;\n```\n\nThe same rule bites on inequality, which is far less obvious. Suppose `status` is `'active'`, `'churned'`, or NULL for people who never set it.\n\n```sql\nSELECT COUNT(*) FROM users WHERE status <> 'churned';\n```\n\nThe NULL people are not returned. `NULL <> 'churned'` is unknown, and unknown is not true, so those rows are dropped. You asked for everyone who is not churned and you got only the actives. Nothing warns you. The number is smaller than the truth, and it looks like a perfectly ordinary number.\n\n```sql\nWHERE (status <> 'churned' OR status IS NULL)   -- what you meant\n```\n\n## The denominator you did not notice changing\n\nAggregates skip NULLs. This is usually what you want, and occasionally it destroys your analysis.\n\nA survey has 1,000 responses. Two hundred people skipped the monthly income question.\n\n- `COUNT(*)` → 1000\n- `COUNT(income)` → 800\n- `AVG(income)` → the total of 800 values, divided by **800**\n\nThat last one is the trap. You will report \"average monthly income of respondents: 47,200 pesos\" and it is the average among the 800 who answered. If people with low incomes were likelier to skip the question (which is exactly what happens in real surveys) your number is biased upward, and nothing in the output hints at it.\n\nAlso: `SUM` over a column that is entirely NULL returns NULL, not 0. A revenue tile on a dashboard showing a blank rather than a zero has usually met this.\n\n## The NOT IN disaster\n\nThis one deserves its own warning.\n\n> Figure: Why the unknown rows vanish from a count. A two by two grid, rows status 'active' and status NULL, columns <> 'churned' and OR status IS NULL: status 'active' and <> 'churned': Kept; status 'active' and OR status IS NULL: Kept; status NULL and <> 'churned': Dropped; status NULL and OR status IS NULL: Kept. WHERE keeps a row only when the condition is true. NULL <> 'churned' is not false, it is unknown, and unknown is not true. You asked for everyone who is not churned and you were given only the actives, with nothing to warn you.\n\n```sql\nSELECT * FROM students\nWHERE id NOT IN (SELECT student_id FROM payments);\n```\n\nIf even one row of `payments.student_id` is NULL, this returns **zero rows, always**. Because `id NOT IN (1, 2, NULL)` expands to `id <> 1 AND id <> 2 AND id <> NULL`, and that last term is unknown, so the whole AND can never be true.\n\nZero rows looks like a real answer. \"Great, every student has paid.\" Use `NOT EXISTS` or the anti-join from the joins lesson instead: both handle NULLs the way you expect:\n\n```sql\nSELECT s.* FROM students s\nWHERE NOT EXISTS (SELECT 1 FROM payments p WHERE p.student_id = s.id);\n```\n\n## Do not paper over it\n\n`COALESCE(income, 0)` replaces NULL with zero. It is the right tool sometimes and a lie other times.\n\nIf your column means \"amount refunded\" and no refund happened, zero is correct. If your column means \"reported income\" and someone skipped the question, replacing it with zero manufactures 200 destitute respondents who do not exist. The distinction between \"it was zero\" and \"we do not know\" is real information, and once you have flattened it you cannot get it back.\n\n## The habit\n\nFor every nullable column in a query, decide out loud what should happen to the unknowns: counted, excluded, or replaced. Then write that decision into the query so the next person can see it. Most NULL bugs are not misunderstandings of SQL. They are decisions nobody made."},"check":{"question":"A `status` column holds 'active', 'churned', or NULL for people who never set it. A team wants everyone who is not churned and runs `WHERE status <> 'churned'`. Which people come back?","options":[{"text":"The 'active' people and the NULL people, since NULL is not equal to 'churned'","correct":false,"tempting":"Treats NULL as an ordinary value that participates normally in comparisons."},{"text":"Only the 'active' people","correct":true},{"text":"No rows at all, because a NULL anywhere in the column makes the comparison unknown for the whole query","correct":false,"tempting":"Generalises the NOT IN behaviour, where one NULL really does poison everything, to ordinary comparisons."},{"text":"The 'active' and NULL people if status is a text column, but only the 'active' ones if it is an enum","correct":false,"tempting":"Assumes NULL handling depends on the column's data type rather than being a rule of the logic itself."}]},"prev":{"slug":"group-by","title":"GROUP BY: making piles and asking about each one","url":"https://addaly.com/learn/data-and-sql/group-by"},"next":{"slug":"averages-that-lie","title":"Averages: the four ways AVG misleads","url":"https://addaly.com/learn/data-and-sql/averages-that-lie"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'NULL, and why your counts are wrong', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/null (Accessed: 31 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"NULL, and why your counts are wrong","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":3,"slug":"summaries-and-the-piles-behind-them","title":"Summaries, and the piles behind them"},"answer":null,"keyIdea":"WHERE keeps only rows where the test is true, and every comparison with NULL is unknown.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"cols":["<> 'churned'","OR status IS NULL"],"rows":["status ‘active’","status NULL"],"type":"matrix","cells":[["Kept","Kept"],["Dropped","Kept"]],"title":"Why the unknown rows vanish from a count","caption":"WHERE keeps a row only when the condition is true. NULL <> 'churned' is not false, it is unknown, and unknown is not true. You asked for everyone who is not churned and you were given only the actives, with nothing to warn you.","cellLabels":[["the test is true","the test is true"],["the test is unknown","IS NULL is true"]]},"alt":"Figure: Why the unknown rows vanish from a count. A two by two grid, rows status ‘active’ and status NULL, columns <> 'churned' and OR status IS NULL: status ‘active’ and <> 'churned': Kept; status ‘active’ and OR status IS NULL: Kept; status NULL and <> 'churned': Dropped; status NULL and OR status IS NULL: Kept. WHERE keeps a row only when the condition is true. NULL <> 'churned' is not false, it is unknown, and unknown is not true. You asked for everyone who is not churned and you were given only the actives, with nothing to warn you."}},"blocks":[{"t":"h2","id":"null-means-unknown","runs":[{"text":"NULL means unknown"}]},{"t":"p","runs":[{"text":"It does not mean zero. It does not mean empty string. It means the database has no value here, and it will not pretend otherwise."}]},{"t":"p","runs":[{"text":"That produces three-valued logic. A comparison can be true, false, or "},{"text":"unknown","b":true},{"text":". And there is one rule that explains almost every NULL surprise you will ever hit:"}]},{"t":"p","runs":[{"text":"WHERE keeps a row only when the condition is true. Not false. Not unknown.","b":true}]},{"t":"h2","id":"the-comparisons-that-never-match","runs":[{"text":"The comparisons that never match"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT * FROM students WHERE phone = NULL;   -- always zero rows"},{"t":"p","runs":[{"text":"Not an error. Zero rows, even for the students whose phone is NULL. “Unknown equals unknown” is unknown, not true. The correct form is a different operator entirely:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT * FROM students WHERE phone IS NULL;\nSELECT * FROM students WHERE phone IS NOT NULL;"},{"t":"p","runs":[{"text":"The same rule bites on inequality, which is far less obvious. Suppose "},{"text":"status","code":true},{"text":" is "},{"text":"'active'","code":true},{"text":", "},{"text":"'churned'","code":true},{"text":", or NULL for people who never set it."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT COUNT(*) FROM users WHERE status <> 'churned';"},{"t":"p","runs":[{"text":"The NULL people are not returned. "},{"text":"NULL <> 'churned'","code":true},{"text":" is unknown, and unknown is not true, so those rows are dropped. You asked for everyone who is not churned and you got only the actives. Nothing warns you. The number is smaller than the truth, and it looks like a perfectly ordinary number."}]},{"t":"code","lang":"sql","label":"SQL","text":"WHERE (status <> 'churned' OR status IS NULL)   -- what you meant"},{"t":"h2","id":"the-denominator-you-did-not-notice-changing","runs":[{"text":"The denominator you did not notice changing"}]},{"t":"p","runs":[{"text":"Aggregates skip NULLs. This is usually what you want, and occasionally it destroys your analysis."}]},{"t":"p","runs":[{"text":"A survey has 1,000 responses. Two hundred people skipped the monthly income question."}]},{"t":"ul","items":[[{"text":"COUNT(*)","code":true},{"text":" → 1000"}],[{"text":"COUNT(income)","code":true},{"text":" → 800"}],[{"text":"AVG(income)","code":true},{"text":" → the total of 800 values, divided by "},{"text":"800","b":true}]]},{"t":"p","runs":[{"text":"That last one is the trap. You will report “average monthly income of respondents: 47,200 pesos” and it is the average among the 800 who answered. If people with low incomes were likelier to skip the question (which is exactly what happens in real surveys) your number is biased upward, and nothing in the output hints at it."}]},{"t":"p","runs":[{"text":"Also: "},{"text":"SUM","code":true},{"text":" over a column that is entirely NULL returns NULL, not 0. A revenue tile on a dashboard showing a blank rather than a zero has usually met this."}]},{"t":"h2","id":"the-not-in-disaster","runs":[{"text":"The NOT IN disaster"}]},{"t":"p","runs":[{"text":"This one deserves its own warning."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT * FROM students\nWHERE id NOT IN (SELECT student_id FROM payments);"},{"t":"p","runs":[{"text":"If even one row of "},{"text":"payments.student_id","code":true},{"text":" is NULL, this returns "},{"text":"zero rows, always","b":true},{"text":". Because "},{"text":"id NOT IN (1, 2, NULL)","code":true},{"text":" expands to "},{"text":"id <> 1 AND id <> 2 AND id <> NULL","code":true},{"text":", and that last term is unknown, so the whole AND can never be true."}]},{"t":"p","runs":[{"text":"Zero rows looks like a real answer. “Great, every student has paid.” Use "},{"text":"NOT EXISTS","code":true},{"text":" or the anti-join from the joins lesson instead: both handle NULLs the way you expect:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT s.* FROM students s\nWHERE NOT EXISTS (SELECT 1 FROM payments p WHERE p.student_id = s.id);"},{"t":"h2","id":"do-not-paper-over-it","runs":[{"text":"Do not paper over it"}]},{"t":"p","runs":[{"text":"COALESCE(income, 0)","code":true},{"text":" replaces NULL with zero. It is the right tool sometimes and a lie other times."}]},{"t":"p","runs":[{"text":"If your column means “amount refunded” and no refund happened, zero is correct. If your column means “reported income” and someone skipped the question, replacing it with zero manufactures 200 destitute respondents who do not exist. The distinction between “it was zero” and “we do not know” is real information, and once you have flattened it you cannot get it back."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"For every nullable column in a query, decide out loud what should happen to the unknowns: counted, excluded, or replaced. Then write that decision into the query so the next person can see it. Most NULL bugs are not misunderstandings of SQL. They are decisions nobody made."}]}],"check":{"question":[{"text":"A "},{"text":"status","code":true},{"text":" column holds ‘active’, ‘churned’, or NULL for people who never set it. A team wants everyone who is not churned and runs "},{"text":"WHERE status <> 'churned'","code":true},{"text":". Which people come back?"}],"options":[{"text":[{"text":"The ‘active’ people and the NULL people, since NULL is not equal to ‘churned’"}],"correct":false,"tempting":[{"text":"Treats NULL as an ordinary value that participates normally in comparisons."}]},{"text":[{"text":"Only the ‘active’ people"}],"correct":true,"tempting":null},{"text":[{"text":"No rows at all, because a NULL anywhere in the column makes the comparison unknown for the whole query"}],"correct":false,"tempting":[{"text":"Generalises the NOT IN behaviour, where one NULL really does poison everything, to ordinary comparisons."}]},{"text":[{"text":"The ‘active’ and NULL people if status is a text column, but only the ‘active’ ones if it is an enum"}],"correct":false,"tempting":[{"text":"Assumes NULL handling depends on the column’s data type rather than being a rule of the logic itself."}]}],"order":[0,2,1,3],"why":null},"prev":{"slug":"group-by","title":"GROUP BY: making piles and asking about each one","minutes":7},"next":{"slug":"averages-that-lie","title":"Averages: the four ways AVG misleads","minutes":9}}}