Search code examples
mysqlselectparenthesesunion-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)

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

Solution

  • 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.