IS NULL vs = NULL in where 子句 + SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2490117/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
IS NULL vs = NULL in where clause + SQL Server
提问by Sreedhar
How to check a value IS NULL [or] = @param(where @param is null)
如何检查一个值IS NULL [or] = @param（@param 为空）
Ex:
前任：
Select column1 from Table1
where column2 IS NULL => works fine
If I want to replace comparing value (IS NULL) with @param. How can this be done
如果我想用@param 替换比较值（IS NULL）。如何才能做到这一点
Select column1 from Table1
where column2 = @param => this works fine until @param got some value in it and if is null never finds a record.
How can this achieve?
这如何实现？
回答by Laurence Gonsalves
select column1 from Table1
where (@param is null and column2 is null)
or (column2 = @param)
回答by Dan
I realize this is an old question, but I had the same one, and came up with another (shorter) answer. Note: this may only work for MS SQL Server, which supports ISNULL(expr,replacement).
我意识到这是一个老问题，但我有同样的问题，并想出了另一个（较短的）答案。注意：这可能仅适用于支持 ISNULL(expr,replacement) 的 MS SQL Server。
SELECT column1 FROM table1
WHERE ISNULL(column2,'') = ISNULL(@param,'')
This also assumes you treat NULL and empty strings the same way.
这也假设您以相同的方式处理 NULL 和空字符串。
回答by KM.
There is no "one size fits all" query approach for this, there are subtle performance implications in how you do this. If you would like to go beyondjust making the query return the proper answer, no matter how slow it is, look at this article on Dynamic Search Conditions in T-SQLby Erland Sommarskog
对此没有“一刀切”的查询方法，您如何执行此操作会产生微妙的性能影响。如果你想去超越只是使该查询返回正确的答案，不管它是如何缓慢是，看看这个文章在T-SQLby厄兰Sommarskog动态搜索条件
here is a link to the portion on x = @x OR @x IS NULL
这是x = @x OR @x IS NULL部分的链接
回答by Diego Mendes
WHERE ((COLUMN1 = @PARAM) OR (COLUMN1 IS NULL AND @PARAM IS NULL))
回答by Mike A
Select column1 from Table1
where (column2 IS NULL and @param IS NULL)
or ( column2 IS NOT NULL AND @param IS NOT NULL AND ( column2 = @param ) )
