如何比较 T-SQL 中可能为空的值 [英] How to compare values which may both be null in T-SQL

问题描述

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

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:

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 不是真的.

since NULL = NULL is not true.

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

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

推荐答案

使用 INTERSECT 运算符.

Use INTERSECT operator.

如果您的所有字段都有复合索引，则它对 NULL 敏感且高效:

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

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 索引，您的生活将变得更加简单.

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

这篇关于如何比较 T-SQL 中可能为空的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！