为什么带括号和不带括号的 UNION ALL 表现不同? [英] Why is UNION ALL with and without parenthesis behaving different?

问题描述

我通过两种方式实现了这个查询:

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 表现不同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！