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

Question

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?

Answer 1

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

(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

Answer 2

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.