Does SQL UNION ALL Preserve the Order of Rows?

Order Preservation in SQL UNION ALL Queries

In SQL, UNION ALL is used to combine the results of multiple SELECT statements into a single result set. Unlike the UNION operator, UNION ALL does not eliminate duplicate rows. However, does it guarantee the order of the resulting dataset?

The short answer is no. UNION ALL does not inherently preserve the order of the results. The order in which the SELECT statements are executed and the rows are returned are implementation-specific and can vary between databases and even within different versions of the same database.

To illustrate this, consider the following query:

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

While in some cases, the result set may appear in the "O-R-D-E-R" sequence, there is no guarantee that this will always be the case.

To ensure a specific order in the results, you must explicitly use the ORDER BY clause after the UNION ALL operator. For example, the following query will order the results in ascending alphabetical order:

SELECT 'O'
UNION ALL
SELECT 'R'
UNION ALL
SELECT 'D'
UNION ALL
SELECT 'E'
UNION ALL
SELECT 'R'
ORDER BY 1

By adding a SortOrder column to each SELECT statement, you can also control the order of the results based on custom criteria:

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

Therefore, it is essential to use ORDER BY when specific ordering of the results is required. Otherwise, the order of the result set may vary unpredictably.

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