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.

Is there anything to do with limitations in application, performance, etc. while using them?
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 (+)

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.

One very good reason to use explicit `JOIN`s rather than implicit (regardless wether they are outer joins or not) is that it's *much* easier to accidently create a cartesian product with the implicit joins. With explicit `JOIN`s you cannot "by accident" create one. The more tables are involved the higher the risk is that you miss one join condition. — , Sep 22 '13 at 12:46
Related question: [Difference between Oracle's plus (+) notation and ansi JOIN notation?](http://stackoverflow.com/questions/1193654/difference-between-oracles-plus-notation-and-ansi-join-notation) 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. — Vincent Malgrat, Sep 23 '13 at 13:04

igr · Answer 1 · 2013-10-14T18:52:55.603

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.

score 14 · Answer 2 · answered Sep 19 '13 at 09:43

14

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.

answered Sep 19 '13 at 09:43

David Aldridge

51,479
8
68
96

Is there anything to do with limitations in application, performance, etc. while using them? – Srini V Sep 19 '13 at 10:10
1

You can't do a full outer join with the old syntax. – David Aldridge Sep 19 '13 at 10:42
1

And the `(+)` syntax can be ambiguous with more than 2 outer joins (and thus not allowed in some cases). – ypercubeᵀᴹ Sep 22 '13 at 12:38
@DavidAldridge: Of course you can do a full outer join in older syntax, it's a combination of (+) and UNION ALL: (LEFT OUTER) UNION ALL (RIGHT OUTER). – itmitica Sep 29 '13 at 06:46
2

@itmitică More accurate to say that you can construct an outer join result with a data set from combined left and right join result sets, but you can also construct the same without any outer joins at all by UNIONing an inner join and two queries with not exists correlated subqueries. So I think my statement stands. – David Aldridge Sep 29 '13 at 09:11
@DavidAldridge OK, I just thought your "you can't do full outer join with the old syntax" was misleading. You can, "do", "construct", whatever you want to call it. – itmitica Sep 29 '13 at 10:11
If needed, a cited reference from Oracle [recommending ANSI Joins :](https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm#sthref2253) – StuartLC Nov 24 '14 at 13:43

score 11 · Accepted Answer · edited May 23 '17 at 12:10

Grouping answers together

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.
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
SQL will not start to perform better after migration to ANSI syntax - it's just different syntax.
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.
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.
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.
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.

Comment to point 3. In Oracle ver. 10g SQL might behave better (or at least differently). The early implementation of ANSI (in fact it not ANSI but ISO SQL) used "newer" math. models in CBO. Since 11g there should be the same exec plan for both JOIN syntaxes. — ibre5041, Dec 03 '13 at 13:03
(Oracle® Performance Tuning for 10gR2 Second Edition --Gavin Powell). When using OPTIMIZER_MODE=CHOOSE, then for some specific cases RULE based optimizer is used. — ibre5041, Dec 03 '13 at 13:56

score 5 · Answer 4 · answered Sep 22 '13 at 12:35

5

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.

answered Sep 22 '13 at 12:35

SchmitzIT

9,227
9
65
92

score 4 · Answer 5 · edited Nov 06 '14 at 16:18

4

As a practice, you should always use ANSI syntax. Better don't rework on packages and procedures. Rather you can fix things when you do any maintenance on those scripts individually. There will not be any difference in plans due to syntax.

Quest SQL optimizer rewrites with all possible combinations to find a better plan. So you still need to search one SQL from its 100+ results.

edited Nov 06 '14 at 16:18

AStopher

4,207
11
50
75

answered Nov 06 '14 at 16:10

+1 for your response. The question is not valid now. But can be considered for others – Srini V Nov 06 '14 at 16:14

stefan.schwetschke · Answer 6 · 2013-09-25T08:53:11.973

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.

Oracle internally converts ANSI syntax to the `(+)` syntax, you can see this happening in the execution plan's Predicate Information section. The ANSI syntax does not provide any additional information to the optimizer, but I agree it does make queries much easier to understand. — Jon Heller, Sep 24 '13 at 17:37
What happens to the full outer join cases with the new syntax. How is that converted in the plan? @jonearles — Srini V, Sep 25 '13 at 07:56
@realspirituals Until recently Oracle would re-write full outer joins with `UNION ALL` and `(+)`. You can see that by using the hint `/*+NO_NATIVE_FULL_OUTER_JOIN*/` or running something like `alter session set optimizer_features_enable = '10.2.0.1';`. In 11g, based on the explain plan and a 10053 trace, Oracle will keep the full outer join syntax but still convert other outer joins to `(+)`. Which leads to some ugly-looking internal queries. — Jon Heller, Sep 25 '13 at 18:15

psaraj12 · Answer 7 · 2013-09-29T06:48:45.447

The (+) sign used in Outer joins is the Oracle syntax for Outer joins

From the information gathered from reliable oracle sources i could see that you can keep the Oracle outer join syntax(+) for existing packages since there are 200+ packages and since internally Oracle still converts them from ANSI syntax to Oracle syntax.

Kindly make use of the ANSI syntax in future where there in limitations for using(+) operator

Please find the links for detailed explanation on (+) sign Outer joins which might help you decide on your migration

Oracle Outer Join syntax and ANSI syntax

Limitations of using (+) Outer joins

More information about Oracle outer join

(+) Outer join Operator recommended in Java when using Oracle JDBC drivers

score 2 · Answer 8 · answered Sep 24 '13 at 16:45

2

I used both approaches on different projects and I prefer JOIN syntax.

There is clear separation for join conditions in ON clause and filter conditions in WHERE clause.
It is easier to read and maintain large queries with a lot of joins.

answered Sep 24 '13 at 16:45

Nazarii Bardiuk

4,272
1
19
22

score 2 · Answer 9 · edited Oct 29 '14 at 10:31

2

Talking from experience in work, using the JOIN rather than (+) is more simple,faster, better looking and better to work with solution ,specially when you work with multi database selects (throughout synonyms), with a lot of tables in it (ex.: 40+ tables) in big database (1000+ tables,some tables with more than 2 billion rows),you will feel big difference.

edited Oct 29 '14 at 10:31

Srini V

11,045
14
66
89

answered Sep 28 '13 at 17:42

Ján Srniček

505
1
10
34

score 2 · Answer 10 · answered Sep 29 '13 at 07:11

2

Some commenteers have said that (+) syntax doesn't allow for full outer joins. It does, so this is not the issue: (LEFT OUTER JOIN) UNION ALL (RIGHT OUTER JOIN).

Others have said that performance is the reason to make the switch. That's a bunch of BS, especially in SQL. Of course, there are some guidelines, but every query and every database has its own peculiarities, you have to tune for specific not for general situations.

The reasons to switch from (+), beside not being standard, are its limitations as opposed to the new explicit syntax: http://docs.oracle.com/cd/E16655_01/server.121/e17209/queries006.htm#SQLRF52354. Start reading from here: "Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator."

answered Sep 29 '13 at 07:11

itmitica

501
3
10

1

I agree the ANSI syntax is better, but there are limitations with both syntaxes. For example, you cannot use ANSI syntax for bitmap join indexes and some types of materialized views. – Jon Heller Sep 29 '13 at 16:52
I'm not sure what you mean. Rather than jumping to conclusion, I'd like more details, please. – itmitica Sep 30 '13 at 11:55
I don't think those limitations are officially documented, but you can find some examples [here](http://jesonmartajaya.wordpress.com/2011/06/23/oracle-bitmap-join-indexes/) and [here](http://rwijk.blogspot.com/2009/09/fast-refreshable-materialized-view.html). (Search for "ANSI" in the second link.) Although those features are not common, the `(+)` limitations are not common either. – Jon Heller Sep 30 '13 at 17:16

score 1 · Answer 11 · answered Nov 04 '19 at 17:22

I was advocate of ANSI joins till last week. Till realised weird behaviour at one of my selects. After digging up Oracle bug base I learned Oracle isn't supporting ANSI joins natively - ANSI joins are being translated to (+) notation and then processed. And this translation has some unacceptable bugs. Unacceptable both because our client isn't applying patches and because some new bugs were introduced even at 12c version - very simple test case with three tables, one record and two outer joins. Seems like guys are not doing automated regression testing at all

Oracle Joins - Comparison between conventional syntax VS ANSI Syntax

11 Answers11

Separating predicates based on selectivity

Hint for the (human!) optimizer

Conclusion

Linked

Related