Is there a difference between IS NULL and IS NOT DISTINCT FROM NULL?

Question

Is there a difference between value IS NULL and value IS NOT DISTINCT FROM NULL?

Is it the same for each SQL dialect?

Answer 1

The IS NOT DISTINCT FROM predicate is part of the SQL standard (SQL:2003)
However, it isn't yet fully adopted by all the DBMS.
Actually, only a few... Well, PostgreSql has.

You can see it as a "NULL tolerant equal"

To compare:

(1 = 1)  --> true
(1 = 0)  --> false
(1 = null) --> unknown/null
(null = null) --> unknown/null

(1 IS NULL) --> false
(null IS NULL) --> true

(1 IS NOT DISTINCT FROM 1) --> true
(1 IS NOT DISTINCT FROM 0) --> false
(1 IS NOT DISTINCT FROM null)  --> false
(null IS NOT DISTINCT FROM null)  --> true

So the main difference between IS NULL versus IS NOT DISTINCT FROM?
Basically, the IS NULL is used to check if an element is empty.
While IS NOT DISTINCT FROM compares 2 elements.

Used in a WHERE clause then this:

WHERE (x IS NOT DISTINCT FROM y)

Has a Standard SQL alternative:

WHERE (x = y OR (x IS NULL AND y IS NULL))