How to find LEFT OUTER JOIN or RIGHT OUTER JOIN with ORACLE JOIN (+)
I am confused about finding the left outer join and the right outer join with the Oracle join sign (+) correctly. Check this one against this . I feel like both are contradicting. I understand that the first link says that the (+) sign is on the right side, this will be the right outer input.
If the second link, my understanding is completely wrong.
Please clarify how to find right and left outer join correctly with example?
source to share
A left outer join means you want to return the entire table on the left side of the join and any corresponding rows from the table on the right.
In old-style Oracle syntax it would be: where t1.col1 = t2.col1 (+)
In ANSI syntax it would be:from t1 left outer join t2 on (t1.col1 = t2.col1)
A direct outer join means you want to return the entire table on the right side of the join and any corresponding rows from the table on the left.
In old-style Oracle syntax it would be: where t2.col1 (+) = t1.col1
In ANSI syntax it would be:from t2 right outer join t1 on (t2.col1 = t1.col1)
You will have noticed, of course, that you can turn the right outer join into a left outer join simply by reordering the tables. Most outer joins are left-handed, probably because it is easier to think of "I want all of this first table and any matching rows from this other table" rather than the other way around. YMMV, of course!
ETA are 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
source to share
Please clarify how to find right and left outer join correctly with example
I will try to show the difference between Oracle's strong external syntax and 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);
CORRECT BUILT-IN WORK -
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 EXTERNAL CONNECTION -
Before native hash function support is completely broken in 11gR1, Oracle internally converts the FULL OUTER JOIN as follows -
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);
Check it out .
source to share