数据准备：
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
