Please use ANSI join syntax

The old Oracle join syntax really should be consigned to history.

This example from a recent topic http://www.orafaq.com/forum/mv/msg/182214/558386/#msg_558386 is typical of many questions regarding join syntax. A lot of developers (and DBAs) persist in using the "old" syntax. I strongly believe that everyone, particularly those starting out with SQL, should use the ANSI syntax. It is so much easier to read and maintain. Conceptually, this old syntax:

SELECT *
FROM   emp,
       dept
WHERE  emp.deptno = dept.deptno
       AND dept.dname = 'SALES';

is generating a cartesian product, and then filtering the result set with a predicate. A bit silly, don't you think? Much better design is this:

SELECT *
FROM   emp
       join dept USING(deptno)
WHERE  dname = 'SALES'; 

now the join is in its own clause, and the predicate is doing what it is meant to do: row selection. The old syntax lets developers produce horrific predicates that are a jumble of join conditions and selections in no particular order. Take this example:

SELECT a.acct_num,
       a.grp_num,
       a.div_num,
       a.sub_pers_num,
       a.pat_pers_num,
       a.pat_dob,
       a.rel_code,
       b.pat_pmt,
       b.deductible_amt,
       b.net_pmt 
FROM   source.master_claim a,
       source.master_line b,
       param.proc_hier c,
       param.con_product d
WHERE  a.clm_num = b.clm_num
       AND a.clm_seq = b.clm_seq
       AND a.clm_stat IN ( 'P', 'H', 'A' )
       AND b.billed_ada_proc = c.proc_key
       AND b.prod_id = d.prod_id
       AND c.max_app = 'BASIC'; 

The predicate has six conditions. The first, second, fourth, and fifth are joins; the third and sixth are filters. I've greatly simplified (and anonymized) that code: the original is truely horrible. Surely anyone must agree that this is simpler to read and maintain:

 
SELECT a.acct_num,
       a.grp_num,
       a.div_num,
       a.sub_pers_num,
       a.pat_pers_num,
       a.pat_dob,
       a.rel_code,
       b.pat_pmt,
       b.deductible_amt,
       b.net_pmt
FROM   source.master_claim a
       join source.master_line b
         ON ( a.clm_num = b.clm_num
              AND a.clm_seq = b.clm_seq )
       join param.proc_hier c
         ON ( b.billed_ada_proc = c.proc_key )
       join param.con_product d
         ON ( b.prod_id = d.prod_id )
WHERE  a.clm_stat IN ( 'P', 'H', 'A' )
       AND c.max_app = 'BASIC'; 

To which version would you prefer to, for instance, add more tables or adjust the filters?

Then we move on to outer joins. I remember that when the ANSI syntax appeared with release 9i it was suggested that we should tell customers something like "the difference is because Oracle invented the outer join long before the ANSI commitees did, and when they finally got around to it, they chose not to use our syntax. But now Oracle participates in all the relevant forums, and is driving the ANSI standard - therefore there will be no more incompatibilities." Well, perhaps. But there is no doubt which of these full outer joins is easier to work with:

SELECT ename,
       dname
FROM   emp,
       dept
WHERE  emp.deptno (+) = dept.deptno
UNION
SELECT ename,
       dname
FROM   emp,
       dept
WHERE  emp.deptno = dept.deptno (+);

SELECT ename,
       dname
FROM   emp
       full outer join dept USING (deptno);

So to conclude, any Oracle professional really should be using the ANSI join syntax. Time to join the twentyfirst century, folks.