51

Can I be sure that the result set of the following script will always be sorted like this O-R-D-E-R ?

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

Can it be proved to sometimes be in a different order?

whytheq
  • 34,466
  • 65
  • 172
  • 267
  • 10
    Why would anyone offer an ordering guarantee except when an `ORDER BY` clause is included? - there's an obvious opportunity for parallelism (if sufficient resources are available) to compute each result set in parallel and serve each result row (from the parallel queries) to the client in whatever order each individual result row becomes available. – Damien_The_Unbeliever Apr 02 '13 at 14:17
  • 2
    @Damien_The_Unbeliever and each one may be optimized to sort differently due to other factors not obvious in the query itself. – Aaron Bertrand Apr 02 '13 at 14:35
  • 2
    @Damien_The_Unbeliever . . . In fact, MySQL does guarantee the ordering after a `group by` (" If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns." at http://dev.mysql.com/doc/refman/5.5/en/select.html). I consider this a major limitation on the ability for MySQL to optimize aggregation expressions. It doesn't even support hash aggregations. – Gordon Linoff Apr 02 '13 at 14:41
  • this smells like the foundations for the justification of an anti-SQL rant. meh. – swasheck Apr 05 '13 at 17:17
  • @swasheck ....by who? – whytheq Apr 05 '13 at 19:29
  • 7
    The question is misleadingly marked as a duplicate, so I have voted for re-open (in spite of it being answered correctly already). The question whether UNION needs ORDER BY is very different from whether UNION ALL needs order by, because the former must look for duplicates and remove them, whereas the latter merely glues to result sets together. Hence nobody should ever expect UNION to preserve the order, whereas such would be possible with UNION ALL. (However, as stated already, even UNION ALL is not guaranteed to preserve the order.) – Thorsten Kettner Feb 05 '15 at 10:05

4 Answers4

58

There is no inherent order, you have to use ORDER BY. For your example you can easily do this by adding a SortOrder to each SELECT. This will then keep the records in the order you want:

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

You cannot guarantee the order unless you specifically provide an order by with the query.

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • +1 Better answer before Gordon's edit. – Kermit Apr 02 '13 at 14:23
  • 2
    I hear loads of people saying it will not always be in the same order, and various documentation, but no empirical proof - I'd like to be able to run a query and see it actually spit out the results in a different order. – whytheq Apr 05 '13 at 16:48
  • 9
    @whytheq not seeing a bear in the woods on a hike does not mean there are no bears in the woods. Even if the order *could* be guaranteed, what on earth do you gain from that? Not having to type `ORDER BY` in this one specific case? That's going to save you a ton of productivity over being explicit. – Aaron Bertrand Apr 05 '13 at 16:50
  • 8
    I've never seen anyone get thrown through their windshield in a traffic accident but I still wear my seatbelt just in case. It's free, doesn't hurt, and guarantees the result (no windshield throwing). – JNK Apr 05 '13 at 16:57
  • @whytheq you should write this union as a table expression and then use it with another query. then you'd see that order was not ever guaranteed. – swasheck Apr 05 '13 at 17:16
  • 1
    @whytheq for the record, i've come here because my CTE wasn't preserving the order after multiple UNION ALL of selects over differently sized tables interleaved with single record (nulls) unions to create a visual separation, the best example of optimization, and it leaves the NULL rows in the same position (contiguous) in the final list of the result set, just as if it where execution optimization (in bigquery at least). – Nicolas NZ May 19 '21 at 19:12
40

No it does not. SQL tables are inherently unordered. You need to use order by to get things in a desired order.

The issue is not whether it works once when you try it out. The issue is whether you can trust this behavior. And you cannot. SQL Server does not even guarantee the ordering for this:

select *
from (select t.*
      from t
      order by col1
     ) t

It says here:

When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

A fundamental principle of the SQL language is that tables are not ordered. So, although your query might work in many databases, you should use the version suggested by BlueFeet to guarantee the ordering of results.

Ross Smith II
  • 11,799
  • 1
  • 38
  • 43
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    How ever many times we run it on who ever's machine it still seems to spell "ORDER" ! ...can we prove that sometimes it will be in a different order? – whytheq Apr 02 '13 at 14:16
  • 4
    Don't think in terms of discrete machines. Think in terms of sets. You must must EXPLICITLY declare the order of the set if you need it to be ordered, otherwise it is simply a bag of rows. – Tim Apr 02 '13 at 14:21
  • 7
    @whytheq - it may look the same, but sometimes the 2nd letter and the 5th change positions. – Damien_The_Unbeliever Apr 02 '13 at 14:21
  • @Damien_The_Unbeliever - 2nd and 5th changing order - is that an sql joke? – whytheq Apr 02 '13 at 14:40
  • @Tim "a bag of rows" ...nicely phrased – whytheq Apr 02 '13 at 14:41
  • 4
    @whytheq - it was a form of joke, but also had a serious point - even in your limited testing, you have two rows that appear identical in the result set - so even in your testing, you've not demonstrated that the second result came from the second query, and the fifth result from the fifth query - it could have been the other way around and your observations would still be consistent. – Damien_The_Unbeliever Apr 02 '13 at 14:52
16

Try removing all of the ALLs, for example. Or even just one of them. Now consider that the type of optimization that has to happen there (and many other types) will also be possible when the SELECT queries are actual queries against tables, and are optimized separately. Without an ORDER BY, ordering within each query will be arbitrary, and you can't guarantee that the queries themselves will be processed in any order.

Saying UNION ALL with no ORDER BY is like saying "Just throw all the marbles on the floor." Maybe every time you throw all the marbles on the floor, they end up being organized by color. That doesn't mean the next time you throw them on the floor they'll behave the same way. The same is true for ordering in SQL Server - if you don't say ORDER BY then SQL Server assumes you don't care about order. You may see by coincidence a certain order being returned all the time, but many things can affect the arbitrary order that has been selected next time. Data changes, statistics changes, recompile, plan flush, upgrade, service pack, hotfix, trace flag... ad nauseum.

I will put this in large letters to make it clear:

You cannot guarantee an order without ORDER BY

Some further reading:

Also, please read this post by Conor Cunningham, a pretty smart guy on the SQL team.

Community
  • 1
  • 1
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 3
    Can you make that font a bit larger I don't understand? :) – Taryn Apr 02 '13 at 14:31
  • 3
    @bluefeet only if it linked to a SQLfiddle. :-) – Aaron Bertrand Apr 02 '13 at 14:32
  • I can add that for you if you want. – Taryn Apr 02 '13 at 14:33
  • 2
    @whytheq that's not shouting. If I were shouting, IT WOULD BE IN ALL CAPS. I'm trying to make the point of the answer - in its simplest form - completely obvious to future readers. – Aaron Bertrand Apr 02 '13 at 14:40
  • @AaronBertrand No worries Aaron; I'm pulling your leg - I actually new the answer to the question before asking it, but I thought it'd be a good addition to SO even though previously it has been asked in other guises. It was only after writing the question that I then wondered if there was a definitive way I could actually prove it to myself in SSMS - I will have a nose through your further reading suggestion. – whytheq Apr 02 '13 at 14:52
  • 1
    @whytheq So then you did your research, realized that this was a [duplicate question](http://stackoverflow.com/questions/421049/sql-server-union-what-is-the-default-order-by-behaviour) and thought it would still be a good addition? – Kermit Apr 05 '13 at 17:23
  • @FreshPrinceOfSO fair comment - although I'm not always so skilled at spotting duplicates: if my title is a lot different from the dup then how do I find it? – whytheq Apr 11 '13 at 18:54
  • @whytheq Using Google. – Kermit Apr 11 '13 at 18:58
  • @FreshPrinceOfSO As I mentioned if I google my title "Does UNION ALL guarantee the order of the result set" it doesn't seem to lead to the dupe? – whytheq Apr 11 '13 at 19:14
  • @whytheq [You should search StackOverflow in your Google search](https://www.google.com/search?q=site%3Astackoverflow.com+Does+UNION+ALL+guarantee+the+order+of+the+result) – Kermit Apr 11 '13 at 19:16
  • @FreshPrinceOfSO - perfect; that is a productive comment...your "Using Google" was winding me up as I've seen it before ... but now I know how to check for dupes. Thanks. – whytheq Apr 11 '13 at 19:34
  • Removing ALL changes the optimization as it now has to handle duplicate checking that didn't exist in `UNION ALL` While this question may be bad practice, it does work, UNION ALL does return results in the ORDER they were added to the result set. – Andrew Mar 07 '18 at 22:43
  • @Andrew The point is you can't **guarantee** that. The questions asks to prove a negative ("prove that this can never happen!"), and I don't think we have to do that, as reasonable people. – Aaron Bertrand Mar 08 '18 at 03:56
  • @Andrew Also see [this post](https://dba.stackexchange.com/a/39513/1186). – Aaron Bertrand Mar 08 '18 at 04:15
  • @Kermit UNION and UNION ALL are very different things - and they behave in functionally different ways due to UNION removing duplicates between result sets and UNION ALL leaving duplicates - Though they ask the same thing of the two different types of union, that difference makes this not a duplicate. – Andrew Nov 13 '19 at 19:15
  • @Andrew Sorry, what were you responding to? – Kermit Nov 14 '19 at 23:42
  • @Kermit your comment about this being a duplicate question, with a link to a question about `UNION` when this question is about `UNION ALL` two similar but different functions. – Andrew Nov 25 '19 at 20:23
5

No. You get the records in whatever way SQL Server fetches them for you. You can apply an order on a unioned result set by 1-based index thusly:

SELECT 1, 'O'
UNION ALL
SELECT 2, 'R'
UNION ALL
SELECT 3, 'D'
UNION ALL
SELECT 4, 'E'
UNION ALL
SELECT 5, 'R'
ORDER BY 1
Paul Sasik
  • 79,492
  • 20
  • 149
  • 189
  • 3
    ORDER is spelled "wrong" now because the ordering is alphabetical. If you add a numeric index you can force your "correct" spelling. See the edit above. – Paul Sasik Apr 02 '13 at 14:21
  • 6
    You should apply a column alias to the first column and order by that. [Ordering by ordinal position is a bad habit](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/06/bad-habits-to-kick-order-by-ordinal.aspx) that works fine here but may break elsewhere. – Aaron Bertrand Apr 02 '13 at 14:23
  • Order by ordinal position is very convenient for rapid development, but is not supported in Oracle, for instance. – access_granted Nov 03 '17 at 00:20
  • Archived link to above: https://web.archive.org/web/20190820175355/https://sqlblog.org/2009/10/06/bad-habits-to-kick-order-by-ordinal – ADJenks Aug 20 '19 at 17:56