postgresql add tag
When merging sample sets, one might use UNION ALL


Recently I discovered [postgresql requires parentheses]( given the absence of `TOP()`


However before I googled the correct syntax; when I was keyboard mashing looking for the correct syntax, I encountered this chestnut...


WTH is going on here?
Top Answer
Jack Douglas
The docs are a bit vague about this. In the ["Combining Queries"]( section (which discusses `UNION`/`INTERSECT`/`EXCEPT`) they state:

> query1 and query2 are queries that can use any of the features discussed up to this point

Note that `ORDER BY` is not one for the features discussed ***up to this point***. The *next* section is "[Sorting Rows](".

So a query that is the result of `UNION`/`INTERSECT`/`EXCEPT` cannot have multiple `ORDER BY`[^1]. It can have only one, at the very end.

None of this means you cannot have `ORDER BY` inside a *sub*query[^2].

Putting it together, the following two are equivalent: 

select * from (select * from foo limit 2) x -- no need to use x(i) as the
union all                                   -- column already has a name
select * from bar limit 2

select * from (select * from foo limit 2) x
union all 
select * from bar
limit 2

[^1]: `LIMIT 2` is a special case of the `ORDER BY` clause, with undefined ordering.
[^2]: though doing so does not guarantee the ordering of the top-level query [except in the simplest cases](
Answer #2
Without explicit parentheses, the second `LIMIT` is scoped to the batch, not the statement. Consider the `EXPLAIN`


Note `` is **(never executed)**

If we re-write the query as follows... we see..


From this, we can infer that in the first plan the inner limit (plan line four, query line 1) satisfies the outer limit (plan line 1, query line 3). The plan line 3 limit is parsed as limiting the entire batch (hence the need for strict parentheses usage) and the `` is short-circuited. 

The absence of "correct" parentheses usage allows us to limit the inner statement and then capture the outer statement with a larger rowcount.

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.