Does UNION ALL Guarantee Result Set Order in SQL Queries?

Order Guarantee in UNION ALL Queries

In database systems, the UNION ALL operator combines the results of multiple queries into a single result set. However, it's important to understand whether UNION ALL guarantees the order of the result set.

Consider the following example:

SELECT 'O'
UNION ALL
SELECT 'R'
UNION ALL
SELECT 'D'
UNION ALL
SELECT 'E'
UNION ALL
SELECT 'R'

The question arises, will the result set always be sorted as "O-R-D-E-R"? The answer is no, there is no inherent order guaranteed by UNION ALL.

To prove this, consider a scenario where the database engine internally executes the queries as follows:

1. SELECT 'O'
2. SELECT 'D'
3. SELECT 'R'
4. SELECT 'E'
5. SELECT 'R'

In this case, the result set will appear as "O-D-R-E-R", which is different from the expected order.

To ensure a specific order, it is necessary to include an ORDER BY clause in the query. For example:

SELECT 'O', 1 SortOrder
UNION ALL
SELECT 'R', 2
UNION ALL
SELECT 'D', 3
UNION ALL
SELECT 'E', 4
UNION ALL
SELECT 'R', 5
ORDER BY SortOrder

By adding a SortOrder field and including an ORDER BY clause, you explicitly specify the order in which the results should be displayed.

The above is the detailed content of Does UNION ALL Guarantee Result Set Order in SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!