On Mon, Feb 8, 2016 at 12:47 AM, Paul <devgs at ukr.net> wrote: > 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); > This looks like a simple select
select 1 as super_id,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; > this looks like a join select foo.id,qux from foo join baz on baz.id=foo.id > 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 And this select foo.id as super_id,qux from foo join baz on baz.id=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>) > ..; > and both of those.... > 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. > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
