Please use ANSI join syntax

articles: 

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.

Comments

Appreciate you have a preference, but perhaps this is subjective at best. I find the "old" syntax much easier to read and maintain. The ANSI one is very verbose and I prefer the more concise oracle syntax. I am always frustrate by the verbosity of the new syntax when I come across it. It always appears to be a lot of text to convey what what can be communicated in a much clearer and simpler manner. I suppose once you are use to one way it gets hardwired and a different approach inevitably seems clunky. For me there is no doubt that the (+) syntax is easier to work with. The left and right variation seems positively strange to me. Adding direction to the definition of a relationship seems convoluted and without added value. Why add another concept to the relationship construct.

Regards

Preference for ANSI join isn't subjective at all. Take a look this topic,
http://www.orafaq.com/forum/mv/msg/182214/558381/0/#msg_558381
the lack of a join predicate was causing a cartesian join, resulting in trillions of rows being generated. If the query were written with ANSI syntax, it would be impossible to make a mistake like that: you get a cartesian join only if you ask for it. The old Oracle syntax is far too prone to error.

I've been working with Oracle since the early 90's and personally, I prefer using the "old" Oracle syntax. I agree that I find it easier to read and maintain. I also agree the ANSI one is too verbose for my liking - it's like writing a small story.

One issue I have is working at a shop that employs both syntax's. This can make it difficult for those responsible for modifying or tuning someone else's query's. This can be a nightmare.

As far as I'm concerned, anyone new to SQL should use whichever syntax they find easier or whichever one is shoved down their throat. It alwasy seems that people who cross-over from SQL Server to Oracle are the ones who bitch the most.

While I fully understand the ANSI syntax, I have no interest in developing any SQL code using it. Just like Spanish is prevalent now throughout doesn't mean I have to go learn it.

It's not subjective at all.
I'll quote the part of Trevor North's answer:
...
Starting with Oracle 9i, Oracle recommends that SQL developers use the ANSI join syntax instead of the Oracle proprietary (+) syntax. There are several reasons for this recommendation, including:
1. Easier to segregate and read (without mixing up join versus restriction code)
2. Easier to construct join code correctly (especially in the case of “outer” joins)
3. Portable syntax will work on all other ANSI compliant databases, such as MS SQL Server, DB2, MySQL, PostgreSQL, et al
4. Since it’s the universally accepted standard, it’s the general target for all future database and third party vendors’ tools
5. The proprietary Oracle outer-join (+) syntax can only be used in one direction at a time, it cannot perform a full outer join
6. The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
7. A condition containing the (+) operator cannot be combined with another condition using the OR logical operator
8. A condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.
9. A condition cannot compare any column marked with the (+) operator with a sub-query."

Thus it’s time to embrace the ANSI join syntax – and move into the 21st century

Hi all,

Thanks for your views and points.
If Oracle interprets the ANSI syntax differently and optimize the output in any way, then it
is worth adopting.

Can somebody put some good weight behind the ANSI syntax with examples and explain plan?
It would be great help for all.

Regards

Prashant

The plans are the same.

... the reality sets in.
I just came across a nasty bug which affects INSERT ... SELECT statements containing joins written in the ANSI format and Oracle Spatial functions on Oracle 12.1 .
No problems instead with the good ole Oracle syntax.

Cheers
Flavio