On Oct 18, 2010, at 6:58 PM, Igor Tandetnik wrote: > In general, I found that the idiom > > TableA left join TableB on (TableA.idInTableB = TableB.someId) where > TableB.someId is null > > almost always performs worse than the equivalent NOT EXISTS or NOT IN query.
Hmmm... in practice it should be a wash... lets see... > explain query plan select count( * ) from foo left join bar on bar.bar_id = > foo.foo_id where bar.bar_id is null; 0|0|TABLE foo 1|1|TABLE bar WITH INDEX bar_bar > select count( * ) from foo left join bar on bar.bar_id = foo.foo_id where > bar.bar_id is null; 90000 CPU Time: user 0.299174 sys 0.000272 > explain query plan select count( * ) from foo where not exists (select * from > bar where bar.bar_id = foo.foo_id ); 0|0|TABLE foo 0|0|TABLE bar WITH INDEX bar_bar > select count( * ) from foo where not exists (select * from bar where > bar.bar_id = foo.foo_id ); 90000 CPU Time: user 0.335951 sys 0.000371 Yep, same difference. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
