On Sun, Sep 6, 2015 at 4:36 AM, E.Pasma <pasma10 at concepts.nl> wrote:
> > Op 6 sep 2015, om 03:17 heeft Scott Robison het volgende geschreven: > >> >> select * from (select a as b from c) where b is something >> >> Still column expressions are called mutiple times. > > SQLite 3.8.11 2015-07-27 13:49:41 b8e92227a469de677a66da62e4361f099c0b79d0 > create table c(a) > ; > explain query plan > select 1 from (select (select a from c) as b) where b>0 > ; > 1|0|0|EXECUTE SCALAR SUBQUERY 2 > 2|0|0|SCAN TABLE c > 1|0|0|EXECUTE SCALAR SUBQUERY 3 > 3|0|0|SCAN TABLE c > 0|0|0|SCAN SUBQUERY 1 One, the argument wasn't whether or not column expressions were called multiple times. My understanding of the debate was that using aliases in place of their definitions made code more understandable and more easily maintained than gratuitous repetition of the definitions. Two, you used a different query. Looking at my query again (fleshed out since I'm on a computer vs my phone): sqlite> create table c(a); sqlite> insert into c values(1); sqlite> insert into c values(2); sqlite> insert into c values(3); sqlite> insert into c values(4); sqlite> select * from (select a as b from c) where b > 2; 3 4 sqlite> explain query plan select * from (select a as b from c) where b > 2; 0|0|0|SCAN TABLE c sqlite> select * from (select abs(a-a-a) as b from c) where b > 2; 3 4 sqlite> explain query plan select * from (select abs(a-a-a) as b from c) where b > 2; 0|0|0|SCAN TABLE c Clearly, this gives you an opportunity to replace definitions with aliases, since the aliases in the inner query become the column names of the outer query. Your example doesn't do the same thing at all (ignoring the difference between your condition of > 0 and mine of > 2). Your query is made of scalar subqueries and you will never get more than a single row back. The following two queries are not the same: select 1 from (select (select a from c) as b) where b > 0; select 1 from (select a as b from c) where b > 0; In any case, this is a perfectly good option to query building that don't require non-standard behavior (as far as I can tell) while still giving the benefit of DRY (don't repeat yourself) as someone noted previously. It's more verbose. It may not be as intuitive. But it works. I am not a SQL guru. If I am wrong about my alternative invoking only standard behavior, my apologies. > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Scott Robison
