Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A PostgreSQL query with NOT IN returns nothing when the subquery contains NULL

Error(0 rows) from a SELECT with WHERE col NOT IN (SELECT ...) although you know unmatched rows exist

Short 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.

  • Applies to PostgreSQL 15, PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1The first step returns a number above zero, and the outer query returns 0 rows.The subquery returns a NULLFix
    2. 2The 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. 3Rows 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

What does the error look like?

  • (0 rows) from a SELECT with WHERE col NOT IN (SELECT ...) although you know unmatched rows exist
  • The 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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.