-1

What is meant by + sign right after column name while join in oracle .

Example :

select a,d,f,c
From table1, table 2
Where table1.x(+) = table2.y:
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
Ananthu rajeev
  • 21
  • 3
  • 2
    For what it's worth, ANSI syntax (using `JOIN` instead of `FROM a, b`) was published in the 1992 standard, if my Google fu was accurate. So after 30+ years it's pretty much safe to be used in new developments. – Álvaro González Feb 03 '23 at 11:40

2 Answers2

2

The (+) is used to indicate an outer join. It is placed on the side where the row in the table may be missing. That is old Oracle syntax I have known since Oracle 7. Presently, even Oracle encourages using ANSI syntax for joins.

Bogdan Dincescu
  • 683
  • 6
1

That's the old Oracle's outer join operator.

Old:

SQL>   SELECT d.deptno, d.dname, e.ename
  2      FROM dept d, emp e
  3     WHERE e.deptno (+) = d.deptno
  4  ORDER BY d.deptno, e.ename;

    DEPTNO DNAME          ENAME
---------- -------------- ----------
        10 ACCOUNTING     CLARK
        10 ACCOUNTING     KING
        10 ACCOUNTING     MILLER
        20 RESEARCH       ADAMS
        20 RESEARCH       FORD
        20 RESEARCH       JONES
        20 RESEARCH       SCOTT
        20 RESEARCH       SMITH
        30 SALES          ALLEN
        30 SALES          BLAKE
        30 SALES          JAMES
        30 SALES          MARTIN
        30 SALES          TURNER
        30 SALES          WARD
        40 OPERATIONS

15 rows selected.

Nowadays, we do it as

SQL>   SELECT d.deptno, d.dname, e.ename
  2      FROM dept d LEFT JOIN emp e ON e.deptno = d.deptno
  3  ORDER BY d.deptno, e.ename;

    DEPTNO DNAME          ENAME
---------- -------------- ----------
        10 ACCOUNTING     CLARK
        10 ACCOUNTING     KING
        10 ACCOUNTING     MILLER
        20 RESEARCH       ADAMS
        20 RESEARCH       FORD
        20 RESEARCH       JONES
        20 RESEARCH       SCOTT
        20 RESEARCH       SMITH
        30 SALES          ALLEN
        30 SALES          BLAKE
        30 SALES          JAMES
        30 SALES          MARTIN
        30 SALES          TURNER
        30 SALES          WARD
        40 OPERATIONS

15 rows selected.

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • There is nothing wrong with using the native Oracle syntax where ANSI syntax doesn't provide additional capabilities needed to accomplish the task. It's like two dialects, and it's a programmer's preference which one they prefer. – Paul W Feb 03 '23 at 13:28
  • I prefer OUTER JOIN, @Paul, because Oracle won't let you (+) *outer join* one table to two (or more) other tables. OUTER JOIN will. Of course, unless I'm wrong. – Littlefoot Feb 03 '23 at 13:39
  • 1
    That used to be the case, but that restriction was lifted in a recent version. You can outer join from two tables using (+) in 19c just fine. But it is a matter of preference, and I respect those who prefer the ANSI style... and I use it myself when required because there are things it can do (like OUTER APPLY, etc..) that can't be done otherwise. – Paul W Feb 03 '23 at 13:47
  • Aha; didn't know they (Oracle) fixed it. Thank you for info! – Littlefoot Feb 03 '23 at 13:49