How can you find rows where two values differ and one of them can be NULL? Why is NULL != 1 false?

Question

In Postgres I want to find rows where one column is different than another. To my surprise, the comparison of 1 != NULL yields NULL which is a falsy value I guess and it is not included in the results.

SELECT * FROM (VALUES
  (0, 0),
  (0, 1),
  (1, 0),
  (1, 1),
  (0, NULL),
  (1, NULL),
  (NULL, 0),
  (NULL, 1),
  (NULL, NULL)
) v (x, y)
WHERE x != y

Results:

x   y
0   1
1   0

Here is online SQL fiddle to demonstrate the problem: https://www.db-fiddle.com/f/hTbJQJw36k3KrrJfUASpn9/0

I did find out, that when comparing anything with null, the result is always null and that is how it's supposed to work.

So I can get a bit closer when I ask for WHERE x = y IS NOT TRUE:

SELECT *, (x!=y) AS comp, (x=y IS NOT TRUE) AS not_true FROM (VALUES
  (0, 0),
  (0, 1),
  (1, 0),
  (1, 1),
  (0, NULL),
  (1, NULL),
  (NULL, 0),
  (NULL, 1),
  (NULL, NULL)
) v (x, y)

x     y     comp  not_true
0     0     false false
0     1     true  true
1     0     true  true
1     1     false false
0     null  null  true
1     null  null  true
null  0     null  true
null  1     null  true
null  null  null  true

I would want the last row to be true though. I understand that I am comparing anything with NULL and therefor getting NULL which is not TRUE, so yeah.

I can write an extra condition like WHERE (x = y IS NOT TRUE AND (x IS NOT NULL OR y IS NOT NULL)), but that seems overly complicated. Here is the updated SQL fiddle:

https://www.db-fiddle.com/f/hTbJQJw36k3KrrJfUASpn9/1

Is there a simpler way?

Answer 1

You can use the NULL safe comparison operator:

where x is distinct from y

Here is a db<>fiddle.

As to your "why" question. NULL has well-defined semantics (meaning) in SQL. It means that the value is unknown. Hence, almost all comparisons returns NULL ("unknown"). The expression 1 <> NULL evaluates to NULL, because NULL could be 1 -- or any other value.

The SQL Standard also implements functionality that handles this is distinct from and is not distinct from. Postgres happens to be one of the few databases that actually implement this operator, but SQL does have appropriate comparison operators for equality to NULL.