How Can I Correctly Use ORDER BY in UNION Queries to Avoid Errors?

Implement Order By

in UNION query

In database programming, a UNION query combines results from different tables or subqueries. However, using ORDER BY directly in a UNION statement may cause exceptions when data is extracted from multiple tables and the results need to be sorted. As shown in the following example:

<code class="language-sql">SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY table2.field1</code>

This query will fail, especially when using the MS Access Jet database engine. The solution lies in enclosing each subquery in its own ORDER BY clause:

<code class="language-sql">SELECT * FROM 
(
  SELECT table1.field1 FROM table1 ORDER BY table1.field1
) DUMMY_ALIAS1

UNION ALL

SELECT * FROM
( 
  SELECT table2.field1 FROM table2 ORDER BY table2.field1
) DUMMY_ALIAS2</code>

The ORDER BY statement will be applied to each subquery individually by enclosing the subquery in parentheses and assigning it an alias (DUMMY_ALIAS1 and DUMMY_ALIAS2). This ensures correct ordering of the combined results without causing errors.

The above is the detailed content of How Can I Correctly Use ORDER BY in UNION Queries to Avoid Errors?. For more information, please follow other related articles on the PHP Chinese website!