ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.

advertisement

AddThis Social Bookmark Button
Article:
  How to Misuse SQL's FROM Clause
Subject:   Doesn't work for Oracle, Db2 UDB, DB2 or SQLserver
Date:   2006-03-23 10:46:41
From:   sfaroult
Response to: Doesn't work for Oracle, Db2 UDB, DB2 or SQLserver

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.