Does `UNION ALL` Preserve the Order of Rows in SQL Result Sets?

Does UNION ALL Guarantee Order in Result Sets?

In SQL, the UNION ALL operator combines the results of multiple SELECT statements. However, it does not guarantee the order of the results. In the absence of an ORDER BY clause, the order of the rows is undefined and may vary.

Consider the example provided in the question:

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

It is possible that the results will be displayed in the order "O-R-D-E-R," but there is no guarantee. The order may differ depending on factors such as the database engine, data distribution, and other optimizations.

To enforce a specific order, an ORDER BY clause must be used. For the given example, the SortOrder column can be added to each SELECT statement:

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

This query will explicitly sort the results in the order "O-R-D-E-R." Please note that adding the SortOrder column is only necessary if the desired order is different from the default order (which is undefined in this case).

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