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?

Are both `MY_FIELD{1,6}` (from the table) and variables `@IN_MY_FILED{1,6}` nullables or is it just `MY_FIELD{1,6}`? — dolmen, Mar 01 '16 at 16:08
This answer is related: https://stackoverflow.com/a/12456786/952135. Also this article: https://modern-sql.com/feature/is-distinct-from — Oliv, May 04 '20 at 12:28

score 102 · Answer 1 · edited May 23 '17 at 12:10

102

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

edited May 23 '17 at 12:10

Community

1
1

answered Jun 21 '13 at 10:55

Graeme Job

1,089
1
8
4

10

This is definitely the tightest for Equality testing (`A = B OR ISNULL(A, B) IS NULL` without the unnecessary parens), but it's not safe to invert due to the way UNKNOWN fails to invert. The shortest Inequality test I've seen is @WileCau's. – tovodeverett May 15 '14 at 05:01
This will have problem with indexes – Royi Namir Oct 03 '17 at 10:31
Yes, but this will return NULL if one or both values are NULL. We want something that returns *false* if one or both values are null. – Pxtl Sep 26 '19 at 19:17

Quassnoi · Accepted Answer · 2009-07-02T16:04:53.173

59

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.

edited Jul 02 '09 at 16:04

answered Jul 02 '09 at 15:55

Quassnoi

413,100
91
616
614

1

Note INTERSECT is only available from SQLServer 2005 onwards. Some of us are few years behind :) – butterchicken Jul 02 '09 at 16:43
2

When this approach was used as a partial join predicate for a single field it performed much slower than the equivalent "(a=b or (a is null and b is null))". Fields a and b happened to be sql_variants, perhaps that was why. – crokusek Jan 01 '15 at 01:58

score 49 · Answer 3 · edited May 23 '17 at 12:10

I needed a similar comparison when doing a 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 <> anyValue to be True, and NULL <> NULL to be False.

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):

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:

Equality: ISNULL(NULLIF(A, B), NULLIF(B, A)) IS NULL
Inequaltiy: 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

