1 2 Previous Next 26 Replies Latest reply: Nov 8, 2010 10:03 AM by 6363 Go to original post RSS
      • 15. Re: ANSI to traditional (+) join syntax
        andyschwarz
        So after all these years, you guys still prefer loooong where clauses where you "miss" easily a field, either in the join criteria or just a (+) and accept the "fact" that you can not "left" to more than one table and.....

        I can understand what you are feeling, it was "funny" when I first had to use it, but men, that's a long time ago.
        And don't tell me the "(+)" is not "funny" and "most unnecessarily complicated" (.e.g if you have more than 1-3 join conditions...)

        -- andy
        • 16. Re: ANSI to traditional (+) join syntax
          odie_63
          Here we go again... ;\
          I was just about to say that.

          ANSI join syntax being overly complicated is a point of view, IMHO.
          If one makes the effort to learn it (as one can do for any other thing), it'll become easy to understand.
          I personally feel comfortable with ANSI syntax, as it often improves readability (what is a join, what is a predicate, etc.) in complex queries.
          • 17. Re: ANSI to traditional (+) join syntax
            6363
            andyschwarz wrote:

            So after all these years, you guys still prefer loooong where clauses where you "miss" easily a field, either in the join criteria or just a (+) and accept the "fact" that you can not "left" to more than one table and.....
            No.

            I have simply not drank the ANSI kool aid and therefore do not have to justify the fact that I wasted my time learning what is in effect computer programming written in Latin for no added value, apart from the fact that once I have wasted my time learning it it is easier to read.

            Oracle can outer join to more than one table, you misunderstand the error message.

            Re: ORA-01417: a table may be outer joined to at most one other table

            All you need to implement set theory in SQL is that something is equal to or optionally equal to something else. The original syntax does that very clearly. Everything ANSI added is not needed and complicated, which is a typical result of design by committee.

            http://en.wikipedia.org/wiki/Design_by_committee

            >
            Design by committee is a term referring to a style of design and its resultant output when a group of entities comes together to produce something (often the design of technological systems or standards), particularly in the presence of poor leadership. The defining characteristics of "design by committee" are needless complexity, internal inconsistency, logical flaws, banality, and the lack of a unifying vision.
            • 18. Re: ANSI to traditional (+) join syntax
              6363
              odie_63 wrote:
              Here we go again... ;\
              I was just about to say that.

              ANSI join syntax being overly complicated is a point of view, IMHO.
              If one makes the effort to learn it (as one can do for any other thing), it'll become easy to understand.
              If it is not more complicated why does it take extra effort to learn?
              I personally feel comfortable with ANSI syntax, as it often improves readability (what is a join, what is a predicate, etc.) in complex queries.
              Please explain why a join should not be considered a predicate.
              • 19. Re: ANSI to traditional (+) join syntax
                Z?
                To be honest, I found it quite simple to learn.

                Also, looking at the list of limitations in the Oracle docs was enough to make me switch...
                Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:
                
                •You cannot specify the (+) operator in a query block that also contains FROM clause join syntax.
                
                •The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view.
                
                •If A and B are joined by multiple join conditions, then you must use the (+) operator in all of these conditions. If you do not, then Oracle Database will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.
                
                •The (+) operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query.
                
                •You cannot use the (+) operator to outer-join a table to itself, although self joins are valid. For example, the following statement is not valid:
                
                -- The following statement is not valid:
                SELECT employee_id, manager_id 
                   FROM employees
                   WHERE employees.manager_id(+) = employees.employee_id;
                However, the following self join is valid:
                
                SELECT e1.employee_id, e1.manager_id, e2.employee_id
                   FROM employees e1, employees e2
                   WHERE e1.manager_id(+) = e2.employee_id
                   ORDER BY e1.employee_id, e1.manager_id, e2.employee_id;
                •The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
                
                •A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.
                
                •A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.
                
                If the WHERE clause contains a condition that compares a column from table B with a constant, then the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated nulls for this column. Otherwise Oracle returns only the results of a simple join.
                
                In a query that performs outer joins of more than two pairs of tables, a single table can be the null-generated table for only one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C. Refer to SELECT for the syntax for an outer join.
                I suppose it is just down to personal preference in the end :)
                • 20. Re: ANSI to traditional (+) join syntax
                  6363
                  Munky wrote:

                  Also, looking at the list of limitations in the Oracle docs was enough to make me switch...
                  Seriously, did you actually read that list?

                  >
                  You cannot use the (+) operator to outer-join a table to itself,
                  >

                  Outer join a table to itself? How is that supposed to work? What combination of natural, inner, left, outer, right, full and cross joins would get you that in ANSI? They obviously missed a trick there and there will soon be self left and right outer (optional) joins to perform this much needed omission from using standard equivalence operators. Or possibly when forward left under outer joins are implemented they will be able to do it. Who can tell?

                  Re: Left outer join versus right outer join - Whats different ?
                  To be honest, I found it quite simple to learn.
                  You aren't from Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch are you?

                  Re: Implicit Join or Explicit Join...which is more efficient???

                  http://www.llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch.com/
                  • 21. Re: ANSI to traditional (+) join syntax
                    odie_63
                    If it is not more complicated why does it take extra effort to learn?
                    I didn't mention "extra" effort, I just said "effort" as in "decide to do something".
                    Please explain why a join should not be considered a predicate.
                    Yes, abuse of language here, my bad.
                    But I think you got what I meant : join predicates grouped in a JOIN clause vs. filter predicates in the WHERE clause.
                    • 22. Re: ANSI to traditional (+) join syntax
                      Z?
                      I honestly think that it's easier to read and understand unless you've been exclusively using the old Oracle syntax for countless years and find it difficult (can't be bothered) learning a different way of doing something.
                      >
                      Seriously, did you actually read that list?
                      >
                      Yes.
                      >
                      You aren't from Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch are you?
                      >
                      No.

                      Pointing to countless threads whereby you have expressed the same opinion to someone else doesn't really back up your point.

                      As I said, it's a matter of opinion - we disagree, and I can't see us changing each other minds, so let's leave it at that! :D
                      • 23. Re: ANSI to traditional (+) join syntax
                        6363
                        Munky wrote:

                        Pointing to countless threads whereby you have expressed the same opinion to someone else doesn't really back up your point.
                        Some of them are links showing things like the original syntax joining to more than one table in response to countless assertions from proponents of ANSI syntax that it cannot be done. I should know better than to try and mix logic with discussions of ANSI syntax.
                        • 24. Re: ANSI to traditional (+) join syntax
                          Z?
                          >
                          Some of them are links showing things like the original syntax joining to more than one table in response to countless assertions from proponents of ANSI syntax that it cannot be done. I should know better than to try and mix logic with discussions of ANSI syntax.
                          >
                          I spotted that and read the original threads at the time. I disagree that the old Oracle syntax is easier to read or understand, you disagree that ANSI is. I think, therefore, that we have reached an impasse - so why not just leave it there?

                          Cheers

                          Ben
                          • 25. Re: ANSI to traditional (+) join syntax
                            BluShadow
                            3360 wrote:
                            Munky wrote:

                            Pointing to countless threads whereby you have expressed the same opinion to someone else doesn't really back up your point.
                            Some of them are links showing things like the original syntax joining to more than one table in response to countless assertions from proponents of ANSI syntax that it cannot be done. I should know better than to try and mix logic with discussions of ANSI syntax.
                            Nah, it's not that it cannot be done with oracle syntax, just that it's easier with ANSI syntax as it doesn't involve creating subqueries.

                            I used to be a big user of Oracle syntax myself and hated the idea of learning the ANSI syntax, but then I came upon a situation where I needed to outer join to more than one table in a pretty big query and adding another sub-select wasn't going to be pretty, so I converted the query to ANSI syntax, and found that actually, it's not really any different from Oracle syntax, it just reads a little differently, and I now find that it helps to layout the joins from the filters more easily and helps to ensure you don't miss out the join conditions easily (which is easily done in a complex query using oracle syntax). I still use oracle syntax for small queries, but when there's multiple table with multiple join conditions and outer joins, the ANSI syntax just seems to make it easier to follow imho.

                            Each to their own. ;)
                            • 26. Re: ANSI to traditional (+) join syntax
                              6363
                              BluShadow wrote:
                              3360 wrote:

                              Some of them are links showing things like the original syntax joining to more than one table in response to countless assertions from proponents of ANSI syntax that it cannot be done. I should know better than to try and mix logic with discussions of ANSI syntax.
                              Nah, it's not that it cannot be done with oracle syntax, just that it's easier with ANSI syntax as it doesn't involve creating subqueries.
                              Well, I know that it is not true that it cannot be done and I have never said that it cannot be done, which is why I posted the link in response to pro-ANSI statements in this thread and others that it cannot be done.

                              E.g.

                              >
                              and accept the "fact" that you can not "left" to more than one table and.....
                              >

                              And I also agree that easier to read is entirely subjective and is simply an opinion.

                              This leaves us with the only thing you can truly say about ANSI SQL is that after you have learned it you find it easier to read than before you learned it.

                              It has no other advantages whatsoever.

                              In my opinion I find subqueries natural and easy to read. A lengthy statement without them is a nightmare.
                              Each to their own. ;)
                              Agreed.
                              1 2 Previous Next