SQL“或”运算符。在以下场景中它是如何工作的？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/473943/
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
SQL 'Or' operator. How does it work in the following scenario?
提问by JamesEggers
I've been working on optimizing a query and have ran into a situation that's making me question how I've always used SQL's OR operator. (SQL Server 2000 also)
我一直致力于优化查询，但遇到了一种情况，这让我怀疑我一直是如何使用 SQL 的 OR 运算符的。（SQL Server 2000 也是）
I have a query where the conditional (WHERE) clause looks something like this:
我有一个查询，其中条件 (WHERE) 子句如下所示：
WHERE (Column1 = @Param1 or Column1 LIKE @Param1 + '%')
AND (@Param2 = '' OR Column2 = @Param2 OR Column2 LIKE @Param2 + '%')
Now, I've always understood that OR in SQL evaluated both expressions. So all records that evaluated true for the left expression would be returned along with all records that evaluated true on the right expression. For example:
现在，我一直明白 SQL 中的 OR 计算两个表达式。因此，所有对左表达式求值为 true 的记录将与对右表达式求值为 true 的所有记录一起返回。例如：
SELECT * FROM TABLE1
WHERE COL1 = 'Test' OR Col2 = 'Data'
This would return back all records where COL1 is'Test' as well as any record where Col2 is 'Data'
这将返回 COL1 为“Test”的所有记录以及 Col2 为“Data”的任何记录
In the example above, I modified the Column2 conditional to the following:
在上面的示例中，我将 Column2 条件修改为以下内容：
AND(Column2 LIKE ISNULL(@Param2, '') + '%')
All of the sudden, I get 0 rows returned.
突然之间，我返回了 0 行。
Have I been mistaken in that OR only evaluates expressions until it find a TRUE result or is there a condition that would cause the 2 different to return different results?
我是否误认为 OR 仅对表达式求值，直到找到 TRUE 结果，或者是否存在导致 2 个不同结果返回不同结果的条件？
回答by Cade Roux
"OR only evaluates expressions until it find a TRUE result"
“OR 只计算表达式，直到找到 TRUE 结果”
It only has to, but that's not your problem (actually this is what was saving you in your original case). Your two queries are not really equivalent.
它只需要，但这不是您的问题（实际上，这就是您在原始案例中拯救您的原因）。您的两个查询实际上并不等效。
I'm thinking you have NULLs in Column2 which will never cause (Column2 LIKE ISNULL(@Param2, '') + '%')to be true - and in your original version the @Param2 = ''was masking this case, since it IS true (sometimes)
我认为您NULL在 Column2 中有s 永远不会(Column2 LIKE ISNULL(@Param2, '') + '%')成为真的 - 在您的原始版本中@Param2 = ''，它掩盖了这种情况，因为它是真的（有时）
Perhaps:
也许：
(ISNULL(Column2, '') LIKE ISNULL(@Param2, '') + '%')
Remember the three-valued logic for NULLs:
记住 NULL 的三值逻辑：
TRUE and UNKNOWN: UNKNOWN
TRUE or UNKNOWN: TRUE
FALSE and UNKNOWN: FALSE
FALSE or UNKNOWN: UNKNOWN
But I'm not sure your optimization is really helping.
但我不确定您的优化是否真的有帮助。
回答by Joel Coehoorn
OR is not all-encompassing, especially as it's in parentheses. What you have in a larger since is: WHERE X AND Y. That fact that X and Y are themselves boolean expressions that make use of an OR is not important: they are evaluated separately and then results are fed to the AND operator.
OR 并非包罗万象，尤其是在括号中。你在更大的范围内拥有的是：WHERE X AND Y。X 和 Y 本身是使用 OR 的布尔表达式这一事实并不重要：它们被单独计算，然后结果被馈送到 AND 运算符。
[edit]:
Reading again, I may have misunderstood your question. With that in mind, I'll have to go with the other answer, because NULL LIKE '%'returns NULL, which is the same as false in this case. You might try this instead:
[编辑]：
再次阅读，我可能误解了您的问题。考虑到这一点，我将不得不采用另一个答案，因为NULL LIKE '%'返回 NULL，在这种情况下与 false 相同。你可以试试这个：
COALESCE(Column2,'') LIKE COALESCE(@param2,'') + '%'
回答by Dave Costa
FYI, there are very simple experiments you can do to see that not all conditions are necessarily evaluated.
仅供参考，您可以通过一些非常简单的实验来了解并非所有条件都必须进行评估。
I did this in Oracle but I expect you would have a similar result in SQL Server.
我在 Oracle 中做了这个，但我希望你在 SQL Server 中会有类似的结果。
dev> select * from dual where 1=1 or 1/0 = 3;
D
-
X
The condition after the OR must not have been evaluated, since it would raise a divide-by-zero error.
OR 之后的条件一定没有被评估，因为它会引发除以零错误。
This handling of boolean operators is generally known as "short-circuiting" and, AFAIK, is pretty standard in modern languages. It can also apply in an AND expression -- if the first condition is false, there is no point in evaluating the second condition, since the whole expression cannot possibly be TRUE.
这种布尔运算符的处理通常被称为“短路”，AFAIK，在现代语言中是非常标准的。它也可以应用于 AND 表达式——如果第一个条件为假，则评估第二个条件没有意义，因为整个表达式不可能为真。
More info: http://en.wikipedia.org/wiki/Short-circuit_evaluation
更多信息：http: //en.wikipedia.org/wiki/Short-circuit_evaluation
Anyway as Cade said, your real problem is probably mishandling of NULLs.
无论如何，正如凯德所说，您真正的问题可能是对 NULL 的处理不当。
回答by Will Dieterich
MS-SQL will evaluate the left hand side first and not proceed unless it needs to.
MS-SQL 将首先评估左侧，除非需要，否则不会继续。
This is the same with the AND connector, the left side will be evaluated and if false the right will not be evaulated.
这与 AND 连接器相同，将评估左侧，如果为 false，则不会评估右侧。
