Oracle SQL Query Filter in JOIN ON vs WHERE

Question

For inner joins, is there any difference in performance to apply a filter in the JOIN ON clause or the WHERE 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

Answer 1

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.