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.

Answer 1

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.

Answer 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