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

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;

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;

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 between the two counts is your missing-data count, which makes this pair the fastest completeness check in SQL. The whole COUNT family, including DISTINCT, is in COUNT in SQL.

Now picture running COUNT(*) next to COUNT(the_column) on the most important numeric column in your own data. Do you know, right now, what the gap would be? Most people find out later than they wanted to.

6. The full before and after

Same table, same question: how fast does each region close tickets?

Before

SELECT region, AVG(minutes_to_close) AS avg_minutes
FROM tickets
GROUP BY region;

Three quiet problems, no errors. The average silently skips the three unrecorded close times, so nobody can see how much data is behind each number. The two tickets with no region form their own blank-looking group with no explanation. And the reader has no way to tell any of this from the output.

After

-- ============================================================
-- STEP 4: How fast does each region close tickets?
-- WHY: minutes_to_close is missing on 3 of 10 tickets, and
--      region is missing on 2. Averages skip the NULLs, so
--      each average is shown WITH how many tickets back it.
-- NOTE: 'No region' is real missing data, reported as its
--      own line rather than dropped or blank.
-- ============================================================
--SELECT the region (labelled when missing), tickets in the
--   group, how many have a recorded close time, and their avg
SELECT COALESCE(region, 'No region')  AS region_display,
       COUNT(*)                        AS tickets,
       COUNT(minutes_to_close)         AS with_close_time,
       ROUND(AVG(minutes_to_close), 1) AS avg_minutes
FROM tickets
GROUP BY region;

The blank group now has a name, every average sits beside the count of values that produced it, and the missingness is visible instead of ambient. The comment format is the one from how to comment SQL so it teaches. To get this shape into your fingers, type the query yourself in the SQL Drill rather than pasting it.

7. Edge cases: groups, joins, and sort order

Before the explanation: NULL never equals NULL, yet the two no-region tickets just landed in one group together. How can both of those be true?

Because different parts of SQL treat NULL differently, on purpose, and the seams are where surprises live. Four to know.

GROUP BY puts all NULLs in one group. Grouping asks "which rows belong together?", and the standard answers that all missing values belong together, even though no two of them are equal. On the worked table, grouping by region returns West 4, East 4, and a NULL group of 2, which I ran to confirm. That blank-looking row is not a glitch. It is your missing-region count, and it is worth reading every time.

LEFT JOIN manufactures NULLs. A LEFT JOIN keeps every row from the left table, and when a row finds no match on the right, the right-hand columns arrive as NULL. Joining tickets to a two-row regions lookup that only lists East, I ran it: the four East tickets get their manager, and the other six rows show NULL, covering both real West tickets with no lookup entry and tickets whose region was already missing. So a NULL after a join means "no match or missing key", and telling those apart takes an IS NULL check on the join key. SQL joins walks the whole family.

ORDER BY has to put NULLs somewhere, and databases disagree. Sorting the worked table by minutes_to_close ascending in SQLite puts the three NULL rows first, before 15; descending puts them last. SQLite and SQL Server treat NULLs as smallest, PostgreSQL and Oracle treat them as largest, and PostgreSQL and Oracle accept an explicit NULLS FIRST or NULLS LAST to say what you mean. The portable habit: when a sorted report matters, state the placement, either with that clause or by sorting on the_column IS NULL first.

Empty text is not NULL, except where it is. An empty string is a known value: someone said "nothing", rather than nobody saying anything. Filters and counts treat the two differently on most databases, so a column can look blank while IS NULL finds nothing. Oracle is the famous exception, storing empty strings as NULL. When blanks behave strangely, check both: WHERE col IS NULL OR col = ''.