Article:		How to Misuse SQL's FROM Clause
Subject:		Doesn't work for Oracle, Db2 UDB, DB2 or SQLserver
Date:		2006-03-21 15:55:13
From:		mm0
I assembled sample data from my ssytem to test this out because I didn't believe it was generalizable. It's the opposite of the suggestion for Oracle 8/9 and sqlserver in my test databases, and DB2 and DB2 UDB rewrite the subquery as a join. Tables are indexed on constraint columns, indexed on join columns, cust table has ~25 million rows and order table has ~60 million rows. I don't have a mysql instance up on these servers to test with. I think you're making a generalization of a mysql-specific suggestion.

Showing messages 1 through 1 of 1.

• Doesn't work for Oracle, Db2 UDB, DB2 or SQLserver
  2006-03-23 10:46:41  Stéphane Faroult [Reply | View]
  
  
  I am afraid that you are misunderstanding my point. It's not a "performance tip", but a matter of good practice. Indeed, a good optimizer may well, in a simpler case, rewrite the query as it should have been rewritten in the first place and do the right thing. Throw in aggregates, outer joins, subqueries. Will it perform as well? Maybe, maybe not. Create a view upon the join, and imagine that you are joining the resulting view to one of the tables in the view. Do you think that the DISTINCT will make an intelligent rewrite easier?
  There is also an interesting case, which is the one of queries in which the WHERE clause is modified on the fly. It's easy to add a subquery if you have a particular condition that is the existence of given values in a related table. Add a DISTINCT and this table to the FROM clause, all queries for which no screening condition references this table will join it for nothing ...
  There is nothing DBMS dependent in all these cases.