Oracle Outer Join Syntax

Oracle (+) Operator

That's Oracle specific notation for an OUTER JOIN, because the ANSI-89 format (using a comma in the FROM clause to separate table references) didn't standardize OUTER joins.

The query would be re-written in ANSI-92 syntax as:

   SELECT ...
     FROM a
LEFT JOIN b ON b.id = a.id

This link is pretty good at explaining the difference between JOINs.

---

It should also be noted that even though the (+) works, Oracle recommends not using it:

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. 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:

Oracle outer join(+) syntax

The columns that are joined on are just before the plus sign.

 ... FROM CustomerShip s LEFT OUTER JOIN (....) i ON s.ShipSeq = i.ShipSeq

Oracle using (+) for left outer join

Need to include the (+) Operator on the Condition with the Literal

The older outer join approach requires that you include the (+) operator for the condition on the literal.

Just put your "retro" mental cap on and return back to the 90's. :)

Here is how it should look with this approach:

SELECT
    c
.DATE,
 u.user_id
FROM
    order_detail_trans c,
    order_detail_trans u
WHERE
    u.trans_id (+) = c.trans_id
    AND   u.trans_type (+) = 'UPDATE DETAIL'

Here is an example with the tables, emp and dept:

SCOTT@db>SELECT
  2      d.deptno,
  3      e.job
  4  FROM
  5      dept d
  6      LEFT OUTER JOIN emp e ON d.deptno = e.deptno
  7      and e.job = 'CLERK'
  8  GROUP BY
  9      d.deptno,
 10      e.job
 11  ORDER BY
 12      1,
 13      2;
  DEPTNO JOB     
      10 CLERK   
      20 CLERK   
      30 CLERK   
      40         

SCOTT@db>SELECT
  2      d.deptno,
  3      e.job
  4  FROM
  5      dept d,
  6      emp e
  7  WHERE
  8      d.deptno = e.deptno (+)
  9   AND e.job (+) = 'CLERK'
 10  GROUP BY
 11      d.deptno,
 12      e.job;
  DEPTNO JOB     
      20 CLERK   
      30 CLERK   
      10 CLERK   
      40    

Believe it or not, I believe most Oracle Applications shops just use this older outer join approach.

Old Style Oracle Outer Join Syntax - Why locate the (+) on the right side of the equals sign in a Left Outer join?

The (+) identifies the table that is being outer joined to. The way I was taught, the (+) indicated the table that would have missing rows for which new NULL rows had to be added.

If you look at the alternate left outer join syntaxes that various databases supported before LEFT OUTER JOIN became part of the ANSI standard, the proprietary operator was generally applied to the table that was "missing" rows. DB2 also supports the (+) operator for outer joins in the same way that Oracle does.

Linking multiple tables with an Oracle outer join

Say you have your tables like the following:

insert into tableA values (1);
insert into tableA values (2);
insert into tableB values ( 1, 10);
insert into tableB values ( -2, 20);
insert into tableC values ( 10, 100);
insert into tableC values ( 20, 200);
insert into tableD values ( 200);
insert into tableD values ( 999);

If I understand well, you need to use on outer join even on B and C, not only D; in the old Oracle syntax this is:

SELECT *
FROM
  TABLEA A, TABLEB B, TABLEC C, TABLED D
WHERE
  A.XX = B.XX(+) AND
  B.YY = C.YY(+) AND
  C.ZZ = D.WW (+)

And in (better) ANSI SQL:

select *
from tableA A
       left outer join
     tableB B on ( A.xx = B.xx)
       left outer join 
     tableC C on ( B.yy = C.yy)
       left outer join
     tableD D on ( C.zz = D.ww)

They both give:

        XX         XX         YY         YY         ZZ         WW
---------- ---------- ---------- ---------- ---------- ----------
         2
         1          1         10         10        100

Outer Join not working as expected in Oracle

