Solved

Oracle (+) Join Syntax SQL

Posted on 2014-03-18
12
477 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
Comment Utility
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
Comment Utility
The (+) is an outer join.
All your examples are missing the joined table.
0
 
LVL 1

Author Comment

by:DB-aha
Comment Utility
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
Comment Utility
the above won't work it's cartesian product.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 223 total points
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 55 total points
Comment Utility
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
Comment Utility
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
Comment Utility
>> 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
Comment Utility
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
Comment Utility
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
Comment Utility
All very useful information. Thanks all!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…

763 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

11 Experts available now in Live!

Get 1:1 Help Now