Oracle SQL Query Filter in JOIN ON vs WHERE
There should be no difference. The optimizer should generate the same plan in both cases and should be able to apply the predicate before, after, or during the join in either case based on what is the most efficient approach for that particular query.
Of course, the fact that the optimizer can do something, in general, is no guarantee that the optimizer will actually do something in a particular query. As queries get more complicated, it becomes impossible to exhaustively consider every possible query plan which means that even with perfect information and perfect code, the optimizer may not have time to do everything that you'd like it to do. You'd need to take a look at the actual plans generated for the two queries to see if they are actually identical.
Comments
-
invertigo almost 2 years
For inner joins, is there any difference in performance to apply a filter in the
JOIN ON
clause or theWHERE
clause? Which is going to be more efficient, or will the optimizer render them equal?JOIN ON
SELECT u.name FROM users u JOIN departments d ON u.department_id = d.id AND d.name = 'IT'
VS
WHERE
SELECT u.name FROM users u JOIN departments d ON u.department_id = d.id WHERE d.name = 'IT'
Oracle 11gR2
-
Will Marcouiller over 9 yearsBesides, joining tables inside the
WHERE
clause consists of an old-style syntax in regards to SQL89. From SQL92 and higher, it is encouraged to use theJOIN
syntax for the sake of readability, it not for performance as they should perform equal, depending on its complexity. -
Justin Cave over 9 years@WillMarcouiller - I don't think anyone is talking about putting the join predicate in the
WHERE
clause rather than using the SQL99 join syntax. This question is just about putting filter predicates in the join clause rather than in theWHERE
clause. Of course, Oracle was relatively late to adopt the SQL99 join syntax and the first couple versions of the parser had some bugs that caused the old-style syntax to be more efficient, at least at times. That's probably not an issue so much in 11.2 but it did sour some folks on the newer syntax.