Oracle: What is "(+) ="?

Question

I don't currently have any access to any Oracle books and my searches online are returning literally no results ( (+) in Google shows nothing). I'm looking through existing code to add some fields to a query and saw:

AND lookup.LookupID (+) = AuditID

However, I have no idea what this does. Any help would be appreciated.

Note: I'm not seeing a community wiki checkbox while posting this in Google Chrome, so if someone can change it to that for me, I'd be appreciative. Thanks.

Answer 1

What you're seeing is an "old" way for specifying an outer join. Oracle needed a syntax to represent outer joins and before the ANSI commitee settled on a standard syntax, Oracle came up with their own.

(+) should be seen only in legacy code and today it is recommended to simply avoid this syntax and stick with the standards.

In this Oracle FAQ you can read some historical info. Not too detailed, but it will give you an idea of how this operator came to be.

Relevant part:

In query2 we see the "Old Style" Oracle syntax for OUTER-JOIN. It is the PLUS SIGN in parenthesis. This syntax was not actually invented by Oracle but rather some smart guy whose name escapes me at the moment. Oracle saw its value and was an early adopter of OUTER-JOIN but doing so required them to use a syntax before ANSI SQL COMMITTEE settled on one. The new and improved ANSI SQL is fine and all, some say even better, but being an old bird I tend to stick with old and proven ways till something makes me change.

Apparently not even Oracle did invent this syntax. It's good to have standards...

Answer 2

It is Oracle way to specify a outer join (ANSI way would be RIGHT JOIN/LEFT JOIN)