How to convert an "obsolete" expression of an external outer join in Oracle?

Question

I have two tables (A and G) in an Oracle database that can be combined with an account number. One caveat is that one of the tables (G) has 80 less records than the other. When I query two tables together, I need to get all the rows so that we see the NULL data for the missing 80 rows in the columns.

I currently have an Oracle statement that queries an external outer join using the following "legacy" syntax:

SELECT A.AccountNo,
       A.ParcelNo,
       A.LocalNo,
       A.PrimaryUseCode, 
       A.DefaultTaxDistrict,
       RTRIM(G.Section),
       RTRIM(G.Township),
       RTRIM(g.Range)

  FROM tblAcct A, tblAcctLegalLocation G

 WHERE A.verstart <= '20100917999' AND A.verend > '20100917999' AND A.DefaultTaxDistrict = '2291' 
       AND (SUBSTR(A.AccountNo,1,1) = 'R' or SUBSTR(A.AccountNo,1,1)= 'I') 
       AND SUBSTR(a.ParcelNo,1,1)<> '7' and substr(a.ParcelNo,1,1)<>'8'
       AND A.AcctStatusCode IN ('A', 'T', 'E') 
       AND A.AccountNo = G.AccountNo(+)
       AND G.verstart(+) <= '20100917999' and G.verend(+) > '20100917999'
ORDER BY A.ParcelNo, A.LocalNo

I am trying to convert this query to a "standard" query of type LEFT JOIN, as I am told that newer versions of Oracle support this syntax. I tried the basic

LEFT OUTER JOIN ON A.AccountNo = G.AccountNo 

, , . 80 , .

- , ?

Answer 1

:

  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

, , (+), OUTER. JOIN .