blocks|key|1299369|text|使用ISNULL|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1299370|ISNULL(MY_FIELD1,+'NULL')+=+ISNULL(@IN_MY_FIELD1,+'NULL')|code-block|syntax|javascript|1299371|如果这样做更有意义，您可以将'NULL'更改为类似于'All+Values'的内容。|1299372|应该注意的是，使用两个参数时，ISNULL的工作方式与COALESCE相同，如果您有几个值要测试(即-COALESCE(@IN_MY_FIELD1,+@OtherVal,+'NULL'))，则可以使用它。在第一个非空值之后，COALESCE也会返回，这意味着如果您希望MY_FIELD1为空，那么它(稍微)更快。但是，我发现ISNULL的可读性更好，所以我在这里使用它。|1299373|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms184325.aspx|1|http://msdn.microsoft.com/en-us/library/ms190349.aspx^0|2|6|2|6|0|0|0|E|6|Q|C|0|F|6|R|8|1F|16|34|8|4I|6|R|8|1|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@$9|11|A|12|1|13]]|E|$]]|$1|F|3|G|5|H|7|14|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|15|8|@$9|16|A|17|B|C]|$9|18|A|19|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|1A|8|@$9|1B|A|1C|B|C]|$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]|$9|1H|A|1I|B|C]|$9|1J|A|1K|B|C]]|D|@$9|1L|A|1M|1|1N]]|E|$]]|$1|O|3|-4|5|6|7|1O|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]|W|$5|R|S|T|E|$U|X]]]]

Use <a href="http://msdn.microsoft.com/en-us/library/ms184325.aspx" rel="noreferrer"><code>ISNULL</code></a>:

<pre><code>ISNULL(MY_FIELD1, 'NULL') = ISNULL(@IN_MY_FIELD1, 'NULL')
</code></pre>

You can change <code>'NULL'</code> to something like <code>'All Values'</code> if it makes more sense to do so. 

It should be noted that with two arguments, <code>ISNULL</code> works the same as <a href="http://msdn.microsoft.com/en-us/library/ms190349.aspx" rel="noreferrer"><code>COALESCE</code></a>, which you could use if you have a few values to test (i.e.-<code>COALESCE(@IN_MY_FIELD1, @OtherVal, 'NULL')</code>). <code>COALESCE</code> also returns after the first non-null, which means it's (marginally) faster if you expect MY_FIELD1 to be blank. However, I find <code>ISNULL</code> much more readable, so that's why I used it, here.

blocks|key|1514175|text|您必须使用IS+NULL或ISNULL。在它周围真的没有一个远离它的地方。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1514176|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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

blocks|key|1299408|text|您可以在您的字段上创建一个主键，并让引擎强制执行唯一性。IF+EXISTS逻辑无论如何都是不正确的，因为竞争条件存在缺陷。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1299409|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1514202|text|您可以合并每个值，但这有点让人畏缩：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1514203|++++IF+EXISTS(SELECT+*+FROM+MY_TABLE+WHERE+
++++coalesce(MY_FIELD1,'MF1')+=+coalesce(@IN_MY_FIELD1,'MF1')++AND
++++...
++++BEGIN
++++++++++++goto+on_duplicate
++++END|code-block|syntax|javascript|1514204|您还需要确保coalesced值不是相关列上的其他有效值。例如，如果MY_FIELD1的值可能是'MF1‘，那么这将导致许多虚假的命中。|offset|length|style|CODE|1514205|entityMap^0|0|0|6|9|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You could coalesce each value, but it's a bit wince-inducing:

<pre><code> IF EXISTS(SELECT * FROM MY_TABLE WHERE 
 coalesce(MY_FIELD1,'MF1') = coalesce(@IN_MY_FIELD1,'MF1') AND
 ...
 BEGIN
 goto on_duplicate
 END
</code></pre>

You'd also need to ensure that the <code>coalesced</code> 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.

blocks|key|1299426|text|使用INTERSECT运算符。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1299427|如果您对所有字段都有一个复合索引，那么它就是NULL、-sensitive和efficient：|1299428|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|code-block|syntax|javascript|1299429|请注意，如果您在字段上创建一个UNIQUE索引，您的工作将会简单得多。|1299430|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms188055.aspx^0|2|9|2|9|0|0|M|4|0|0|F|6|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@$9|Z|A|10|1|11]]|E|$]]|$1|F|3|G|5|6|7|12|8|@$9|13|A|14|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|15|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|16|8|@$9|17|A|18|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]]]

