CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10goracle12c
Srini V
Srini V

Reputation: 11375

Oracle Joins - Comparison between conventional syntax VS ANSI Syntax

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 (+)

  1. 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.

Upvotes: 35

Views: 62410

Answers (11)

magdute
magdute

Reputation: 11

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

Upvotes: 1

user4223622
user4223622

Reputation:

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.

Upvotes: 5

Ján Srniček
Ján Srniček

Reputation: 525

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.

Upvotes: 3

Srini V
Srini V

Reputation: 11375

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.

Upvotes: 12

igr
igr

Reputation: 3499

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.

Upvotes: 29

itmitica
itmitica

Reputation: 511

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."

Upvotes: 3

psaraj12
psaraj12

Reputation: 5072

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

Upvotes: 3

stefan.schwetschke
stefan.schwetschke

Reputation: 8932

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.

Upvotes: 3

Nazarii Bardiuk
Nazarii Bardiuk

Reputation: 4342

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.

Upvotes: 2

SchmitzIT
SchmitzIT

Reputation: 9602

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.

Upvotes: 6

David Aldridge
David Aldridge

Reputation: 52386

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.

Upvotes: 15

Related Questions