Don't use that obsolete old join syntax. It's less powerful (there are some things you can't express this way), it can be ambiguous (it's not always clear when a condition is part of a JOIN and when it's part of a WHERE clause, or which JOIN a condition belongs to), and it's harder to read because it mixes all the conditions from a query together. It's been more or less obsolete since at least 1999.

In this case, it's likely the "other conditions for 1&2" part are the problem. Since you have no distinction between JOIN and WHERE clause, they are applied as a WHERE clause. But in the case where there is no record from CUSTOMER_DETAILS_1 or CUSTOMER_DETAILS_2, the condition is comparing with NULL, which effectively results in FALSE, and the whole CUSTOMER_MASTER record is excluded from the result set.

There's not always a good fix for this using the old syntax, but with modern join syntax it's easy. This will probably work as you expect:

SELECT * 
FROM CUSTOMER_MASTER MAS
LEFT JOIN CUSTOMER_DETAILS_1 SUB1 ON SUB.CUSTOMER_ID = MAS.CUSTOMER_ID
     -- AND other conditions for CUSTOMER_DETAILS_1
LEFT JOIN CUSTOMER_DETAILS_2 SUB2 ON SUB2.CUSTOMER_ID = MAS.CUSTOMER_ID 
     -- AND other conditions for CUSTOMER_DETAILS_2

This forces the additional conditions to be treated as part of the JOIN rather than the WHERE, such that they will not cause a CUSTOMER_MASTER record to be excluded.

Multiple LEFT OUTER JOIN on multiple tables

The join on D is an inner join, the rest are left outer joins:

SELECT *
FROM TABLEA A JOIN
     TABLED D
     ON D.Z = A.Z LEFT JOIN
     TABLEB B
     ON A.X = B.X LEFT JOIN
     TABLEC C
     ON B.Y = C.Y 
WHERE MY_COL = @col_val;

I always start chains of joins with inner joins followed by the left outer join. I never use right join, and full join rather rarely. The inner joins define the rows in the result set, so they come first.

Oracle LEFT OUTER JOIN on 3+ tables - (+) Syntax versus ANSI Syntax

You have two date literals as outer join conditions in your first query, but you leave them in the where clause in the second query. To change the syntax properly, those criteria need to be left as part of the join criteria. It's also bad form to combine the two join syntax (i.e. having comma seperated tables and the join keyword in the same query).

Below is the first query properly adapted to SQL-99 syntax:

SELECT a.userid,
       a.firstname,
       a.lastname,
       b.name AS positionname,
       d.name AS hierarchy
FROM   cs_participant a
       JOIN cs_position b ON a.payeeseq = b.payeeseq
       LEFT JOIN cs_positionrelation c
          ON     b.ruleelementownerseq = c.childpositionseq
             AND c.removedate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
       LEFT JOIN cs_positionrelationtype d
          ON     c.positionrelationtypeseq = d.datatypeseq
             AND d.removedate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
WHERE      b.removedate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
       AND b.effectiveenddate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
       AND a.removedate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
       AND a.effectiveenddate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')

Once that's done, adapting it to join on either column is trivial:

SELECT a.userid,
       a.firstname,
       a.lastname,
       b.name AS positionname,
       d.name AS hierarchy
FROM   cs_participant a
       JOIN cs_position b ON a.payeeseq = b.payeeseq
       LEFT JOIN cs_positionrelation c
          ON     (   c.parentpositionseq = b.ruleelementownerseq
                  OR c.childpositionseq = b.ruleelementownerseq)
             AND c.removedate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
       LEFT JOIN cs_positionrelationtype d
          ON     c.positionrelationtypeseq = d.datatypeseq
             AND d.removedate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
WHERE      b.removedate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
       AND b.effectiveenddate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
       AND a.removedate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')
       AND a.effectiveenddate = TO_DATE ('01/01/2200', 'dd/mm/yyyy')

---

Related Topics