realspirituals realspirituals - 4 months ago 50
SQL Question

Oracle Joins - Comparison between conventional syntax VS ANSI Syntax

Off-late, I see too many geeks commenting on Oracle questions saying that "Do not use (+) operator, rather use JOIN syntax".

I do see that both works well. But what is the real difference in 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 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

Grouping answers together

  1. Use explicit JOINs rather than implicit (regardless whether they are outer joins or not) is that it's much easier to accidently create a cartesian product with the implicit joins. With explicit JOINs you cannot "by accident" create one. The more tables are involved the higher the risk is that you miss one join condition.
  2. Basically (+) is severely limited compared to ANSI joins. Furthermore it is only available in Oracle whereas the ANSI join syntax is supported by all major DBMS
  3. SQL will not start to perform better after migration to ANSI syntax - it's just different syntax.
  4. Oracle strongly recommends that you use the more flexible FROM clause join syntax shown in the former example. 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.
  5. Using the JOIN operators ensure your SQL code is ANSI compliant, and thus would allow a front-end application to be more easily ported for other database platforms.
  6. Join conditions have a very low selectivity on each table and a high selectivity on the tuples in the theoretical cross product. Conditions in the where statement usually have a much higher selectivity.
  7. Oracle internally converts ANSI syntax to the (+) syntax, you can see this happening in the execution plan's Predicate Information section.

Possible Pitfall in using ANSI syntax on 12c engine

Including a possibility of bug in JOIN in 12c. See here

FOLLOW UP:

Quest SQL optimizer tool rewrites the SQL to ANSI syntax.