The NULL in your NOT IN

A NOT IN query can return the wrong answer without telling you. It is valid SQL, it runs without an error, and it hands back a perfectly well-formed result set that happens to be empty when it should not be. No warning, no hint, nothing in the logs: just zero rows where you expected hundreds, and a database that considers it correct. Almost always the cause is a single NULL sitting somewhere you forgot to look, combined with two keywords you have typed a thousand times: NOT IN. None of it is a Postgres bug. This is exactly what the SQL standard mandates, implemented faithfully. That is precisely what makes it so easy to walk into, and why the planner could not safely optimize around it for about twenty-five years. It comes down to one if statement in the parser. Sample schema Nothing elaborate. A table of products, one of which has no category assigned yet, and a table of archived categories that happens to contain a NULL: CREATE TABLE products (id int, category_id int); INSERT INTO…