Use <a href="http://msdn.microsoft.com/en-us/library/ms188055.aspx" rel="noreferrer"><code>INTERSECT</code></a> operator.

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

<pre><code>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
</code></pre>

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

blocks|key|1299439|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1299440|与IFNULL/COALESCE解决方案相比，Wordy。但不必考虑什么值不会出现在可以用作NULL的替换值的数据中。|unstyled|1299441|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>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
</code></pre>

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.

blocks|key|1514247|text|你检查过NULLIF了吗？http://msdn.microsoft.com/en-us/library/ms177562.aspx|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1514248|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms177562.aspx^0|D|1H|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

Did you check NULLIF?
<a href="http://msdn.microsoft.com/en-us/library/ms177562.aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/ms177562.aspx</a>

blocks|key|1299502|text|使用与@Eric's+answer相同的线条，但不使用'NULL'符号。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1299503|(Field1+=+Field2)+OR+(ISNULL(Field1,+Field2)+IS+NULL)|code-block|syntax|javascript|1299504|只有当两个值都为non-NULL且相等，或者两个值都为NULL时，才会出现这种情况|1299505|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/1075142/how-to-compare-values-which-may-both-be-null-is-t-sql#answer-1075150^0|R|6|3|E|0|0|0|8|8|R|4|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|F|3|G|5|H|7|10|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|11|8|@$9|12|A|13|B|C]|$9|14|A|15|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|16|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

Along the same lines as <a href="https://stackoverflow.com/questions/1075142/how-to-compare-values-which-may-both-be-null-is-t-sql#answer-1075150">@Eric's answer</a>, but without using a <code>'NULL'</code> symbol.

<pre><code>(Field1 = Field2) OR (ISNULL(Field1, Field2) IS NULL)
</code></pre>

This will be true only if both values are <code>non-NULL</code>, and equal each other, or both values are <code>NULL</code>

blocks|key|1514347|text|如果您想对不相等的值进行比较，该怎么办？仅仅在前面提到的比较前使用"+not“是行不通的。我能想到的最好的结果是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1514348|(Field1+<>+Field2)+OR+(NULLIF(Field1,+Field2)+IS+NOT+NULL)+OR+(NULLIF(Field2,+Field1)+IS+NOT+NULL)|code-block|syntax|javascript|1514349|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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:

<pre><code>(Field1 &lt;&gt; Field2) OR (NULLIF(Field1, Field2) IS NOT NULL) OR (NULLIF(Field2, Field1) IS NOT NULL)
</code></pre>

blocks|key|1514356|text|NULLIF(TARGET.relation_id,+SOURCE.app_relation_id)+IS+NULL+Simple+solution|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1514357|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>NULLIF(TARGET.relation_id, SOURCE.app_relation_id) IS NULL Simple solution
</code></pre>

blocks|key|1514416|text|您可以使用SET+ANSI_NULLS来指定等于(=)和不等于(<>)比较运算符在与空值一起使用时的行为。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1514417|entityMap^0|5|E|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

You could use <code>SET ANSI_NULLS</code> in order to specify the behavior of the Equals (=) and Not Equal To (&lt;>) comparison operators when they are used with null values.

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:

<pre><code>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
</code></pre>

since NULL = NULL is not true.

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

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

我想确保我没有在我的表中插入重复的行(例如，只有主键不同)。我所有的字段都允许null，因为我已经决定null表示“所有值”。由于存在空值，我的存储过程中的以下语句无法工作：IF EXISTS(SELECT * FROM MY_TABLE WHERE     MY_FIELD1 = @IN_MY_FIELD1  AND...

问如何比较T-SQL中可能都为null的值
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何比较T-SQL中可能都为null的值EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何比较T-SQL中可能都为null的值
EN