10

I've come across such code which I need to re-write using ANSI Standard and I want to make sure I'm keeping the joins correct. Also, what convention is this?

What is the syntax for the other types of joins using this convention?

Using this convention will the (+) always be on the right hand side of the expression?

Where can I learn more about this?

Ollie
  • 17,058
  • 7
  • 48
  • 59
zundarz
  • 1,540
  • 3
  • 24
  • 40
  • 1
    Here's an explanation I gave in response to a similar question: http://stackoverflow.com/questions/6559261/left-outer-join-using-sign-in-oracle-11g/6559318#6559318 Hope that helps. – Wiseguy Nov 30 '11 at 16:36

2 Answers2

9

In Oracle syntax, this signifies a LEFT OUTER JOIN, the right side being optional (nullable). If the + was on the left side by A.ID, it would have been a RIGHT OUTER JOIN. This syntax is (I believe) deprecated by ISO SQL 99, but remains in Oracle.

It is the equivalent of this preferred syntax:

SELECT
 A.ID,
 B.somecol
FROM A LEFT OUTER JOIN B ON A.ID = B.ID
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • you said it was a right join, but then in the example, used left join. – Joe Nov 30 '11 at 16:23
  • The `(+)` syntax is proprietary syntax and was never in the SQL Standard in the first place. Outer join first appeared in the SQL Standard with effect from SQL-92. – onedaywhen Dec 01 '11 at 10:03
4

This looks to be a basic left outer join using alternate syntax. Here's the Joins page on Wikipedia where you can see it.

Basically, it is a way to perform a conditional join and the (+) seems to be an alternate syntax. Hope this helps.

DevinBM
  • 91
  • 1
  • 9