SQL 三表联查_sql中用join连接3个表

---

因需要在三个表中查东西，还不能要交集，要并集。

创建三张表

我用的是DBaver来创建，下面就是代码，跑完之后会出现三个表。

create table tas_master.hk_test_1(
	id INT not null,
	name1 varchar(100) NULL
);

create table tas_master.hk_test_2(
	id INT not null,
	name1 varchar(100) null,
	name2 varchar(100) NULL
);

create table tas_master.hk_test_3(
	id INT not null,
	name1 varchar(100) null,
	name3 varchar(100) NULL
);


insert into tas_master.hk_test_1 
(id, name1) 
values 
(1, 's1'),
(2, 's2'),
(3, 's3');


insert into tas_master.hk_test_2 
(id, name1, name2) 
values 
(2, 's2', '2s2'),
(3, 's3', '3s3'),
(4, 's4', '4s4');


insert into tas_master.hk_test_3 
(id, name1, name3) 
values 
(1, 's1', '1l1'),
(1, 'ss1', '1l1'),
(2, 's2', '2l2'),
(3, 's3', '3l3'),
(4, 's4', '4l4'),
(5, 's5', '5l5');

select * from tas_master.hk_test_1;
select * from tas_master.hk_test_2;
select * from tas_master.hk_test_3;
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

第一个表tas_master.hk_test_1

第二个表tas_master.hk_test_2

第三个表tas_master.hk_test_3

来三表联查一下吧

就是选用三个表的数据，一定是满足on条件的。

inner join 内连接

select coalesce(t1.id, t2.id, t3.id) as id, t1.name1, t2.name1, t2.name2, t3.name1, t3.name3 from 
(select * from tas_master.hk_test_1) as t1 
join (select * from tas_master.hk_test_2) as t2 
on (t1.id = t2.id and t1.name1 = t2.name1) 
join (select * from tas_master.hk_test_3) as t3
on (t2.id = t3.id and t3.name1 = t2.name1);
1
2
3
4
5
6

结果，可看作交集

full join 全连接

select coalesce(t1.id, t2.id, t3.id) as id, t1.name1, t2.name1, t2.name2, t3.name1, t3.name3 from 
(select * from tas_master.hk_test_1) as t1 
full join (select * from tas_master.hk_test_2) as t2 
on (t1.id = t2.id) 
full join (select * from tas_master.hk_test_3) as t3
on (t1.id = t3.id);
1
2
3
4
5
6

结果，可看作并集，注意on后面只能跟一个条件，多了不好用

我们看下用不同的on后面条件，出来结果是否一样。

insert into tas_master.hk_test_3 
(id, name1, name3) 
values 
(6, 's1', '1l2');
1
2
3
4

tas_master.hk_test_3 就变成这样，多了id_6

name1条件

select coalesce(t1.id, t2.id, t3.id) as id, t1.name1, t2.name1, t2.name2, t3.name1, t3.name3 from 
(select * from tas_master.hk_test_1) as t1 
full join (select * from tas_master.hk_test_2) as t2 
on (t1.name1 = t2.name1) 
full join (select * from tas_master.hk_test_3) as t3
on (t1.name1 = t3.name1);
1
2
3
4
5
6

id条件

select coalesce(t1.id, t2.id, t3.id) as id, t1.name1, t2.name1, t2.name2, t3.name1, t3.name3 from 
(select * from tas_master.hk_test_1) as t1 
full join (select * from tas_master.hk_test_2) as t2 
on (t1.id = t2.id) 
full join (select * from tas_master.hk_test_3) as t3
on (t1.id = t3.id);
1
2
3
4
5
6

两个还不是一样的，我认为只要on条件中，筛出来的那一列肯定是有值的。
我有尝试了不同的条件组合，发现这个on条件中还是只放一个为好，下面是我做的记录。

select t1.id, t2.id, t3.id, t1.name1, t2.name1, t2.name2, t3.name1, t3.name3 from 
(select * from tas_master.hk_test_1) as t1 
full join (select * from tas_master.hk_test_2) as t2 
on (t1.id = t2.id) 
full join (select * from tas_master.hk_test_3) as t3
on (t2.id = t3.id and t1.id = t3.id and t1.id = t2.id);
1
2
3
4
5
6

select t1.id, t2.id, t3.id, t1.name1, t2.name1, t2.name2, t3.name1, t3.name3 from 
(select * from tas_master.hk_test_1) as t1 
full join (select * from tas_master.hk_test_2) as t2 
on (t1.id = t2.id) 
full join (select * from tas_master.hk_test_3) as t3
on (t2.id = t3.id and t1.id = t3.id);
1
2
3
4
5
6

select t1.id, t2.id, t3.id, t1.name1, t2.name1, t2.name2, t3.name1, t3.name3 from 
(select * from tas_master.hk_test_1) as t1 
full join (select * from tas_master.hk_test_2) as t2 
on (t1.id = t2.id) 
full join (select * from tas_master.hk_test_3) as t3
on (t2.id = t3.id);
1
2
3
4
5
6

left join 左连接

这个就简单了就是以左边的为准。

select t1.id, t2.id, t3.id, t1.name1, t2.name1, t2.name2, t3.name1, t3.name3 from 
(select * from tas_master.hk_test_1) as t1 
left join (select * from tas_master.hk_test_2) as t2 
on (t1.id = t2.id) 
left join (select * from tas_master.hk_test_3) as t3
on (t1.id = t3.id);
1
2
3
4
5
6

select t1.id, t2.id, t3.id, t1.name1, t2.name1, t2.name2, t3.name1, t3.name3 from 
(select * from tas_master.hk_test_1) as t1 
left join (select * from tas_master.hk_test_2) as t2 
on (t1.id = t2.id) 
left join (select * from tas_master.hk_test_3) as t3
on (t3.id = t2.id);
1
2
3
4
5
6

给我的感觉是，三表联查，主要是看on条件中是什么样的。想三表联查还是with写好点。