4

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

Is it the same for each SQL dialect?

Chris Catignani
  • 5,040
  • 16
  • 42
  • 49
Art
  • 2,235
  • 18
  • 34
  • If `value` is `NULL`, then the first returns "true" and the second "false". They are nothing alike. – Gordon Linoff Nov 22 '19 at 16:09
  • "*Is it the same for each SQL dialect?*" - To my knowledge Postgres is the only DBMS that supports the standard `is distinct` operator. So the answer to that is: no –  Nov 22 '19 at 16:10
  • @a_horse_with_no_name whoopsie! Correct, the `NOT` was missing. – Art Nov 22 '19 at 16:48

2 Answers2

4

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))
LukStorms
  • 28,916
  • 5
  • 31
  • 45
  • Note that the proposed standard SQL alternative in this answer is not equivalent to IS NOT DISTINCT FROM for all use cases. If x is 1 and y is null, the alternative will return null, not false. That's fine in a WHERE clause (because WHERE null does the same thing as WHERE false), but in e.g. a SELECT clause, the proposed alternative would not be equivalent to IS NOT DISTINCT FROM. – Christian Long Jun 22 '23 at 16:15
  • Here's a full alternative that returns True or False in all cases, and never returns unknown/null: `((x IS NOT NULL AND y IS NOT NULL AND x = y) OR (x IS NULL AND y IS NULL))` – Christian Long Jun 22 '23 at 16:26
2

There are subtle differences between these tests; compare

SELECT ROW(NULL, NULL) IS NOT DISTINCT FROM NULL;

and

SELECT ROW(NULL, NULL) IS NULL;
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 2
    `IS [NOT] DISTINCT FROM` is part of the SQL standard, it's not a Postgres extension –  Nov 22 '19 at 16:21
  • @laurenz-albe Could you please explain why these 2 examples produce different results? What's the point of these results to differ? – Art Nov 22 '19 at 17:08
  • There is not much sense in this. Compound types and NULL is one of those places where the standard committee must have taken the wrong drugs. I just wanted to point out that these expressions are not totally identical. – Laurenz Albe Nov 25 '19 at 05:11