Op 6 sep 2015, om 16:44 heeft Scott Robison het volgende geschreven: > > 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. >
Yes. the debate is about column aliases. Still the OP also wished to not call column expressions multiple times. And I had to change your query to make that appear in the query plan. But here is an example very close to yours where b is an alias for random(). I changed thea original where clause (b>2) to b <> b abd I hope it is obvious that the function is caled multiple times for each row. create table c(a); insert into c values(1); insert into c values(2); insert into c values(3); insert into c values(4); select * from (select random() as b from c) where b <> b; 232218896271007264 -1043354911054439855 5116834959932449572 7115658816317887453 By the way I really like your coding style and it had no impact on speed thanks to "subquery flattening" by the optimizer.
