A left outer join makes sure that all records of the «left» table are returned, even if there is no matching record in the «right» table:
create table tq84_oj_left (
a number,
b varchar2(10)
);
create table tq84_oj_right (
c number,
d varchar2(10)
);
insert into tq84_oj_left values (1, 'one' );
insert into tq84_oj_left values (2, 'two' );
insert into tq84_oj_left values (3, 'three');
insert into tq84_oj_left values (4, 'four' );
--
insert into tq84_oj_right values (1, 'foo');
insert into tq84_oj_right values (1, 'bar');
insert into tq84_oj_right values (1, 'baz');
insert into tq84_oj_right values (3, 'Paris');
insert into tq84_oj_right values (3, 'Berlin');
insert into tq84_oj_right values (3, 'London');
insert into tq84_oj_right values (4, 'London');
-------------------------------------------------------------
--
-- The «problem» with the following select statement is:
--
-- No record for 'two' and 'four' are returned,
-- but we'd like to see them.
--
select
b,
d
from
tq84_oj_left left join
tq84_oj_right on a = c
where
d != 'London'
order by
a;
-------------------------------------------------------------
--
--
-- If the condition d != 'London' is made part
-- of the «explicit» left join condition, then
-- we have at least a record returned for all records
-- of table TQ84_OJ_LEFT.
select
b,
d
from
tq84_oj_left left join
tq84_oj_right on a = c and
d!='London'
order by
a;
drop table tq84_oj_left purge;
drop table tq84_oj_right purge;
create table tq84_a (
a1 number,
a2 varchar2(10)
);
insert into tq84_a values (1, 'foo');
insert into tq84_a values (1, 'bar');
insert into tq84_a values (1, 'baz');
insert into tq84_a values (2, 'abc');
insert into tq84_a values (2, 'def');
insert into tq84_a values (3, 'XXX');
create table tq84_b (
b1 number primary key,
b2 varchar2(10)
);
insert into tq84_b values (1, 'some text');
insert into tq84_b values (3, 'more text');
create table tq84_c (
c1 number primary key,
c2 varchar2(10)
);
insert into tq84_c values (3, '....');
select
a.a1,
a.a2,
b.b2,
c.c2
from
tq84_a a left join
tq84_b b on a.a1 = b.b1 left join
tq84_c c on b.b1 = c.c1
order by
a.a1;
-- A1 A2 B2 C2
-- ---------- ---------- ---------- ----------
-- 1 foo some text
-- 1 bar some text
-- 1 baz some text
-- 2 abc
-- 2 def
-- 3 XXX more text ....
drop table tq84_c purge;
drop table tq84_b purge;
drop table tq84_a purge;
--
--
-- +-----+ +-----+ +-----+
-- | A +---<| B +---<| C | l
-- +-----+ +--+--+ +-----+
-- |
-- ^
-- +-----+
-- | D |
-- +-----+
--
create table tq84_a (a number primary key);
create table tq84_b (b number primary key references tq84_a);
create table tq84_c (c number references tq84_b);
create table tq84_d (d number references tq84_b);
insert into tq84_a values (1);
insert into tq84_a values (2);
insert into tq84_a values (3);
insert into tq84_a values (4);
insert into tq84_b values (2);
insert into tq84_b values (3);
insert into tq84_b values (4);
insert into tq84_c values (3);
insert into tq84_c values (4);
insert into tq84_d values (2);
insert into tq84_d values (3);
column a format 99
column b format 99
column c format 99
column d format 99
prompt
prompt __________________ Without C:
select a,b,cast(null as number) c,d
from tq84_a a join
tq84_b b on b.b = a.a join
tq84_d d on d.d = b.b;
prompt
prompt __________________ all tables, no left joins:
select a,b,c,d
from tq84_a a join
tq84_b b on b.b = a.a join
tq84_c c on c.c = b.b join
tq84_d d on d.d = b.b;
prompt
prompt __________________ all tables, left joining c:
select a,b,c,d
from tq84_a a join
tq84_b b on b.b = a.a left join
tq84_c c on c.c = b.b join
tq84_d d on d.d = b.b;
prompt
drop table tq84_d purge;
drop table tq84_c purge;
drop table tq84_b purge;
drop table tq84_a purge;
create table tq84_partitioned_outer_join (
col_1 varchar2(10),
col_2 varchar2(10),
num number
);
insert into tq84_partitioned_outer_join values ('one' , 'foo', 1);
insert into tq84_partitioned_outer_join values ('one' , 'foo', 1);
--
insert into tq84_partitioned_outer_join values ('one' , 'bar', 2);
insert into tq84_partitioned_outer_join values ('one' , 'bar', 0);
insert into tq84_partitioned_outer_join values ('two' , 'bar', 3);
insert into tq84_partitioned_outer_join values ('two' , 'bar', 1);
--
insert into tq84_partitioned_outer_join values ('two' , 'baz', 2);
insert into tq84_partitioned_outer_join values ('two' , 'baz', 2);
insert into tq84_partitioned_outer_join values ('three', 'foo', 5);
insert into tq84_partitioned_outer_join values ('three', 'foo', 7);
--
insert into tq84_partitioned_outer_join values ('three', 'baz', 6);
insert into tq84_partitioned_outer_join values ('three', 'baz', 6);
--
insert into tq84_partitioned_outer_join values ('four' , 'xxx', 9);
select
col_1,
col_2,
sum(num)
from
tq84_partitioned_outer_join
group by
col_1, col_2
order by
col_1,
col_2;
prompt
prompt ------
prompt
with fbb as (
select 'foo' c from dual union all
select 'bar' c from dual union all
select 'baz' c from dual
)
select
tab.col_1,
fbb.c,
sum(tab.num) s
from
fbb left outer join
tq84_partitioned_outer_join tab
partition by (tab.col_1)
on fbb.c = tab.col_2
group by
tab.col_1,
fbb.c
order by
tab.col_1,
fbb.c;
drop table tq84_partitioned_outer_join purge;
create table tq84_a(u number);
create table tq84_b(v number);
insert into tq84_a values(1);
insert into tq84_a values(2);
insert into tq84_b values(1);
prompt
prompt *** 1 ***
select
u, v
from
tq84_a , tq84_b
where
u = 2 and
u = v(+) -- => selects one record
;
prompt
prompt *** 2 ***
select
u, v
from
tq84_a, tq84_b
where
u = 2 and
2 = v(+) -- => selects no record
;
prompt
prompt *** 3 ***
select
u, v
from
tq84_a left join
tq84_b on v = 2 -- => selects two records
;
drop table tq84_a purge;
drop table tq84_b purge;
create table A (i number(1) primary key);
create table Z (i char (1) primary key);
create table A2Z (
ia not null references A,
flg char(1) null,
iz char(1) null
);
insert into A values (1);
insert into A values (2);
insert into A values (3);
insert into A values (4);
insert into A values (5);
insert into A values (6);
insert into A values (7);
insert into Z values ('a');
insert into Z values ('b');
insert into Z values ('c');
insert into Z values ('d');
insert into A2Z values (1, 'y', 'a' );
insert into A2Z values (1, 'n', 'b' );
insert into A2Z values (2,null, 'c' );
insert into A2Z values (2, 'q', 'd' );
insert into A2Z values (3, 'y', 'e' );
insert into A2Z values (4,null, 'f' );
insert into A2Z values (5, 'y', null);
insert into A2Z values (6, 'v', null);
select
A.i a_i,
Z.i z_i
from
A, A2Z, Z
where
A.i = A2Z.ia (+) and
A2Z.flg (+) = 'y' and
A2Z.iz = Z.i (+)
order by
A.i;
drop table A2Z purge;
drop table Z purge;
drop table A purge;
create table tq84_a (
a number
);
create table tq84_b (
b number
);
create table tq84_c (
c number
);
insert into tq84_a values (1);
insert into tq84_a values (2);
insert into tq84_a values (3);
insert into tq84_b values (1);
insert into tq84_b values (2);
insert into tq84_c values (1);
insert into tq84_c values (3);
prompt
prompt ___ Select three records ___
select
a.a,
b.b,
c.c
from
tq84_a a left join
tq84_b b on a = b left join
tq84_c c on b = c;
prompt
prompt ___ Select one record ____
select
a.a,
b.b,
c.c
from
tq84_a a left join
tq84_b b on a = b join
tq84_c c on b = c;
drop table tq84_a purge;
drop table tq84_b purge;
drop table tq84_c purge;