Does UNION ALL Guarantee Result Set Order?

Can UNION ALL Ensure Result Set Order?

When utilizing UNION ALL to combine multiple result sets, a common question arises: Is the order of the combined results guaranteed?

• Question: Can we rely on the following query to return a sorted result set: "SELECT 'O' UNION ALL SELECT 'R' UNION ALL SELECT 'D' UNION ALL SELECT 'E' UNION ALL SELECT 'R'"? Specifically, will it consistently return "O-R-D-E-R"?
• Answer: Unfortunately, relying on UNION ALL alone does not provide any inherent ordering. The result order can vary. To ensure a specific order, it is necessary to utilize the ORDER BY clause.

Here's an example that demonstrates how to achieve the desired ordering:

SELECT 'O', 1 AS 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 column and ordering the results by it, we can guarantee the desired order.

In conclusion, while UNION ALL provides a convenient way to combine result sets, it does not guarantee their order. To ensure a specific order, it is essential to use ORDER BY.

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