问题描述:

I am writing a query builder in both C# and Java, and am concerned about generating UNION statements. I am aware that each individual statement in a UNION clause cannot contain an ORDER BY clause, and this must instead be placed at the end of the complete union statement. Are there any other conditions I should be aware of? i.e. conditions which usually apply to single select statements, however cause problems when placed in a UNION statement? As far as I can tell, each individual statement can contain GROUP BYs, WHEREs and HAVINGs. Have I missed something?

网友答案:

According to the SQL syntax specification, a select statement returning multiple rows is a query expression followed by an optional ORDER BY clause:

<direct_select_statement:multiple_rows> ::=
    <query_expression> [ <order_by_clause> ]

In turn, query expression is defined as

<query_expression> ::= <non-join_query_expression> | <joined_table>

and non-join query expression is where the UNION is specified:

<non-join_query_expression>    ::= 
     <non-join_query_term>
 |     <query_expression> UNION [ ALL ] [ <corresponding_spec> ] <query_term>
 |     <query_expression> EXCEPT [ ALL ] [ <corresponding_spec> ] <query_term>

Therefore, using ORDER BY in an individual query expression in a UNION ALL clause is syntactically incorrect; there are no other similar query clauses that must not be used in a UNION.

相关阅读:
Top