How to Rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 2008R2?

Rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 2008R2

Question:

How do you rewrite an expression that contains the IS DISTINCT FROM and IS NOT DISTINCT FROM operators in Microsoft SQL Server 2008R2 (which does not support the IS DISTINCT FROM and IS NOT DISTINCT FROM operators)?

Answer:

IS DISTINCT FROM

IS DISTINCT FROM predicate guarantees a binary result (true or false) when comparing two values. For SQL Server 2008R2, this predicate can be simulated using the following expression:

<code class="language-sql">((a != b OR a IS NULL OR b IS NULL) AND NOT (a IS NULL AND b IS NULL))</code>

IS NOT DISTINCT FROM

Similarly, IS NOT DISTINCT FROM checks whether there is a significant difference between two values. It can be rewritten as:

<code class="language-sql">(NOT (a != b OR a IS NULL OR b IS NULL) OR (a IS NULL AND b IS NULL))</code>

Comparison with wrong answer

The incorrect answer given fails to take into account the evaluation of NULL values ​​in Boolean expressions. For example, NULL IS DISTINCT FROM NULL should evaluate to False, but the incorrect expression yields Unknown.

The above is the detailed content of How to Rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 2008R2?. For more information, please follow other related articles on the PHP Chinese website!