I was attempting to solve this before Googling and managed to come up with `NULLIF(A, B) IS NULL AND NULLIF(B, A) IS NULL` (for Equality) and NOT'ing the whole mess with parens for inequality. Yours is 3 characters shorter for Equality and 4 characters shorter for Inequality, but I think mine is slightly easier to understand (although that's definitely relative in this case). — tovodeverett, May 15 '14 at 04:50
This is pretty unreadable so it's better to compare ISNULLed with impossible values if you can, but sometimes that's not a solution (i.e. when comparing NULLable bit columns). Sure would be nice if there was a built-in solution, but I think there's some ideological motivation to keep that kind of thing out of T-SQL to prevent people from using NULLs in the "wrong" way. — Tmdean, Jul 21 '15 at 13:20
I think INTERSECT and EXCEPT provides nice methods to do equality/inequality searches as NULLs are handled as values... — HansLindgren, Aug 04 '15 at 11:24
Wonderful answer, but to me it demonstrates how 3-value logic is a completely inexcusable abomination for real-world usage, at least without a bunch more operators than exist in any SQL dialect. — Pxtl, Sep 26 '19 at 19:20
@Pxtl ISO SQL has the `IS DISTINCT FROM` and `IS NOT DISTINCT FROM` operators ( https://modern-sql.com/feature/is-distinct-from ) - but it's waaay too verbose, and SQL Server still doesn't support it yet. I really like MySQL's use of `<=>` for `IS DISTINCT FROM` - though it doesn't have a succint `IS NOT DISTINCT FROM` syntax though. — Dai, Jun 01 '20 at 02:31

Eric · Answer 4 · 2009-07-02T15:49:53.653

35

Use 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.

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

edited Jul 02 '09 at 15:49

answered Jul 02 '09 at 15:44

Eric

92,005
12
114
115

14

What if the value of the field is 'NULL' ? – Remus Rusanu Jul 02 '09 at 15:46
2

Use an alternative value which cannot occur, such as 'x' for a number field, or a too long string for a string field. – l0b0 Jul 02 '09 at 15:48
@Remus: I was using a stand-in value. Obviously, it has to be a unique, unused value in the field. – Eric Jul 02 '09 at 15:51
3

@l0b0 Using a too long string will not work, as ISNULL will truncate the replacement value to match the size of the checked value. – Aaroninus Jul 07 '15 at 13:50
1

this will fail for integer (cannot convert varchar to int) – Muflix Sep 14 '18 at 12:47
2

This could cause problems for Jennifer Null: https://gizmodo.com/it-really-sucks-to-be-named-jennifer-null-1767159091 – Paul T Davies Nov 07 '18 at 15:06

score 21 · Answer 5 · answered Jul 02 '09 at 16:30

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.

score 11 · Answer 6 · answered Jul 02 '09 at 15:46

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 coalesced value 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.

score 11 · Answer 7 · answered Sep 19 '18 at 22:15

Equals comparison:

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

Not Equal To comparison: Just negate the Equals comparison 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.

As verbose as this is, it's the best answer. Many other answers return NULL instead of FALSE, and so will to be TRUE if negated. — Pxtl, Sep 26 '19 at 19:18

score 7 · Answer 8 · answered Oct 08 '13 at 16:41

7

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:

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

answered Oct 08 '13 at 16:41

Jamie G

81
1
2

This approach doesn't work if both arguments are null. ` DECLARE @F1 AS BIT = NULL; DECLARE @F2 AS BIT = NULL; SELECT IIF((@F1 <> @F2) OR (NULLIF(@F1, @F2) IS NOT NULL) OR (NULLIF(@F2, @F1) IS NOT NULL), N'Yes', N'No') AS [Not Equal ?]; ` – CalvinDale Jun 26 '22 at 17:19

score 6 · Answer 9 · answered Jul 02 '09 at 15:45

6

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.

answered Jul 02 '09 at 15:45

Remus Rusanu

288,378
40
442
569

1

Or a unique constraint, since he's indicated that there's already a primary key – bdukes Jul 02 '09 at 15:59

score 2 · Answer 10 · answered Nov 04 '17 at 23:05

2

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.

answered Nov 04 '17 at 23:05

drowa

682
5
13

2

NOTE: SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison. Source: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-ver16 – Mark Robinson Jul 15 '22 at 11:03

score 1 · Answer 11 · answered Jan 17 '20 at 22:41

Above, @drowa shows a verbose approach that I agree with. It's good because it avoids the 3-value logic problem. Many of the other approaches provided here will fail in subtle and unexpected ways when negated because they're treating null as equivalent to false which it is not.

However, I have a workflow that I find makes it convenient-ish, here's a regex. Given code of the form

(leftSide <=> rightSide)

regex find this:

\(([a-zA-Z0-9_.@]+)\s*<=>\s*([a-zA-Z0-9_.@]+)\)

and replace with this:

(/*$1 <=> $2*/ ($1 IS NULL AND $2 IS NULL) OR ($1 IS NOT NULL AND $2 IS NOT NULL AND $1 = $2))

So I write the (leftSide <=> rightSide) code and apply the above regex transformation to get the expanded form. It'd be nicer if MSSQL offered some kind of macro expansion so I wouldn't have to do it manually, but it doesn't.

@Drowa's answer quoted for reference:

Equals comparison:

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

Not Equal To comparison: Just negate the Equals comparison 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.

score 1 · Answer 12 · answered Jul 28 '22 at 01:20

From SQL Server 2022 the "null safe" version of

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

would be

WHERE 
    MY_FIELD1 IS NOT DISTINCT FROM @IN_MY_FIELD1  AND
    MY_FIELD2 IS NOT DISTINCT FROM @IN_MY_FIELD2  AND
    MY_FIELD3 IS NOT DISTINCT FROM @IN_MY_FIELD3  AND 
    MY_FIELD4 IS NOT DISTINCT FROM @IN_MY_FIELD4  AND
    MY_FIELD5 IS NOT DISTINCT FROM @IN_MY_FIELD5  AND
    MY_FIELD6 IS NOT DISTINCT FROM @IN_MY_FIELD6

IS [NOT] DISTINCT FROM (Transact-SQL)

score 0 · Answer 13 · answered Jul 02 '09 at 15:45

0

You will have to use IS NULL or ISNULL. There really isn't a away around it.

answered Jul 02 '09 at 15:45

William Edmondson

3,619
3
32
41

score 0 · Answer 14 · edited May 11 '21 at 06:59

0

NULLIF(TARGET.relation_id, SOURCE.app_relation_id) IS NULL Simple solution

edited May 11 '21 at 06:59

Dale K

25,246
15
42
71

answered Dec 29 '14 at 14:56

user3763117

327
1
5
18

4

This does not work if the first value is null but the second value is not. – Aaroninus Jul 07 '15 at 14:09

score -2 · Answer 15 · answered Jul 02 '09 at 16:34

-2

Did you check NULLIF? http://msdn.microsoft.com/en-us/library/ms177562.aspx

answered Jul 02 '09 at 16:34

THEn

1,920
3
28
35

1

This does not work in OP's case. Both: nullif(cast(null as varchar(max)), 'X') and nullif(cast(null as varchar(max)), null) return null – Warren Jan 21 '15 at 19:16

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

15 Answers15

Linked

Related