1

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?

Community
  • 1
  • 1
ever alian
  • 938
  • 2
  • 10
  • 39

2 Answers2

2

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
Boneist
  • 22,345
  • 1
  • 24
  • 39
  • 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
2

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.

Lalit Kumar B
  • 45,678
  • 12
  • 90
  • 118
  • 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