36

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

Zabbala
  • 2,183
  • 1
  • 17
  • 17
  • dupe: http://stackoverflow.com/questions/430274/oracle-what-does-do-in-a-where-clause – Alkini Feb 05 '09 at 21:48
  • 1
    I actually tried searching for an answer before posting the question but didn't get any results when searching for '+' or '(+). It's weird that the question title in the URL seems to skip the (+) part as well. – Zabbala Feb 05 '09 at 21:52
  • This is a duplicate of [this post](http://stackoverflow.com/questions/430274/oracle-what-does-do-in-a-where-clause). You may find some more information there. – Bernard Dy Feb 05 '09 at 20:36

4 Answers4

53

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
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 1
    I'll note for the next person, pay attention to what's happened to the `a`'s and `b`'s here. In the example the from clause is `[a, b]`, but the equivalency check is `[b.id = a.id]`. My habit is to keep the equivalency `[a.id = b.id]`; so I misread the example. An hour of my life I won't get back ;^) – BIBD Mar 13 '15 at 17:24
  • 5
    To that, I would interpret `(+)` as oracle's way of saying "this side gets nulls when no match can be found". Using my norm for equivalencies of `[a.id = b.id]`; then there's a mirror happening. If I'm re-writing the query to follow the modern standard, `[a.id = b.id(+)]` becomes a LEFT OUTER and `[a.id(+) = b.id]` becomes a RIGHT OUTER. – BIBD Mar 13 '15 at 17:30
  • The thing is the (+) operator is placed directly in the conditional statement and on the side of the optional table (the one which is allowed to contain empty or null values within the conditional). – Yergalem Jul 14 '17 at 14:49
4

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

Geoff
  • 9,340
  • 7
  • 38
  • 48
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 all customers and all their purchases, do an outer join (+) on the Purchase table so customers that haven't purchased anything still show up in your report.

Cory House
  • 14,235
  • 13
  • 70
  • 87
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.

Harper Shelby
  • 16,475
  • 2
  • 44
  • 51