1 2 Previous Next 25 Replies Latest reply on May 20, 2010 9:47 AM by sql_coder Go to original post
      • 15. Re: Using (+) for joins ??
        William Robertson
        I would have preferred it if the nonsensical LEFT/RIGHT keywords were optional and you could just write

        FROM strongside OUTER JOIN weakside

        which I think would be a lot clearer. I can't imagine how theey dreamt up the word "OUTER" either, for that matter. Some relational theorist chooses a random word and we all have to make what sense we can of it. Surely the distinction is not between "inner" and "outer" anything, but between weak and strong sides of the join.
        • 16. Re: Using (+) for joins ??
          Laurent Schneider
          not to forget CROSS JOIN, which is no join (carthesian product) most of the time
          select * from a,b 
          is the same as
          select * from a cross join b
          but I have already used CROSS JOIN once or twice here, like in
          select * from a full join (b cross join c);
          which is a pain to write with (+)
          • 17. Re: Using (+) for joins ??
            14728
            >
            SELECT a.col1,
            b.col2,
            c.col3,
            d.col4
            FROM a LEFT OUTER JOIN b ON a.colx = b.colx,
            a LEFT OUTER JOIN c ON a.coly = c.coly,
            b LEFT OUTER JOIN d ON b.colz = d.colz
            devmiral thats not quite right, as by seperating with , you include the table again.

            I think (no db access to test) this is correct:
            SELECT a.col1,
                   b.col2,
                   c.col3,
                   d.col4
            FROM a
                  LEFT OUTER JOIN b ON a.colx = b.colx
                  LEFT OUTER JOIN c ON a.coly = c.coly
                  LEFT OUTER JOIN d ON b.colz = d.colz
            • 18. Re: Using (+) for joins ??
              14728
              Agreed LEFT and RIGHT are rubbish!
              • 19. Re: Using (+) for joins ??
                William Robertson
                Or you could write the join conditions forwards. Or Jonathan Lewis and I could give up banging on about it since everyone seems to prefer backwards for reasons I will never understand (much like the enduring popularity of BINARY_INTEGER in PL/SQL, just to wander completely off topic - anyone else noticed that?)
                • 20. Re: Using (+) for joins ??
                  John Spencer
                  "anyone else noticed that?" What, you wandering completely off topic?

                  John
                  • 21. Re: Using (+) for joins ??
                    6363
                    Which way did he go left or right?

                    I am much happier knowing all you need is left and that right is only for twisted psychos. Unfortunately now I have to get used to forward and backward as well. No doubt the eventual incorporation of forward and backwards into the SQL syntax by ANSI will help clarify that too. It seems that we only really need forward joins, but that people are mostly backward psychos in this respect.
                    • 22. Re: Using (+) for joins ??
                      SomeoneElse
                      but that people are mostly backward psychos in this respect.
                      !taht tneser I ,yeH
                      • 23. Re: Using (+) for joins ??
                        William Robertson
                        > Which way did he go left or right?

                        He made three lefts ;)
                        • 24. Re: Using (+) for joins ??
                          Luis Cabral
                          Hi

                          The main reason I absolutely prefer ansi joins to Oracle joins is that you separate the join conditions from the business logic conditions.

                          Eg.

                          select *
                          from table1, table2
                          where table2.c1=table1.c1 -- join condition
                          and table2.c2=table1.c2 -- join condition
                          and table1.c3='abc' -- business rule condition
                          and table1.c4 is not null -- business rule condition

                          select *
                          from table1
                          join table2 on table2.c1 = table1.c1
                          and table2.c2 = table1.c2
                          where table1.c3='abc'
                          and table1.c4 is not null


                          The example above is very simple, but imagine a join with 10 tables; the where clause can get huge and hard to identify what conditions are joining tables and which ones are based on business rules. The ansi syntax makes this separation much clear I think.

                          Luis
                          • 25. Re: Using (+) for joins ??
                            sql_coder
                            I agree on that, to read SQL queries with explicit JOIN syntax, not only for OUTER JOINS but also for INNER JOINS is much better to read, and also more easy to write. The only reason to use the old syntax is when you are too lazy to learn something new. I my company we do not allow implicit SQL anymore.

                            Ikrischer
                            1 2 Previous Next