SQL“或” 操作员。在下面的场景中它是如何工作的？

发布于 2024-07-12 12:59:19 字数 694 浏览 4 评论 0原文

我一直致力于优化查询，但遇到了一种情况，这让我质疑我一直如何使用 SQL 的 OR 运算符。（还有 SQL Server 2000）

我有一个查询，其中条件 (WHERE) 子句看起来像这样：

WHERE (Column1 = @Param1 or Column1 LIKE @Param1 + '%')
AND (@Param2 = '' OR Column2 = @Param2 OR Column2 LIKE @Param2 + '%')

现在，我一直理解 SQL 中的 OR 对两个表达式求值。因此，对于左侧表达式计算为 true 的所有记录将与对于右侧表达式计算为 true 的所有记录一起返回。例如：

SELECT * FROM TABLE1
WHERE COL1 = 'Test' OR Col2 = 'Data'

这将返回 COL1 为“测试”的所有记录以及 Col2 为“数据”的任何记录

在上面的示例中，我将 Column2 条件修改为以下内容：

AND(Column2 LIKE ISNULL(@Param2, '') + '%')

突然间，我返回了 0 行。

我是否错误地认为 OR 只计算表达式直到找到 TRUE 结果，或者是否存在会导致 2 个不同结果返回不同结果的条件？

原文

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)

I have a query where the conditional (WHERE) clause looks something like this:

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:

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'

In the example above, I modified the Column2 conditional to the following:

AND(Column2 LIKE ISNULL(@Param2, '') + '%')

All of the sudden, I get 0 rows returned.

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

℉絮湮 2024-07-19 12:59:19

“OR 仅评估表达式，直到找到 TRUE 结果”

它只需要这样做，但这不是你的问题（实际上这就是在原始情况下拯救你的原因）。您的两个查询并不真正等同。

我认为 Column2 中有 NULL ，这永远不会导致 (Column2 LIKE ISNULL(@Param2, '') + '%') 为 true - 并且在您的原始版本 @Param2 = '' 掩盖了这种情况，因为它是真的（有时）

也许：

(ISNULL(Column2, '') LIKE ISNULL(@Param2, '') + '%')

记住 NULL 的三值逻辑：

TRUE and UNKNOWN: UNKNOWN
TRUE or UNKNOWN: TRUE

FALSE and UNKNOWN: FALSE
FALSE or UNKNOWN: UNKNOWN

但我不确定您的优化是否真的有帮助。

"OR only evaluates expressions until it find a TRUE result"

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)

Perhaps:

(ISNULL(Column2, '') LIKE ISNULL(@Param2, '') + '%')

Remember the three-valued logic for NULLs:

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.

回复收藏 0 原文

仙女 2024-07-19 12:59:19

OR 并不包罗万象，尤其是当它在括号中时。更大的因为是：WHERE X AND Y。 X 和 Y 本身是使用 OR 的布尔表达式这一事实并不重要：它们分别计算，然后将结果输入 AND 运算符。

[编辑]：
再次阅读，我可能误解了你的问题。考虑到这一点，我将不得不选择另一个答案，因为 NULL LIKE '%' 返回 NULL，在本例中与 false 相同。你可以尝试这个：

COALESCE(Column2,'') LIKE COALESCE(@param2,'') + '%'

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.

[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:

COALESCE(Column2,'') LIKE COALESCE(@param2,'') + '%'

回复收藏 0 原文

绻影浮沉 2024-07-19 12:59:19

仅供参考，您可以做一些非常简单的实验来了解并非所有条件都必须进行评估。

我在 Oracle 中做到了这一点，但我希望您在 SQL Server 中也会得到类似的结果。

dev> select * from dual where 1=1 or 1/0 = 3;

D
-
X

不得评估 OR 之后的条件，因为它会引发被零除错误。

这种布尔运算符的处理通常称为“短路”，据我所知，这在现代语言中是相当标准的。它也可以应用于 AND 表达式——如果第一个条件为 false，则评估第二个条件就没有意义，因为整个表达式不可能为 TRUE。

不管怎样，正如凯德所说，你真正的问题可能是对 NULL 的错误处理。

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.

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.

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.

More info: http://en.wikipedia.org/wiki/Short-circuit_evaluation

Anyway as Cade said, your real problem is probably mishandling of NULLs.

回复收藏 0 原文