M6:连接查询，inner join、outer join、7种 join 理论

6.9 连接查询

6.9.1 内连接（相等连接）

定义: 要求构成连接的每个表的共有列匹配，假若不匹配则排除。简而言之，两张表进行连表查询，存在共有列的记录保存，不存在的排除。

关键字: inner join、join、where

语法：

select 字段|* from 表1 inner join 表2 on 表1.字段 = 表2.字段;

举个例子，假如存在如下两张表:

表1：专业信息表(subject)

id	name
1	软件工程
2	数字媒体技术
4	计算机科学与技术

表2：学生信息表(student)

student_id	student_name	subject_id
1	赵老大	2
2	钱老二	3
3	孙老三	1
4	李老四	2

当我们执行以下语句

select * from 
studnet as s 
inner join subject as sub
on s.subject_id=sub.id;
此语句中的inner可以省略，因为Join好像就是inner join的缩写。

上述语句可以改写：
select * from 
studnet as s,
subject as sub
where s.subject_id=sub.id;

执行结果如下：

student_id	student_name	subject_id	id(subject)	name
1	赵老大	2	2	数字媒体技术
3	孙老三	1	1	软件工程
4	李老四	2	3	数字媒体技术

结果分析：

​ 1、学生表 中的 钱老二 的subject_id是 3 ，但是专业信息表 中并没有编号为3的专业，所以，钱老二的记录被排除。

​ 2、同理可得，专业信息表 中的编号为 4 的 计算机科学技术 也没有出现在查询结果之中，也被派出了。

​ 3、select查询的时候，如果没有指定具体的字段，那么就会把所有的两张表信息的信息都会显示出来。并且显示结果的记录数，以主表的记录数为准。

注意，关于inner join、where、join都可以实现内连接,表面看来没什么区别，可能底层原理有所不同。where可以写多个条件，inner join也可以，虽然没有必要

6.9.2 外连接outer join

简介： outer join与inner join并不是很一样，inner join是直接的关键字，但是outer join却不是，它分为left outer join、right outer join、full outer join,并且outer不可以省略写。

left join: 以左表为主，除了内连接部分的数据，还包含左表不满足条件的数据，右表的空值用null表示。

语法：

select * from 表1 left [outer] join 表2 on 条件

right join： 以右表为主，除了内连接部分的数据，还包含右表不满足条件的数据，左表的空值用null表示。

语法：

select * from 表1 right [outer] join 表2 on 条件

例子：

表1：种族表（category）

id	values
1	人族
2	妖族
3	神族

表二：信息表

name	categoryID
牛魔王	2
刘彦昌	1
弥勒佛	4

执行左连接的结果：

name	categoryID	id	values
牛魔王	2	2	妖族
刘彦昌	1	1	人族
弥勒佛	3	null	null

执行右连接的结果：

name	categoryID	id	values
牛魔王	2	2	妖族
刘彦昌	1	1	人族
null	null	3	神族

6.9.3 7种join理论

​ 第一种：inner join

实现代码：

select * from A inner join B on A.key=B.key;

---

第二种：left join包含inner join

实现代码：

select * from A left join B on A.key=B.key;

第三种：left join排除inner join

实现代码：

select * from A left join B on A.key=B.key where B.key is null;

第四种：right join包含inner join

实现代码：

select * from A right join B on A.key=B.key;

第五种：right join排除inner join

实现代码：

select * from A right join B on A.key=B.key where A.key is null;

第六种：full outer join包含inner join

实现代码：

select * from A full outer join B on A.key=B.key;

第七种：full outer join排除inner join

实现代码：

select * from A full outer join B on A.key=B.key where A.key is null or B.key is null;

6.10 复合查询与子查询

6.10.1 复合查询

定义： 所谓复合查询，就是联表查询时，where多写几个条件。

例子：

select * from student as s,
sbuject as sub
where s.subject_id=sub.id and s.sex='男';

上述语句可以查询出所有的男学生的个人信息,以及专业信息。

---

2021.07.25