Hello!
I am curious what is a particular reason that aliased columns in a query not
visible to sub-queries?
CREATE TABLE foo(
id INTEGER,
bar INTEGER
);
INSERT INTO foo VALUES(1, 2), (3, 4);
SELECT 1 as super_id, (SELECT bar FROM foo WHERE id = super_id);
Gives an error:
Error: no such column: super_id
On the other hand, we can easily refer to values of a table that participates
in a query.
CREATE TABLE baz(
id INTEGER,
qux INTEGER
);
INSERT INTO baz VALUES(1, 6), (2, 8);
SELECT id, (SELECT qux FROM baz WHERE id = foo.id) FROM foo;
id (SELECT qux FROM baz WHERE id = foo.id)
---------- ---------------------------------------
1 6
3
But still not though alias:
SELECT id as super_id, (SELECT qux FROM baz WHERE id = super_id) FROM foo;
Error: no such column: super_id
--------------------------------------------------------------------------------------------------------------------------------
Why would this be useful?
Sometimes you need a sub-select by a result of an expression.
SELECT ..., <expression X>, (SELECT ... FROM FOO where id = <result of
expression X>) ..;
Of course it is possible to duplicate expression in sub-query
SELECT ..., <expression X>, (SELECT ... FROM FOO where id = <expression X>) ..;
But this would not be as efficient as to access result of already evaluated
expression.
Especially if <expression X> itself is a sub-select. Also imagine a chain of
sub-queries.
