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

Reply via email to