• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 518
  • Last Modified:

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.
0
DB-aha
Asked:
DB-aha
  • 4
  • 3
  • 2
  • +3
9 Solutions
 
sdstuberCommented:
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_DBACommented:
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
MikeOM_DBACommented:
the above won't work it's cartesian product.
0
 
sdstuberCommented:
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
 
sdstuberCommented:
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 GeerlingsDatabase 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
 
sdstuberCommented:
>> 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
 
PortletPaulCommented:
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
 
johnsoneSenior 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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now