How to find LEFT OUTER JOIN or RIGHT OUTER JOIN with ORACLE JOIN (+)

Question

I am confused with finding left outer join and right outer join properly with Oracle join (+) sign. Check this vs this. I feel both contradict. What I understand is, First link says if the (+) sign is in right hand side, it will be Right Outer Join.

Whereas with second link, my understanding is totally wrong.

Please clarify how to find the right and left outer join properly with an example?

@a_horse_with_no_name: Nobody would bother finding about `(+)` if they need not to maintain legacy Oracle SQL. — sampathsris, Feb 13 '15 at 11:41
@a_horse_with_no_name But my application use it in 2015. This is why I want to understand. Otherwise how do I even know about it. — ever alian, Feb 13 '15 at 11:50
There are exceptions. If you like to create a materialized view with `FAST REFRESH` you **have** to use the old Oracle symtax, otherwise fast refresh is not possible. Oracle themselves do not consider this as a bug. Answer on my SR was: This is just a lack of documentation! — Wernfried Domscheit, Feb 13 '15 at 11:54
@Wernfried: Thanks - Although I would never have expected a mview with an outer join to be fast refreshable in the first place... — , Feb 13 '15 at 12:00
@a_horse_with_no_name: This limitation applies for any join, not only outer joins. — Wernfried Domscheit, Feb 13 '15 at 12:18

Boneist · Answer 1 · 2015-02-18T09:20:01.513

Left outer join just means that you want to return all of the table on the left hand side of the join, and any matching rows from the table on the right.

In old-style Oracle syntax, that would be: where t1.col1 = t2.col1 (+) In ANSI syntax, that would be: from t1 left outer join t2 on (t1.col1 = t2.col1)

Right outer join means that you want to return all of the table on the right hand side of the join, and any matching rows from the table on the left.

In old-style Oracle syntax, that would be: where t2.col1 (+) = t1.col1 In ANSI syntax, that would be: from t2 right outer join t1 on (t2.col1 = t1.col1)

You will, of course, have spotted that you can turn a right outer join into a left outer join simply by reversing the order of the tables. Most outer joins are left ones, probably because it's easier to think of "I want all of this first table, and any matching rows from this other table" rather than the other way round. YMMV, of course!

ETA the following examples:

Left Outer Join:

with t1 as (select 1 col1, 10 col2 from dual union all
            select 2 col1, 20 col2 from dual union all
            select 3 col1, 30 col2 from dual),
     t2 as (select 1 col1, 100 col2 from dual)
select t1.*, t2.*
from   t1, t2
where  t1.col1 = t2.col1 (+)
order by t1.col1;

      COL1       COL2     COL1_1     COL2_1
---------- ---------- ---------- ----------
         1         10          1        100
         2         20                      
         3         30   

with t1 as (select 1 col1, 10 col2 from dual union all
            select 2 col1, 20 col2 from dual union all
            select 3 col1, 30 col2 from dual),
     t2 as (select 1 col1, 100 col2 from dual)
select t1.*, t2.*
from t1 left outer join t2 on (t1.col1 = t2.col1)
order by t1.col1;

      COL1       COL2     COL1_1     COL2_1
---------- ---------- ---------- ----------
         1         10          1        100
         2         20                      
         3         30

Right Outer Join:

with t1 as (select 1 col1, 10 col2 from dual union all
            select 2 col1, 20 col2 from dual union all
            select 3 col1, 30 col2 from dual),
     t2 as (select 1 col1, 100 col2 from dual)
select t1.*, t2.*
from   t1, t2
where t2.col1 (+) = t1.col1
order by t1.col1;

      COL1       COL2     COL1_1     COL2_1
---------- ---------- ---------- ----------
         1         10          1        100
         2         20                      
         3         30   

with t1 as (select 1 col1, 10 col2 from dual union all
            select 2 col1, 20 col2 from dual union all
            select 3 col1, 30 col2 from dual),
     t2 as (select 1 col1, 100 col2 from dual)
select t1.*, t2.*
from t2 right outer join t1 on (t2.col1 = t1.col1)
order by t1.col1;

      COL1       COL2     COL1_1     COL2_1
---------- ---------- ---------- ----------
         1         10          1        100
         2         20                      
         3         30

Can I write `Right Outer Join` like `t1.col1(+) = t2.col1` ( `FROM t1 RIGHT OUTER JOIN t2 ON t1.col1 = t2.col` ) in your same example? because I am confuse since you change the side of the table. See Lalit Kumar reply. — ever alian, Feb 18 '15 at 02:28
In my scenario above, the t1 table was the table I wanted to retrieve all the rows from, and the t2 table was the one I only wanted matching rows. That's why I swapped the table names over between the two sets of examples; they're all effectively the same query. — Boneist, Feb 18 '15 at 09:09

score 2 · Answer 2 · answered Feb 13 '15 at 12:03

Please clarify how to find the right and left outer join properly with an example

I will give a try to show the difference between Oracle outer join syntax and the ANSI/ISO Syntax.

LEFT OUTER JOIN -

SELECT e.last_name,
  d.department_name
FROM employees e,
  departments d
WHERE e.department_id = d.department_id(+);

SELECT e.last_name,
  d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);

RIGHT OUTER JOIN -

SELECT e.last_name,
  d.department_name
FROM employees e,
  departments d
WHERE e.department_id(+) = d.department_id;

SELECT e.last_name,
  d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);

FULL OUTER JOIN -

Before the native support of hash full outerjoin in 11gR1, Oracle would internally convert the FULL OUTER JOIN the following way -

SELECT e.last_name,
  d.department_name
FROM employees e,
  departments d
WHERE e.department_id = d.department_id(+)
UNION ALL
SELECT NULL,
  d.department_name
FROM departments d
WHERE NOT EXISTS
  (SELECT 1 FROM employees e WHERE e.department_id = d.department_id
  );

SELECT e.last_name,
  d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);

Have a look at this.

Take the `Right Outer Join` example. If I exchange the side of the column names in where clause ONLY, it would be `Left Outer Join`. Eg : `WHERE d.department_id(+) = e.department_id` . So this means we can't identify `Left Outer Join ` Or `Right Outer Join` by the side of the `(+)` sign. can we? — ever alian, Feb 26 '15 at 11:28

How to find LEFT OUTER JOIN or RIGHT OUTER JOIN with ORACLE JOIN (+)

2 Answers2

Linked

Related