为什么在 SQL 查询中添加括号会导致结果发生变化？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8172455/
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
Why would the addition of parentheses in SQL query cause the results to change?
提问by Sam F.
When I execute the following query, even though there are 11 records that match, none are returned as written. However, if I remove the parentheses on lines 6 and 9, all 11 records are returned as expected.
当我执行以下查询时，即使有 11 条记录匹配，也没有按写入方式返回。但是，如果我删除第 6 行和第 9 行上的括号，则会按预期返回所有 11 条记录。
1 select obj_id, obj_title, UI_DISPLAYNAME
2 from PITS_OBJECT
3 LEFT OUTER JOIN ui_displayname_view ON obj_create_ui_id = UI_ID
4 where
5 /* w/ parens, no results, w/o parens, expected results */
6 (
7 Upper( UI_DISPLAYNAME ) LIKE Upper( '%smith%' )
8 OR Upper( OBJ_TITLE ) LIKE Upper( '%smith%' )
9 )
10 /* end w/ parents, no results.... */
11 AND OBJ_ID IN (select obj_id from PITS_OBJECT where
12 (UPPER( OBJ_TITLE ) LIKE UPPER( '%smith%' ))
13 AND obj_id in( select sa_obj_id as obj_id from security_access
14 where sa_type_id = 494
15 and sa_usrgrp_id = 35
16 and sa_usrgrp_type_id = 230
17 union
18 select sa_obj_id from security_access
19 where sa_type_id = 494
20 and sa_usrgrp_type_id = 231
21 and sa_usrgrp_id in ( select ug_gi_id from user_group where ug_ui_id = 35)) )
Why would this matter? Wouldn't the OR statement mean that one or the other must be true? What am I missing here?
为什么这很重要？OR 语句不是意味着其中一个必须为真吗？我在这里缺少什么？
回答by ean5533
Three words: order of operations. It's like you learned in math, certain operators take precedence over others (like multiplying comes before adding) unless you use parentheses to force it your way. In this case, ANDhas a higher precedence than OR.
三个字：操作顺序。就像您在数学中学到的一样，某些运算符优先于其他运算符（例如乘法在加法之前），除非您使用括号来强制它。在这种情况下，AND具有比 更高的优先级OR。
Without adding in your own parentheses, your WHEREclause gets evaluated like this:
不添加您自己的括号，您的WHERE子句会像这样评估：
Upper( UI_DISPLAYNAME ) LIKE Upper( '%smith%' )
OR
(Upper( OBJ_TITLE ) LIKE Upper( '%smith%' )
AND OBJ_ID IN (select obj_id from PITS_OBJECT where
...)
But when you manually add in those parentheses, you're forcing the ORto be evaluated first.
但是，当您手动添加这些括号时，您会强制OR首先评估 。
(Upper( UI_DISPLAYNAME ) LIKE Upper( '%smith%' )
OR Upper( OBJ_TITLE ) LIKE Upper( '%smith%' ))
AND OBJ_ID IN (select obj_id from PITS_OBJECT where
...
Edit:I should directly answer your question about why you're getting back more data. The reason is because, without the parentheses, the engine will short-circuit its check if it finds that line 7 is true. In other words, it will include all records where Upper( UI_DISPLAYNAME ) LIKE Upper( '%smith%' ), regardless of the other criteria.
编辑：我应该直接回答您关于为什么要取回更多数据的问题。原因是，如果没有括号，引擎会在发现第 7 行为真时短路它的检查。换句话说，它将包括所有记录 where Upper( UI_DISPLAYNAME ) LIKE Upper( '%smith%' )，而不管其他标准。
When you add in those parentheses, the logic changes. It will include records where Upper( UI_DISPLAYNAME ) LIKE Upper( '%smith%' ) OR Upper( OBJ_TITLE ) LIKE Upper( '%smith%' ), and then it checks that the record ALSO satisfies the inner select that starts on line 12. Those extra records don't show up because they're not meeting the criteria of that inner select.
当您添加这些括号时，逻辑会发生变化。它将包括记录 where Upper( UI_DISPLAYNAME ) LIKE Upper( '%smith%' ) OR Upper( OBJ_TITLE ) LIKE Upper( '%smith%' )，然后检查该记录是否也满足从第 12 行开始的内部选择。那些额外的记录不会出现，因为它们不符合该内部选择的标准。
回答by Pursuit
Your where clause is currently analogous to the following:
您的 where 子句目前类似于以下内容：
(A || B) && C && D
Without parenthesis, it is as follows
不加括号，如下
A || B && C && D
Which is equivalent to
这相当于
A || (B && (C && D))
回答by Raihan
It has to do with the precedence of ANDand ORoperators. ANDhas higher precedence.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/conditions001.htm#i1034834
它与ANDandOR运算符的优先级有关。AND有更高的优先级。
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/conditions001.htm#i1034834
回答by Maess
Because you have changed your where statement to always return records where
Upper( OBJ_TITLE ) LIKE Upper( '%smith%' )is true, regardless of your next AND
因为您已将 where 语句更改为始终返回记录 where
Upper( OBJ_TITLE ) LIKE Upper( '%smith%' )为真，无论您的下一个 AND
回答by Jemaclus
This is basic order of operations. The parentheses mean that everything is tested before anything else is tested. The value returned after evaluating the items in the parentheses will then be used to evaluate the rest. Since AND takes precedence over OR, the parentheses make SQL evaluate the ORs first.
这是基本的操作顺序。括号表示在测试任何其他内容之前先测试所有内容。评估括号中的项目后返回的值将用于评估其余项目。由于 AND 优先于 OR，括号使 SQL 首先评估 OR。
