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. You also need to look through the entire A table in a Left join because NULLs in the B table does not disqualify
them, unless of course there is specifically either a NOT NULL or ColVal='Someting Specific'. I am now not sure if these would be
the only set of cases for the optimisation, but they are the only ones coming to mind currently.
Or we could hope that the SQL database has a query planner of its own which can
do such optimisations... :)
Quite a reasonable request if the optimisation can be boiled down to a specific always working set of rules. As I mention above, the
ones coming to mind is that the WHERE clause is simple and specifically excludes NULLs. Even if NULLs are not specifically excluded,
the column schema might include "NOT NULL" for that column which may also incur the optimisation (if it doesn't already), or even
have the sqlite_statx analyze point out column that do not contain any nulls, or rather not as that may change. I know there is an
optimisation step in SQLite NGQP that promotes ON clauses to WHERE clause statements (except for Left Joins for this reason), though
more complex queries might have some caveats which eludes me currently. Also if several left-joins exist in the same query, a light
implementation of this optimisation might influence the truth of the result (and of course I am not implying that the implementation
would be light, just that the optimisation might need a lot of work). It also doesn't affect people who know when to not use a left
join, but that is never a reason not to implement an obvious optimisation (as your use case illustrates).
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. :)
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
