How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?

Question

How do you rewrite expressions containing the standard IS DISTINCT FROM and IS NOT DISTINCT FROM operators in the SQL implementation in Microsoft SQL Server 2008R2 that does not support them?

Epact

Answer 1

The IS DISTINCT FROM predicate was introduced as feature T151 of SQL:1999, and its readable negation, IS NOT DISTINCT FROM, was added as feature T152 of SQL:2003. The purpose of these predicates is to guarantee that the result of comparing two values is either True or False, never Unknown.

These predicates work with any comparable type (including rows, arrays and multisets) making it rather complicated to emulate them exactly. However, SQL Server doesn't support most of these types, so we can get pretty far by checking for null arguments/operands:

• a IS DISTINCT FROM b can be rewritten as:

  ((a <> b OR a IS NULL OR b IS NULL) AND NOT (a IS NULL AND b IS NULL))
  

• a IS NOT DISTINCT FROM b can be rewritten as:

  (NOT (a <> b OR a IS NULL OR b IS NULL) OR (a IS NULL AND b IS NULL))
  

---

Your own answer is incorrect as it fails to consider that FALSE OR NULL evaluates to Unknown. For example, NULL IS DISTINCT FROM NULL should evaluate to False. Similarly, 1 IS NOT DISTINCT FROM NULL should evaluate to False. In both cases, your expressions yield Unknown.

Oxidase

((a <> b OR a IS NULL OR b IS NULL) AND NOT (a IS NULL AND b IS NULL)): cant we just a<>b or a is null xor b is null – Perseid

Why can't we rewrite a IS NOT DISTINCT FROM b as a = b OR a IS NULL and b IS NULL? Seems much more concise that way. – Pointsman

@Pointsman because when only one operand is null, a = b evaluates to null which then causes the entire expression to evaluate to null. – Oxidase

Is IS DISTINCT FROM implemented in sql server 2019? It is not implemented in sql server 2017. – Springfield

@costa No, SQL Server 2019 still does not support IS DISTINCT FROM - nor does it have any way to succinctly perform NULL-safe comparisons (other than using set-operations in predicates, which is impractical for scalar comparisons). – Rasp

Can you provide an example of when the distinction between False and Unknown matters? OR is not infected by Unknown and you even take advantage of that in your expression. For now I am continuing to use (a IS NULL AND b IS NULL OR a = b) for a IS NOT DISTINCT FROM b. – Varia

@Varia Aside from the obvious NOT (negation), I cannot in SQL Server. The lack of T031 boolean type makes it difficult to use boolean expressions. – Oxidase

@ChrisBandy Thanks, NOT is the one example that I have encountered before but could not remember. Being able to NOT the expression blindly does matter to me, so I might need to use this pattern or one of the set-based options in the future. – Varia

Answer 2

Another solution I like leverages the true two-value boolean result of EXISTS combined with INTERSECT. This solution should work in SQL Server 2005+.

• a IS NOT DISTINCT FROM b can be written as:

  EXISTS(SELECT a INTERSECT SELECT b)

As documented, INTERSECT treats two NULL values as equal, so if both are NULL, then INTERSECT results in a single row, thus EXISTS yields true.

• a IS DISTINCT FROM b can be written as:

  NOT EXISTS(SELECT a INTERSECT SELECT b)

This approach is much more concise if you have multiple nullable columns you need to compare in two tables. For example, to return rows in TableB that have different values for Col1, Col2, or Col3 than TableA, the following can be used:

SELECT *
FROM TableA A
   INNER JOIN TableB B ON A.PK = B.PK
WHERE NOT EXISTS(
   SELECT A.Col1, A.Col2, A.Col3
   INTERSECT
   SELECT B.Col1, B.Col2, B.Col3);

Paul White explains this workaround in more detail: https://sql.kiwi/2011/06/undocumented-query-plans-equality-comparisons.html

Gratian

This should be the accepted answer, as it rewrites the predicate in a way that doesn't duplicate references to a and b. For nondeterministic expressions a and b, or expressions with side-effects (such as logging), that would be very useful. Your second example also emulates (A.Col1, A.Col2, A.Col3) IS DISTINCT FROM (B.Col1, B.Col2, B.Col3), which is only supported natively by PostgreSQL (to my knowledge). A very useful predicate, at times. – Suicidal

