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

It should not be any difference. You can always check the plan with `EXPLAIN PLAN FOR` or actual execution plan with `dbms_xplan` functions. — a1ex07, Aug 15 '14 at 16:26
when testing, they seem to be identical, though my understanding was you get a benefit including the filter in the join (sort of a pre-filter) — invertigo, Aug 15 '14 at 16:27
The query engines do the optimizations themselves before executing a query. — user2989408, Aug 15 '14 at 16:39
This really is a duplicate of the question, "Inner join vs Where", http://stackoverflow.com/questions/121631/inner-join-vs-where?rq=1. — Patrick Bacon, Aug 15 '14 at 16:55
@Patrick not necessarily, I'm asking about additional filtering, not the linking criteria. now, the answers may be the same, but the question is (albeit slightly) different — invertigo, Aug 15 '14 at 16:57
@invertigo Yes, with the additional filtering; it is different. — Patrick Bacon, Aug 15 '14 at 17:06

score 13 · Accepted Answer · answered Aug 15 '14 at 16:39

13

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.

answered Aug 15 '14 at 16:39

Justin Cave

227,342
24
367
384

2

Besides, 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 the `JOIN` syntax for the sake of readability, it not for performance as they should perform equal, depending on its complexity. – Will Marcouiller Aug 15 '14 at 16:58
5

@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 the `WHERE` 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. – Justin Cave Aug 15 '14 at 17:19

score 0 · Answer 2 · edited Jun 08 '21 at 17:12

I prefer putting the filter criteria in the where clause.

With data warehouse queries, putting the filter criteria in the join seems to cause the query to last significantly longer.

For example, I have Table1 indexed by field Date, and Table2 partitioned by field Partition. Table2 is the biggest table in the query and is in another database server. I use driving_site hint to tell the optimizer to use Table2 partitions.

select /*+driving_site(b)*/ a.key, sum(b.money) money
  from schema.table1 a
  join schema2.table2@dblink b
    on a.key = b.key
 where b.partition = to_number(to_char(:i,'yyyymm'))
   and a.date = :i
 group by a.key`

If I do the query this way, it takes about 30 - 40 seconds to return the results.

If I don't do the query this way, it takes about 10 minutes until I cancel the execution with no results.

Oracle SQL Query Filter in JOIN ON vs WHERE

2 Answers2

Linked