In PostgreSQL 18, table t holds x values 1, 2 and 3, and table s holds y values 2 and NULL. How many rows does SELECT * FROM t WHERE x NOT IN (SELECT y FROM s); return?

Explanation

For x = 1 the predicate is (1 <> 2) AND (1 <> NULL), which is true AND null, so null, and the row is dropped; x = 3 behaves the same way and x = 2 is plainly false. The tempting answer 2 assumes the null row is simply skipped, which is what the NOT EXISTS rewrite does - it returns 2 rows on the same data, so the two forms are not interchangeable.