mysql - 将 SQL 查询包装在括号中会导致语法错误

Question

以下查询有效:

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

但是一旦我用括号括起查询，它就不再起作用了:

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

导致语法错误:

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

将其他选择查询包裹在括号中不会导致问题，例如:

(SELECT DISTINCT number FROM johnson.employee);

这些有什么区别？

Answer 1

这与 MySQL 实现其 SQL 语法的方式有关。

A <query>可以是:

SELECT ... [ UNION <query>]

或

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

但显然不是

( SELECT ... UNION <query> )

阅读sql/sql_yacc.yy如果您需要详细信息，请在 MySQL 源代码中查看。

作为解决方法，您可以这样做:

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