12、连接查询

1、定义

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

2、内连接

• 先确定主表，仍然使用FROM 的语法；
• 再确定需要连接的表，使用INNER JOIN 的语法， 在使用内连接时INNER可以省略，直接写成JOIN；
• 然后确定连接条件，使用ON ，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
• 可选：加上WHERE子句、ORDER BY等子句。

选出所有学生，同时返回班级名称：

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score 
FROM students s 
INNER JOIN classes c 
ON s.class_id = c.id;

id	name	class_id	class_name	gender	score
1	小明	1	一班	M	90
2	小红	1	一班	F	95
3	小军	1	一班	M	88
4	小米	1	一班	F	73
5	小白	2	二班	F	81
6	小兵	2	二班	M	55
7	小林	2	二班	M	85
8	小新	3	三班	F	91
9	小王	3	三班	M	89
10	小丽	3	三班	F	88

假如多表呢？以下语法，如果后面还有多个表，JOIN ON ，一直加就行了

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score 
FROM students s 
INNER JOIN classes c 
ON s.class_id = c.id
INNER JOIN ... ON ... 
INNER JOIN ... ON ...

 

3、外连接

外连接包括左连接（LEFT OUTER JOIN）、右连接（RIGHT OUTER JOIN）和全连接（FULL OUTER JOIN）

在开始之前我们先给students表加一行数据

INSERT INTO students (class_id, name, gender, score) values (5, '新生', 'M', 88);

那么现在students表应该是这样：

id	class_id	name	gender	score
1	1	小明	M	90
2	1	小红	F	95
3	1	小军	M	88
4	1	小米	F	73
5	2	小白	F	81
6	2	小兵	M	55
7	2	小林	M	85
8	3	小新	F	91
9	3	小王	M	89
10	3	小丽	F	88
11	5	新生	M	88

classes表还是这样：

id	name
1	一班
2	二班
3	三班
4	四班

 

3.1、左连接

定义：是以左表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分。左连接全称为左外连接，是外连接的一种。

LEFT OUTER JOIN则返回左表都存在的行。我们已经给students表增加一行，并添加class_id=5，由于classes表并不存在id=5的行，所以，LEFT OUTER JOIN的结果会增加一行，对应的class_name是NULL：

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score 
FROM students s 
LEFT OUTER JOIN classes c 
ON s.class_id = c.id;

结果：

id	name	class_id	class_name	gender	score
1	小明	1	一班	M	90
2	小红	1	一班	F	95
3	小军	1	一班	M	88
4	小米	1	一班	F	73
5	小白	2	二班	F	81
6	小兵	2	二班	M	55
7	小林	2	二班	M	85
8	小新	3	三班	F	91
9	小王	3	三班	M	89
10	小丽	3	三班	F	88
11	新生	5	NULL	M	88

解析：

• students表是左表，那么可以看到students里面的所有相关数据都出来了的。
• 我们知道students里面的class_id对应classes里面的id，这个“新生”的class_id是5，在class表里面没有id为5的，也就是id为5时，在classes表里面找不到对应的class_name，那怎么办？让它变成NULL

3.2、右连接

定义：是以右表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将右表所有的查询信息列出，而左表只列出ON后条件与右表满足的部分。右连接全称为右外连接，是外连接的一种。

RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段：

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score 
FROM students s 
RIGHT OUTER JOIN classes c 
ON s.class_id = c.id;

结果：

id	name	class_id	class_name	gender	score
1	小明	1	一班	M	90
2	小红	1	一班	F	95
3	小军	1	一班	M	88
4	小米	1	一班	F	73
5	小白	2	二班	F	81
6	小兵	2	二班	M	55
7	小林	2	二班	M	85
8	小新	3	三班	F	91
9	小王	3	三班	M	89
10	小丽	3	三班	F	88
NULL	NULL	NULL	四班	NULL	NULL

解析：

• classes是右表，那么可以看到classes里面的所有相关数据都出来了的。
• 在classes表里面，在罗列classes的时候，它里面有四班，但是students里面并没有四班的人，那么多余的列全部为NULL

3.3、全连接

定义：可以理解为全连接就是左连接、右连接结果集合的并集

最后，我们使用FULL OUTER JOIN，它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL：

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score 
FROM students s 
FULL OUTER JOIN classes c 
ON s.class_id = c.id;

结果：

id	name	class_id	class_name	gender	score
1	小明	1	一班	M	90
2	小红	1	一班	F	95
3	小军	1	一班	M	88
4	小米	1	一班	F	73
5	小白	2	二班	F	81
6	小兵	2	二班	M	55
7	小林	2	二班	M	85
8	小新	3	三班	F	91
9	小王	3	三班	M	89
10	小丽	3	三班	F	88
11	新生	5	NULL	M	88
NULL	NULL	NULL	四班	NULL	NULL

 

4、小结

• JOIN查询需要先确定主表，然后把另一个表的数据“附加”到结果集上；
• INNER JOIN是最常用的一种JOIN查询（内连接），与此同时还有OUTER JOIN（外连接），外连接包括左连接（LEFT OUTER JOIN）、右连接（RIGHT OUTER JOIN）和全连接（FULL OUTER JOIN），内连接可以简写为 JOIN，外连接可以简写为LEFT JOIN、RIGHT JOIN、FULL JOIN
• JOIN查询仍然可以使用WHERE条件和ORDER BY排序
• 下图可简单说明连接查询的定义