Does UNION ALL guarantee the order of the result set

sql

best-practices

join

order-by

byAnton Shumikhin·Feb 6, 2025

No, UNION ALL does not ensure result ordering. If you need a guaranteed order, don't hesitate to dress your query with a ORDER BY hat. Here's a rapid-fire example:

SELECT 'A' AS Col     -- Just minding my own business
UNION ALL
SELECT 'B' AS Col     -- Oh hello there, mind if I join you?
ORDER BY Col;         -- Now, let's have some manners!

In absence of ORDER BY, let's not fool ourselves — order is as stable as a house of cards.

Acing the deterministic order

To form an unshakable backbone for your SQL statements, an explicit sort order is your best bet. Without an ORDER BY clause, the result set is susceptible to changes arising from data edits, server recompiles, query optimizations and more. Thus, whenever utilizing UNION ALL, stick to a proven formula — always append an ORDER BY.

Maintaining order in complex queries

When dealing with a zoo of datasets and UNION ALL, maintaining the order can be as tricky as marching ants on a log. Fear not! By incorporating a SortOrder column in each SELECT statement and taming the combined result with a master ORDER BY, you have your parade in line with guaranteed predictability.

A picnic setup of the above idea:

SELECT 1 AS SortOrder, Col1 FROM TableA     -- Ants   
UNION ALL      
SELECT 2 AS SortOrder, Col2 FROM TableB    -- meet   
ORDER BY SortOrder, Col1, Col2;            -- the Log

The perils of omitted ORDER BY

Relying on internal mechanisms for maintaining order is a game of chance. Any changes in the server's configuration, data statistics, or even query recompilations can bring the house down. In other words, ordering without ORDER BY is like building sandcastles and expecting them to resist the tide.

Steps to a well-ordered query

For those who fancy their results in an expected and well-behaved order, follow these steps when working with UNION ALL queries:

Always kick-off the query with an index column, preserving the source and the order intended.
Speckle your query with the ORDER BY clause, referencing all necessary columns to define an explicit sort direction.
Pay attention to how the indexes queried might influence the result set, and synchronize them with your ORDER BY strategy.

Long story short, when your goal is predictability, don't skimp on the ORDER BY sauce!

explain-codes / Sql / Does UNION ALL guarantee the order of the result set

Linked

How to order by with union in SQL?



How to use order by with union all in SQL?



Sql Query - Using Order By in UNION



Combining ORDER BY AND UNION in SQL Server



Sql multiple column ordering