SQLite Forum

Table aliases on parenthesized results of JOINS and set operations (UNION etc.) -- feature or chance?
Login
I'll start with the easy thing first: Thank you for poining me to the fact that a compound select statement (what I -- ambiguously -- called a "set operation" above) is actually just an extended subselect. That indeed follows from the railroad diagram, if one doesn't overlook that the main diagram is called `select-stmt` ...


As to the apparently more tricky question, thank you very much for your thoughtful explanation! It occurs to me almost on a regular basis that in a select statement, `table-name` is semantically the same as `SELECT * FROM table-name`, and with your explanations, I can see why it makes sense for a parser to interpret

<code>
SELECT foo, bar
FROM (table-name)
</code>

as exactly the same as

<code>
SELECT foo, bar
FROM (SELECT * FROM table-name)
</code>

(the example is trivial to keep it minimal).

Since I am concerned with whether one can *rely* on this behaviour, I have (even though your remarks about even you having "no clue" should probably have deterred me) made a feeble attempt to consult the SQL standard about this, only to learn that you have to buy it, which I wasn't prepared to do. It's a really weird thing there is no such thing as a reference implementation (such as the W3C has for HTML up to version 4), given that SQL has been around for so long. But even if the standard said something about this, this would not automatically mean that SQLite follows it.

So, what to do? It would be nice to use that behaviour, as it makes the code more compact, but what the parser does is (while interesting) technically none of the SQL programmer's business. Currently, the conclusion seems to me that the only safe option would be to write such a "named join" as an explicit subselect. Of course, I would be extremely interested if Richard Hipp had any comments on this, so I'll come back to check here.

Thanks very much again! Florian