Re: Performance impact of filtering in ON vs WHERE clause
For inner joins, I really can't see any semantic difference between having the conditions in the join part and having them in the where part of the query and the optimizer should just use either conditions whenever it thinks that it is appropriate. So for inner joins, I think that the difference is purely cosmetic, at least with MySQL as I remember that the manual for older MySQL versions said that join conditions for inner joins were simply treated as where conditions. And now it says "For each table in a join, a simpler WHERE is constructed to get a fast WHERE evaluation for the table and also to skip rows as soon as possible.". That still sounds to me like MySQL is treating both forms the same, using both join and where conditions when doing an inner join.
Of course for outer joins the situation is different, as semantics change when you exchange where conditions for join conditions.
Subject
Views
Written By
Posted
15353
April 28, 2006 12:17PM
5486
April 28, 2006 12:26PM
4459
May 01, 2006 08:40AM
4472
May 01, 2006 12:54PM
4594
May 02, 2006 01:06PM
4522
May 02, 2006 01:15PM
Re: Performance impact of filtering in ON vs WHERE clause
5363
May 02, 2006 02:21PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.