DEV Community

Discussion on: Why postgresql does not return null values once filtered by not_in

Collapse
 
dmfay profile image
Dian Fay • Edited

Boolean logic is built around the binary values TRUE and FALSE. SQL, however, is built around a three-valued logic: TRUE, FALSE, and NULL, where the last is an unknown or undefined value. For more detail you could consult Wikipedia or appendix D in de Haan and Koppelaars' Applied Mathematics for Database Professionals, but the short version is that NULL cannot be compared to a known value: any comparison with an unknown value yields an unknowable result.

Postgres has a shortcut: x IS [NOT] DISTINCT FROM y, although that's likely not much help here with a list of values.