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

sql, sql-server, stored-procedures, t-sql

Solution

Use `INTERSECT` operator.

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

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

Problem

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 ``` since NULL = NULL is not true. How can I check for the duplicates without having an IF IS NULL statement for every column?

Original source

Related problems