SELECTs and queries

This simple query does what we expect it to do.

(SELECT 'a' LIMIT 1)
UNION
(SELECT 'b' LIMIT 1)

It returns:

What if we remove parenthesis?

SELECT 'a' LIMIT 1
UNION
SELECT 'b' LIMIT 1

syntax error at or near "UNION"
LINE 2: UNION

Alright, a syntax error. What if we remove paretenthesis from the last select?

(SELECT 'a' LIMIT 1)
UNION
SELECT 'b' LIMIT 1

What!!? Where is the 'b'?

It turns out that LIMIT is not parsed as a part of the select query (where FROM and GROUP BY are). It is parsed as part of set query, which is the parent of select query.

So our last query is parsed the same as this:

(SELECT 'a' LIMIT 1)
UNION
(SELECT 'b')
LIMIT 1

This is also the reason why the query without parenthesis produces a syntax error: LIMIT means the end of set query, which cannot be directly encapsulated in UNION. UNION can join either select queries or parenthesized subqueries.

How lovely. \s