Equality comparison and null in postgres

null is weird in postgres. Sure, it's a way of saying that there is no data. But if there is a null value Postgres doesn't want to be responsible for filtering the null value unless you explicitly tell it to.

psql> select 1 where null;
 ?column?
----------
(0 rows)

Comparing null to null with = returns null, not true.

psql> select 1 where null = null;
 ?column?
----------
(0 rows)

And comparing a value to null returns neither true nor false, but null.

psql> select 1 where 17 != null or 17 = null;
 ?column?
----------
(0 rows)

So when we apply a comparison to a nullable column over many rows, we have to be cognisant that null rows will not be included.

psql> select x.y from (values (null), (1), (2)) x(y) where x.y != 1;
 y
---
 2
(1 row)

To include the rows which have null values we have to explicitly ask for them with is null.

psql> select x.y from (values (null), (1), (2)) x(y) where x.y != 1 or x.y is null;
 y
---
 ø
 2
(2 rows)