Thumbs up for exists(...intersect...) idea. Useful when a and b are long expressions. – Christoper

I modified some of my queries that had predicates similar to other answers here. I concluded that using INTERSECT results in a much faster query. Thanks for sharing! – Pointsman

Answer 3

If your SQL implementation does not implement the SQL standard IS DISTINCT FROM and IS NOT DISTINCT FROM operators, you can rewrite expressions containing them using the following equivalencies:

In general:

a IS DISTINCT FROM b <==>
(
    ((a) IS NULL AND (b) IS NOT NULL)
OR
    ((a) IS NOT NULL AND (b) IS NULL)
OR
    ((a) <> (b))
)

a IS NOT DISTINCT FROM b <==>
(
    ((a) IS NULL AND (b) IS NULL)
OR
    ((a) = (b))
)

This answer is incorrect when used in a context where the difference between UNKNOWN and FALSE matters. I think that is uncommon, though. See the accepted answer by @ChrisBandy.

If a placeholder value can be identified that does not actually occur in the data, then COALESCE is an alternative:

a IS DISTINCT FROM b <==> COALESCE(a, placeholder) <> COALESCE(b, placeholder)
a IS NOT DISTINCT FROM b <==> COALESCE(a, placeholder) = COALESCE(b, placeholder)

Epact

This is a wrong answer though. See the last paragraph in Chris' answer. – Wilfredowilfrid

Yes, this answer is incorrect when used in a context where the difference between UNKNOWN and FALSE matters. I think that is uncommon, though. – Epact

@JasonKresowaty: This isn't uncommon at all. In any predicate like (a IS DISTINCT FROM b) AND something, the distinction between UNKNOWN and FALSE is essential. If a and b are both NULL, then your emulation will generate NULL, regardless if something is TRUE or FALSE. – Suicidal

You can also use coalesce(a = b, a is null and b is null) to test if they're the same, thus (a IS NOT DISTINCT FROM b) – Finable

For some reason the first approach in this answer (using logic) is an order of magnitude slower than the second (using functions) for me (SQL Server). – Emmenagogue

@Finable In what version of SQL Server? Not 2019 - this gives a syntax error. It seems SQL dialects vary in whether (a) conditions are expressions, and there is a boolean data type (b) expressions and conditions are distinct syntactic constructs. It seems MS SQL is of kind (b). – Selfexpression

@LukasEder Are you sure about this? My experience of unknown boolean values in SQL is that they aren't black holes - rather, UNKNOWN AND FALSE evaluates to FALSE, and UNKNOWN AND TRUE evaluates to UNKNOWN. – Selfexpression

@Stewart: Yes, that's what I meant, though indeed, not what I said. My point being that the suggested emulation isn't equivalent to the standard SQL one, which can only be TRUE or FALSE, never NULL – Suicidal

Answer 4

Just to extend John Keller's answer. I prefer to use EXISTS and EXCEPT pattern:

a IS DISTINCT FROM b
<=>
EXISTS (SELECT a EXCEPT SELECT b)
-- NOT EXISTS (SELECT a INTERSECT SELECT b)

and

a IS NOT DISTINCT FROM  b
<=>
NOT EXISTS (SELECT a EXCEPT SELECT b)
-- EXISTS (SELECT a INTERSECT SELECT b)

for one particular reason. NOT is aligned whereas with INTERSECT it is inverted.

---

SELECT 1 AS PK, 21 AS c, NULL  AS  b
INTO tab1;

SELECT 1 AS PK, 21 AS c, 2 AS b
INTO tab2;

SELECT *
FROM tab1 A
JOIN tab2 B ON A.PK = B.PK
WHERE EXISTS(SELECT A.c, A.B
              EXCEPT
              SELECT B.c, B.b);

DBFiddle Demo

Dandify

Answer 5

One caveat in rewriting IS DISTINCT FROM and IS NOT DISTINCT FROM would be to not interfere with using indexes, at least when using SQL Server. In other words, when using the following:

