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

Question

I am trying to write SQL UNION that works on both MySQL and SQLite.

(select_clause_A) UNION (select_clause_B) 

SQLite does not like parentheses for these statements (see "complex statement"): http://www.sqlite.org/lang_select.html

Unfortunately, I think MySQL requires brackets if you use the "in order" clause: http://dev.mysql.com/doc/refman/5.0/en/union.html

Does anyone know which database conforms to the SQL standard? I suppose it's possible, both of them ...

Answer 1

No parentheses / brackets are needed in a UNION statement.

MySQL is the only one that I know about at the moment that allows you to define ORDER BY and LIMIT clauses specific to each query, if the query is enclosed in brackets - standard SQL allows ORDER BY for the final result. GROUP BY and HAVING 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 not cause the end of burning if you want / need to port to other databases.

Standard SQL allows only:

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

Answer 2

Paranas should not be required for MySQL, and from what I can say that reading the specification should not be there.

MySQL is also non-standard in that it supports ORDER BY in each "part" of a join, so if you write it for non-MySQL, you still cannot do it.

Answer 3

You can do this in SQLite:

  select col1, col2.. from ( select col1, col2 from T order by col1 limit 5) union select col1, col2.. from ( select col1, col2 from T order by col2 desc limit 10) 

Not sure about mySQl.