Meaning of (+) in SQL queries

Question

I've come across some SQL queries in Oracle that contain '(+)' and I have no idea what that means. Can someone explain its purpose or provide some examples of its use? Thanks

Answer 1

It's Oracle's synonym for OUTER JOIN.

SELECT *
FROM a, b
WHERE b.id(+) = a.id

gives same result as

SELECT *
FROM a
     LEFT OUTER JOIN b
     ON b.id = a.id

Answer 2

The + is a short cut for OUTER JOIN, depending on which side you put it on, it indicates a LEFT or RIGHT OUTER JOIN

Check the second entry in this forum post for some examples

Answer 3

IIRC, the + is used in older versions of Oracle to indicate an outer join in the pre-ANSI SQL join syntax. In other words:

select foo,bar
from a, b
where a.id = b.id+

is the equivalent of

select foo,bar
from a left outer join b
on a.id = b.id

NOTE: this may be backwards/slightly incorrect, as I've never used the pre-ANSI SQL syntax.

Answer 4

You use this to assure that the table you're joining doesn't reduce the amount of records returned. So it's handy when you're joining to a table that may not have a record for every key you're joining on.

For example, if you were joining a Customer and Purchase table:

To list of all customers and all their purchases, you want to do an outer join (+) on the Purchase table so customers that haven't purchased anything still show up in your report.

Answer 5

This is a duplicate of this post. You may find some more information there.