如何对值进行比较，其都可以是空的T-SQL(How to compare values which

我想确保我不会插入重复行到我表（例如只有主键不同）。 我所有的领域允许空值，因为我已经决定为空表示“所有值”。 由于空的，在我的存储过程以下语句不能正常工作：

IF EXISTS(SELECT * FROM MY_TABLE WHERE 
    MY_FIELD1 = @IN_MY_FIELD1  AND
    MY_FIELD2 = @IN_MY_FIELD2  AND
    MY_FIELD3 = @IN_MY_FIELD3  AND 
    MY_FIELD4 = @IN_MY_FIELD4  AND
    MY_FIELD5 = @IN_MY_FIELD5  AND
    MY_FIELD6 = @IN_MY_FIELD6)
    BEGIN
        goto on_duplicate
    END

因为NULL = NULL是不正确的。

我如何检查的重复，而无需一个。如果每列NULL语句？

Answer 1:

使用INTERSECT运算符。

这是NULL敏感的和有效的，如果你对所有领域的复合指数：

IF      EXISTS
        (
        SELECT  MY_FIELD1, MY_FIELD2, MY_FIELD3, MY_FIELD4, MY_FIELD5, MY_FIELD6
        FROM    MY_TABLE
        INTERSECT
        SELECT  @IN_MY_FIELD1, @IN_MY_FIELD2, @IN_MY_FIELD3, @IN_MY_FIELD4, @IN_MY_FIELD5, @IN_MY_FIELD6
        )
BEGIN
        goto on_duplicate
END

请注意，如果您创建一个UNIQUE您域的索引，你的生活就会简单得多。

Answer 2:

沿着相同的路线为@ Eric的答案 ，但没有使用'NULL'的象征。

(Field1 = Field2) OR (ISNULL(Field1, Field2) IS NULL)

这将是真实的，只有当两个值都是non-NULL ，并且彼此相等，或这两者的值是NULL

Answer 3:

使用ISNULL ：

ISNULL(MY_FIELD1, 'NULL') = ISNULL(@IN_MY_FIELD1, 'NULL')

你可以改变'NULL'的东西像'All Values' ，如果它更有意义，这样做。

应当指出的是，有两个参数， ISNULL的工作方式相同COALESCE ，而如果你有测试几个值（即-你可以使用COALESCE(@IN_MY_FIELD1, @OtherVal, 'NULL') COALESCE还后的第一个非空的，这意味着它的（轻微）更快，如果你希望MY_FIELD1是空白返回。 不过，我觉得ISNULL更可读的，所以这就是为什么我用它，在这里。

Answer 4:

我需要做一个MERGE时，类似的比较：

WHEN MATCHED AND (Target.Field1 <> Source.Field1 OR ...)

额外的检查是为了避免更新所有列已经是同一行。 对于我而言，我想NULL <> anyValue是真实的，并且NULL <> NULL是假的。

溶液变化如下：

第一次尝试：

WHEN MATCHED AND
(
    (
        -- Neither is null, values are not equal
        Target.Field1 IS NOT NULL
            AND Source.Field1 IS NOT NULL
            AND Target.Field1 <> Source.Field1
    )
    OR
    (
        -- Target is null but source is not
        Target.Field1 IS NULL
            AND Source.Field1 IS NOT NULL
    )
    OR
    (
        -- Source is null but target is not
        Target.Field1 IS NOT NULL
            AND Source.Field1 IS NULL
    )

    -- OR ... Repeat for other columns
)

第二次尝试：

WHEN MATCHED AND
(
    -- Neither is null, values are not equal
    NOT (Target.Field1 IS NULL OR Source.Field1 IS NULL)
        AND Target.Field1 <> Source.Field1

    -- Source xor target is null
    OR (Target.Field1 IS NULL OR Source.Field1 IS NULL)
        AND NOT (Target.Field1 IS NULL AND Source.Field1 IS NULL)

    -- OR ... Repeat for other columns
)

第三次尝试（灵感来自@随后的回答 ）：

WHEN MATCHED AND
(

    ISNULL(
        NULLIF(Target.Field1, Source.Field1),
        NULLIF(Source.Field1, Target.Field1)
    ) IS NOT NULL

    -- OR ... Repeat for other columns
)

相同ISNULL / NULLIF逻辑可被用来测试等式和不等式：

• 平等： ISNULL(NULLIF(A, B), NULLIF(B, A)) IS NULL
• Inequaltiy： ISNULL(NULLIF(A, B), NULLIF(B, A)) IS NOT NULL

下面是一个SQL-小提琴展示它是如何工作http://sqlfiddle.com/#!3/471d60/1

Answer 5:

IF EXISTS(SELECT * FROM MY_TABLE WHERE 
            (MY_FIELD1 = @IN_MY_FIELD1 
                     or (MY_FIELD1 IS NULL and @IN_MY_FIELD1 is NULL))  AND
            (MY_FIELD2 = @IN_MY_FIELD2 
                     or (MY_FIELD2 IS NULL and @IN_MY_FIELD2 is NULL))  AND
            (MY_FIELD3 = @IN_MY_FIELD3 
                     or (MY_FIELD3 IS NULL and @IN_MY_FIELD3 is NULL))  AND
            (MY_FIELD4 = @IN_MY_FIELD4 
                     or (MY_FIELD4 IS NULL and @IN_MY_FIELD4 is NULL))  AND
            (MY_FIELD5 = @IN_MY_FIELD5 
                     or (MY_FIELD5 IS NULL and @IN_MY_FIELD5 is NULL))  AND
            (MY_FIELD6 = @IN_MY_FIELD6
                     or (MY_FIELD6 IS NULL and @IN_MY_FIELD6 is NULL)))
            BEGIN
                    goto on_duplicate
            END

罗嗦因为相比IFNULL / COALESCE溶液。 但是，将工作，而无需考虑什么价值将不会出现在可使用在空支架上的数据。

Answer 6:

你可以凝聚每一个值，但它是一个有点畏缩诱导：

    IF EXISTS(SELECT * FROM MY_TABLE WHERE 
    coalesce(MY_FIELD1,'MF1') = coalesce(@IN_MY_FIELD1,'MF1')  AND
    ...
    BEGIN
            goto on_duplicate
    END

你想也需要确保coalesced价值是不是有问题的列一个非常有效的价值。 例如，如果有可能的是MY_FIELD1的值可以是“MF1”那么这将导致大量的假命中。

Answer 7:

如果你想要做的不相等的值进行比较呢？ 只使用一个“NOT”在前面提到的比较前不起作用。 我能想出是最好的：

(Field1 <> Field2) OR (NULLIF(Field1, Field2) IS NOT NULL) OR (NULLIF(Field2, Field1) IS NOT NULL)

Answer 8:

你创建你的域主键，让发动机强制唯一性。 做的，如果EXISTS逻辑是不正确反正是有缺陷与竞争条件。

Answer 9:

You could use SET ANSI_NULLS in order to specify the behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values.

Answer 10:

等于比较：

((f1 IS NULL AND f2 IS NULL) OR (f1 IS NOT NULL AND f2 IS NOT NULL AND f1 = f2))

不等于比较：就否定了equals上面的对比。

NOT ((f1 IS NULL AND f2 IS NULL) OR (f1 IS NOT NULL AND f2 IS NOT NULL AND f1 = f2))

它是冗长？ 是的。 然而，因为它不调用任何功能，它是有效的。 该想法是使用短路谓词以确保相等的运算符（=）仅与非空值使用，否则将返回null在表达式树向上传播。

Answer 11:

你将不得不使用IS NULL或ISNULL。 是不是真的有一个远在其周围。

Answer 12:

NULLIF（TARGET.relation_id，SOURCE.app_relation_id）IS NULL简单的解决方案

Answer 13:

你检查NULLIF？ http://msdn.microsoft.com/en-us/library/ms177562.aspx

文章来源: How to compare values which may both be null is T-SQL