What does the SQL standard say about parentheses in SQL UNION/EXCEPT/INTERSECT statements?

Question

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

(select_clause_A) UNION (select_clause_B)

SQLite doesn't like the parentheses for those statements (see the 'compound operater'): http://www.sqlite.org/lang_select.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

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

Answer 1

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

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.

MySQL supports:

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

Standard SQL only allows:

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

Answer 2

The parens should not be required for MySQL, and from what I can tell reading the spec are not supposed to be there.

MySQL is also nonstandard in that it supports an ORDER BY in each "part" of the union, so if you're writing it for non-MySQL you can't do that anyway.