1 2 Previous Next 27 Replies Latest reply on Apr 28, 2017 6:59 PM by unleashed

    Which join syntax is better?

    d072ee8b-ecdc-4624-9d62-e3d4ab2d35b5

      Oracle experts, which join syntax is more efficient, correct or proper? I learned it the first way (with ( + )) but someone is saying the second way is better. I have seen documentation with both but most recently like ex 2.

       

      Ex 1

      select count(*)

      from

      fmsdata.gl001m18 gl001,

      fmsdata.gl020m18 gl020,

      fmsdata.gl014m18 gl014,

      fmsdata.gl012m18 gl012,

      where gl001.s_source = gl020.source_code

      and gl001.ac_account = gl014.account

      and gl001.ac_subacct = gl014.subacct

      and gl001.ac_detacct = gl014.detacct

      and gl001.d_asset_number = gl012.asset_number(+)

      and gl001.d_asset_type = gl012.asset_type(+)

       

      Ex 2

      select *

      from (

      (fmsdata.gl001m18 gl001

      inner join fmsdata.gl020m18 gl020 on gl001.s_source = gl020.source_code)

      inner join fmsdata.gl014m18 gl014 on ((gl001.ac_account = gl014.account)

      and (gl001.ac_subacct = gl014.subacct)) and (gl001.ac_detacct = gl014.detacct)

      )

      left outer join fmsdata.gl012m18 gl012 on (gl001.d_asset_number = gl012.asset_number)

          and (gl001.d_asset_type = gl012.asset_type)

        • 1. Re: Which join syntax is better?
          John Thorton

          d072ee8b-ecdc-4624-9d62-e3d4ab2d35b5 wrote:

           

          Oracle experts, which join syntax is more efficient, correct or proper? I learned it the first way (with ( + )) but someone is saying the second way is better. I have seen documentation with both but most recently like ex 2.

           

          Ex 1

          select count(*)

          from

          fmsdata.gl001m18 gl001,

          fmsdata.gl020m18 gl020,

          fmsdata.gl014m18 gl014,

          fmsdata.gl012m18 gl012,

          where gl001.s_source = gl020.source_code

          and gl001.ac_account = gl014.account

          and gl001.ac_subacct = gl014.subacct

          and gl001.ac_detacct = gl014.detacct

          and gl001.d_asset_number = gl012.asset_number(+)

          and gl001.d_asset_type = gl012.asset_type(+)

           

          Ex 2

          select *

          from (

          (fmsdata.gl001m18 gl001

          inner join fmsdata.gl020m18 gl020 on gl001.s_source = gl020.source_code)

          inner join fmsdata.gl014m18 gl014 on ((gl001.ac_account = gl014.account)

          and (gl001.ac_subacct = gl014.subacct)) and (gl001.ac_detacct = gl014.detacct)

          )

          left outer join fmsdata.gl012m18 gl012 on (gl001.d_asset_number = gl012.asset_number)

          and (gl001.d_asset_type = gl012.asset_type)

          post  EXECUTION PLANS from both SELECT  statements so we can compare them

           

           

          Re: 2. How do I ask a question on the forums?

          • 2. Re: Which join syntax is better?
            Gary_A

            I believe it's a matter of preference. To me, example 1 is easier to understand because I have been using Oracle syntax for over 20 years. I still find it hard to decipher ANSI syntax sometimes.

            • 3. Re: Which join syntax is better?
              Frank Kulash

              Hi,

              d072ee8b-ecdc-4624-9d62-e3d4ab2d35b5 wrote:

               

              Oracle experts, which join syntax is more efficient, correct or proper? I learned it the first way (with ( + )) but someone is saying the second way is better. I have seen documentation with both but most recently like ex 2.

               

              Ex 1

              select count(*)

              from

              fmsdata.gl001m18 gl001,

              fmsdata.gl020m18 gl020,

              fmsdata.gl014m18 gl014,

              fmsdata.gl012m18 gl012,

              where gl001.s_source = gl020.source_code

              and gl001.ac_account = gl014.account

              and gl001.ac_subacct = gl014.subacct

              and gl001.ac_detacct = gl014.detacct

              and gl001.d_asset_number = gl012.asset_number(+)

              and gl001.d_asset_type = gl012.asset_type(+)

               

              Ex 2

              select *

              from (

              (fmsdata.gl001m18 gl001

              inner join fmsdata.gl020m18 gl020 on gl001.s_source = gl020.source_code)

              inner join fmsdata.gl014m18 gl014 on ((gl001.ac_account = gl014.account)

              and (gl001.ac_subacct = gl014.subacct)) and (gl001.ac_detacct = gl014.detacct)

              )

              left outer join fmsdata.gl012m18 gl012 on (gl001.d_asset_number = gl012.asset_number)

              and (gl001.d_asset_type = gl012.asset_type)

              In most cases (including the one you posted, probably), the two ways are equally efficient.  When there is a difference, ANSI syntax (using the keyword JOIN) will be more efficient.

               

              Which one is more correct or proper depends on what you mean by "correct" and "proper".

               

              For new coding, I always use ANSI syntax, for all joins, not just outer joins.  There are some things (like partitioned outer joins and full outer joins) that are much simpler using ANSI syntax.  Also, ANSI syntax has fewer restrictions on what kinds of join conditions you can use in outer joins.

               

              I find ANSI syntax easier to read and understand, and that means debugging and maintenance is easier.

              • 4. Re: Which join syntax is better?
                CarlosDLG

                I worked for many years using the old syntax too, but now I prefer (and recommend) the ANSI syntax, for the same reasons mentioned by Frank in the previous reply.

                 

                I remember there are some very rare situations in which Oracle still expects the old syntax to be able to do certain things, but in general, using the ANSI syntax has more advantages.

                 

                That being said, I still recommend knowing how to do things in both ways, because you will most likely have to maintain old code that uses the old syntax, and it is usually not a good idea to mix both syntaxes.

                • 5. Re: Which join syntax is better?
                  mathguy

                  ANSI syntax (the newer one) has a few advantages. It is SQL standard, so if you ever need to port your code to other RDBMS's, it will be easier to do so for queries using the standard syntax. Also, the standard (ANSI) syntax forces you to write the join condition right after each join, so it is much harder to forget join conditions (leading to cross joins).

                   

                  There are, however, a very small number of instances where ANSI syntax is not allowed, only the old Oracle proprietary syntax will work. One example is in materialized views, or at least a subclass of those (perhaps those with fast refresh... not sure, but a Google search would clarify this point quickly). It is not important to memorize exactly which instances require old syntax, it is important only to remember that they exist - and when something doesn't work, check (Google) to see if that may be the reason.

                  • 6. Re: Which join syntax is better?
                    Tércio Costa

                    Don´t use Oracle join operator. Oracle don´t recommend this anymore. See the link: http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm#SQLRF52336

                    • 7. Re: Which join syntax is better?
                      unleashed

                      It should operationally equivalent in Oracle.  It comes to clarity and self-documentation.  I believe the ANSI join syntax is easier to read and therefore, I can more quickly ascertain the intent of the query and its joins.  I also believe it makes the intent of the joins more clear.  So, I was able to make the ANSI joins the standard for joins on my most recent project.   However, we still had Oracle style joins from over 15 years of development.

                      • 8. Re: Which join syntax is better?
                        AndrewSayer

                        Use whichever you feel most comfortable with.

                         

                        For the record, both are examples of ANSI syntax, the older style is ANSI-89 and the newer style is ANSI-92.

                         

                        There are examples of things you can do in one style but not the other, mathguy has already mentioned restrictions on materialized views with ANSI-92, you may also find you can't use ANSI-92 to join to a table that you are using flashback query on.

                         

                        ANSI-89 has it's own restrictions you may have already run into.

                        • 9. Re: Which join syntax is better?
                          CarlosDLG

                          Andrew Sayer wrote:

                           

                          ...

                           

                          For the record, both are examples of ANSI syntax, the older style is ANSI-89 and the newer style is ANSI-92.

                           

                          ....

                           

                          The Oracle outer join operator was not part of the ANSI-89 standard.

                          • 10. Re: Which join syntax is better?
                            Mustafa KALAYCI

                            There is no performance issue as other said, I always use ANSI syntax, much much more easier to read for me, when, let's say, 4 table is joined and these table are related with 2 or 3 columns with each other a select statement can be like this:

                             

                            select ...

                            from Table1 a, table2 b,table3 c,table4 d

                            where a.field1 = b.field1

                            and a.field2 = b.field2

                            and b.field3 = c.field3

                            and b.field4 = c.field4

                            ....

                            and a.fieldx = 'bala'

                            and c.fieldx = 'gla gla'

                            ...

                            d.field1 = a.field1

                             

                            this syntax is too complicated and hard to read for me, join conditions and filter conditions are mixed and I never can see clearly which table is connecting to which one. Also much vulnerable for missing conditions. this on the other hand:

                             

                            select ....
                            from   table1 a 
                                     join table2 b on (b.field1 = a.field1 and a.field2 = b.field2)
                                       join table3 c (c.field3 = b.field3 and c.field4 = b.field4)
                                    join table4 d on (....)
                            where a.fieldx = 'bla'
                              and d.fieldx = 'gla gla'
                            

                             

                            is much more clear to me and it is impossible to forget join one of them and cause to a cross join for ex. also as far as I know, you can not do FULL JOIN without ansi syntax.

                            • 11. Re: Which join syntax is better?
                              AndrewSayer

                              CarlosDLG wrote:

                               

                              Andrew Sayer wrote:

                               

                              ...

                               

                              For the record, both are examples of ANSI syntax, the older style is ANSI-89 and the newer style is ANSI-92.

                               

                              ....

                               

                              The Oracle outer join operator was not part of the ANSI-89 standard.

                              Cheers for the correction, I guess I was always under the wrong impression.

                              • 12. Re: Which join syntax is better?
                                jaramill

                                I used to like the Oracle syntax but as someone pointed out via a link to the documentation, even Oracle says at some point in the future that the outer join syntax (aka the plus sign) will go away.  And at my current client company, they use a mix and match.  Some code is all ANSI, some is not, some is a mix.  I have gone to just writing it the ANSI, and soon we will have to change some of our scripts due to a "bug" in Oracle that I accidentally came across recently.

                                 

                                ORA-01792: maximum number of columns in a table or view is 1000

                                 

                                After researching this on the web, there are two (2) threads here on these forums where customers have ran into
                                this same issue.

                                 

                                1. https://community.oracle.com/thread/3691573
                                2. https://community.oracle.com/thread/4001267

                                 

                                This is caused when a SQL query uses ANSI JOIN syntax, and combined with multiple tables using the SELECT * notation,  Oracle
                                combines all referenced tables using the SELECT * notation and “internally” tries to create a table or view with all the columns.  Well in Oracle the limit on the number of columns a table can have is 1000.

                                 

                                And for the programs we have here, this is a major problem as the majority of our extracts use this syntax of SELECT *.  There is an Oracle patch (MOS Document 9256994.8) for this bug issue (bug 9256994) but it is only to “disable” the error from being thrown.  The real solution would be to spell out ALL the columns, or not use the ANSI JOIN syntax.

                                 

                                Just my 2 cents on the topic.

                                • 13. Re: Which join syntax is better?
                                  Sven W.

                                  The second (ANSI) syntax is better. But the example is bad. All the parenthesis in the second example can be removed.

                                  Also the INNER keyword from INNER JOIN and the OUTER keyword from OUTER JOIN is not needed.

                                   

                                  Ex 1

                                  select count(*)

                                  from

                                  fmsdata.gl001m18 gl001,

                                  fmsdata.gl020m18 gl020,

                                  fmsdata.gl014m18 gl014,

                                  fmsdata.gl012m18 gl012,

                                  where gl001.s_source = gl020.source_code

                                  and gl001.ac_account = gl014.account

                                  and gl001.ac_subacct = gl014.subacct

                                  and gl001.ac_detacct = gl014.detacct

                                  and gl001.d_asset_number = gl012.asset_number(+)

                                  and gl001.d_asset_type = gl012.asset_type(+)

                                  ;

                                   

                                  Ex 2

                                  select *

                                  from fmsdata.gl001m18 gl001

                                  join fmsdata.gl020m18 gl020 on gl001.s_source = gl020.source_code

                                  join fmsdata.gl014m18 gl014 on gl001.ac_account = gl014.account and gl001.ac_subacct = gl014.subacct and gl001.ac_detacct = gl014.detacct

                                  left join fmsdata.gl012m18 gl012 on gl001.d_asset_number = gl012.asset_number and gl001.d_asset_type = gl012.asset_type

                                  ;

                                  Now imagine that your task is to remove table gl014m18 from the count.

                                  Is this easier to do in the first example or in the second example?

                                   

                                  Maintenance of code is where the ansi syntax by far outperforms the traditional oracle syntax.

                                   

                                  The other typical example is if you want to change a join into an outer join. Lets say table gl20m18 needs to be outer join.

                                   

                                  Useing ansi syntax you just change the JOIN to LEFT JOIN. (and check if the following tables refer to table gl20m18 and need to be outer joins as well).

                                  Using the traditional oracle syntax you need to check

                                  a) where are all the columns that join this table

                                  and b) which side of the equation needs the ( + ) operator to go.

                                   

                                  This is way more difficult to do.

                                  • 14. Re: Which join syntax is better?
                                    Jonathan Lewis

                                    In most cases (including the one you posted, probably), the two ways are equally efficient.  When there is a difference, ANSI syntax (using the keyword JOIN) will be more efficient.

                                     

                                    When there is a difference it is more likely to be the original syntax that is more efficient since the optimizer transforms the newer syntax into a traditional form before optimising it - and there have been cases where some of the transformations available to "pure" Oracle syntax are not available to the transformed version of something that is notionally the equivalent "modern" syntax.

                                     


                                    Regards

                                    Jonathan Lewis

                                    1 2 Previous Next