第05章 连表查询

前面章节中介绍的SQL查询语句都是针对一个表进行操作。若一个查询同时涉及两个或两个以上的表，即从两个或两个以上的表中检索数据，称之为连接查询。连接查询包括笛卡尔积，等值连接，非等值连接，自然连接，自身连接，外连接和复合条件连接。

1. 笛卡尔积：就是将两个表的记录相乘。

如果对两个表进行笛卡尔积运算。那么执行过程是：首先在表1中找到第一条记录，然后从头开始扫描表2，逐一将表2的记录与表1中第一条记录拼接起来。表2全部查询完后，在找表1中第二条记录，然后在从头开始扫描表2中所有记录，并连接到第二条记录上。重复以上操作，直到表1中所有记录处理完毕。

SELECT `student_info`.*, `class_info`.* FROM `student_info`, `class_info`;

这种将两个表相乘的方式，在日常查询中并不经常使用，需要在此基础上进一步处理。因为得到的记录基本上是比较错乱的，无意义的记录。例如，学生“小明”是位于“一班”，因此对于“小明”和“二班”的记录是错误的。

2. 等值连接和非等值连接：在笛卡尔积的基础上增加连接条件，去掉无意义的记录。等值连接和非等值连接的区别在于运算符不同。当使用“=“操作符的时候就是等值连接，当使用“>,>=,<,<=”的时候，就是非等值连接。等值连接使用的情况比较多。

select `class_info`.*, `student_info`.* from `class_info`, `student_info` where `class_info`.`class_id` = `student_info`.`class_id`;

查询班级表和学生表，他们通过公共字段class_id实现连接，这样就会查询出每一个学生对应的学生信息和班级信息。这样的结果集才是有意义的。

3. 自然连接：把等值连接中去掉重复属性就是自然连接。

select c.`class_id`, c.`class_name`, i.`stu_id`, i.`stu_name` from `class_info` as c, `student_info` as i where c.`class_id` = i.`class_id`;

我们可以在select中指定列的形式去掉上述连接查询中重复的class_id字段。

4. 复合条件查询：当进行连接查询的时候，除了设置连接公共属性之外，还可以使用where语句添加其他条件。

select c.`class_id`, c.`class_name`, i.`stu_id`, i.`stu_name` from `class_info` as c, `student_info` as i where c.`class_id` = i.`class_id` and i.`stu_sex` = 0;

就是查询所有“女”学生的信息。

5. 自身连接：就是一个表与自己进行连接。

假如我们有一个分类表，如下所示：

