nawfal nawfal - 4 months ago 14
MySQL Question

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)


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

Answer

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.

Comments