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

我想确保我没有在表中插入重复的行(例如，只有主键不同)。我的所有字段都允许 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

因为 NULL = NULL 不为真。

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

118133 次浏览
小开

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.

小开

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

小开

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.

小开

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.

小开
最佳答案

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.

小开 
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.

小开

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

小开

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

小开

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)
小开

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

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

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.

小开

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.

小开

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.

小开

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)


提交答案