NULL in SQL: Why = NULL Finds Nothing and What to Write Instead

By Michael Nocito, data analyst · Published August 7, 2026 By the end of this page you can predict what any query does when it meets a missing value, which is the skill that separates "my query returned nothing and I don't know why" from a two-second fix. You will know why = NULL matches zero rows, why one NULL can empty an entire NOT IN , and how NULLs quietly move averages, counts, groups, and sort orders. It is about twenty minutes. Here is what to actually do with it. Next time a filter returns fewer rows than you expected, ask one question first: does the column I am comparing have NULLs in it? Run WHERE the_column IS NULL and look. That one check explains most mystery row counts before any real debugging starts. The short version: NULL means unknown, not zero and not empty text. A comparison with unknown answers "unknown", and WHERE only keeps rows that answer "yes". So every NULL comparison silently drops rows. That yes-only gate is the one idea under everything on this page, so it gets the picture. The original carries a diagram here. In words: A left-to-right picture of six rows approaching a gate labelled WHERE. Each row carries the answer its comparison produced: two rows answer yes, two rows answer no, and two rows carry a question mark meaning unknown, because their compared value was NULL. Beyond the gate there are three paths. The yes path continues through to the result, holding two rows. The no path is stopped at the gate. The unknown path is drawn separately in a warning color, and it is also stopped at the gate, landing in the same discard area as the no rows. The picture shows that a comparison in SQL can answer yes, no, or unknown, and that the WHERE gate lets only yes rows pass, so unknown rows disappear from the result exactly as if they had answered no. The worked example is real. Every number on this page comes from a 10-row support-tickets table with NULLs seeded on purpose, and every query was run against it in SQLite before its output was pasted here. The table is small enough to check by eye. If SELECT and WHERE are new, start with SQL foundations and come back. Here is the table. Ten tickets. Three are missing their close time, two are missing their region, and two have no assignee. | ticket_id | region | minutes_to_close | assignee | |---|---|---|---| | 1 | East | 45 | Priya | | 2 | East | NULL | Priya | | 3 | West | 30 | Marcus | | 4 | West | 90 | NULL | | 5 | NULL | 60 | Marcus | | 6 | East | 15 | Dana | | 7 | West | NULL | NULL | | 8 | NULL | 120 | Dana | | 9 | East | 75 | Priya | | 10 | West | NULL | Marcus | 1. What NULL actually means, and why = NULL finds nothing Before the explanation: two tickets in that table have no assignee. What do you expect this query to return? SELECT * FROM tickets WHERE assignee = NULL; Zero rows. Not the two unassigned tickets. Zero, out of ten, every time, on every database. I ran it and got an empty result, and the reason is the definition of NULL itself. NULL does not mean zero, and it does not mean empty text. NULL means unknown: this row has an assignee slot, and nobody has said what goes in it. So assignee = NULL asks "does this ticket's assignee equal an unknown value?", and the only honest answer is "I don't know". Even for ticket 4, where the assignee is itself NULL, the question is "does one unknown equal another unknown?", and the answer is still unknown. I ran SELECT NULL = NULL and the database returns NULL, not true. SQL therefore gives you a separate verb for the question you actually meant. IS NULL asks "is this value missing?", which is a question about the slot, not the value, so it has a real yes-or-no answer. SELECT * FROM tickets WHERE assignee IS NULL; | ticket_id | region | minutes_to_close | assignee | |---|---|---|---| | 4 | West | 90 | NULL | | 7 | West | NULL | NULL | There are the two unassigned tickets. IS NOT NULL is the mirror, and it returns the other 8 rows. The rule to keep: = and <> compare values, IS NULL and IS NOT NULL check for missing ones, and they are not interchangeable. 2. Three-valued logic in everyday words Before the explanation: the table has 10 tickets and 3 of them are assigned to Priya. How many rows does WHERE assignee <> 'Priya' return? Most people say seven. The database says five. I ran it: 5 rows. Here is the machinery, in everyday words. In most of life a question has two answers, yes or no. In SQL a comparison has three: yes, no, and unknown, where unknown is what you get whenever a NULL is involved. This is called three-valued logic, and the name matters less than the consequence: WHERE keeps only the rows that answer "yes". Rows that answer "no" are dropped, and rows that answer "unknown" are dropped too, silently, with no error and no warning. So for assignee <> 'Priya' : five tickets answer yes (Marcus and Dana's), three answer no (Priya's), and tickets 4 and 7 answer unknown, because comparing NULL to 'Priya' has no honest answer. The unknowns vanish. Ten in, five out, and the two unassigned tickets are in neither the Priya pile nor the not-Priya pile. Say out loud where tickets 4 and 7 went before reading on, because that sentence is the whole trick. They were not excluded for being Priya's. They were excluded for being unanswerable. When you truly mean "everyone except Priya, including unassigned", you have to say so. SELECT COUNT() AS n FROM tickets WHERE assignee <> 'Priya' OR assignee IS NULL; -- n = 7 3. The NOT IN trap: one NULL, zero rows Before the explanation: you have a list of staff who close tickets, and you want tickets handled by anyone not on the list. The list has three entries: Priya, Marcus, and one NULL from a bad import. How many rows do you think come back? SELECT * FROM tickets WHERE assignee NOT IN (SELECT name FROM closers); Zero rows. Not "the Dana tickets", not "everything but Priya and Marcus". Zero, and I ran it to confirm. This is the single nastiest NULL surprise in SQL, because the query looks completely reasonable and returns an empty set with no error. The reason follows from step two. NOT IN unrolls into a chain of comparisons: assignee <> 'Priya' AND assignee <> 'Marcus' AND assignee <> NULL . That last comparison answers unknown for every row in the table. And a chain of ANDs can only answer yes if every link answers yes, so the best any row can do is unknown. No row answers yes, WHERE keeps only yes, and the result is empty. One NULL in the list poisons all ten rows. Two fixes, both of which I ran. Screen the NULL out of the subquery, which returns the 2 Dana tickets: SELECT ticket_id, assignee FROM tickets WHERE assignee NOT IN ( SELECT name FROM closers WHERE name IS NOT NULL ); -- tickets 6 and 8, both Dana Or use NOT EXISTS , which checks "no matching row exists" one row at a time and is immune to the trap. It returned 4 rows on the same data: Dana's two tickets plus the two unassigned ones, because an unassigned ticket genuinely has no match in the list. The two fixes disagree about tickets 4 and 7, and neither is wrong. They answer different questions, so the fork is: should unassigned tickets count as "not on the list"? If yes, NOT EXISTS says what you mean. If no, the screened NOT IN does. What decides is the sentence you would say to a stakeholder, and it is worth writing that sentence into the query as a comment. 4. COALESCE for defaults, and when not to use it Before the explanation: your ticket report goes to a manager who keeps asking what the blank assignee cells mean. What would you like those cells to say instead? COALESCE is the tool. It takes a list of values and returns the first one that is not NULL, which makes it a fill-in-the-blank function: COALESCE(assignee, 'Unassigned') means "the assignee, or the word Unassigned when there is none". SELECT ticket_id, COALESCE(assignee, 'Unassigned') AS assignee_display FROM tickets ORDER BY ticket_id; I ran it: tickets 4 and 7 now read "Unassigned" and the other eight show their names unchanged. That is the honest use of COALESCE : labeling missingness so a reader can see it. The dishonest use is papering over it. COALESCE(minutes_to_close, 0) makes the NULLs disappear into zeros, and a zero-minute close time is a claim, not a label: it says the ticket closed instantly, when the truth is nobody recorded it. Three of the ten tickets here have no close time. That is a 30% hole in the column, and a hole that size is a finding your reader deserves to hear about, not a formatting problem to smooth away. When you fill a value for display, say so in the report, and when the missingness is large, report it as its own line. Documenting data limitations covers how to write that up without burying the analysis. 5. How NULLs move your counts and averages Before the explanation: minutes_to_close has seven real values and three NULLs. When you take AVG(minutes_to_close) , what number does the database divide by, ten or seven? Seven. Aggregate functions skip NULLs entirely: SUM , AVG , MIN , MAX , and COUNT(column) all act only on the rows where the value exists. I ran the numbers on the worked table. SELECT COUNT() AS all_rows, COUNT(minutes_to_close) AS with_minutes, AVG(minutes_to_close) AS avg_minutes FROM tickets; | all_rows | with_minutes | avg_minutes | |---|---|---| | 10 | 7 | 62.1 | The average is 435 divided by 7, which is 62.1 minutes. Force the NULLs to zero with COALESCE and the same column averages 43.5, because now it is 435 divided by 10. That is a 30% swing in a headline number from one decision about missing data, and neither number is automatically right. 62.1 is the average of the tickets whose close time was recorded. 43.5 pretends unrecorded means instant. The honest report states the first number and the hole: "62.1 minutes average, across the 7 of 10 tickets with a recorded close time". COUNT() against COUNT(column) is the same skip in count form. COUNT() counts rows, 10 here. COUNT(minutes_to_close) counts non-NULL values, 7 here, and COUNT(assignee) gives 8. The gap be