A PostgreSQL query with NOT IN returns nothing when the subquery contains NULL
(0 rows) from a SELECT with WHERE col NOT IN (SELECT ...) although you know unmatched rows existShort answer
If the subquery behind NOT IN returns even one NULL, the result is NULL instead of true for every row that has no match, so the WHERE clause keeps nothing. Use NOT EXISTS, or filter NULLs out of the subquery. If your own column can be NULL, add an explicit IS NULL condition.
Fix it fast
1Find out which cause you have
Count NULLs in the column the subquery returns. One is enough to break NOT IN.
SELECT count(*) FROM other_table WHERE other_col IS NULL;2Match what you see, then jump to its fix
- 1
The first step returns a number above zero, and the outer query returns 0 rows.The subquery returns a NULLFix - 2
The NULLs in the other table are meaningless, and you prefer to keep the NOT IN form.You want to keep the NULLs out of the subquery insteadFix - 3
Rows where your own column is NULL are missing from the result, and you expected them.The left-hand column is NULL, and those rows should be keptFix
- 1
What does the error look like?
(0 rows) from a SELECT with WHERE col NOT IN (SELECT ...) although you know unmatched rows existThe same query returns the right rows after you delete NULLs from the subquery table
Cause 1 of 3
The subquery returns a NULL
1Check
When no right-hand value equals the left-hand value and at least one right-hand row is NULL, NOT IN gives NULL, not true. In a WHERE clause NULL discards the row.
SELECT 1 NOT IN (SELECT 2 UNION ALL SELECT NULL);2Fix
Rewrite as NOT EXISTS, which only asks whether a matching row exists and is not affected by NULLs in the other table.
SELECT t.*
FROM my_table t
WHERE NOT EXISTS (
SELECT 1 FROM other_table o WHERE o.other_col = t.col
);3Verify
The query now returns the unmatched rows. Compare the count with a LEFT JOIN where the right side IS NULL if you want a second opinion.
SELECT count(*) FROM my_table t
LEFT JOIN other_table o ON o.other_col = t.col
WHERE o.other_col IS NULL;Confirmed by postgresql.org
Cause 2 of 3
You want to keep the NULLs out of the subquery instead
1Check
NOT IN is safe when the right-hand side can never contain NULL. Make that true inside the subquery.
SELECT count(*) FROM other_table WHERE other_col IS NOT NULL;2Fix
Add IS NOT NULL to the subquery.
SELECT * FROM my_table
WHERE col NOT IN (
SELECT other_col FROM other_table WHERE other_col IS NOT NULL
);3Verify
Rows without a match come back again.
SELECT count(*) FROM my_table WHERE col NOT IN (SELECT other_col FROM other_table WHERE other_col IS NOT NULL);Confirmed by postgresql.org
Cause 3 of 3
The left-hand column is NULL, and those rows should be kept
1Check
If the left-hand expression is NULL, the NOT IN result is NULL too, so those rows are dropped even when the subquery has no NULLs.
SELECT count(*) FROM my_table WHERE col IS NULL;2Fix
Say what should happen to them with an explicit condition.
SELECT * FROM my_table
WHERE col IS NULL
OR col NOT IN (SELECT other_col FROM other_table WHERE other_col IS NOT NULL);3Verify
The NULL rows appear in the result.
SELECT count(*) FILTER (WHERE col IS NULL) FROM my_table;Confirmed by postgresql.org
How do I stop it coming back?
- Default to NOT EXISTS for anti-joins; it keeps working when a nullable column is added later.
- Mark a column NOT NULL when NULL has no business meaning, so NOT IN stays safe.
- Add a test row with a NULL to any fixture used for exclusion queries.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix