4 SQL NULL Traps That Silently Break Your Queries (NOT IN Returns 0 Rows!)

This query should return three users. It returns zero. No error, no warning:

SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned); -- 0 rows 

The cause is a single NULL. Here are four NULL traps, each with a fix. I ran every query below in PostgreSQL 17 and SQLite 3.51, and both gave the same results.

Video version:

The setup

CREATE TABLE users (id int, name text, country text); INSERT INTO users VALUES (1, 'Ann', 'US'), (2, 'Bob', 'UK'), (3, 'Cat', NULL), -- 👈 this row (4, 'Dan', 'US'); CREATE TABLE banned (user_id int); INSERT INTO banned VALUES (2), (NULL); 

The rule behind every trap

NULL doesn't mean "empty". It means unknown. Is unknown equal to unknown? Nobody knows:

SELECT NULL = NULL; -- NULL (not true!) SELECT 'Hi ' || NULL; -- NULL SELECT NULL IS NULL; -- true 

SQL uses three-valued logic: true, false and unknown. A WHERE clause keeps only the rows where the condition is true, so rows where it's unknown quietly disappear.

Trap 1: = NULL matches nothing

SELECT * FROM users WHERE country = NULL; -- 0 rows ❌ SELECT * FROM users WHERE country IS NULL; -- 1 row (Cat) ✅ 

country = NULL is unknown for every row, even Cat's. To test for NULL, always use IS NULL / IS NOT NULL.

Trap 2: != silently drops NULL rows

"Everyone who is not from the US":

SELECT * FROM users WHERE country != 'US'; -- 1 row: Bob (Cat is missing!) 

NULL != 'US' is unknown, so Cat's row is dropped. The fixes:

-- PostgreSQL (and the SQL standard) WHERE country IS DISTINCT FROM 'US' -- Bob, Cat ✅ -- works in any database WHERE country != 'US' OR country IS NULL -- Bob, Cat ✅ 

IS DISTINCT FROM compares values like !=, but it treats NULL as a normal value instead of "unknown". In SQLite the equivalent is IS NOT.

Trap 3: one NULL makes NOT IN return zero rows

This is the one from the top, and it's the nastiest because it looks completely correct:

SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned); -- banned: 2, NULL -- 0 rows ❌ 

For Ann (id 1), NOT IN (2, NULL) expands to:

1 != 2 AND 1 != NULL -- true AND unknown → unknown 

So every row is unknown, and every row is dropped. It only takes one NULL in the subquery, for example from a nullable foreign key or a LEFT JOIN.

The fix is NOT EXISTS:

SELECT * FROM users u WHERE NOT EXISTS ( SELECT 1 FROM banned b WHERE b.user_id = u.id ); -- 3 rows: Ann, Cat, Dan ✅ 

NOT EXISTS asks "is there a matching row?", which is always true or false, never unknown. You could also add WHERE user_id IS NOT NULL inside the subquery, but NOT EXISTS is safe by default.

Trap 4: COUNT and AVG skip NULLs

SELECT COUNT(*), COUNT(country) FROM users; -- 4 | 3 -- scores: 10, NULL, 20 SELECT AVG(score) FROM scores; -- 15 (not 10!) SELECT AVG(COALESCE(score, 0)) FROM scores; -- 10 

COUNT(*) counts rows. COUNT(column) counts non-NULL values. AVG, SUM, MIN and MAX all ignore NULLs, so the NULL score isn't in the average at all.

Sometimes that's exactly what you want, since "no score yet" shouldn't drag the average down. But if missing should mean zero, say so with COALESCE(score, 0).

Cheat sheet

The best fix of all: declare columns NOT NULL whenever a value is always required. A NULL that can't exist can't break your queries.

Which of these has bitten you? 👇

I post one tested coding tip every day on YouTube at Refactorly. The 30-second version of this one is here.