PeterVandivier
When merging sample sets, one might use UNION ALL
<>https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a9e866dbb7936bf54a570cc1bb4bdc36&hide=7
Recently I discovered [postgresql requires parentheses](https://stackoverflow.com/a/37352339/4709762) given the absence of `TOP()`
<>https://dbfiddle.uk/?rdbms=postgres_12&fiddle=def3312530ea496cdb0d38e74d75e41b&hide=31
However before I googled the correct syntax; when I was keyboard mashing looking for the correct syntax, I encountered this chestnut...
<>https://dbfiddle.uk/?rdbms=postgres_12&fiddle=def3312530ea496cdb0d38e74d75e41b&hide=47
WTH is going on here?
Top Answer
Jack Douglas
The docs are a bit vague about this. In the ["Combining Queries"](https://www.postgresql.org/docs/13/queries-union.html) 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](https://www.postgresql.org/docs/13/queries-order.html)".
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](https://dba.stackexchange.com/a/185862/1396)
Answer #2
PeterVandivier
Without explicit parentheses, the second `LIMIT` is scoped to the batch, not the statement. Consider the `EXPLAIN`
<>https://dbfiddle.uk/?rdbms=postgres_12&fiddle=3c217da1fe188bbfdfc960d66fc7af14&hide=111
Note `select...bar` is **(never executed)**
If we re-write the query as follows... we see..
<>https://dbfiddle.uk/?rdbms=postgres_12&fiddle=3c217da1fe188bbfdfc960d66fc7af14&hide=63
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 `select...bar` 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.