What is the best option to use in sql?
在sql中使用的最佳选项是什么？
ISNULL(x, 0) = 0
or
要么
(x is null OR x = 0)
Thanks
谢谢
4 个解决方案
#1
4
Unless you are experiencing any performance issues I would suggest using which ever is easiest for you to read. COALESCE and ISNULL are identical when there are just two values (i.e. NULL and 0)
除非您遇到任何性能问题，否则我建议您使用最简单易读的内容。当只有两个值（即NULL和0）时，COALESCE和ISNULL是相同的
#2
2
The "best" one to use depends on the query you are writing, this is from the COALESCE page in BOL
要使用的“最佳”取决于您正在编写的查询，这是来自BOL中的COALESCE页面
ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.
ISNULL和COALESCE虽然相同，但行为可能不同。涉及具有非空参数的ISNULL的表达式被认为是NOT NULL，而涉及具有非空参数的COALESCE的表达式被认为是NULL。
So without knowing more about the whole query they are probably equivalent but YMMV and without actually testing you cannot second guess which will be faster.
因此，在不了解更多关于整个查询的情况下，它们可能是等效的但YMMV并且没有实际测试，您无法再猜测哪个更快。
Also, the comparison of ISNULL to COALESCE is only useful when you have to choose between two values. As soon as there are more than two you will need to use COALESCE.
此外，ISNULL与COALESCE的比较仅在您必须在两个值之间进行选择时才有用。一旦有两个以上，您将需要使用COALESCE。
#3
0
In this situation, my pattern is to use coalesce:
在这种情况下，我的模式是使用coalesce：
SELECT @SomeVariable = COALESCE(@SomeInputVariable, 0)
OR
要么
SELECT * FROM Table WHERE Column > COALESCE(@SomeInputVariable, 0)
#4
0
They should be identical, but I actually did some tests on some very big select statements and isnull(x,0)=0 similar kind of condition proved to be faster.
它们应该是相同的，但我实际上对一些非常大的select语句进行了一些测试，并且isnull（x，0）= 0类似的条件被证明更快。
The logical conclusion out of this is that the SQL team optimized this somehow.
这个逻辑结论是SQL团队以某种方式对其进行了优化。
Not to the point, coalesce should be used over isnull as coalesce is the standard sql method while isnull is MS specific. That means that in the future (depending on MS thoughts) isnull might be deprecated.
不是这一点，coalesce应该用于isnull，因为coalesce是标准的sql方法，而isnull是MS特定的。这意味着将来（取决于MS的想法）isnull可能会被弃用。
#1
4
Unless you are experiencing any performance issues I would suggest using which ever is easiest for you to read. COALESCE and ISNULL are identical when there are just two values (i.e. NULL and 0)
除非您遇到任何性能问题，否则我建议您使用最简单易读的内容。当只有两个值（即NULL和0）时，COALESCE和ISNULL是相同的
#2
2
The "best" one to use depends on the query you are writing, this is from the COALESCE page in BOL
要使用的“最佳”取决于您正在编写的查询，这是来自BOL中的COALESCE页面
ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.
ISNULL和COALESCE虽然相同，但行为可能不同。涉及具有非空参数的ISNULL的表达式被认为是NOT NULL，而涉及具有非空参数的COALESCE的表达式被认为是NULL。
So without knowing more about the whole query they are probably equivalent but YMMV and without actually testing you cannot second guess which will be faster.
因此，在不了解更多关于整个查询的情况下，它们可能是等效的但YMMV并且没有实际测试，您无法再猜测哪个更快。
Also, the comparison of ISNULL to COALESCE is only useful when you have to choose between two values. As soon as there are more than two you will need to use COALESCE.
此外，ISNULL与COALESCE的比较仅在您必须在两个值之间进行选择时才有用。一旦有两个以上，您将需要使用COALESCE。
#3
0
In this situation, my pattern is to use coalesce:
在这种情况下，我的模式是使用coalesce：
SELECT @SomeVariable = COALESCE(@SomeInputVariable, 0)
OR
要么
SELECT * FROM Table WHERE Column > COALESCE(@SomeInputVariable, 0)
#4
0
They should be identical, but I actually did some tests on some very big select statements and isnull(x,0)=0 similar kind of condition proved to be faster.
它们应该是相同的，但我实际上对一些非常大的select语句进行了一些测试，并且isnull（x，0）= 0类似的条件被证明更快。
The logical conclusion out of this is that the SQL team optimized this somehow.
这个逻辑结论是SQL团队以某种方式对其进行了优化。
Not to the point, coalesce should be used over isnull as coalesce is the standard sql method while isnull is MS specific. That means that in the future (depending on MS thoughts) isnull might be deprecated.
不是这一点，coalesce应该用于isnull，因为coalesce是标准的sql方法，而isnull是MS特定的。这意味着将来（取决于MS的想法）isnull可能会被弃用。