資料準備：
create table T1(
A varchar(10) not null,
B varchar(10) not null,
C tinyint not null
);
create table T2(
B varchar(10) not null,
E tinyint not null
);
insert into T1
values
(\'a1\', \'b1\', 5),
(\'a1\', \'b2\', 6),
(\'a2\', \'b3\', 8),
(\'a2\', \'b4\', 12);
insert into T2
values
(\'b1\', 3),
(\'b2\', 7),
(\'b3\', 10),
(\'b3\', 2),
(\'b5\', 2);
select * from T1;
select * from T2;
結果：
1 A B C
2 ---------- ---------- ----
3 a1 b1 5
4 a1 b2 6
5 a2 b3 8
6 a2 b4 12
7
8 (4 行受影響)
9
10 B E
11 ---------- ----
12 b1 3
13 b2 7
14 b3 10
15 b3 2
16 b5 2
17
18 (5 行受影響)
1、内連接配接
1 select * from T1 inner join T2 on T1.B = T2.B
2
3 A B C B E
4 ---------- ---------- ---- ---------- ----
5 a1 b1 5 b1 3
6 a1 b2 6 b2 7
7 a2 b3 8 b3 10
8 a2 b3 8 b3 2
2、左外連接配接
1 select * from T1 left outer join T2 on T1.B = T2.B
2
3 A B C B E
4 ---------- ---------- ---- ---------- ----
5 a1 b1 5 b1 3
6 a1 b2 6 b2 7
7 a2 b3 8 b3 10
8 a2 b3 8 b3 2
9 a2 b4 12 NULL NULL
3、右外連接配接
1 select * from T1 right outer join T2 on T1.B = T2.B
2
3 A B C B E
4 ---------- ---------- ---- ---------- ----
5 a1 b1 5 b1 3
6 a1 b2 6 b2 7
7 a2 b3 8 b3 10
8 a2 b3 8 b3 2
9 NULL NULL NULL b5 2
4、完全連接配接
1 select * from T1 full outer join T2 on T1.B = T2.B
2
3 A B C B E
4 ---------- ---------- ---- ---------- ----
5 a1 b1 5 b1 3
6 a1 b2 6 b2 7
7 a2 b3 8 b3 10
8 a2 b3 8 b3 2
9 a2 b4 12 NULL NULL
10 NULL NULL NULL b5 2
5、交叉連接配接（笛卡爾乘積）
1 select * from T1 cross join T2
2
3 A B C B E
4 ---------- ---------- ---- ---------- ----
5 a1 b1 5 b1 3
6 a1 b1 5 b2 7
7 a1 b1 5 b3 10
8 a1 b1 5 b3 2
9 a1 b1 5 b5 2
10 a1 b2 6 b1 3
11 a1 b2 6 b2 7
12 a1 b2 6 b3 10
13 a1 b2 6 b3 2
14 a1 b2 6 b5 2
15 a2 b3 8 b1 3
16 a2 b3 8 b2 7
17 a2 b3 8 b3 10
18 a2 b3 8 b3 2
19 a2 b3 8 b5 2
20 a2 b4 12 b1 3
21 a2 b4 12 b2 7
22 a2 b4 12 b3 10
23 a2 b4 12 b3 2
24 a2 b4 12 b5 2
