1 2 Previous Next 25 Replies Latest reply on May 20, 2010 9:47 AM by sql_coder

    Using (+) for joins ??

    BOG
      I know that years ago I'd see this as a way to do outer joins
      a.user_id = b.user_id (+)
      but did not know that its still supported.

      The reason I'm asking is I overheard one of our developers answering a question on how to do outer joins and I then asked her to send me her code.

      So my question is, should this still be used as a way to do joins?
        • 1. Re: Using (+) for joins ??
          Ghulam Mustafa Butt
          Until Oracle 9i database I have used this. I am not 100% sure about 10G.

          Thanks
          Ghulam
          • 2. Re: Using (+) for joins ??
            Laurent Schneider
            SQL REference:
            Using Outer Joins: Examples The following example shows how a partitioned outer join fills data gaps in rows to facilitate analytic function specification and reliable report formatting. The example first creates a small data table to be used in the join:

            SELECT d.department_id, e.last_name
            FROM departments d LEFT OUTER JOIN employees e
            ON d.department_id = e.department_id
            ORDER BY d.department_id, e.last_name;


            Users familiar with the traditional Oracle Database outer joins syntax will recognize the same query in this form:

            SELECT d.department_id, e.last_name
            FROM departments d, employees e
            WHERE d.department_id = e.department_id(+)
            ORDER BY d.department_id, e.last_name;


            Oracle strongly recommends that you use the more flexible FROM clause join syntax shown in the former example.
            • 3. Re: Using (+) for joins ??
              SomeoneElse
              Yes, it still works.

              But Oracle recommends the use of the OUTER JOIN keyword over the Oracle-only (+) operator.

              (+) has some restrictions that don't exist with the ANSI syntax.
              • 4. Re: Using (+) for joins ??
                orawarebyte
                Yes ,its traditional oracle syntax for left outer join and still in use,it returns all the rows from the table on the left side of the join, along with the values from the right hand side, or NULLs if a matching row doesn't exist.

                SQL> SELECT e.ename,d.dname,e.deptno
                  2  FROM   emp e,dept d
                  3  WHERE  e.deptno=d.deptno(+)
                  4  /
                
                ENAME      DNAME              DEPTNO
                ---------- -------------- ----------
                MILLER     ACCOUNTING             10
                CLARK      ACCOUNTING             10
                KING       ACCOUNTING             10
                SCOTT      ACCOUNTING             10
                SCOTT      ACCOUNTING             10
                SCOTT      ACCOUNTING             10
                SCOTT      ACCOUNTING             10
                SCOTT      ACCOUNTING             10
                SCOTT      ACCOUNTING             10
                SCOTT      ACCOUNTING             10
                SCOTT      ACCOUNTING             10
                
                ENAME      DNAME              DEPTNO
                ---------- -------------- ----------
                ADAMS      RESEARCH               20
                SCOTT      RESEARCH               20
                SMITH      RESEARCH               20
                FORD       RESEARCH               20
                JONES      RESEARCH               20
                WARD       SALES                  30
                JAMES      SALES                  30
                TURNER     SALES                  30
                ALLEN      SALES                  30
                MARTIN     SALES                  30
                BLAKE      SALES                  30
                Now since 9i supports the ANSI/ISO standards and its recommend to use ANSI/ISO standard which allows to migration between diffrent RDBMS to reuse this capability.
                SQL> SELECT e.ename,d.dname,e.deptno
                  2  FROM   emp e
                  3         LEFT OUTER JOIN dept d ON (e.deptno=d.deptno)
                  4  /
                
                ENAME      DNAME              DEPTNO
                ---------- -------------- ----------
                MILLER     ACCOUNTING             10
                CLARK      ACCOUNTING             10
                KING       ACCOUNTING             10
                SCOTT      ACCOUNTING             10
                SCOTT      ACCOUNTING             10
                SCOTT      ACCOUNTING             10
                SCOTT      ACCOUNTING             10
                SCOTT      ACCOUNTING             10
                SCOTT      ACCOUNTING             10
                SCOTT      ACCOUNTING             10
                SCOTT      ACCOUNTING             10
                
                ENAME      DNAME              DEPTNO
                ---------- -------------- ----------
                ADAMS      RESEARCH               20
                SCOTT      RESEARCH               20
                SMITH      RESEARCH               20
                FORD       RESEARCH               20
                JONES      RESEARCH               20
                WARD       SALES                  30
                JAMES      SALES                  30
                TURNER     SALES                  30
                ALLEN      SALES                  30
                MARTIN     SALES                  30
                BLAKE      SALES                  30
                Khurram
                • 5. Re: Using (+) for joins ??
                  chuckers
                  Anybody else wonder about this? We have about 15 Developers who'd mostly learn the new syntax quickly, and 15 Analysts who'd take much longer. The existing syntax is understood by all, though.

                  We recently decided to stick with the (+) syntax for 2 reasons:
                  1) because of the time it would take to get everyone up-to-speed.
                  2) ODBC drivers recognized the (+) syntax (which I didn't expect) so it costs us little to convert from native drivers to ODBC where the native drivers are becoming unsupported.

                  I hope it was the right choice.
                  -=cf
                  • 6. Re: Using (+) for joins ??
                    6363
                    15 Analysts who'd take much longer. The existing syntax is understood by all, though.
                    I sympathize. I know there are benefits to the ANSI syntax but it makes my head hurt.

                    Based on the examples shown earlier
                    FROM departments d, employees e
                    WHERE d.department_id = e.department_id(+)
                    Where it is clearly outer joining to the employees table.

                    Becomes
                    FROM departments d LEFT OUTER JOIN employees e
                    ON d.department_id = e.department_id
                    Where does the left come from? Is it the left most table or column? If it is why is the outer join not on department. How can I tell which table is on the left?

                    Is it the same as
                    FROM employees e RIGHT OUTER JOIN departments d
                    ON d.department_id = e.department_id
                    Or
                    FROM employees e RIGHT OUTER JOIN departments d
                    ON e.department_id = d.department_id
                    I hope it was the right choice.
                    I would say the old style syntax has about as much chance of going away as empty strings being not null, or longs from the data dictionary, or decode. It would break so much code it would not be funny. It would at least appear to be a safe choice.
                    • 7. Re: Using (+) for joins ??
                      William Robertson
                      > FROM departments d LEFT OUTER JOIN employees e
                      ON d.department_id = e.department_id

                      Where does the left come from? Is it the left most table or column? If it is why is the outer join not on department. How can I tell which table is on the left?>

                      Aha but first you would write the damn join forwards to make it easier to read. Then you might begin each join condition on its own line, giving something like this

                      <blockquote><pre><b>FROM </b>departments d
                      <b>LEFT OUTER JOIN </b>employees e
                      <b>ON </b>e.department_id = d.department_id</pre></blockquote>

                      Now I think it becomes clear that
                      1. We start with DEPARTMENTS.
                      2. For each row, we want the corresponding EMPLOYEES rows (if any).
                      3. The EMPLOYEES rows we want are the ones where e.department_id = the one we already have.

                      The LEFT and RIGHT keywords are a pet peeve mine though since as far as I can tell they mean nothing useful, but just allow you to write the same query counterintuitively backwards, i.e. starting with the "outer" table and joining back in to the main one. I can only imagine the ANSI committee couldn't agree which way outer joins should work, so they gave up and allowed both. The RIGHT OUTER JOIN alternative is IMHO only of interest to twisted psychos.
                      • 8. Re: Using (+) for joins ??
                        555742
                        Eric, you mention some restrictions on the (+) operator. What are they. In school they taught me they were equivalent, and at work shop standards say to use the (+) operator.
                        • 9. Re: Using (+) for joins ??
                          SomeoneElse
                          For one thing, using ANSI syntax allows you to do FULL OUTER JOIN.

                          The equivalent in Oracle-only syntax would be WHERE a.col(+) = b.col(+) but you can't have the operator on both sides.

                          You can read more about the restrictions here:

                          http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#sthref3174
                          • 10. Re: Using (+) for joins ??
                            501447
                            Hi,

                            If I need to outer join multiple tables, how can I achieve that with ANSI syntax? Like,

                            SELECT a.col1, b.col2, c.col3, d.col4
                            FROM a, b, c, d
                            WHERE a.colx = b.colx(+)
                            AND a.coly = c.coly(+)
                            AND b.colz = d.colz(+)

                            Thx
                            • 11. Re: Using (+) for joins ??
                              546595
                              Hi,

                              If I need to outer join multiple tables, how can I
                              achieve that with ANSI syntax? Like,

                              SELECT a.col1, b.col2, c.col3, d.col4
                              FROM a, b, c, d
                              WHERE a.colx = b.colx(+)
                              AND a.coly = c.coly(+)
                              AND b.colz = d.colz(+)

                              Thx
                              http://www.psoug.org/reference/joins.html
                              http://www.oracle-base.com/articles/9i/ANSIISOSQLSupport.php

                              may be this
                               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
                              hope that helps
                              • 12. Re: Using (+) for joins ??
                                6363
                                William thank you for the explanation it does make the intent a lot clearer now.
                                Aha but first you would write the damn join forwards
                                Although your reference to forward joins and the fact that the format now places one table underneath makes me fear for forward left under outer joins should ANSI ever hear of it.
                                • 13. Re: Using (+) for joins ??
                                  SomeoneElse
                                  makes me fear for forward left under outer joins should ANSI ever hear of it.
                                  SHHHHH!
                                  • 14. Re: Using (+) for joins ??
                                    Laurent Schneider
                                    also note that you can save your keyboard by omitting the outer keyword
                                    SQL>select ename, dname from dept left join emp using (deptno);
                                    
                                    ENAME      DNAME
                                    ---------- --------------
                                    CLARK      ACCOUNTING
                                    KING       ACCOUNTING
                                    MILLER     ACCOUNTING
                                    JONES      RESEARCH
                                    FORD       RESEARCH
                                    ADAMS      RESEARCH
                                    SMITH      RESEARCH
                                    SCOTT      RESEARCH
                                    WARD       SALES
                                    TURNER     SALES
                                    ALLEN      SALES
                                    JAMES      SALES
                                    BLAKE      SALES
                                    MARTIN     SALES
                                               OPERATIONS
                                    1 2 Previous Next