1 2 Previous Next 27 Replies Latest reply on Apr 28, 2017 6:59 PM by unleashed Go to original post
      • 15. Re: Which join syntax is better?
        BluShadow

        jaramill wrote:

         

        Some code is all ANSI, some is not, some is a mix.

         

         

         

        You should consider fixing those.

        Mixing ANSI and Oracle syntax joins in a single query is liable to cause problems and is certainly not recommended.

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

          BluShadow wrote:

           

          jaramill wrote:

           

          Some code is all ANSI, some is not, some is a mix.

           

           

           

          You should consider fixing those.

          Mixing ANSI and Oracle syntax joins in a single query is liable to cause problems and is certainly not recommended.

          Oh I'm in agreement with you 100%, but unfortunately, if I'm working on a task, I'm just to "fix//enhance" the requirement, and not do any clean-up.  Now if it's a NEW script I'm creating, then I go all ANSI.

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

            Sven W. wrote:

             

            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.

             

             

            I'm actually all for using optional keywords, so when I write my ANSI queries, I'll write "INNER JOIN" or "LEFT OUTER JOIN", for readability purposes.

            • 18. Re: Which join syntax is better?
              BluShadow

              jaramill wrote:

               

              BluShadow wrote:

               

              jaramill wrote:

               

              Some code is all ANSI, some is not, some is a mix.

               

               

               

              You should consider fixing those.

              Mixing ANSI and Oracle syntax joins in a single query is liable to cause problems and is certainly not recommended.

              Oh I'm in agreement with you 100%, but unfortunately, if I'm working on a task, I'm just to "fix//enhance" the requirement, and not do any clean-up. Now if it's a NEW script I'm creating, then I go all ANSI.

               

               

              Removing mixed syntax isn't "cleaning up", it is fixing it.  Mixed syntax is a bug in the code as it CAN have unpredictable results.

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

                BluShadow wrote:

                 

                jaramill wrote:

                 

                BluShadow wrote:

                 

                jaramill wrote:

                 

                Some code is all ANSI, some is not, some is a mix.

                 

                 

                 

                You should consider fixing those.

                Mixing ANSI and Oracle syntax joins in a single query is liable to cause problems and is certainly not recommended.

                Oh I'm in agreement with you 100%, but unfortunately, if I'm working on a task, I'm just to "fix//enhance" the requirement, and not do any clean-up. Now if it's a NEW script I'm creating, then I go all ANSI.

                 

                 

                Removing mixed syntax isn't "cleaning up", it is fixing it. Mixed syntax is a bug in the code as it CAN have unpredictable results.

                Arguably, an problem will be seen in thorough testing. If it passed testing years ago and has been working on prod, why try and rewrite it (and retest)?

                • 20. Re: Which join syntax is better?
                  BluShadow

                  It may have passed testing years ago, but then databases may have been patched or upgraded or suchlike, and you cannot guarantee that the testing used the correct circumstances to encounter an issue with it.  On the surface it may appear to work ok, but it's a known fact that mixing 'old and new' outer join syntax CAN result in issues.  If it's a known problem, then having such code is a known bug; and bugs don't always appear with every use... that can sometimes just appear in the right circumstances.

                   

                  If I ever see mixed code in our systems, it is considered a bug and is fixed.  Simple as.

                  • 21. Re: Which join syntax is better?
                    Cookiemonster76

                    I suspect that Jaramill means that in his scripts/procedures some of the SQL is written in ANSI and some in old syntax. Not that individual SQLs contain both.

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

                      It's a known fact that mixing 'old and new' outer join syntax CAN result in issues.  If it's a known problem, then having such code is a known bug; and bugs don't always appear with every use

                      Invalid argument, compare with:

                      It's a known fact that the newer syntax CAN result in issues.  If it's a known problem, then having such code is a known bug; and bugs don't always appear with every use.

                       

                      The premise in both cases is correct - but I don't think you'd argue for the second conclusion.  (Of course it's also a know fact that the older syntax CAN results in issues so .... .

                       

                      A knock-on thought - if you use new syntax to join stored views that were written with the old syntax should you also insist on rewriting the view definitions to new syntax - and shoud you then rewrite any old syntax SQL that references the view to new syntax ?

                       

                      Regards

                      Jonathan Lewis

                      • 23. Re: Which join syntax is better?
                        BluShadow

                        I had had that same knock-on thought too.

                        Indeed, we try to ensure that any queries on a system we write are all using a consistent syntax (the newer ANSI syntax joins), even if that means going and reworking views etc. too.

                        • 24. Re: Which join syntax is better?
                          Sergei Krasnoslobodtsev

                          A simple answer to your question , most likely impossible.

                          It all depends on the specific situation.

                          For example :  a) ANSI syntax for complex queries is more readable .

                          b) join option as  FULL JOIN  is   present only ANSI synax.

                           

                          So...We can cite a lot of examples of  these dialects variants.

                          But, I believe that the choice of syntax for writing query is a secondary question.

                          It all depends on the specific situation.

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

                            BluShadow wrote:

                             

                             

                             

                             

                            Removing mixed syntax isn't "cleaning up", it is fixing it. Mixed syntax is a bug in the code as it CAN have unpredictable results.

                             

                            Well cleaning up "perceived" bugs, or however it's worded, it's not part the requirement given.  If that were the case, then ANY task that I would get, I would spend time just cleaning and optimizing.  I know, I know, it is the right thing to do but unfortunately, then QA will then have to regression test, the changes I made that had nothing to do with the requirement.  Believe me, when I see bad code, I want to fix it but if it is currently working in production, I just let sleeping dogs lie because then they'll see "why did you touch that"?


                            Been there, done that.

                             

                            Not arguing your suggestion as it is best practices.

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

                              Cookiemonster76 wrote:

                               

                              I suspect that Jaramill means that in his scripts/procedures some of the SQL is written in ANSI and some in old syntax. Not that individual SQLs contain both.

                               

                               

                              Actually it is both Cookiemonster76.  I've seen two separate SQL statements, one in Oracle syntax, and the other all ANSI syntax.

                              Then I've seen ANOTHER SQL statement that is a mix of the two.   But as I stated in my previous reply to Blushadow, any task we get, if it's not part of the requirement and it's not broken, then leave it alone.

                               

                              Now anything new, yes of course I do it all in ANSI.  But then another developer may not follow my thinking and if he gets a requirement to enhance my previous work of code, he may just add to the SQL in Oracle syntax, or not.  Unless management is enforcing coding standards, it's hard for everyone to be in sync.

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

                                "I'm actually all for using optional keywords, so when I write my ANSI queries, I'll write "INNER JOIN" or "LEFT OUTER JOIN", for readability purposes."

                                 

                                I fully agree with this.  To me, I go for readability and prefer INNER JOIN and LEFT OUTER JOIN as well as putting the join criteria within parentheses, even if there is only one join condition.

                                1 2 Previous Next