如何比较 T-SQL 中可能为空的值

How to compare values which may both be null in T-SQL

提问by srmark

I want to make sure I'm not inserting a duplicate row into my table (e.g. only primary key different). All my fields allow NULLS as I've decided null to mean "all values". Because of nulls, the following statement in my stored procedure can't work:

我想确保我没有在我的表中插入重复的行（例如，只有主键不同）。我的所有字段都允许 NULLS，因为我决定 null 表示“所有值”。由于空值，我的存储过程中的以下语句无法工作：

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

since NULL = NULL is not true.

因为 NULL = NULL 不是真的。

How can I check for the duplicates without having an IF IS NULL statement for every column?

如何在没有每列的 IF IS NULL 语句的情况下检查重复项？

采纳答案by Quassnoi

Use INTERSECToperator.

使用INTERSECT运算符。

It's NULL-sensitive and efficient if you have a composite index on all your fields:

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

Note that if you create a UNIQUEindex on your fields, your life will be much simpler.

请注意，如果您UNIQUE在字段上创建索引，您的生活会简单得多。

回答by Graeme Job

Along the same lines as @Eric's answer, but without using a 'NULL'symbol.

与@Eric 的回答相同，但不使用'NULL'符号。

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

This will be true only if both values are non-NULL, and equal each other, or both values are NULL

仅当两个值都为non-NULL，并且彼此相等，或者两个值都为NULL

回答by WileCau

I needed a similar comparison when doing a MERGE:

在进行 MERGE 时，我需要类似的比较：

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

The additional checks are to avoid updating rows where all the columns are already the same. For my purposes I wanted NULL <> anyValueto be True, and NULL <> NULLto be False.

额外的检查是为了避免更新所有列都已经相同的行。为了我的目的，我想NULL <> anyValue成为真，也NULL <> NULL想成为假。

The solution evolved as follows:

解决方案演变如下：

First attempt:

第一次尝试：

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
)

Second attempt:

第二次尝试：

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
)

Third attempt (inspired by @THEn's answer):

第三次尝试（受@THEn 的回答启发）：

WHEN MATCHED AND
(

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

    -- OR ... Repeat for other columns
)

The same ISNULL/NULLIF logic can be used to test equality and inequality:

可以使用相同的 ISNULL/NULLIF 逻辑来测试相等和不相等：

• Equality: ISNULL(NULLIF(A, B), NULLIF(B, A)) IS NULL
• Inequaltiy: ISNULL(NULLIF(A, B), NULLIF(B, A)) IS NOT NULL

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

Here is an SQL-Fiddle demonstrating how it works http://sqlfiddle.com/#!3/471d60/1

这是一个 SQL-Fiddle 演示它是如何工作的http://sqlfiddle.com/#!3/471d60/1

回答by Eric

Use ISNULL:

使用ISNULL：

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

You can change 'NULL'to something like 'All Values'if it makes more sense to do so.

如果这样做更有意义，您可以更改'NULL'为类似的'All Values'内容。

It should be noted that with two arguments, ISNULLworks the same as COALESCE, which you could use if you have a few values to test (i.e.-COALESCE(@IN_MY_FIELD1, @OtherVal, 'NULL')). COALESCEalso returns after the first non-null, which means it's (marginally) faster if you expect MY_FIELD1 to be blank. However, I find ISNULLmuch more readable, so that's why I used it, here.

应该注意的是，使用两个参数时，ISNULL与 的作用相同COALESCE，如果您有几个要测试的值（即- COALESCE(@IN_MY_FIELD1, @OtherVal, 'NULL')），您可以使用它。COALESCE也会在第一个非空值之后返回，这意味着如果您希望 MY_FIELD1 为空，它会（略微）更快。但是，我发现ISNULL更具可读性，所以这就是我在这里使用它的原因。

回答by Shannon Severance

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

Wordy As compared to the IFNULL/COALESCE solution. But will work without having to think about what value will not appear in the data that can be used as the stand in for NULL.

罗嗦 与 IFNULL/COALESCE 解决方案相比。但是无需考虑什么值不会出现在可用作 NULL 的替代的数据中即可工作。

回答by butterchicken

You could coalesce each value, but it's a bit wince-inducing:

您可以合并每个值，但这有点令人畏缩：

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

You'd also need to ensure that the coalescedvalue is not an otherwise valid value on the column in question. For example, if it was possible that the value of MY_FIELD1 could be 'MF1' then this would cause a lot of spurious hits.

您还需要确保该coalesced值不是相关列上的其他有效值。例如，如果 MY_FIELD1 的值可能是“MF1”，那么这将导致大量虚假命中。

回答by Jamie G

What if you want to do a comparison for values that ARE NOT equal? Just using a "NOT" in front of the previously mentioned comparisons does not work. The best I could come up with is:

如果您想对不相等的值进行比较怎么办？仅在前面提到的比较前使用“NOT”是行不通的。我能想到的最好的方法是：

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

回答by Remus Rusanu

You create a primary key on your fields and let the engine enforce the uniqueness. Doing IF EXISTS logic is incorrect anyway as is flawed with race conditions.

您在字段上创建主键并让引擎强制执行唯一性。无论如何，执行 IF EXISTS 逻辑是不正确的，因为存在竞争条件。

回答by drowa

Equalscomparison:

相等比较：

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

Not Equal Tocomparison: Just negate the Equalscomparison above.

不等于比较：只需否定上面的等于比较。

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

Is it verbose? Yes, it is. However it's efficient since it doesn't call any function. The idea is to use short circuit in predicates to make sure the equal operator (=) is used only with non-null values, otherwise null would propagate up in the expression tree.

它很冗长吗？是的。但是它很有效，因为它不调用任何函数。这个想法是在谓词中使用短路来确保相等运算符 (=) 仅用于非空值，否则 null 将在表达式树中向上传播。

回答by drowa

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

您可以使用SET ANSI_NULLS来指定等于 (=) 和不等于 (<>) 比较运算符与空值一起使用时的行为。