This content has been marked as final.
Show 3 replies
-
1. Re: Is inner Join faster than where clause ?
Mariano Ramirez Apr 30, 2013 8:19 PM (in response to 876094)Not, the inner join and where clause is the same.
The inner join belong to "The ANSI-99 SQL Joins Standard" and the where clause belong to Oracle-proprietary, the syntax does not offer any performance benefits.
If you have it an index over the column3 on tbl2 you will get the same execution plan with inner join and where clause.
Best Regards,
Mariano Ramirez.-
Edited by: Mariano Ramirez on Apr 30, 2013 4:19 PM -
2. Re: Is inner Join faster than where clause ?
jgarry Apr 30, 2013 9:54 PM (in response to 876094)Sometimes it's worth checking the bug database for ansi join. Yes, even 11.2.0.3 can give wrong results as the optimizer does complex view merges. -
3. Re: Is inner Join faster than where clause ?
SomeoneElse Apr 30, 2013 11:00 PM (in response to 876094)You need to be very careful about mixing join conditions and filtering conditions in the ON clause. Especially when doing outer joins, the results might not be the same.
The first example has the condition in the ON clause, in the second I moved it to the WHERE clause.
Your example is an inner join, so I think you're OK either way. Still, for consistency I always put only the join conditions in the ON clause and filtering conditions in the WHERE clause.SQL> select * from t1; COL1 C -------------------- - 1 a 2 b 3 c 4 d SQL> select * from t2; COL1 C -------------------- - 1 x SQL> select * 2 from t1 3 left join t2 on (t1.col1 = t2.col1 and t2.col2 = 'xyz') 4 ; COL1 C COL1 C -------------------- - -------------------- - 4 d 3 c 1 a 2 b SQL> select * 2 from t1 3 left join t2 on (t1.col1 = t2.col1) 4 where t2.col2 = 'xyz'; no rows selected