Why does one NULL in a NOT IN subquery return zero rows?

You write a NOT IN subquery to find customers who have never ordered, and the result is 0 rows. There is no syntax error and no warning. If the subquery result holds even one NULL, NOT IN keeps no rows at all. With a correlated subquery, one that refers to the outer row, only the outer rows that receive the NULL are affected. After reading this you can work out why in terms of true, false and NULL, and you can tell when switching to NOT EXISTS changes the result.

The examples were run on 2026-11-01 on macOS 27.0.1. SQLite is the 3.54.0 shell that ships with macOS. PostgreSQL is not a server install: it is PGlite 0.5.10 on Node.js v24.21.0, a WebAssembly build of PostgreSQL, and version() reported PostgreSQL 18.6. Each statement came from one file and was sent unchanged to both databases, so "the same statement" below means the same characters. The customers and orders are a made-up example. MySQL, Oracle, SQL Server and a server install of PostgreSQL were not run.

One guest order empties the result

Three customers and four orders. Order 102 is a guest order, so its customer_id is NULL. Order 103 points at customer 999, who does not exist; that row is used later.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
INSERT INTO customers VALUES (1, 'Mina'), (2, 'Jun'), (3, 'Sora');
INSERT INTO orders VALUES (100, 1), (101, 1), (102, NULL), (103, 999);

The customers with no orders are Jun and Sora. But this statement returned 0 rows on both SQLite and PostgreSQL.

SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders) ORDER BY id;

Compare with an unknown and the answer is unknown

The PostgreSQL documentation says ordinary comparison operators give NULL, neither true nor false, when either input is NULL. So 2 = NULL and 2 <> NULL are both NULL, and that is what the run showed.

WHERE keeps only rows where the condition is true. By the PostgreSQL description of WHERE, a row whose result is false or NULL is discarded. So when NOT IN comes out NULL, the row disappears just as it would for false.

The PostgreSQL documentation calls NOT IN with a list of values shorthand for comparing with each value using <> and joining the results with AND, and it explains the NULL results of the subquery form by the same normal rules for combining Boolean values. So the two forms were run side by side, with the values the subquery returns put in as a list: 1, 1, NULL, 999.

SELECT 2 NOT IN (1, 1, NULL, 999), 2 <> 1 AND 2 <> 1 AND 2 <> NULL AND 2 <> 999;
SELECT 1 NOT IN (1, 1, NULL, 999), 1 <> 1 AND 1 <> 1 AND 1 <> NULL AND 1 <> 999;

On the first line both columns were NULL on both databases. On the second both columns were false; SQLite returned false as 0 and PostgreSQL as false.

Put together: with a NULL in the subquery, a row whose value matches is false and a row whose value does not match is NULL. Nothing can be true, so 0 rows is not a quirk of this data. The PostgreSQL description of NOT IN and the result table in the SQLite documentation both give NULL for the no-match case.

DEV NOTES

Why NOT IN keeps no rows at all

Subquery result: 1, 1, NULL, 999 (the customer_id values in the example)

• Customer 2, Jun: no orders

  2 NOT IN (1, 1, NULL, 999) -- NULL

  • 2 <> 1 is true
  • 2 <> 1 is true
  • 2 <> NULL is NULL
  • 2 <> 999 is true
  • All four joined by AND give NULL, so the row is discarded

• Customer 1, Mina: has orders

  1 NOT IN (1, 1, NULL, 999) -- false

  • 1 <> 1 is false
  • One false in an AND makes the whole thing false, so the row is discarded

• What WHERE keeps

  • Only true rows stay; false and NULL are discarded
  • With a NULL in the subquery, no customer can be true
  • This subquery does not depend on the outer row, so the result is 0 rows whatever the other data is

NOT IN and the written-out AND gave the same value on SQLite 3.54.0 and PGlite 0.5.10 (PostgreSQL 18.6), run 2026-11-01. Other databases were not run.

This holds when the subquery returns the same rows for every outer row and NOT IN is itself the WHERE condition. Below, the subquery is correlated: each customer looks only at the order numbered 100 plus their own id.

SELECT c.name FROM customers c WHERE c.id NOT IN (SELECT o.customer_id FROM orders o WHERE o.id = 100 + c.id) ORDER BY c.id;

Mina received 1 and was false, Jun received NULL and was NULL, and Sora received 999 and was true, so both databases returned Sora. And when the opening statement was rewritten as WHERE (id NOT IN (...)) IS NOT TRUE, false and NULL both passed and both databases returned all three customers.

Three statements that fix it

The first takes the NULLs out of the subquery, the second uses NOT EXISTS, and the third uses LEFT JOIN. On this data all three returned Jun and Sora on both databases.

SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL) ORDER BY id;
SELECT name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) ORDER BY c.id;
SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL ORDER BY c.id;

The LEFT JOIN tests the primary key of orders for NULL. On a joined row a primary key cannot be NULL, so NULL in that column means no order was joined.

The plans were also checked in PGlite with EXPLAIN (COSTS OFF), using the NOT IN and NOT EXISTS statements without ORDER BY. NOT IN was a Seq Scan with a hashed SubPlan filter, and NOT EXISTS was a Hash Right Anti Join. That shows only that the plans differ. The tables hold a handful of rows and speed was not measured.

Where NOT EXISTS changes the result

Those fixes dealt with a NULL on the subquery side. If the left value can be NULL, NOT IN and NOT EXISTS do not mean the same thing. This time the question is which orders have no customer.

SELECT id FROM orders WHERE customer_id NOT IN (SELECT id FROM customers) ORDER BY id;
SELECT o.id FROM orders o WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id) ORDER BY o.id;

The first returned only 103. The second returned 102 and 103. The customer_id of 102 is NULL, so under NOT IN the result is NULL and the row is discarded, while under NOT EXISTS no customer row matches and the result is true. Whether a guest order belongs in the result depends on your question; neither answer is right by default. What matters is that swapping one for the other out of habit can add rows.

There is one case where NOT IN is true even with NULL on the left: when the subquery has no rows. The SQLite documentation says that when the right side is an empty set, IN is false and NOT IN is true even if the left operand is NULL, and the PostgreSQL documentation gives true for NOT IN when the subquery returns no rows. The run agreed. The PostgreSQL note on NULL also says a NULL left side gives NULL, but the PGlite output for the empty subquery was true.

The same trap in <>

You do not need NOT IN to hit this. WHERE customer_id <> 1 returned only 103, and 102 with its NULL was left out. If NULL should count as a different value, WHERE customer_id IS DISTINCT FROM 1 returned 102 and 103. That operator ran as the same statement on SQLite 3.54.0 and PGlite here; other databases were not checked.

The one thing to remember: if the subquery column can hold NULL, use NOT EXISTS instead of NOT IN, and decide separately whether rows with NULL on the left belong in the result. That advice is a judgement drawn from these runs and the documentation; another choice may suit a different database or table size.