Oracle Joins - Comparison between conventional syntax VS ANSI Syntax
Preamble
Of late, I see too many geeks commenting on Oracle questions saying that "Do not use (+) operator, rather use JOIN syntax".
Question
I do see that both work well. But what is the real difference between using them and what makes you feel using them? I would welcome answers, more from experience.
1. Is there anything to do with limitations in application, performance,
etc. while using them?
2. What would you suggest for me?
I did read something on Oracle documentation but not good enough to make me understand or feel comfortable with the comprehensive information.
Note: I am planning to migrate 200+ packages and procedures, if the Keyword should be used instead of (+)
3. Also is there any freeware tools to do the rewrite?
Posting samples
┌───────────────────────────────────┬─────────────────────────────────────────────┐
│ INNER JOIN - CONVENTIONAL │ INNER JOIN - ANSI SYNTAX │
├───────────────────────────────────┼─────────────────────────────────────────────┤
│ SELECT │ SELECT │
│ emp.deptno │ ename, │
│ FROM │ dname, │
│ emp, │ emp.deptno, │
│ dept │ dept.deptno │
│ WHERE │ FROM │
│ emp.deptno = dept.deptno; │ scott.emp INNER JOIN scott.dept │
│ │ ON emp.deptno = dept.deptno; │
├───────────────────────────────────┼─────────────────────────────────────────────┤
│ LEFT OUTER JOIN - CONVENTIONAL │ LEFT OUTER JOIN - ANSI SYNTAX │
├───────────────────────────────────┼─────────────────────────────────────────────┤
│ SELECT │ SELECT │
│ emp.deptno │ ename, │
│ FROM │ dname, │
│ emp, │ emp.deptno, │
│ dept │ dept.deptno │
│ WHERE │ FROM │
│ emp.deptno = dept.deptno(+); │ scott.emp LEFT OUTER JOIN scott.dept │
│ │ ON emp.deptno = dept.deptno; │
├───────────────────────────────────┼─────────────────────────────────────────────┤
│ RIGHT OUTER JOIN - CONVENTIONAL │ RIGHT OUTER JOIN - ANSI SYNTAX │
├───────────────────────────────────┼─────────────────────────────────────────────┤
│ SELECT │ SELECT │
│ emp.deptno │ ename, │
│ FROM │ dname, │
│ emp, │ emp.deptno, │
│ dept │ dept.deptno │
│ WHERE │ FROM │
│ emp.deptno(+) = dept.deptno; │ scott.emp RIGHT OUTER JOIN scott.dept │
│ │ ON emp.deptno = dept.deptno; │
├───────────────────────────────────┼─────────────────────────────────────────────┤
│ FULL OUTER JOIN - CONVENTIONAL │ FULL OUTER JOIN - ANSI SYNTAX │
├───────────────────────────────────┼─────────────────────────────────────────────┤
│ SELECT │ SELECT │
│ * │ * │
│ FROM │ FROM │
│ emp, │ scott.emp FULL OUTER JOIN scott.dept │
│ dept │ ON emp.deptno = dept.deptno; │
│ WHERE │ │
│ emp.deptno = dept.deptno(+) │ │
│ UNION ALL │ │
│ SELECT │ │
│ * │ │
│ FROM │ │
│ emp, │ │
│ dept │ │
│ WHERE │ │
│ emp.deptno(+) = dept.deptno │ │
│ AND emp.deptno IS NULL; │ │
└───────────────────────────────────┴─────────────────────────────────────────────┘
PS: Read the summary of answers for all updates grouped.
Answer
If your 200+ packages work as intended with "old fashioned" syntax, let it be.
SQL will not start to perform better after migration to ANSI syntax - it's just different syntax.
All that being said, ANSI syntax is cleaner - you are not going to normal join if you forget (+) in some multi-column outer join.
In the past there were some bugs with ANSI syntax but if you go with latest 11.2 or 12.1 that should be fixed already.
Of course, you know your environment and priorities better - as SchmitzIT said - ANSI syntax is part of SQL standard and it would help when going to use some other RDBMS product.