Difference join between (+) and (-) notation

 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