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/177410/537822/0/#msg_537822 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.