CREATE TABLE `cat_info` (
  `cat_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '分类ID',
  `cat_parent` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT '上级分类',
  `cat_name` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '分类名称',
  PRIMARY KEY (`cat_id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='分类信息表';

INSERT  INTO `cat_info`(`cat_id`,`cat_parent`,`cat_name`) VALUES 
(1,0,'服装'),(2,0,'家纺'),(3,1,'男装'),(4,1,'女装'),(5,2,'床单'),(6,2,'被罩');

我们的分类是“两层”结构。第一层是“服装”和“家纺”；而“服装”包括“男装”和“女装”两个子类，而“家纺”包括“床单”和“被罩”两个子类，这就是第二层。

select c1.*, c2.* from `cat_info` as c1, `cat_info` as c2 where c1.cat_parent = c2.cat_id;

我们可以查询第二层分类，以及它的所属父类，唯一不足的地方就是没有了第一层分类。

6. 外连接：在上面的等值连接过程中，只有满足连接条件的记录才能输出，我们称之为内连接。在实际应用中还会遇到另一种情况，就是a表显示全部数据，b表显示连接条件的记录，这种情况称之为左外连接；或者a表显示连接条件的记录，b表显示全部数据，这种情况称之为右外连接；或者a,b表都全部显示，这种情况称之为全外连接。MySQL中不支持全外连接FULL OUTER JOIN，但是可以通过使用left join union right join来实现。

insert into `class_info`(`class_name`, `add_time`) values ('三班', '2022-04-01 09:00:00');

添加class_id = 3的 三班 记录信息，但是学生表中没有任何三班的学生记录。

insert into `student_info`(`class_id`,`stu_name`,`stu_age`,`stu_sex`,`add_time`) values (4,'阿兰',14,1,'2011-03-01 09:00:00');

添加一个学生信息，班级信息为class_id，请注意并没有 班级=4的数据。

首先，我们进行一个连接查询：

select c.`class_id`, c.`class_name`, i.`stu_id`, i.`stu_name` from `class_info` as c, `student_info` as i where c.`class_id` = i.`class_id`;

没有阿兰的记录和3班记录，也就是说两表只会显示双方共有的数据。

上述查询也可以写成如下方式：

select c.`class_id`, c.`class_name`, i.`stu_id`, i.`stu_name` from `class_info` as c inner join `student_info` as i on c.`class_id` = i.`class_id`;

以上是“内连接”的写法，效果和上面的是一样的，只会显示两表共有的数据。From后面是第一张表，inner join后面是连接的第二种表，on后面是两表的连接字段。

接下来，我们再进行一个连表查询

select c.`class_id`, c.`class_name`, i.`stu_id`, i.`stu_name` from `class_info` as c left join `student_info` as i on c.`class_id` = i.`class_id`;

三班信息出现，对应的学生信息为NULL，因为三班没有学生信息。

很明显，这是左外连接，主表是“class_info”班级信息表，也就是from后面的表；副表是“student_info”学生信息表，也就是“left join”后面的表；on后面依然是两表关联字段。左外连接会显示主表是“class_info”班级信息表的所有记录信息，然后关联显示副表“student_info”学生信息。如果副表中没有对应的记录信息的话，就以NULL显示。

select c.`class_id`, c.`class_name`, i.`stu_id`, i.`stu_name` from `class_info` as c right join `student_info` as i on c.`class_id` = i.`class_id`;

阿兰信息出现，对应的班级信息为NULL，这就是“right join” 右外连接查询。如果我们对调class_info表和student_info表的位置的话，是不是等效于上面的左外连接呢？

mysql的join连接分为inner join内连接和外连接，而外连接又分为left join左外连接和right join右外连接。内外连接的区别在于两表数据匹配的角度不同。内连接要求左右表的数据等值，外连接则不严格要求数据等值，它允许一方可以存在null数据。也就是说，可以要求存在匹配不上的数据出现在结果集中。那么，未匹配成功的数据自然就是null了。请大家注意的是，不管内外连接，它都属于笛卡尔积基础上进行的条件操作。

关于连接查询的优化：当发生连接查询时，先在驱动表中开始寻找记录，当找到满足条件的记录，再去被驱动表中寻找满足关联条件on的记录。

当使用left join时，左表是驱动表，右表是被驱动表；当使用right join时，右表时驱动表，左表是被驱动表；当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。MySQL使用优化器决定哪个表是驱动表，哪个表是被驱动表。优化器一般会优先选择小表做驱动表。注意，不是按照表中的数量来决定大表小表，而是根据参与计算的表的数量来决定大表还是小表。也就是说，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。一般情况下，我们会为被驱动表关联字段加上索引，优化查询被驱动表的速度。连表查询的本质就是嵌套循环，当两个表时相当于双层循环，三个表时相当于三层循环，联表越多时间复杂度呈指数级别增长，联表的性能开销会非常大。因此，应该尽量选择驱动表为小表，用小表驱动大表，这样可以减少循环次数。

接下来，我们介绍嵌套查询，就是在form或where条件中使用select查询的结果集作为条件判断的依据。注意，这种查询不属于笛卡尔积基础。

1. 使用in的子查询

select * from `exam_info` where `sub_id` in (select `sub_id` from `subject_info` where `sub_name` = '数学');

我们要查询“数学”学科的考试成绩。对于子查询的过程是优先执行子查询，再执行父查询。以上子查询可以转化为连接查询的。如下所示：

select e.* from `exam_info` as e join `subject_info` as s on e.`sub_id` = s.`sub_id` where s.`sub_name` = '数学';

2. 使用比较运算符（=, !=, >, >=, <, <=）和限量词any和all，其中any表示任意一个值，all代表所有值。有时候，我们也可以使用 = 来代替in操作符。

select * from `exam_info` where `exam_score` > all (select `exam_score` from `exam_info` where `stu_id` in (7,8));

其实就是查询分数高于“小李”和“小张”的学生成绩。

3. 使用exists / not exists进行子查询。exists 关键字是判断是否存在的，存在则返回true，不存在则返回false， not exists则是不存在时返回true，存在返回false。

select * from `student_info` where exists (select `class_id` from `class_info` where `class_id` = 3);

如果存在class_id=3的数据的话，就查询所有学生记录。