How to Properly Order Results from a SQL UNION?

UNION and sorting in SQL

In SQL, the UNION operator allows us to combine multiple SELECT statements into a single result set. However, sorting combined data can be tricky when each SELECT statement may return different data.

Consider the following query that retrieves data from the Student table based on two criteria:

<code class="language-sql">SELECT id, name, age
FROM Student
WHERE age > 20
UNION
SELECT id, name, age
FROM Student
WHERE Name LIKE "%a%";</code>

We want to sort the combined result set by the name column. Unfortunately, the following query does not work as expected:

<code class="language-sql">SELECT id, name, age
FROM Student
WHERE age > 20
ORDER BY name;</code>

In this query, the ORDER BY clause is applied to the combined results of the WHERE conditions, rather than the complete result set of the UNION. Therefore, the data is not sorted.

To sort the combined result set by name, simply move the ORDER BY clause outside the UNION operator:

<code class="language-sql">SELECT id, name, age
FROM Student
WHERE age > 20
UNION
SELECT id, name, age
FROM Student
WHERE Name LIKE "%a%"
ORDER BY name;</code>

In this modified query, the ORDER BY clause is applied to the complete result set, ensuring that the combined data is sorted correctly.

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