NOT IN and NULL in SQL: Why Rows Disappear

Training scenario: an administration tool should show pages that are absent from an exclusion list. One missing identifier enters the list, and suddenly the result is empty. There is no syntax error, the pages have not been deleted, and caching is unrelated. Combining NOT IN and NULL in SQL can make a condition unknown rather than true. Below, we investigate this using three fictional pages, without accessing a production database.

The aim is not to mechanically replace one operator with another, but to define the correct exclusion rule. We need to see the intermediate result, obtain the expected list and test edge cases. This approach is useful for catalogue filters, task lists and operational reports. All identifiers are fictional; the example is not a real client case and does not establish that this is the cause in your project.

Show the condition result before hiding it

Open an SQL editor connected to a local test SQLite database that supports WITH and VALUES. No additional extensions, administrator privileges or table creation are required. The working directory does not matter: run the code in the SQL editor, not at a shell command prompt. The names pages and excluded are local expressions within the current query; they do not read the website’s physical tables. Do not put real client data into public SQL playgrounds.

WITH pages(id) AS (
  VALUES (101), (102), (103)
), excluded(page_id) AS (
  VALUES (101), (NULL)
)
SELECT id,
       id NOT IN (SELECT page_id FROM excluded) AS allowed
FROM pages
ORDER BY id;

In SQLite, expect three rows: allowed is 0 for 101, and NULL for 102 and 103. Some clients display NULL as a blank cell: check the display settings and do not describe it as an empty string. This result distinguishes “found in the exclusions” from “absence could not be confirmed”. It is not yet the final list of pages.

The list contains 101, so that page is unambiguously excluded. For the other identifiers, the unknown part of the list prevents NOT IN from returning true. The rules and result matrix for different cases are available in the SQLite documentation. Do not treat NULL as zero: that changes the meaning of the data instead of repairing the condition.

Compare the empty selection with the learning data

WITH pages(id) AS (
  VALUES (101), (102), (103)
), excluded(page_id) AS (
  VALUES (101), (NULL)
)
SELECT id
FROM pages
WHERE id NOT IN (SELECT page_id FROM excluded)
ORDER BY id;

This self-contained query repeats the same learning rows but moves the condition into WHERE. Expect no rows. The absence of pages here does not establish that all of them appear in the exclusions: WHERE does not pass an unknown condition result. This is why it is worth displaying a calculated field before using it as a filter.

Make a control change only in the learning query’s text: remove the (NULL) row and the extra comma, leaving VALUES (101). Now expect 102 and 103. Restore NULL and run it again. This pair of checks separates the operator’s behaviour from incidental date, status or permission restrictions. It changes nothing in stored tables, so there is no database to restore after the exercise.

In the actual application, first investigate where the undefined identifier originates: an unfinished import, an optional field or a mistake constructing the subquery. These are hypotheses to investigate, not established causes. Do not delete such records blindly or replace them with an arbitrary number. Retain a few anonymised examples and agree whether an undefined relationship can be ignored.

Express the absence of a match with NOT EXISTS

WITH pages(id) AS (
  VALUES (101), (102), (103)
), excluded(page_id) AS (
  VALUES (101), (NULL)
)
SELECT p.id
FROM pages AS p
WHERE NOT EXISTS (
  SELECT 1
  FROM excluded AS e
  WHERE e.page_id = p.id
)
ORDER BY p.id;

The third query returns 102 and 103: for each page, it checks that no matching exclusion row exists. A row with an unknown page_id does not create an equality match. The names p and e are only aliases, but the condition e.page_id = p.id is essential. If that link is omitted, the subquery starts answering a different question, and the result may become empty again.

This is not universal permission to ignore NULL. If an unknown exclusion means “verification is not complete”, the tool may need to display a separate warning or stop the operation. Decide this before making a fix. NOT EXISTS is useful when the actual rule is “there is no row with this identifier”. The PostgreSQL documentation also explains subquery semantics; check compatibility with your own environment separately.

Another possible approach is to filter NULL out of the NOT IN subquery with WHERE page_id IS NOT NULL. This is appropriate only under an agreed policy of ignoring such values. Separately define what happens when the left-hand value, pages.id, is NULL: NOT IN and NOT EXISTS should not be treated as unconditionally interchangeable. All page identifiers in our initial data set are present.

Lock down the rule with tests before changing the application

Keep the original failing selection and the corrected version as separate tests. Check a list without NULL, a list containing NULL, repeated 101 values, no matching identifiers and a completely empty subquery. Write down the expected IDs for each case in advance. Compare the identifiers themselves, not just the count: one missing page and one extra page can produce a plausible total.

Before moving the rule into website code, preserve the previous version and review the changes in Git. Use a test copy and an account with read-only permissions. A large SELECT can still create load, so do not run an unrestricted subquery against production data as an experiment. Do not promise a speed improvement from replacing an operator alone: performance must be tested against your schema and data.

Conclusion: an empty NOT IN result does not always mean the pages you need are absent. Display the condition value, check for NULL and make the matching rule explicit. If an operational list or website filter hides records for no obvious reason, contact AriZone with a minimal anonymised example and the expected IDs. This makes it possible to start with testable logic rather than changing real data blindly.