On Wed, 2014-11-05 at 16:00 +0200, RSmith wrote: > On 2014/11/05 15:26, David Woodhouse wrote: > > On Wed, 2014-11-05 at 15:13 +0200, RSmith wrote: > > I don't think it's anything to do with the table data being special, > is it? Isn't it generically true that for any LEFT JOIN of > > a,b WHERE b.anything IS NOT NULL, the results are going to be equal > with an INNER JOIN? > > Yes, I was simply pointing to the fact that if indeed you had NULL > values and omit the specific WHERE clause the results will depend > on your table.
Right. The proposed optimisation does fundamentally depend on having a WHERE clause which discards all the additional records which the LEFT JOIN includes, that the INNER JOIN would not. Put simply, you can only change LEFT JOIN to INNER JOIN if you're going to throw away the records from the output which make them different. The optimiser's detection of such a WHERE clause could actually start off being relatively simplistic (perhaps only notice that it can make this optimisation if it sees an explicit IS NOT NULL on a column from the right-hand table), and could grow to recognise more cases over time as required. False negatives don't really hurt; they're just a missed optimisation. For example, I don't think anyone would be losing sleep if it initially failed to spot that 'WHERE b.foo IS NOT NULL OR b.bar IS NOT NULL' also means the optimisation could kick in. Or 'WHERE a.foo = b.bar' when a.foo is actually a "NOT NULL" field. > In fact this may allow much wider use of Left-joins which is always > the preferred join method system-side because it doesn't hide > missing/unlinked items. Ok, I convinced myself, +1 to the request. :) If this is going to end up in the tracker, could I also request that the optimisations from here be tracked too, please: https://www.mail-archive.com/sqlite-users@sqlite.org/msg86350.html https://www.mail-archive.com/sqlite-users@sqlite.org/msg86643.html -- dwmw2
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
