SQLite Forum

Table aliases on parenthesized results of JOINS and set operations (UNION etc.) -- feature or chance?
My understanding from all literature at my disposal was that table aliases 
can be bestowed upon either literal table names or on subqueries.

I have now noticed that if you parenthesise a join, 
as in the following example:


CREATE TABLE foo (id, wkd, a, b, c);
INSERT INTO foo VALUES ('Yes',   'Mon', 1, 2, 3),
                       ('No',    'Tue', 2, 3, 5),
                       ('Maybe', 'Wed', 3, 4, 7);

CREATE TABLE bar (id, a, b, c);
INSERT INTO bar VALUES ('Yes',   1, 2, 4),
                       ('No',    2, 3, 5),
                       ('Maybe', 3, 6, 7);

   SET a     = ba.a,
       c     = ba.c,
       wkd   = ba.wkd
       (bar JOIN (SELECT 'Thu' AS wkd)) AS ba
 WHERE f.id = ba.id;


the table alias works perfectly (both on wkd and on the id column).

It also seems possible to assign an alias to the result of a set operation,
such as UNION (Example uses table "foo" from above):


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


also works.

I am now wondering if I am just too stupid to follow obvious logic or 
whether this is a feature that's merely undocumented (or the documentation 
of which I haven't been able to find), or some behaviour that just came 
about unintentionally.

Whatever the cause, the behaviour seems useful to me, but since one should 
never rely on undocumented features, I would like to inquire:

- is this intentional?
- is it realiably supported?
- is it official?

If so, I would appreciate very much finding it in the documentation. I WOULD 
like to use it, but am weary of writing code that may break at some point in 
the future.

Many thanks in advance!