NULL, three-valued logic and NOT IN

SQL, indexing and PostgreSQL Mid-level Python 3.13+ PostgreSQL 17+ Reviewed 31 Oct 2026 By Best Latest Apps

Predict the output

What does the SELECT print?

SQL · PostgreSQL 17

CREATE TABLE users (id int);
CREATE TABLE banned (user_id int);
INSERT INTO users VALUES (1), (2), (3);
INSERT INTO banned VALUES (2), (NULL);
SELECT count(*) FROM users WHERE id NOT IN (SELECT user_id FROM banned);

Options023An error

Hint 1 of 1

What is 1 <> NULL?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A 0

Correct. For ids 1 and 3, id <> NULL is unknown, so NOT IN is never true. NOT EXISTS would return 2.

x NOT IN (2, NULL) means x <> 2 AND x <> NULL, and comparing with NULL gives unknown, never true. No row passes the WHERE clause, so the count is 0.

Why not the others?

B 2. That's what NOT EXISTS returns; NOT IN with a NULL in the list returns nothing.
C 3. Id 2 is banned for certain, so 3 is impossible either way.
D An error. NULLs in a subquery are legal; they just make NOT IN unknown.

Result on PostgreSQL 17 (psql)

count
0

(1 row)

30-second answer

In SQL, NULL means unknown, so any comparison with it is unknown, not true or false, and WHERE keeps only true rows. Use IS NULL or IS DISTINCT FROM instead. x NOT IN (1, NULL) means x <> 1 AND x <> NULL; the second part is unknown, so the whole condition is never true and the query returns nothing. Use NOT EXISTS, which isn't affected by NULLs in the subquery.

Practise saying it · 30 s. No recording, just a timer

Explain NULL in SQL.

Say your answer out loud, then check which of these points you covered:

NULL is unknown
Comparisons yield unknown
IS NULL / IS DISTINCT FROM
NOT IN trap, use NOT EXISTS

Deep dive

NULL = NULL is NULL; NULL IS NOT DISTINCT FROM NULL is true.

Aggregates skip NULLs: count(col) counts non-null values, count(*) counts rows.

UNIQUE allows many NULLs by default; PostgreSQL 15+ has NULLS NOT DISTINCT to allow only one.

coalesce(col, 0) substitutes a value, but hides the difference between zero and unknown.

NOT EXISTS ignores the NULL Ran on PostgreSQL 17.11

SQL · PostgreSQL 17

CREATE TABLE users (id int);
CREATE TABLE banned (user_id int);
INSERT INTO users VALUES (1), (2), (3);
INSERT INTO banned VALUES (2), (NULL);
SELECT count(*) FROM users u
WHERE NOT EXISTS (SELECT 1 FROM banned b WHERE b.user_id = u.id);

Common mistakes

WHERE deleted_at = NULL.
NOT IN against a column that may contain NULL.
Expecting count(col) to equal count(*).

Senior take & follow-ups

Explain three-valued logic, then the NOT IN trap and the NOT EXISTS fix.

Follow-up questions

Think of your answer first, then open the hint.

What does NULL OR true evaluate to?
Show hint
true; unknown OR true is true.
How do you make NULLs sort last?
Show hint
ORDER BY col NULLS LAST.

Suggest a fix if something here is wrong or out of date.