WHERE COALESCE(@input, x) = COALESCE(column, x)

SQL Server won't be able to use any index that includes column. So in a WHERE clause, it would be preferable to use the form

WHERE @input = column OR (@input IS NULL AND column IS NULL)

to take advantage of any indexes for column. (Parens only used for clarity)

Gabionade

+1 for mentioning for how functions kill the use of the index. It's how I ended up here in the first place. – Cantina

Answer 6

We are happy to announce that IS [NOT] DISTINCT FROM is now supported as of SQL Server 2022 CTP 2.1 (and the cloud versions as well). So, hopefully the workarounds are no longer needed generally, though they will still work. Documentation Page Link

Sunlight

Answer 7

Spelling it out using CASE

For the reference, the most canonical (and readable) implementation of IS [ NOT ] DISTINCT FROM would be a well-formatted CASE expression. For IS DISTINCT FROM:

CASE WHEN [a] IS     NULL AND [b] IS     NULL THEN 0 -- FALSE
     WHEN [a] IS     NULL AND [b] IS NOT NULL THEN 1 -- TRUE
     WHEN [a] IS NOT NULL AND [b] IS     NULL THEN 1 -- TRUE
     WHEN [a] =               [b]             THEN 0 -- FALSE
     ELSE                                          1 -- TRUE
END

Obviously, other solutions (specifically John Keller's, using INTERSECT) are more concise.

More details here.

Using DECODE if available

I know this question is about SQL Server, but for completeness' sake, Db2 and Oracle support a DECODE() function, in case of which the following can be emulated:

-- a IS DISTINCT FROM b
DECODE(a, b, 1, 0) = 0

-- a IS NOT DISTINCT FROM b
DECODE(a, b, 1, 0) = 1

Suicidal

FALSE and TRUE are not constants in SQL Server. You pulled an example for MySQL. – Varia

@binki: Yes you're right. But the question was also about ansi-sql (see tag), and generally database products "such as SQL Server" – Suicidal

I think the ansi-sql tag is just there to refer to the fact that the ANSI standard defines IS DISTINCT FROM. The tag sql is probably there just because that is the broader topic. Three of the tags are about SQL Server: tsql, sql-server, and sql-server-2008-r2. The question itself says “such as”, unfortunately, but it also specifies an exact SQL Server version and that is how everyone else interpreted it. – Varia

@binki: Eh, I just tried to provide something useful, you know. Whatever, here's the updated answer. – Suicidal

Answer 8

These expressions can be a good substitute for the IS DISTINCT FROM logic and perform better than the previous examples because they end up being compiled by SQL server into a single predicate expression which will result in approx. half the operator cost on a filter expression. They are essentially the same as the solutions as provided by Chris Bandy, however they use nested ISNULL and NULLIF functions to perform the underlying comparisons.

(... obviously ISNULL could be substituted with COALESCE if you prefer)

• a IS DISTINCT FROM b can be rewritten as:

  ISNULL(NULLIF(a, b), NULLIF(b, a)) IS NOT NULL

• a IS NOT DISTINCT FROM b can be rewritten as:

  ISNULL(NULLIF(a, b), NULLIF(b, a)) IS NULL

Rainy

Answer 9

a IS NOT DISTINCT FROM b

can be rewritten as:

(a IS NOT NULL AND b IS NOT NULL AND a=b) OR (a IS NULL AND b is NULL)

---

a IS DISTINCT FROM b

can be rewritten as:

NOT (a IS NOT DISTINCT FROM b)

Uncompromising

Answer 10

This is an old question and there is a new answer. It is easier to understand and maintain.

-- a IS DISTINCT FROM b
CASE WHEN (a = b) OR (a IS NULL AND b IS NULL) THEN 1 ELSE 0 END = 0

-- a IS NOT DISTINCT FROM b
CASE WHEN (a = b) OR (a IS NULL AND b IS NULL) THEN 1 ELSE 0 END = 1

It should be noted that this syntax alternative to IS [NOT] DISTINCT FROM works in all major SQL databases (see link at the end). This and the alternatives are elaborately explained here

Niobe