What's the difference between using oracle's plus notation over the ansi standard 'join' notation?
Is there a difference in performance?
Is the plus notation deprecated?
Thanks
+5
A:
The notation is still supported as of Oracle 10 (and I believe 11). It's use is considered "old fashioned", and also is not as database portable as the ANSI JOIN syntax. It's also considered much less readable, although if you come from the + background getting used to ANSI JOIN can take a little time. The important thing to know before hurling brickbats at Oracle is that they developed their + syntax before the ANSI committee had completed the definitions for the joins.
There is no performance difference; they are expressing the same thing.
Edit: By "not as portable" I should have said "only supported in Oracle SQL"
Tony Miller
2009-07-28 12:08:53
Be aware, though, that there have been a number of bugs where the Oracle optimizer has generated incorrect query plans or incorrect results when ANSI syntax was used. This is much less common in 11.1 than in 10.1 or 10.2, but it has happened often enough and burned enough people that were early adopters of ANSI syntax that large parts of the Oracle community are hesitant to embrace ANSI syntax.
Justin Cave
2009-07-28 14:46:26
+3
A:
I agree with Tony Miller's answer and would like to add that there are also a few things that you can NOT do with the (+) synthax:
- You can not FULL OUTER JOIN two tables, you have to do it manually with a UNION ALL of two joins,
- You can not OUTER JOIN a table to two or more tables, you have to manually create a subquery (ie:
b.id = a.id (+) AND c.id = a.id (+)is not an acceptable clause)
Vincent Malgrat
2009-07-28 12:33:33