SQL NOT IN and NULL: Why an Exclusion Query Returns No Rows

There are three users and only one excluded ID, but the query returns nobody. Before investigating indexes or join performance, check whether the NOT IN subquery contains NULL. This is a condition-semantics problem, not a query-speed problem.

The example uses integer IDs and a simple rule: retain users without a matching ID in the exclusions table. Run the fixture in a test database, not production.

1. Start with a tiny fixture

CREATE TABLE users (id INTEGER PRIMARY KEY);
CREATE TABLE exclusions (user_id INTEGER);

INSERT INTO users (id) VALUES (1), (2), (3);
INSERT INTO exclusions (user_id) VALUES (1), (NULL);

Here users.id is always populated. The unknown exclusion ID should not exclude every unrelated user under the chosen rule.

SELECT u.id
FROM users AS u
WHERE u.id NOT IN (SELECT e.user_id FROM exclusions AS e)
ORDER BY u.id;

The result is empty rather than IDs 2 and 3. Adding an index will not change that logical outcome.

2. NOT IN requires more than finding no match

Ordinary SQL comparisons with NULL can be unknown. The expression 2 NOT IN (1, NULL) does not become true: being different from 1 does not establish inequality with an unknown value. WHERE retains only true conditions.

See PostgreSQL Subquery Expressions. NULL is not a special ID or zero.

3. Express a nonexistence requirement directly

For the rule “no exclusion row has this user's ID,” use:

SELECT u.id
FROM users AS u
WHERE NOT EXISTS (
  SELECT 1
  FROM exclusions AS e
  WHERE e.user_id = u.id
)
ORDER BY u.id;

This returns 2 and 3. A NULL exclusion value does not make the equality true, so it does not create a matching row. Duplicate exclusion IDs do not multiply outer rows.

This is a semantic choice, not a claim that NOT EXISTS is always faster. Once correctness is established, compare plans and costs on representative data.

4. Filter NULL only when the contract permits it

If unknown exclusion IDs must be ignored, an explicit filter is another option:

SELECT u.id
FROM users AS u
WHERE u.id NOT IN (
  SELECT e.user_id
  FROM exclusions AS e
  WHERE e.user_id IS NOT NULL
)
ORDER BY u.id;

This also returns 2 and 3 for the fixture. However, silently ignoring NULL can hide an import failure or missing mandatory relationship. Address the write path and schema according to the cause rather than merely making the screen populate again.

A fabricated COALESCE replacement can collide with real data and obscure the contract. Use IS NULL or IS NOT NULL for missing-value checks; see Comparison Functions and Operators for their distinction from ordinary equality.

5. Check the left side and data scope too

The outer key in this example is non-null. For an optional column, decide explicitly whether a NULL outer value should be retained. NOT EXISTS with ordinary equality can keep that unmatched row; NOT IN behaves differently depending on the right-hand data. The two forms are not unconditional replacements in every schema.

In a multi-tenant system, the complete requirement may be “no exclusion in the same tenant.” Include the appropriate scope and derive authorization from the authenticated identity. An ID in another organization must not accidentally exclude the current organization's result.

6. Test edge cases

• Empty exclusions: retain all three IDs.
• Only ID 1: return 2 and 3.
• ID 1 plus NULL: the original query is empty; both deliberate alternatives return 2 and 3.
• Duplicate ID 1 rows: outer results are not multiplied.
• Only NULL: verify the chosen unknown-data policy.
• Add separate fixtures when outer keys are nullable or tenant scope applies.

The displayed queries and basic edge cases were run with in-memory PDO SQLite. They write no on-disk database, access no production database and are not a PostgreSQL benchmark. SQLite SQL Language Expressions documents IN/NOT IN behavior, including an empty right-hand set.

7. Review exclusion queries systematically

1. Write the business rule and expected IDs for a small fixture.
2. Inspect nullability on both sides and the subquery's data source.
3. Choose nonexistence or explicit NULL filtering according to the contract.
4. Assert specific IDs rather than only row counts.
5. Then evaluate indexes and plans on the actual database engine.

When an exclusion query unexpectedly becomes empty, look for NULL before tuning it. A small fixture and an explicit data rule usually clarify the problem faster than trying many SQL variants against a large dataset.