SQLite Forum

Table aliases on parenthesized results of JOINS and set operations (UNION etc.) -- feature or chance?
(bar JOIN (SELECT 'Thu' AS wkd)) as ba

is a subquery which is the result of joining the table `bar` with the table that is the result of the singleton query (select 'Thu' as Wkd).  That is, it is the same as the (universally correct) spelling:

((select * from bar) JOIN (select 'Thu' as Wkd)) as ba

Is this valid syntax:

SELECT * FROM (a JOIN (b JOIN c) as d) as e

It certainly is in SQLite3.  Is it everywhere?  Not a clue.  Is this behaviour required by the standard?  Not a clue there either.

The root of the issue is when the parser sees the open-parenthesis does it interpret that as commencing a subquery or as merely an order-of-operations marker?  

If the former, then parsing will always work correctly.  If the latter then the parser, when it sees the `) AS` has to go back and "whoa, that open-parenthesis started a subquery so now we have to go back and do it again properly this time".  

The SQLite3 parser is a "go forwards single pass" parser and would at that point have no action other than to declare an "error".  But it does not because all open-parenthesis where a table-name is required means that we are commencing a subquery.  

If the spec requires that (a JOIN b) JOIN c is different than a JOIN (b JOIN C) (ie, that the parenthesis are meaningful) then a bare-table-name is merely "syntactic sugar" for the subquery `select * from bare-table-name` until all mere "order of operation" parenthesis are replaced by subquery opening parenthesis.  Of course, `a JOIN b JOIN c` may be any of may be any of six possible descent orders depending on whatever the query planner believes is the most efficient order at the time of execution.

Richard might be able to add some meaningful comment here, but I will note that treating the (...) where a table expression is required as an order-of-operations designator rather than a subquery designator both defeats the entire purpose of SQL as a declarative language and makes parsing more difficult.

Yes, there are come people (all from Microsoft) who seem to believe that () always designate order of operations and are in love with putting them everywhere they possibly can, but this is (to be polite) somewhat ill-conceived.

The second form:

SELECT take_two.a FROM
(SELECT * FROM foo UNION ALL SELECT * FROM foo) AS take_two;

is perfectly standards compliant SQL that should be processed by everything claiming to support named subquerys (where compound subsquerys are not unsupported, which I do not think is anything you will find to exist at present).