6 Replies Latest reply on Oct 10, 2013 7:59 AM by Sven W.

    use ansi join syntax

    natpidgeon

      From what i have been reading is better to use the new syntax(dont know how new it is)

       

        • 1. Re: use ansi join syntax

          From what i have been reading is better to use the new syntax(dont know how new it is)

          We can't comment on that since you didn't bother to post a link to what you 'have been reading'.

          which i did in this code. i get no error and it works.

          my question is

          1) even thought it works, does it look correct?

           

          Your question makes no sense. What 'cosmetic' things are we supposed to comment about? The indenting, spacing, etc?

          2) is the new way  suppose to be faster?

          1. you understand two languages, french and german

          2. your significant other wants you to go to the market and pick up a list of food items

           

          Can you do the work faster if you are given the shopping list in German o will you be faster if the list is in French?

          me personally i like the old way but i have to change it

          No - you CHOOSE to change it. If someone else is really forcing you to change it then it doesn't really make any difference since any issues or problems will be THEIR responsibility.

           

          p.s. there can be some issues and problems for some query constructs.

          • 2. Re: use ansi join syntax
            Paul  Horth

            To be honest it doesn't look correct. I am assuming that cvUnitNum and in_property_id are variables?

            I am also assuming that the actual join condition is between pun.resv_unit_id and ru.resv_unit_id.

            In which case, that is what needs to be in the ON part of the inner join.

             

            As far as I know, the speed is the same.

            • 3. Re: use ansi join syntax
              davidp 2

              The ANSI join syntax was new to Oracle in Oracle 8i in 1998 - that's 15 years old.

              It can produce more readable code, and is both more readable and less human-error prone for outer joins.

              The ANSI format lets you outer join between multiple tables in a way the old oracle-specific ( + ) syntax did not and introduces FULL OUTER JOIN which you should use very rarely.

              You should not use NATURAL JOIN in code - adding unrelated columns to the tables involved can make it give very different results.

              There have not been significant bugs for ANSI joins in Oracle since Oracle 10.2 was introduced about 8 years ago.

               

              As Paul said, the ON part should be the join criteria between the tables. The were clause should be the filtering of the joined tables.

              So assuming start_date, end_date and in_property_id are variables

              SELECT resv_num, unit_date

                  FROM p_resv_unit ru

                  INNER JOIN p_pm_unit_night pun

                  ON pun.resv_unit_id = ru.resv_unit_id

                  WHERE pun.property_id = in_property_id

                  AND pun.unit_date BETWEEN start_date AND end_date

                  AND pun.pm_unit_num = cvUnitNum;

              If start_date and end_date were columns in p_resv_unit the query would be:

              SELECT resv_num, unit_date

                  FROM p_resv_unit ru

                  INNER JOIN p_pm_unit_night pun

                  ON pun.resv_unit_id = ru.resv_unit_id AND pun.unit_date BETWEEN ru.start_date AND ru.end_date

                  WHERE pun.property_id = in_property_id

                      AND pun.pm_unit_num = cvUnitNum;

               

              Inner join queries work with criteria in the wrong place, but they're harder to read. Outer joins don't work unless you put the criteria in the right place.

              • 4. Re: use ansi join syntax
                Inner join queries work with criteria in the wrong place, but they're harder to read. Outer joins don't work unless you put the criteria in the right place.

                Here are two links re Oracle vs. ANSI syntax worth bookmarking.

                 

                The first has several simple examples of the differences in the syntax especially as relates to predicates and where clauses. It is written by Maria Colgan who is a member of Oracle's Optimizer development team and has written many articles on optimizer topics.

                https://blogs.oracle.com/optimizer/entry/outerjoins_in_oracle

                 

                The other link is to one of Jonathan Lewis' Oracle Scratchpad articles

                http://jonathanlewis.wordpress.com/2010/12/03/ansi-argh/

                • 5. Re: use ansi join syntax
                  Frank Kulash

                  Hi,

                   

                  Regarding efficiency, I suspect that some things that require a lot more coding in the old syntax (FULL OUTER JOIN in particular) might be faster using ANSI syntax.  In most correctly coded queries, however, the two ways will be equally efficient.  (I added "correctly coded" because inefficient unintended cross-joins often happen with the old notation.  That's not really an efficiency issue, of course.)

                   

                  Just to emphasize a couple of things already said:

                  For INNER joins, you'll get the same results whether conditions are in the ON clause or the WHERE clause,  It's very confusing, though, if you have conditions involving 2 (or more) tables in the WHERE clause.  The compiler will understand, but people debugging and maintaining the code (including you) will waste time figuring things out.

                  For OUTER joins, it does make a difference.  Moving a condition from the ON clause to the WHERE clause, or vice versa, will, in general, change the results.  If you say "FROM mandatory_table LEFT OUTER JOIN optional_table", then, most of the time, all conditions involving columns from optional_table should be in ON clauses, not the WHERE clause.

                  • 6. Re: use ansi join syntax
                    Sven W.

                    Suggestion: Write a query that involves not 2 tables but 10.

                    Then compare the old Oracle against the Ansi syntax.

                    Now eliminate a table and a join but add two others.

                    Compare again.

                     

                    My main point for using ansi style is that it helps tremendously to write maintainable code. Code that other programmers will also understand and be able to handle.

                    The old problem of creating cross joins is now a thing of the past. Very often I encountered queries where there was a DISTINCT in the select list. Why? Because some programmer moron didn't noticed that he forgot a join condition in the where clause and then eliminated the resulting duplicates with the distinct.

                     

                    Compared to that possible tiny performance differences do not play any role in choosing the syntax style.