Solved

Oracle (+) Join Syntax SQL

Posted on 2014-03-18
12
510 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 74

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 74

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 74

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
 
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 74

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 49

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 35

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

726 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