Oracle Joins - Comparison between conventional syntax VS ANSI Syntax

Question

Preamble

As of late, I see too many geeks commenting on Oracle questions saying "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? 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 it’s 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 are 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.

Solution 1

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.

Solution 2

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.

Solution 3

In 11g you should be using ANSI join syntax. It is more flexible (support for full outer joins and partitioned joins), and as the documentation states:

Oracle strongly recommends that you use the more flexible FROM clause join syntax shown in the former example.

That's reason enough.

Solution 4

Apart from the reasons mentioned by others, 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.

Solution 5

Separating predicates based on selectivity

Separating your query into join conditions and where conditions can give the optimizer an additional hint. 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.

In a join condition for each row on the left there is very likely a value on the right (and vice versa). So such conditions are good to join results from two tables, but they doe not help very much in eliminating values from each individual table from the result set.

Conditions in the where clause can usually be used to eliminate individual rows from one table from the result set.

Hint for the (human!) optimizer

So is is a good strategy to run first the where conditions on the individual tables and eliminate as much rows as possible from the result set. After that one can use the join conditions to combine the surviving rows.

It's not clear if the Oracle optimizer really uses the position of the condition in the SQL statement as a hint for optimizing the query. I guess it is more interested in the hard facts in the table statistics (there was some change in how Oracle handles different joins in 11g R1, see this post from the Oracle optimizer team for more details).

At least for me as a human it is very helpful to know if a statement has selectivity on a singe table when I try to understand and optimize a query. You should also consider this, when you want to put multiple condition in a ON clause (e.g. ON (a.x=b.x AND a.y=b.y) vs. putting one of the conditions in the where clasue: Just check, how selective the condition is.

Conclusion

For existing queries keep the syntax as is. When creating a new query or refactor an existing one, try yo sort the predicates on selectivity using the "JOIN ON" syntax: If it is not very selectable on a single table put it in the ON part, otherwise in the WHERE part.

View more solutions