On Mon, Dec 12, 2011 at 9:33 AM, Nick Smallbone <nick.smallb...@gmail.com>wrote:
> "Black, Michael (IS)" <michael.bla...@ngc.com> > writes: > > > Why do you have a subselect....what are you doing there that you can't > > do in the "on" clause? > > In the "real" example I'm joining with a view: > create view v as select * from b where ... > select * from a left natural join v where id = 1; > > IIUC, when I execute the query on the second line, SQLite replaces the > use of "v" with v's definition, "select * from b where ...", so as far > as the query planner is concerned there is a subquery. The only way to > avoid the subquery is not to use the view. > We used to try to optimize the right side of a LEFT JOIN by "flattening" the subquery into the main query. But that doesn't always work (as ticket http://www.sqlite.org/cvstrac/tktview?tn=3300 demonstrated) so that optimization was disabled. > > Nick > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users