MySQL全连接查询

mysql没有全连接，但我们可以通过以下两种方式实现

1. 使用union和left join实现全连接

select xxx -- 查询字段
from table1 left join table2 
on xxx -- 连接条件，下同
where xxx
union
select xxx
from table2 left join table1
on xxx
where xxx

示例：下表中存储了学生的考试成绩（以下均假定学生姓名不会重复），需要查询所有学生的数学和英语成绩，过滤掉两科都没有成绩的结果

-- exam_result definition
CREATE TABLE `exam_result` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `student_ name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学生姓名',
  `subject` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学科',
  `score` int NOT NULL COMMENT '分数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into exam_result (student_name, subject, score) values 
('zhangsan', 'MATH', 99), ('lisi', 'ENG', 88), ('wangwu', 'MATH', 92), ('wangwu', 'ENG', 90),
('zhaoliu', 'MATH', 82), ('zhaoliu', 'ENG', 89), ('zhaoliu', 'BIO', 93), ('lisi', 'BIO', 95)

数据如下

id	student_ name	subject	score
1	zhangsan	MATH	99
2	lisi	ENG	88
3	wangwu	MATH	92
4	wangwu	ENG	90
5	zhaoliu	MATH	82
6	zhaoliu	ENG	89
7	zhaoliu	BIO	93
8	lisi	BIO	95

sql

select math.student_name, math.score as math_score, ifnull(eng.score, -1) as eng_score
from exam_result math
left join exam_result eng on math.student_name = eng.student_name and eng.subject = 'ENG'
where math.subject = 'MATH'
union
select eng.student_name, ifnull(math.score, -1) as math_score, eng.score as eng_score
from exam_result eng
left join exam_result math on math.student_name = eng.student_name and math.subject = 'MATH'
where eng.subject = 'ENG'

这种方法缺点很明显，sql很长，而且当要查询的学科数量多起来的时候代码量是平方级增长，例如如果要把生物成绩也加上，sql就会变成下面这样

select math.student_name, math.score as math_score, ifnull(eng.score, -1) as eng_score, ifnull(bio.score, -1) as bio_score
from exam_result math
left join exam_result eng on math.student_name = eng.student_name and eng.subject = 'ENG'
left join exam_result bio on math.student_name = bio.student_name and bio.subject = 'BIO'
where math.subject = 'MATH'
union
select eng.student_name, ifnull(math.score, -1) as math_score, eng.score as eng_score, ifnull(bio.score, -1) as bio_score
from exam_result eng
left join exam_result math on math.student_name = eng.student_name and math.subject = 'MATH'
left join exam_result bio on eng.student_name = bio.student_name and bio.subject = 'BIO'
where eng.subject = 'ENG'
union
select bio.student_name, ifnull(math.score, -1) as math_score, ifnull(eng.score, -1) as eng_score, bio.score as bio_score
from exam_result bio
left join exam_result math on math.student_name = bio.student_name and math.subject = 'MATH'
left join exam_result eng on eng.student_name = bio.student_name and eng.subject = 'ENG'
where eng.subject = 'BIO'

上面的代码简直是个灾难，所以更好的办法是下面这种。

2. 使用另一张表作为主表去关联需要全连接的其他表

select xxx
from table0
left join table1 on xxx -- table0和table1的关联关系
left join table2 on xxx -- table0和table2的关联关系
where xxx

同样以上面的示例，用一张学生表作为主表

-- student definition

CREATE TABLE `student` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(100) NOT NULL COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

student表数据如下

id	name
1	zhangsan
2	lisi
3	wangwu
4	zhaoliu
5	xiaoba

select stu.name student_name, ifnull(math.score, -1) math_score, ifnull(eng.score, -1) eng_score, ifnull(bio.score, -1) bio_score 
from student stu
left join exam_result math on math.student_name = stu.student_name and math.subject = 'MATH'
left join exam_result eng on eng.student_name = stu.student_name and eng.subject = 'ENG'
left join exam_result bio on bio.student_name = stu.student_name and bio.subject = 'BIO'
having (math_score ,eng_score, bio_score) != (-1, -1, -1)

需要注意on中的关联关系尽量都和第三方主表关联而不是和其他left join表关联，否则容易漏数据或出现笛卡尔积

查询结果除顺序外都与第一种方法相同

student_name	math_score	eng_score	bio_score
zhangsan	99	-1	-1
wangwu	92	90	-1
zhaoliu	82	89	93
lisi	-1	88	95