>Thanks for acknowledging this, you are absolutely right, that's why I >stated >that no DB does perfect optimisations and that computing the >alternative >-OR- based closures are probably much harder to tackle. Also E. Pasma >pointed out the -OR- queries as I wrote them are not really >semantically >equivalent unless the 2 joins are disjunct.
I suspect that query re-write of an outer join to an inner join would be violating some rule in the new SQL standards, most likely about visitation (nested loop) order -- which is freely reorderable for INNER JOINS but cannot be re-ordered for OUTER JOINS. Back in the "olden days" one specified outer join conditions in the WHERE clause using *= =* or *=* syntax, where the * was on the side of the operator where all rows came from. This was deprecated many years ago when the <type> JOIN ON <conditions> syntatactic sugar was created because too many people where forgetting that they need to use the appropriate *<operator> to "bind" the where condition to the appropriate outer join binding, then complaining that it was too difficult to remember or figure out where a plain "WHERE" clause was needed and where a "OUTER JOIN" bound condition was required, resulting in many calls to SQL Database support lines. And these same vendors also happen to be who write the specs, so they promptly changed the spec to eliminate the support calls. So the *<operator>* format was written out of the standard and the OUTER JOIN ON became mandatory ONLY FOR OUTER JOIN operations. In all other cases the JOIN ON syntax was just syntactic sugar for the old "list of tables" and where clause. Don't recall exactly when this occurred but it was about two decades ago. ( I remember it well because there were many queries that could not be expressed in the new-fangled format ) So really, adding conditions to apply to an OUTER JOIN to the where clause is equivalent to the common error of yester-decades of forgetting the *. OLD SYNTAX: select * from a, b where a.a *= b.a was replaced by select * from a LEFT JOIN b ON a.a = b.a Of course, the old syntax allowed one to specify algebraic conditions that can no longer be expressed with the new syntax. But that is OK, it is easier and simpler to say: go RTFM, it is jolly clear, and if you don't like it then boo-hoo on you. A good implementation was destroyed. Live with it, love it, and get over it. I would sincerely doubt that there is *any* SQL optimizer or query planner that can optimize mis-spoken queries containing OUTER JOINS. Not even DB2's exhaustive search query planner/optimizer can do it, and it is quite possibly now one of the best in existence if you tell it that it can take unlimited time and resources to generate (and run) the plan. All others pale in comparison. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
