问题描述
我通过两种方式实现了这个查询:
I have this query implemented in two ways:
SELECT race1, mode1
FROM organization
WHERE condition = 1 LIMIT 1
UNION ALL
(SELECT race2, mode2
FROM organization
WHERE condition = 1 LIMIT 1)
UNION ALL
(SELECT race, mode FROM organization_new
WHERE PK_Id = 1)
还有
SELECT race1, mode1
FROM organization
WHERE condition = 1 LIMIT 1
UNION ALL
SELECT race2, mode2
FROM organization
WHERE condition = 1 LIMIT 1
UNION ALL
SELECT race, mode FROM organization_new
WHERE PK_Id = 1
如您所见,区别仅在于第一个查询中的括号.在第一个查询中,我得到了预期的结果(从所有三个选择中获取所有值,不需要解释).但是当我继续第二个查询时,我得到了想要的结果,但不是预期的,这只是第一个 select 中满足 WHERE
子句的值.也就是说,如果存在 race1, mode1
其中 condition = 1
,那么我只会得到那个结果.如果没有,那么我得到 race2, mode2
其中 condition = 1
.如果第二个 select 语句为空,那么我会根据第三个 select 语句获取值.如果没有提供括号,为什么 UNION ALL
表现得像一个 OR
?
As you can see, the difference is only in the parentheses in the first query. In the first query, I get the results as expected (gets all the values from all three selects, no explanation needed). But when I go ahead with the second query, I get results as desired but not as expected, that is only the values from first select which meets the WHERE
clause. That is if there is a race1, mode1
where condition = 1
, then I get only that result. If there isn't then I am getting race2, mode2
where condition = 1
. If even the second select statement is empty, then I get the values according to third select statement. Why is UNION ALL
behaving like an OR
if no parentheses are provided?
我使用的是 MySQL 5.0.51a
I am using MySQL 5.0.51a
推荐答案
那是因为你在使用 LIMIT.
That is because you are using LIMIT.
MySql 参考说如果你想在单个选择上使用 ORDER BY 或 LIMIT,那么你必须用括号将你的选择括起来.
MySql reference says that if you want to use ORDER BY or LIMIT on individual selects, then you have to surround your selects with parentheses.
示例(来自 MySQL 参考):
Example (From MySQL reference):
要将 ORDER BY 或 LIMIT 应用于单个 SELECT,请将子句放在包含 SELECT 的括号内:
To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:
(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
资源可以在这里找到:http://dev.mysql.com/doc/refman/5.0/en/union.html
更改了参考链接,因为之前的链接是针对 5.5 版的.但答案没有改变.
Changed reference link because previous one was for version 5.5 . But answer didn`t change.
这篇关于为什么带括号和不带括号的 UNION ALL 表现不同?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!