Solved

Oracle (+) Join Syntax SQL

Posted on 2014-03-18
12
486 Views
Last Modified: 2014-03-19
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
Comment
Question by:DB-aha
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 223 total points
ID: 39937941
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
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 112 total points
ID: 39937942
The (+) is an outer join.
All your examples are missing the joined table.
0
 
LVL 1

Author Comment

by:DB-aha
ID: 39937955
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
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 112 total points
ID: 39937971
the above won't work it's cartesian product.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 223 total points
ID: 39937979
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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 223 total points
ID: 39937987
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 55 total points
ID: 39938017
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
 
LVL 1

Author Comment

by:DB-aha
ID: 39938071
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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 223 total points
ID: 39938089
>> 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
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 55 total points
ID: 39938554
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
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 55 total points
ID: 39939272
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
 
LVL 1

Author Closing Comment

by:DB-aha
ID: 39939664
All very useful information. Thanks all!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now