How to Properly Order Results from a SQL UNION Query?

SQL UNION query sorting

When using SQL queries to combine data from multiple tables using the UNION operator, you often need to sort the results. However, simply adding an ORDER BY clause at the end of the query may cause errors.

Consider 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 attempts to sort the results of a UNION operation, but it throws an exception because the ORDER BY clause is not allowed outside the parentheses surrounding the individual SELECT statements.

To correctly order a UNION query, you need to include an ORDER BY clause in every SELECT statement. This ensures that the individual data sets are sorted before they are combined. Here's how to rewrite the above query to order the results correctly:

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

With this approach, the individual data sets are sorted within parentheses before being combined using UNION ALL. The combined results are then ordered according to the requirements of the ORDER BY clause in the outermost SELECT statement.

The above is the detailed content of How to Properly Order Results from a SQL UNION Query?. For more information, please follow other related articles on the PHP Chinese website!