对于SQL UNION/EXCEPT/INTERSECT语句中的括号，SQL标准怎么说? [英] What does the SQL standard say about parentheses in SQL UNION/EXCEPT/INTERSECT statements?

问题描述

我正在尝试编写一个在MySQL和SQLite上都可以使用的SQL UNION.

I'm trying to write a SQL UNION that works on both MySQL and SQLite.

(select_clause_A) UNION (select_clause_B)

SQLite不喜欢这些语句的括号(请参见 复合运算符"): http://www.sqlite.org/lang_select.html

SQLite doesn't like the parentheses for those statements (see the 'compound operater'): http://www.sqlite.org/lang_select.html

不幸的是，如果您使用 "order by"子句: http://dev.mysql.com/doc/refman/5.0/en /union.html

Unfortunately I think MySQL requires parentheses if you use an 'order by' clause: http://dev.mysql.com/doc/refman/5.0/en/union.html

有人碰巧知道哪个数据库遵循SQL标准吗?我想它们可能都是...

Does anyone happen to know which database is following the SQL standard? I suppose its possible they both are...

推荐答案

在UNION语句中无需使用方括号/括号.

There's no need for brackets/parenthesis in a UNION statement.

MySQL是我目前所知道的唯一一个，它允许您定义特定于每个查询的ORDER BY和LIMIT子句，只要查询被括在方括号中-标准SQL仅允许ORDER BY为最终结果. GROUP BY和HAVING子句特定于组成UNION语句的每个查询.

MySQL is the only one I'm aware of at this moment, which allows you to define ORDER BY and LIMIT clauses specific to each query as long as the query is enclosed in brackets -- standard SQL only allows a ORDER BY for the final result. The GROUP BY and HAVING clauses are specific to each query that makes up the UNION'd statement.

 (SELECT a.column
    FROM A_TABLE a
ORDER BY a.column DESC)
UNION
SELECT b.column
  FROM B_TABLE b

...如果您希望/需要移植到其他数据库，这将不会带来麻烦.

...which will cause no end of grief if you want/need to port to other databases.

SELECT a.column
  FROM A_TABLE a
UNION
SELECT b.column
  FROM B_TABLE b
ORDER BY column DESC

这篇关于对于SQL UNION/EXCEPT/INTERSECT语句中的括号，SQL标准怎么说?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！