On Fri, Nov 11, 2011 at 11:58 AM, Petite Abeille <petite.abei...@gmail.com> wrote: >> It returns the same results, but it doesn't seem much faster. Is there any >> performance difference to be expected from using IN instead of JOIN, or >> does SQLite internally rewrite JOIN queries to something similar as IN, >> which would explain they perform nearly the same? > > They should be equivalent in terms of cost. That said, you might want to use > the 'exists' clause instead for the sake of clarity.
No, "exists" in this case will change query plan significantly and performance can degrade drastically as a result. For the original question: it's not that SQLite rewrites JOIN queries to be as IN. It's just in your particular case both queries can be executed in the same way: find all rows in table1 with the necessary amount, for each row look into table2 and find rows with the same rowid. Query with JOIN however could be executed differently - for each row in table2 find all rows with the same rowid in table1 and then check amount in them. SQLite decided that this query plan will be less effecient. Pavel _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
