How to Rewrite Is Distinct from and Is Not Distinct from in SQL Server 20008R2

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

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.

Does SQL Server support IS DISTINCT FROM clause?

No, it doesn't. The following SO question explains how to rewrite them into equivalent (but more verbose) SQL Server expressions:

• How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM?

There's also a Uservoice entry for this issue, where you can vote for inclusion in the next release:

• Add language and optimizer support for ISO

Best way to evaluate value1 IS NOT DISTINCT FROM value2 in SQL Server

No.

Unfortunately, SQL Server does not implement the null-safe operator, unlike other databases such as Postgres (wich suports the standard IS DISTINCT FROM operator), or MySQL (that has an extension operator called <=>).

So you are basically stucked with the following construct:

(value1 = value2) or (value1 is null and value2 is null)

SQL Server - Compare 2 tables for data in the same columns without checking for equality

You could use:

SELECT
     t1.ID AS Tbl1_ID
    ,t2.ID AS Tbl2_ID
    ,CASE WHEN NOT EXISTS (
                -- here use template and put column list from table 1
        SELECT CASE WHEN t1.Col1 IS NOT NULL THEN -1 ELSE 0 END, 
               CASE WHEN t1.Col2 IS NOT NULL THEN -1 ELSE 0 END, 
               CASE WHEN t1.Col3 IS NOT NULL THEN -1 ELSE 0 END 
        INTERSECT 
               -- here use template and put column list from table 2
        SELECT CASE WHEN t2.Col1 IS NOT NULL THEN -1 ELSE 0 END, 
               CASE WHEN t2.Col2 IS NOT NULL THEN -1 ELSE 0 END, 
               CASE WHEN t2.Col3 IS NOT NULL THEN -1 ELSE 0 END)
     THEN 1 ELSE 0 END AS Discrepancy
FROM @Tbl1 t1
FULL JOIN @Tbl2 t2
    ON t1.ID = t2.ID;

Rextester.com Demo

It is a variant of IS DISTINCT FROM. If you care about actual values then:

SELECT
     t1.ID AS Tbl1_ID
    ,t2.ID AS Tbl2_ID
    ,CASE WHEN NOT EXISTS (
        SELECT t1.Col1, t1.Col2, t1.Col3
        INTERSECT 
        SELECT t2.Col1, t2.Col2, t2.Col3)
     THEN 1 ELSE 0 END AS Discrepancy
FROM @Tbl1 t1
FULL JOIN @Tbl2 t2
    ON t1.ID = t2.ID;

In Postgres, field != 'N' fails to include NULL value

No, this isn't specific to Postgres, it's the same in Oracle (online example a, online example b). A != or <> expression where one of the operands is NULL will evaluate to NULL, and this will be considered falsy in the WHERE clause.

To include NULL values in your results, you can use the SQL-1999 standard

p.is_ready_for_submission IS DISTINCT FROM 'n'

which will not propagate NULL values but consider them as distinct values.

Unfortunately, it's not supported in Oracle, various alternatives exist (like the one in your question).

SQL Server column comparison including Null/Not Null

I thought that you can write the filter condition in a bit more condensed way:

SELECT
    ID,
    Field1,
    Field2
FROM
    TestTable
WHERE
    NOT((Field1 = Field2) OR (Field1 IS NULL AND Field2 IS NULL))
;

But, this query will produce incorrect result.
Your variant is correct.

---

The Connect item about proposed IS DISTINCT FROM operator mentioned by @Heinzi and this answer have a link to a very good post by Paul White: Undocumented Query Plans: Equality Comparisons.

In that post Paul explains that query processor already has this operator, which is used for INTERSECT queries.

Your example can be rewritten as:

SELECT
    ID,
    Field1,
    Field2
FROM
    TestTable AS T
WHERE
    NOT EXISTS
    (
        SELECT T.Field1

        INTERSECT

        SELECT T.Field2
    )
;

• It produces correct results
• It has a good execution plan
• In this form it is easy to add more fields for comparison

SQL Server Compare to NULL

I encountered the same problem with you when taking comparison with nullable value, NULL always returns unknown as far away of our desired only between TRUE or FALSE

I ended up with declare a Scalar-valued functions with these logics like other SQL(s) dealing with null as

Ordinary comparison operators yield null (signifying "unknown"), not
true or false, when either input is null. For example, 7 = NULL yields
null, as does 7 <> NULL. When this behavior is not suitable, use the
IS [ NOT ] DISTINCT FROM constructs:

a IS DISTINCT FROM b => a != b
a IS NOT DISTINCT FROM b => a == b

Which a IS NOT DISTINCT FROM b could be rewritten as

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

I use sql_variant for these basic parameters: int, datetime, varchar,...

create function IsEqual(
    @a sql_variant,
    @b sql_variant
)
returns bit
as
begin
    return (CASE WHEN (@a IS NOT NULL AND @b IS NOT NULL AND @a=@b) OR (@a IS NULL AND @b is NULL) THEN 1 ELSE 0 END);
end

create function IsNotEqual(
    @a sql_variant,
    @b sql_variant
)
returns bit
as
begin
    return 1-dbo.IsEqual(@a,@b);
end

To use

select dbo.IsEqual(null, null) Null_IsEqual_Null,
dbo.IsEqual(null, 1) Null_IsEqual_1,
dbo.IsEqual(1, null) _1_IsEqual_Null,
dbo.IsEqual(1, 1)  _1_IsEqual_1,
dbo.IsEqual(CAST('2017-08-25' AS datetime), null) Date_IsEqual_Null,
dbo.IsEqual(CAST('2017-08-25' AS datetime), CAST('2017-08-25' AS datetime)) Date_IsEqual_Date

For your cases

select dbo.IsNotEqual(123,123) _123_IsNotEqual_123,
dbo.IsNotEqual(5,123) _5_IsNotEqual_123,
dbo.IsNotEqual(Null,123) Null_IsNotEqual_123,
dbo.IsNotEqual(123,Null) _123_IsNotEqual_Null,
dbo.IsNotEqual(Null,Null) Null_IsNotEqual_Null

SQL Server 2008R2: SET ANSI_NULLS OFF does not affect merge matching with null values

SET ANSI_NULLS only affects the semantics of NULL comparisons in very limited cases. Specifically it

affects a comparison only if one of the operands of the comparison is
either a variable that is NULL or a literal NULL. If both sides of the
comparison are columns or compound expressions, the setting does not
affect the comparison. (source)

When you wrap the literal NULL in a derived table this condition is no longer met so it is not expected that this setting will do as you want.

---

Related Topics