Appreciate you have a preference, but perhaps this is subjective at best. I find the "old" syntax much easier to read and maintain. The ANSI one is very verbose and I prefer the more concise oracle syntax. I am always frustrate by the verbosity of the new syntax when I come across it. It always appears to be a lot of text to convey what what can be communicated in a much clearer and simpler manner. I suppose once you are use to one way it gets hardwired and a different approach inevitably seems clunky. For me there is no doubt that the (+) syntax is easier to work with. The left and right variation seems positively strange to me. Adding direction to the definition of a relationship seems convoluted and without added value. Why add another concept to the relationship construct.
Preference for ANSI join isn't subjective at all. Take a look this topic,
the lack of a join predicate was causing a cartesian join, resulting in trillions of rows being generated. If the query were written with ANSI syntax, it would be impossible to make a mistake like that: you get a cartesian join only if you ask for it. The old Oracle syntax is far too prone to error.
I've been working with Oracle since the early 90's and personally, I prefer using the "old" Oracle syntax. I agree that I find it easier to read and maintain. I also agree the ANSI one is too verbose for my liking - it's like writing a small story.
One issue I have is working at a shop that employs both syntax's. This can make it difficult for those responsible for modifying or tuning someone else's query's. This can be a nightmare.
As far as I'm concerned, anyone new to SQL should use whichever syntax they find easier or whichever one is shoved down their throat. It alwasy seems that people who cross-over from SQL Server to Oracle are the ones who bitch the most.
While I fully understand the ANSI syntax, I have no interest in developing any SQL code using it. Just like Spanish is prevalent now throughout doesn't mean I have to go learn it.
It's not subjective at all.
I'll quote the part of Trevor North's answer:
Starting with Oracle 9i, Oracle recommends that SQL developers use the ANSI join syntax instead of the Oracle proprietary (+) syntax. There are several reasons for this recommendation, including:
1. Easier to segregate and read (without mixing up join versus restriction code)
2. Easier to construct join code correctly (especially in the case of “outer” joins)
3. Portable syntax will work on all other ANSI compliant databases, such as MS SQL Server, DB2, MySQL, PostgreSQL, et al
4. Since it’s the universally accepted standard, it’s the general target for all future database and third party vendors’ tools
5. The proprietary Oracle outer-join (+) syntax can only be used in one direction at a time, it cannot perform a full outer join
6. The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
7. A condition containing the (+) operator cannot be combined with another condition using the OR logical operator
8. A condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.
9. A condition cannot compare any column marked with the (+) operator with a sub-query."
Thus it’s time to embrace the ANSI join syntax – and move into the 21st century
Thanks for your views and points.
If Oracle interprets the ANSI syntax differently and optimize the output in any way, then it
is worth adopting.
Can somebody put some good weight behind the ANSI syntax with examples and explain plan?
It would be great help for all.
The plans are the same.