CODEWITHSUNDEEP
sqlsql-order-byunion-all
Ivan Sopov
Ivan Sopov

Reputation: 2370

Is the order of union all guaranteed

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.

Upvotes: 4

Views: 794

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

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.

Upvotes: 1

invertedSpear
invertedSpear

Reputation: 11054

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

Upvotes: 3

sgeddes
sgeddes

Reputation: 62841

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.

Upvotes: 3

Related Questions