CODEWITHSUNDEEP
mysql
Jimmy C
Jimmy C

Reputation: 9660

Wrapping SQL query in parentheses causes syntax error

The following query works:

SELECT DISTINCT `number`
   FROM `employee`
   WHERE `number` IN
            (SELECT `department_manager`
             FROM `department`)
   UNION
     (SELECT DISTINCT `manager`
      FROM `employee`
      WHERE `manager` IS NOT NULL)

But as soon as I wrap the query with parentheses it doesn't work anymore:

 (SELECT DISTINCT `number`
   FROM `employee`
   WHERE `number` IN
            (SELECT `department_manager`
             FROM `department`)
   UNION
     (SELECT DISTINCT `manager`
      FROM `employee`
      WHERE `manager` IS NOT NULL))

Causing syntax error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION

Wrapping other select queries in parentheses doesn't cause problems, this works for example:

(SELECT DISTINCT number FROM johnson.employee);

What is the difference between these?

Upvotes: 3

Views: 3098

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562230

This has to do with the way MySQL implemented its SQL grammar.

A <query> can be:

SELECT ... [ UNION <query>]

or 

( SELECT ... ) [ UNION <query> ]

But apparently not

( SELECT ... UNION <query> )

Read sql/sql_yacc.yy in the MySQL source code if you want the details.

As a workaround, you can do this:

SELECT * FROM (SELECT ... UNION SELECT ... ) AS t;

Upvotes: 2

Chris Johnson
Chris Johnson

Reputation: 21926

Parentheses around queries are subqueries -- think of this almost like a subroutine, where the inner query is executed first, then its result is evaluated in the context of the outer query. In your second example, you are defining a subquery where no outer query exists, hence a syntax error.

You might be able to transform the second example into valid sql simply by putting a SELECT before the first parenthesis and 'FROM DUMMY' after the closing parenthesis; try it.

Upvotes: 0

Related Questions