What type of join is '....where A.ID = B.ID (+)'

Question

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?

Answer 1

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

Answer 2

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.