1 2 Previous Next 23 Replies Latest reply: Sep 20, 2013 2:49 AM by MarkCooper RSS

    Difference-  in conditions (Join and Where Clause)

    MarkCooper

      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

          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

            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

              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

                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

                  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

                    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

                      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

                        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

                          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

                            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

                              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

                                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

                                  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

                                    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