3 Replies Latest reply on Apr 30, 2013 11:00 PM by SomeoneElse

    Is inner Join faster than where clause ?

    876094
    876094 Apr 30, 2013 9:17 AM
      For Example, Consider the below two queries and say me which query runs faster

      Predicate in Inner Join on Clause:

      select * from tabl1 abc join tabl2 def on (abc.column1=def.column1 and def.column3='abc')

      Predicate in where Clause:

      select * from tabl1 abc join tabl2 def on (abc.column1=def.column1)
      where def.column3='abc'

      Hint:

      1. Column3 in tabl2 is non-indexed



      Thanks in Advance
      Thirumurugan Desinghu
        • 1. Re: Is inner Join faster than where clause ?
          Mariano Ramirez
          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
            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
              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. 
              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
              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.