Skip to Main Content
  • Questions
  • Difference join between (+) and (-) notation

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Rass.

Asked: January 10, 2017 - 8:01 am UTC

Last updated: June 14, 2023 - 5:14 pm UTC

Version: Oracle 10g

Viewed 100K+ times! This question is

You Asked

Hi Tom.

I have very old application. Then I saw some store procedure using (+) or (-) notation on the query. What i want to ask are :

1. what is the difference between (+) and (-) notation ?
2. what is the difference between (the position of the notation I put on left or right) ?

SELECT *
FROM A, B
WHERE A.column = B.column(+)

and

SELECT *
FROM A, B
WHERE A.column(+) = B.column

3. I read from forum that notation is only present for backwards compatibility because Oracle debuted it before the ANSI standard for joins. So is there any problem if I'm not change the notation to 'join' statement (ANSI standard) on my old existing query ?

4. Is it recommended or not using notation (+) (-) ?

5. How about the performance. It's good using (+) (-) notation or join notation ?

and Chris said...

The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins.

An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key. If there's no matching row, return null.

Contrast this with an inner join. Here you only get rows matching the join key that exist in both tables.

The location of the (+) indicates which table you're outer joining. The column(s) you place this against define the outer joined table(s).

It's easiest to understand this with examples.

create table t1 (
  x int
);

create table t2 (
  x int
);

insert into t1 values (1);
insert into t1 values (2);
insert into t2 values (2);
insert into t2 values (3);

select * from t1;

X  
1  
2  

select * from t2;

X  
2  
3  


The tables both have two rows. There is one row in common between them (X=2).

With an inner join you only get one row (Oracle and ANSI syntax shown):

select * from t1, t2
where  t1.x = t2.x;

X  X  
2  2  

select * from t1
join   t2
on     t1.x = t2.x;

X  X  
2  2  


With an outer join you get two rows. Which two depends on which table you outer join:

-- outer join t2
select * from t1, t2
where  t1.x = t2.x (+);

X  X  
2  2  
1   

select * from t1
left join t2
on     t1.x = t2.x ;  

X  X  
2  2  
1   

-- outer join t1
select * from t1, t2
where  t1.x (+) = t2.x;

X  X  
2  2  
   3  

select * from t1
right join t2
on     t1.x = t2.x ;

X  X  
2  2  
   3  


You decide whether how to join based on your requirements. Using it can limit the options available to the optimizer. So you should only do this when strictly necessary.

In general Oracle syntax and ANSI syntax are compatible. Though there are still some situations where this isn't the case. Notably query rewrite for MVs don't always work with ANSI syntax:

https://danischnider.wordpress.com/2016/11/30/ansi-join-syntax-and-query-rewrite/

And you can't do a full outer join directly in Oracle syntax:

select * from t1, t2
where  t1.x (+) = t2.x (+);

SQL Error: ORA-01468: a predicate may reference only one outer-joined table

select * from t1
full join t2
on     t1.x = t2.x ;

X  X  
2  2  
   3  
1   


Though you can emulate a full outer join using Oracle syntax with the following long-winded SQL:

select * from t1, t2
where  t1.x = t2.x (+)
union all
select * from t1, t2
where  t1.x (+) = t2.x
and    t1.x is null;

X  X  
2  2  
1     
   3  

Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Rass, January 11, 2017 - 4:00 am UTC


A reader, March 14, 2018 - 6:09 am UTC


Nice Article

A reader, May 10, 2018 - 2:10 pm UTC


very nice examples

A reader, January 20, 2019 - 9:46 pm UTC

very useful, well said and explained, thank you
Connor McDonald
January 21, 2019 - 1:05 am UTC

glad we could help

full outer join in oracle 10.2

Pavel, August 02, 2019 - 5:06 am UTC

SQL> select * from t1
2 full outer join t2 on t1.x = t2.x;

X X
---------- ----------
2 2
1
3

Thanks!

A reader, October 12, 2022 - 7:39 pm UTC

Great answer! Haven't seen this before until today when looking at on old app so appreciate the answer.
Chris Saxon
October 13, 2022 - 12:32 pm UTC

You're welcome

Where criteria for outer joined table

jgroves3, November 28, 2022 - 3:48 pm UTC

What if you need to limit the data in the outer join? For instance, only one data_type should be pulled (ie work phone). I would typically do this with a derived table and outer join since putting where criteria in the main clause for an outer join essentially would make it an inner join. I have seen this accomplished with a (+) in the where clause, ie. where phone_type = 'work'. Will this give the same results as a derived table or would you recommend rewriting the whole query to ANSI standard since the two cannot be mixed?
Chris Saxon
November 29, 2022 - 1:44 pm UTC

I'm unclear what you're asking - do you mean limit data from the inner table?

If so you can do this with ISO or Oracle syntax. For example these return all the departments, joining employees with IT jobs:

select * from hr.departments d
left join hr.employees e
on     d.department_id = e.department_id
and    e.job_id like 'IT%';

select * from hr.departments d, hr.employees e
where  d.department_id = e.department_id (+)
and    e.job_id (+) like 'IT%';


Nice Explanation

Shivam, January 18, 2023 - 11:20 am UTC

Nice explanation and to the point. Thanks !
Chris Saxon
January 18, 2023 - 1:28 pm UTC

You're welcome

Although...you may want to consider union not union all

Jen, February 24, 2023 - 5:49 am UTC

Union will get rid of duplicates
Chris Saxon
February 24, 2023 - 5:53 pm UTC

I assume you're referring to the full outer emulation at the end.

UNION adds an extra de-duplication step - why do you think this is necessary?

The and t1.x is null condition ensures the second subquery returns rows that are only in T2; the the first subquery excludes these.

Great Question - Below is somewhat related...

Ben, April 11, 2023 - 1:44 pm UTC

Hi Tom

Quick question - what does the (+) do in the line
and e.job_id (+) like 'IT%';?

select * from hr.departments d, hr.employees e
where d.department_id = e.department_id (+)
and e.job_id (+) like 'IT%';

I have come across a piece of code that uses the (+) with a value e.g. (+)= 'N'; is this used to match rows with only that value?
Chris Saxon
April 12, 2023 - 1:28 pm UTC

This is necessary to preserve the outer join to employees. If you write:

select * from hr.departments d, hr.employees e
where d.department_id = e.department_id (+)
and e.job_id like 'IT%';


It becomes an inner join - despite the (+) on department_id.

This is because for the departments no employees, job_id is null. But null like 'IT%' is not true. Select only returns rows with true conditions. So this excludes departments with no employees. aka an inner join!

Best explanation out there.

A reader, June 14, 2023 - 2:42 pm UTC

Best explanation out there.
Chris Saxon
June 14, 2023 - 5:14 pm UTC

Thanks

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library