This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Sep 20, 2013 12:49 AM by MarkCooper RSS

Difference-  in conditions (Join and Where Clause)

MarkCooper Newbie
Currently Being Moderated

Hi Folks,

 

I need to be clear about what exactly difference when we put any condition in INNER JOIN and WHERE Clause.

I tried both way and found same results. Even in Statistics Plan not much differences.  All Help would be appreciated.

Like:

 

1. Here I am using location filter in Inner join condition -

"SELECT I.*, Gl * From Sc1.Item I

   Inner Join Sc1.Part P

            On P.Part_Id = I.Part_Id

       Inner Join Sc1.Location Gl

            On Gl.Location_Id = I.Location_Id

              And Gl.Location_Id In ( 1767, 1747,202,1625)

    Inner Join Sc1.Condition C

        On C.Condtion_Id = Gl.Condition_Id

Where  I.Inactive_Ind = 0

      And I.Condition_Id != 325

         

2. Here I am using location filter in Where clause -         

          SELECT I.*, Gl * From Sc1.Item I

   Inner Join Sc1.Part P

            On P.Part_Id = I.Part_Id

       Inner Join Sc1.Location Gl

            On Gl.Location_Id = I.Location_Id

        Inner Join Sc1.Condition C

        On C.Condtion_Id = Gl.Condition_Id

Where  I.Inactive_Ind = 0

       and I.LOCATION_ID in ( 1767, 1747,202,1625)

          And I.Condition_Id != 325

 

Thanks,

