Why do you have a subselect....what are you doing there that you can't do in the "on" clause?
Can you show your subselect? Your example isn't enough. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Nick Smallbone [n...@8325.org] Sent: Saturday, December 10, 2011 4:35 PM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Poor performance with nested query in outer join "Black, Michael (IS)" <michael.bla...@ngc.com> writes: > Natural joins are generally considered to be evil. Too many columns > in common can be bad. > > If you just spell it out it works as expected > > sqlite> explain query plan select * from a left join b where a.id=1 and > b.id=a.id; > 0|0|0|SEARCH TABLE a USING COVERING INDEX sqlite_autoindex_a_1 (id=?) (~1 > rows) > 0|1|1|SEARCH TABLE b USING COVERING INDEX sqlite_autoindex_b_1 (id=?) > (~1 rows) Thanks, but this is a red herring: it makes no difference whether you use natural join or an explicit join here (and I would've been very surprised if it had, because they are exactly the same operation). Rather, the difference between my query and your query is that I have a subquery (select * from b) and you don't. Nick _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
