Why is UNION ALL with and without parenthesis behaving different?

Question

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)

And

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

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?

Edit: I am using MySQL 5.0.51a

you have to use same alias for all query ex:- race1 as race, mode1 as mode — sam_13, Apr 18 '12 at 08:34
@sam_13 that's not needed for MySQL, the column name will be the same as that in the first query. In this case `race1` and `mode1`.. — nawfal, Jun 08 '13 at 06:00

score 2 · Accepted Answer · edited Aug 02 '16 at 07:21

2

That is because you are using 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.

Example (From MySQL reference):

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);

Resource can be found here: http://dev.mysql.com/doc/refman/5.0/en/union.html

EDIT: Changed reference link because previous one was for version 5.5 . But answer didn`t change.

edited Aug 02 '16 at 07:21

Coloured Panda

3,293
3
20
30

answered Apr 18 '12 at 08:38

Jānis Gruzis

905
1
10
25

Ok I get it thanks for that. My question is if I do not want the union of all, but just like how it is working with my second query (with no brackets) like an `OR` can I safely go ahead with the second query? – nawfal Apr 18 '12 at 09:17
My guess is that this wouldnt be really valid MySQL. Since I could not found this behavior in reference i posted and how I understand your query is working like thet only because of LIMIT. When parser parses code it SELECTs the data and sees LIMIT. Since select is not in parentheses it is understood as end of query (look in reference where is written about LIMIT and ORDER BY for all UNION). And if SELECT is empty it optimizes and jumps over SELECT to the next one. To check if this works try to change 2. and 3. SELECT places. Then try if query can go to 3. if 1. and 2. is empty. – Jānis Gruzis Apr 18 '12 at 09:48
@Janis thanks.. Yes it works, without that much testing I wouldn't ask you :) I can even do this: `(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) LIMIT 1;` as mentioned in original documentation – nawfal Apr 18 '12 at 09:57

Why is UNION ALL with and without parenthesis behaving different?

1 Answers1

Linked