This query should return three users. It returns zero . No error, no warning:

SELECT * FROM users WHERE id NOT IN ( SELECT user_id FROM banned ) ; -- 0 rows

Enter fullscreen mode

Exit fullscreen mode

The cause is a single NULL . Here are four NULL traps, each with a fix. I ran every query below in PostgreSQL 17 and SQLite 3.51 , and both gave the same results.

Video version:

The setup

CREATE TABLE users ( id int , name text , country text ) ; INSERT INTO users VALUES ( 1 , 'Ann' , 'US' ) , ( 2 , 'Bob' , 'UK' ) , ( 3 , 'Cat' , NULL ) , -- 👈 this row ( 4 , 'Dan' , 'US' ) ;

CREATE TABLE banned ( user_id int ) ; INSERT INTO banned VALUES ( 2 ) , ( NULL ) ;

Enter fullscreen mode

Exit fullscreen mode

The rule behind every trap

NULL doesn't mean "empty". It means unknown . Is unknown equal to unknown?…