-
Notifications
You must be signed in to change notification settings - Fork 24
/
outer_join_02.sql
71 lines (55 loc) · 1.86 KB
/
outer_join_02.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
--
--
-- +-----+ +-----+ +-----+
-- | 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;