Re: How To Misuse SQLs From Clause

I have just been reading How To Misuse SQL's From Clause (http://www.onlamp.com/pub/a/onlamp/2004/09/30/from_clauses.html) (thanks to Rudy who pointed it it out on his website r937.com).

It makes me go "hmm". It says "...this is a very bad query; it's logically flawed" - well no, actually I don't agree with that. Logically, the query is just perfect. However, it is true that a DBMS with a less than perfect optimizer might not recognize that the query can be transformed into one with a correlated subquery, which may be faster to process.

But, dammit, the whole point of SQL is that we define what we want, not how the DBMS should go about getting it. That's what the optimizer is for! Why should we have to waste our time re-phrasing our queries when the optimizer should be quite capable of doing that itself (by following the algorithm given in the article).

I don't think I'm beaing unreasonable here! ;)hands up all of you who have never tried to influence the way the optimizer handles a query by rephrasing it

:rolleyes:|||"From Clause"?? Influence the Optimizer?? I would just like the user to be able to tell me what he what results he really wants from his query.|||I would just like the user to be able to tell meheh, good one

yes, that's often the biggest hurdle, isn't it

:)|||hands up all of you who have never tried to influence the way the optimizer handles a query by rephrasing it
Not me: in my early Oracle-using days before the cost-based optimizer was introduced, we used to have hours of fun modifying queries by re-arranging the order of the tables in the FROM clause, and the order of predicates in the WHERE clause, because the old rule-based optimizer processed the tables and predicates in the order it read them (right to left).

Thankfully, those days are long gone, and so in most cases has the need to restructure queries in other ways (like the one the article recommends). If two queries are semantically equivalent, then it shouldn't matter which one you write, the optimizer should try both and use the fastest. And it should be much better at it than a human!