As per standard SQL
UNION ALL do not guarantee any particular sort order without an outer
ORDER BY clause - like there is hardly any place in SQL where sort order is guaranteed without
However, Postgres uses an "Append" step for plain cases of
UNION ALL, hence results from the first leg (even if unsorted among their partition) always come before the next leg, etc. Postgres simply appends results from each leg in the given order. This is particularly relevant with a
SELECT 1 FROM tbl -- or any complex query
Obviously this does not hold for
ALL). But other than that, I have never seen Postgres return out of order, i.e. '2' from the above query, while the first
SELECT would also return row(s). Not even if the first leg is extremely expensive.
I have based queries on this behavior in the past. Now I have met a claim Postgres might return rows out of order here, but unsubstantiated with actual proof.
The current Postgres manual has this to say on the matter:
UNIONeffectively appends the result of
query2to the result of
query1(although there is no guarantee that this is the order in which the rows are actually returned). Furthermore, it eliminates duplicate rows from its result, in the same way as
UNION ALLis used.
This is pretty unclear. Does the cited order apply to the list of
SELECT clauses, or rows within each clause, or just the returned set? Also,
UNION ALL is only mentioned in the second sentence, so it remains unclear if the all-important first sentence is supposed to apply to
UNION ALL ...
Can anybody show an example, where rows are returned out of order, breaking the sequence of
UNION ALL clauses? In any version of Postgres. (Even if the latest version would be most interesting.)
If not that, is there reason to believe this might change in a future version?
ORDER BY is not the immediate issue here. The question is whether multiple
UNION ALL clauses return rows in the given sequence (before
LIMIT can kick in and stop further branches from executing).