Oracle (+) Join Syntax SQL

I am reviewing some code and I am having a hard time grasping the concept of the (+) used in oracle to create joins. I have not been able to find a simple explanation online that explains this in great detail. Here are examples of how I have seen this code written in the code I am reviewing

SELECT *
FROM Tablename
WHERE a.column1 = b.column1(+)

SELECT *
FROM Tablename
WHERE a.column1 (+) NOT IN ('a', 'b')


SELECT *
FROM Tablename
WHERE a.column1(+) = b.column1

I cannot understand the differences of any of these.
LVL 1
DB-ahaAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
your examples are too simplistic.

The (+) syntax only applies if you are joining 2 tables or views

Do you know ANSI syntax?  If so, then see if these help:

select * from a,b
where a.column (+) = b.column

is equivalent to

select * from a right outer join b on a.column = b.column

------------------------------------------------------------------------------


select * from a,b
where a.column  = b.column (+)

is equivalent to

select * from a left outer join b on a.column = b.column
0
 
MikeOM_DBAConnect With a Mentor Commented:
The (+) is an outer join.
All your examples are missing the joined table.
0
 
DB-ahaAuthor Commented:
Thanks for the explanation. Any idea how the second example works from my list of samples?

SELECT *
FROM Tablename A, Tablename B
WHERE a.column1 (+) NOT IN ('value1', 'value2')
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
MikeOM_DBAConnect With a Mentor Commented:
the above won't work it's cartesian product.
0
 
sdstuberConnect With a Mentor Commented:
honesly it's kind of a silly example,
 it's unlikely that query would be useful in most circumstances

You have a cross-join (cartesian product) of B with a subset of A.
0
 
sdstuberConnect With a Mentor Commented:
even though it probably doesn't make much sense, you could still translate it to ANSI syntax

as

SELECT *
FROM Tablename A right outer join Tablename B
on a.column1  NOT IN ('value1', 'value2')
0
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
First, you have to understand the difference between a standard (or inner) join and an outer join, because the (+) operator in Oracle always indicates an outer join.  These are not nearly as common (nor as efficient) as inner joins.

Either join implies at least two tables in the "from" clause.  For inner joins, only records that have values in both tables are returned.  For outer joins, the records from both tables are returned, even if some of them have no corresponding records in the other table.

For example in a simple HR system, if there are three departments: HR, Sales, and Accounting, but there are no employees currently assigned to the HR department, a standard (inner) join like this will *NOT* return the HR department

select e.employee_name, d.department_name
from employees e, departments d
where d.dept_no = e.dept_no

This query with the outer join operator *WILL* return a row for the HR department, with a employee_name value of: (None)

select nvl(e.employee_name,'(None)') d.department_name
from employees e, departments d
where d.dept_no = e.dept_no (+)
0
 
DB-ahaAuthor Commented:
I have a good understanding of  joins when they are written in the traditional ANSI standard. The issue is using this (+) as a joion rather than the ANSI standard. Other than saving writing time and space within code, I don't see the purpose of using this notation.
0
 
sdstuberConnect With a Mentor Commented:
>> I don't see the purpose of using this notation.

In general there isn't a reason to anymore but....

If you are using a version prior to 9i  ANSI joins weren't supported.

In 9i and 10g,  there were occasional bugs in the ANSI support so it was more reliable to use the oracle (+) sometimes.

In 11g and higher the ANSI syntax is pretty solidly supported and it's what I use almost exclusively anymore.
0
 
PortletPaulConnect With a Mentor freelancerCommented:
Oracle isn't the only rdbms to support join syntax of this sort either, happily mostly gone the way of button-up boots.

Now that ANSI join syntax is well and truly in place it should always be the preferred syntax IMHO
0
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
Just to add something that someone told me once.

When doing an outer join the (+) goes on the column that could be missing because of the outer join.

That always seemed to make sense to me and that is how I remembered where the (+) went.
0
 
DB-ahaAuthor Commented:
All very useful information. Thanks all!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.