On Mon, Dec 12, 2011 at 9:33 AM, Nick Smallbone <[email protected]>wrote:
> "Black, Michael (IS)" <[email protected]> > 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 > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
