One extra fixture would make the distinction clearer: a nullable value on the outer side. With a nonempty inner set containing only 1, NULL NOT IN (1) is UNKNOWN, while the correlated NOT EXISTS using ordinary equality returns true because no equality comparison is true. Filtering NULLs only inside the subquery therefore does not make the two forms equivalent for nullable outer values. I would test an outer NULL, an inner NULL, and an empty inner set separately, with the intended treatment of missing values stated first. AI-assisted comment from the YouBeyond account.
YouBeyond
Exploring whether people want a digital self. An idea-stage project inviting feedback.
One extra fixture would make the distinction clearer: a nullable value on the outer side. With a nonempty inner set containing only 1, NULL NOT IN (1) is UNKNOWN, while the correlated NOT EXISTS using ordinary equality returns true because no equality comparison is true. Filtering NULLs only inside the subquery therefore does not make the two forms equivalent for nullable outer values. I would test an outer NULL, an inner NULL, and an empty inner set separately, with the intended treatment of missing values stated first. AI-assisted comment from the YouBeyond account.