0

Topic. I', confused and really need a good explanation about that.

Thank You!

RedFux227
  • 101
  • 14

3 Answers3

3

There are a couple of reasons why the (+) syntax isn't used as much nowadays as in earlier times:

  • Oracle now fully supports ANSI syntax (this wasn't the case for earlier Oracle versions)
  • ANSI syntax is portable between different RDBMSs, whereas (+) is not
  • ANSI syntax is (arguably) more readable because it separates the join conditions from the filter conditions
  • you can't do a FULL OUTER JOIN with (+) syntax
  • ANSI syntax allows you to OUTER JOIN on multiple columns, whereas (AFAIK) this is not possible with (+) syntax
Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
  • So (+) is applicable to all versions of Oracle but not work-proof on all RDBMS. Is that right? Thank You. – RedFux227 Jan 08 '13 at 15:58
  • + works on all Oracle versions I've ever seen, but the result is not portable... – Peter Wooster Jan 08 '13 at 16:05
  • @PeterWooster portable you mean another RDBMS? – RedFux227 Jan 08 '13 at 16:12
  • yes, if you write your code in ANSI style and avoid cool features like CONNECT BY you can port to an other RDBMS with less problems. Some things like SEQUENCE may be totally different like on MySQL where you have autoincrement, or slightly different syntax like in PostGresQL. – Peter Wooster Jan 08 '13 at 16:48
  • Another problem is that the `(+)` syntax leads sometimes to ambiguous results, when there are more than one outer joins. – ypercubeᵀᴹ Jan 08 '13 at 17:50
2

The + notation is an Oracle vendor specific form that isn't consistent across vendors, MS SQL Server has a similar form using * that causes huge confusion, I believe the * goes on the other side of the =. The ANSI form where you say LEFT JOIN etc. is much more readable and is the standard.

There are a few things that are easier to do with the old form so you still see it occasionally. Every time I see it, I have to revert to a previous time, put on my bell bottom pants and hope I understand what the author wanted.

Peter Wooster
  • 6,009
  • 2
  • 27
  • 39
  • On the other hand there are some things that are easier using the `JOIN` syntax (such as a full outer join, or filtering on the joined table) –  Jan 08 '13 at 15:54
  • most things are much easier using the ANSI form. Where I used to work there was a woman who wrote the most elegant old style joins, none of the rest of us could make head nor tail of them. I always rewrote them when I had the time. – Peter Wooster Jan 08 '13 at 15:57
0

There's a way to specify it in ANSI SQL, using OUTER/INNER joins syntax.

Only Oracle and may be another vendor supports the (+) syntax, so it's better to use what is supported in all databases.

richardtz
  • 4,993
  • 2
  • 27
  • 38