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) You can expand the where clause of course to do what you want. 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 Simon Slavin [slav...@bigfraud.org] Sent: Saturday, December 10, 2011 8:24 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Poor performance with nested query in outer join On 9 Dec 2011, at 10:25pm, Nick Smallbone wrote: > select * from a left natural join (select * from b) where id = 1; Try not to use sub-selects when you can use a JOIN instead. Especially don't use them in combination. If you express this as just a JOIN you'll find that the optimizer works as expected. The reason it can't work here is because it can work across JOINs but not SELECTs. Simon. _______________________________________________ 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
