Is the order of union all guaranteed

Question

Is it guaranteed that order of two parts of the union all query will be given in the particular order? I.e. that the result of this query:

select 'foo' from dual
union all
select 'bar' from dual

Will always be

foo
bar

and not this

bar
foo

?

I used Oracle syntax but what I want to know is what does ISO Standard says about this.

"In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees ... that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause." — showdev, Jan 17 '13 at 21:19
I don't know whether the order is guaranteed (it shouldn't be), but you could enclose these queries into another one and then manually order the results `SELECT * FROM (SELECT 'foo' AS col_1 ... UNION ...) ORDER BY col_1`. At least such a query will be verbose and unequivocal. — Crozin, Jan 17 '13 at 21:19

score 3 · Accepted Answer · answered Jan 17 '13 at 21:31

In your particular example, the order should not change because you're querying against the DUAL table and you won't have to worry about potential index changes from that particular query. So you will always get Foo then Bar back respectively.

However, in the real world, yes, the order can most certainly change -- depends on several factors such as table indexes, columns being returned, new data being introduced, etc. So if you want your results ordered in a particular way, you need to specify ORDER BY clause.

Hope this helps.

score 2 · Answer 2 · answered Jan 17 '13 at 21:55

2

Suggested rewrite of your query:

SELECT txt FROM (
select 1 as sort, 'foo' as txt from dual
union all
select 2 as sort, 'bar' as txt from dual
) product
ORDER BY sort

answered Jan 17 '13 at 21:55

invertedSpear

10,864
5
39
77

Thanks, this is pretty straightforward. I was wondering whether such rewrite is needed at all. – Ivan Sopov Jan 18 '13 at 09:43
As others have said, you would probably be fine, but it's not guaranteed without specifying. – invertedSpear Jan 18 '13 at 16:44

score 1 · Answer 3 · answered Jan 17 '13 at 22:14

The ISO standard says that tables are inherently unordered. It also says that when you return rows from a query, there is no guarantee of the ordering, unless you use an order by clause.

In practice, most databases will return the foo before the bar in this case. However, if you change union all to union, some databases will probably return the bar first.

To make matter worse, you cannot even say that all the rows from the first query will return before the second. In a parallel environment, for example, the result set from a union all of two tables can intermingle the rows.

If you want foo first, then add order by txt desc or something to that effect.

Is the order of union all guaranteed

3 Answers3