How to Convert a "Legacy" Left Outer Join Statement in Oracle

How do I convert a legacy left outer join statement in Oracle?

Use:

  SELECT a.AccountNo,
         a.ParcelNo,
         a.LocalNo,
         a.PrimaryUseCode, 
         a.DefaultTaxDistrict,
         TRIM(g.Section),
         TRIM(g.Township),
         TRIM(g.Range)
     FROM tblAcct A
LEFT JOIN tblAcctLegalLocation g ON g.accountno = a.accountno
                                AND g.verstart <= '20100917999' 
                                AND g.verend > '20100917999'
    WHERE a.verstart <= '20100917999' 
      AND a.verend > '20100917999' 
      AND a.DefaultTaxDistrict = '2291' 
      AND SUBSTR(a.AccountNo,1,1) IN ('R', 'I') 
      AND SUBSTR(a.ParcelNo,1,1) NOT IN ('7', '8')
      AND a.AcctStatusCode IN ('A', 'T', 'E') 
 ORDER BY a.ParcelNo, a.LocalNo

Everything you see marked with the (+) must be included in the OUTER join criteria. In an outer JOIN, the criteria is applied before the join.

Convert Oracle legacy outer join to Ansi SQL

Your query is equivalent to the below ANSI compliant query:

SELECT a.name,
       a.empno,
       b.loc,
       c.inr
FROM tab a
LEFT JOIN tab b ON a.deptno = b.deptno AND b.empno = 190
LEFT JOIN tab c ON a.deptno = c.deptno AND c.empno = 190;         

You have to place predicates b.empno = 190 and c.empno = 190 inside the ON clauses of the LEFT JOIN operations, otherwise LEFT JOIN becomes an INNER JOIN.

Conversion of legacy outer join to ANSI

No, it's not correct -- only those marked with the "(+)" need to be in the LEFT JOIN, the rest are for the WHERE clause:

   SELECT `<columns>`  
     FROM iel_item iit  
LEFT JOIN iel_item_property iip  ON iip.it_id = iit.it_id  
                                AND iip.iip_change_type = 'I'  
                                AND iip.iel_id = c1item_iel_id
    WHERE iit.iit_change_type = 'I'      
      AND iit.iel_id = c1item_iel_id  

Placement matters with OUTER JOINs -- criteria in the ON clause is applied before the JOIN, while criteria in the WHERE is applied after the JOIN. This can greatly affect the result set returned, depends on data and setup. Placement doesn't matter for INNER JOINS - in the WHERE or ON clause, the result set will be the same.

Replacing oracle specific notation (+) with LEFT JOIN and where clause

The equivalent syntax would be:

SELECT x.a, y.a, z.a
FROM x LEFT JOIN
     y
     ON x.k = y.k LEFT JOIN
     z
     ON x.p = z.p;

Conversion from standard SQL to Oracle Syntax

Your Oracle-style statement needs the (+) operator also on the less-than condition, since that is also part of your join criteria in the standard-SQL version.

select a.*,b.* from testing a, testing b
where a.aid = b.aid(+) 
and a.bid < b.bid(+)
order by a.aid, b.bid;

See sqlfiddle here.

---

Related Topics