Mark



  • 1. Re: Difference-  in conditions (Join and Where Clause)
    Pablolee Journeyer
    Currently Being Moderated

    personally, I find that the ansi syntax is easier to read and manipulate.

     

    This part of the documentation might prove useful:

    Joins

  • 2. Re: Difference-  in conditions (Join and Where Clause)
    ranit B Expert
    Currently Being Moderated

    AFAIK (though not 100% ure), as per the ANSI syntax : the 'ON' clause holds all the joining conditions whereas the 'WHERE' clause holds the filtering conditions.

     

    Read this for more - http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm

  • 3. Re: Difference-  in conditions (Join and Where Clause)
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    Always analyze plan . If you don't see any diff. it means that there is no difference for ORACLE.

  • 4. Re: Difference-  in conditions (Join and Where Clause)
    Frank Kulash Guru
    Currently Being Moderated

    Hi, Mark,

     

    If you have an inner join and a CONNECT BY clause in the same query, then the conditions in the ON clause(s) are applied before the CONNECT BY is done, but the conditions in the WHERE clause are applied afterwards.  Try this query both ways, commenting out one of the "'job != 'PRESIDENT'" conditions:

     

    SELECT  e.ename, e.job

    ,       d.dname

    ,       LEVEL   AS lvl

    FROM    scott.emp   e

    JOIN    scott.dept  d   ON    e.deptno = d.deptno

    --                      AND   job     != 'PRESIDENT' -- excludes all 14 rows

    WHERE                         job     != 'PRESIDENT' -- excludes 1 row

    START WITH  mgr     IS NULL

    CONNECT BY  mgr     = PRIOR empno

    ;

    Right now, I can't think of any other inner join  examples where it matters to the system.

    It matters to people.  Programmers are used to seeing conditions that reference 2 (or more) tables in ON clauses, and conditions that reference 1 (or fewer) tables in the WHERE clause.  If you put conditions in unexpected places, you only confuse and irritate whoever has to debug and maintain the code in future, and that could be you.

     

    Outer joins are a different story.

  • 5. Re: Difference-  in conditions (Join and Where Clause)
    MarkCooper Newbie
    Currently Being Moderated

    Thanks Frank,

    But my question is still there.

    lets say, 

    Location_id has both table ( the base table and inner join) , The right approach is to filter on location_id in where clause. but what if I want to filter the location id in Inner join condition.

     

    Will it make any difference ? or what is the right approach to work?

     

    ( Please see my query eg if needed)

     

    Thanks,

  • 6. Re: Difference-  in conditions (Join and Where Clause)
    MarkCooper Newbie
    Currently Being Moderated

    Hi Ramin,

     

    I have seen sometimes that there is no difference in PLAN but query execution time is different.

  • 7. Re: Difference-  in conditions (Join and Where Clause)
    Paul Horth Expert
    Currently Being Moderated

    MarkCooper wrote:

     

    Hi Ramin,

     

    I have seen sometimes that there is no difference in PLAN but query execution time is different.

    When there are so many other factors that can affect execution time (load on the system, caching etc.) and if the plan is the same

    why do you assume it is the placement of the predicate that is affecting it?

  • 8. Re: Difference-  in conditions (Join and Where Clause)
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    There should be no difference in the result set for inner joins, and (in principle) no difference in the execution plans. It is only on outer joins that you will see that the results and plans may be difference.  A simplistic way of viewing it (though it's probably not entirely true) is that any FILTER conditions in the ON clause take place before the join, while FILTER conditions in the WHERE clause take place after the join.

     

    Regards

    Jonathan Lewis


  • 9. Re: Difference-  in conditions (Join and Where Clause)
    Solomon Yakobson Guru
    Currently Being Moderated

    MarkCooper wrote:

     

    But my question isto understand Inner Inner join condition. Even if you replace the left outer join in your query to Inner join , it will give same records.

    Don't confuse rules and implementation. Ansi join clearly separates join conditions from filters. And this is good comparing to Oracle's native syntax. Why? Let's go back to logical design. That's where we show how entities are related. So when we write SQL joining multiple tables using ANSI joins we can specify such relations in ON clause and rest of requirements as filters in WHERE clause. If we follow such rules, reading such SQL gives a better understanding to a person who is not familiar with logical model. And, which is even more important, because of such separation ANSI syntax allows outer joining to multiple tables while Oracle native join syntax doesn't (well, it was relaxed in 12C). So yes, you will get same inner join results, but personally I prefer separating join conditions from filters.

     

    SY.

  • 10. Re: Difference-  in conditions (Join and Where Clause)
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    MarkCooper wrote:

     

    Hi Ramin,

     

    I have seen sometimes that there is no difference in PLAN but query execution time is different.

    Query Execution Time depends many factors, if you have same execution plan for two queries  ORACLE will execute them with same rule.

  • 11. Re: Difference-  in conditions (Join and Where Clause)
    Frank Kulash Guru
    Currently Being Moderated

    Hi, Mark,

     

    MarkCooper wrote:

     

    Thanks Frank,

    But my question is still there.

    lets say,

    Location_id has both table ( the base table and inner join) , The right approach is to filter on location_id in where clause. but what if I want to filter the location id in Inner join condition.

     

    Will it make any difference ? or what is the right approach to work?

     

    ( Please see my query eg if needed)

     

    Thanks,

    As most of the replies have already said:There is no difference in results or performance in most cases.

     

    I suggest putting conditions like

     

    Gl.Location_Id In ( 1767, 1747,202,1625)

     

    (which refereence only 1 table) in the WHERE clause, just to reduce confusion for people reading the code.

  • 12. Re: Difference-  in conditions (Join and Where Clause)
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    Frank,

     

    Interesting example - I can't decide whether or not it's a bug in Oracle's internal conversion or whether it is the correct behaviour. In favour of being a bug is the thought that the query ought (?) to give identical plans and results as:

     

    SELECT  ename, job
    ,       dname
    ,       LEVEL   AS lvl
    FROM (
    select e.*, d.*
    from
      scott.emp   e
    JOIN    scott.dept  d   ON    e.deptno = d.deptno
    --      AND     job     != 'PRESIDENT'
    WHERE   job     != 'PRESIDENT'
    )
    START WITH  mgr     IS NULL
    CONNECT BY  mgr     = PRIOR empno
    ;

     

    This rewrite, though, gives the same result in both cases (viz: no rows returned)

     

    Regards

    Jonathan Lewis

  • 13. Re: Difference-  in conditions (Join and Where Clause)
    ranit B Expert
    Currently Being Moderated

    Hi Jonathan,

     

    May be I'm speaking off-track but commenting this : START WITH mgr IS NULL

    gives some result.

     

     

     

  • 14. Re: Difference-  in conditions (Join and Where Clause)
    Solomon Yakobson Guru
    Currently Being Moderated

    Jonathan,

     

    Why do you think it is a bug? I'd say it is correct behavior. According to hierarchical query rules (as Frank already noted) WHERE clause is applied afterwards. So by expanding in-line view we would clearly violate it.

     

    SY.

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points