第18课：复杂的连接表

本节要点

连接表的执行步骤
LEFT JOIN与INNER JOIN
WHERE与ON
多表连接
非唯一键连接

表连接的执行步骤

上节课我们讲解了多种表连接的类型。看起来可能比较复杂，其实如果掌握了表连接的执行步骤，按照执行步骤一步步来理解，难度还是不大的。

对于下面这个SQL：

SELECT *
FROM student a
LEFT JOIN teacher b
ON a.teacher_id = b.teacher_id
WHERE a.score > 90;

既有连接条件，又有WHERE过滤条件，那么它的执行步骤是怎么样的呢？

其实，所有的表连接，都是按顺序执行下面这四个步骤：

第一步：参与连接的两个表做笛卡尔积；
第二步：根据ON后的连接条件筛选笛卡尔积的结果；
第三步：补充左表（LEFT JOIN）或右表（RIGHT JOIN）不满足连接条件的数据（INNER JOIN内关联时无此步骤） ；
第四步：根据WHERE后的过滤条件筛选第三步的结果；

注意，这里在第三步，左外关联和右外关联，与内关联是有些区别的。

LEFT JOIN与INNER JOIN

先来看下面两个SQL：

SELECT *
FROM student a
INNER JOIN teacher b
ON a.teacher_id = b.teacher_id;
SELECT *
FROM student a
LEFT JOIN teacher b
ON a.teacher_id = b.teacher_id;

思考一个问题：上面这两个SQL返回的记录数是一样的吗？

我们可以根据前面讲的步骤，一步步来计算这两个SQL最后返回的记录数。

最后可以发现，第二个SQL返回的记录数会多一些。为什么呢？

其实，对于两个表的笛卡尔积的结果，我们可以分成三部分来看：

第一部分：满足关联条件的记录；
第二部分：左表中不满足关联条件的记录；
第三部分：右表中不满足关联条件的记录；

那么，对于内关联来说，返回的就是第一部分的数据；

对于左外关联来说，返回的就是第一部分加上第二部分的数据；

对于右外关联来说，返回的就是第一部分加上第三部分的数据；

而对于全外关联，返回的是所有这三部分数据；

所以，第一个SQL是内关联，它就比第二个SQL左外关联，少返回了第二部分的数据。

理解内关联与外关联的区别，对于SQL编程来说，非常重要！

WHERE与ON

再来看两个SQL：

SELECT *
FROM student a
INNER JOIN teacher b
ON a.teacher_id = b.teacher_id
AND a.score>90;
SELECT *
FROM student a
INNER JOIN teacher b
ON a.teacher_id = b.teacher_id
WHERE a.score>90;

这两个SQL都是内关联，区别在于，a.score > 90这个过滤条件的位置。一个是在ON的后面，一个是在WHERE的后面。

这两种写法，都可以过滤数据，但它们返回的结果是一样的吗？

这里我们仍然使用前面讲的四个步骤，一步步去计算这两个SQL的执行结果。

最后发现，它们返回的结果是一样的。也就是说，对于INNER JOIN来说，过滤条件不管是写在ON后面，还是写在WHERE后面，效果是一样的。

可我们再来看另外两个SQL：

SELECT *
FROM student a
LEFT JOIN teacher b
ON a.teacher_id = b.teacher_id
AND a.score>90;
SELECT *
FROM student a
LEFT JOIN teacher b
ON a.teacher_id = b.teacher_id
WHERE a.score>90;

这两个SQL与前面的SQL基本一样，区别只是在于，将INNER JOIN换成了LEFT JOIN，也就是将内关联，换成了左外关联。

那么再思考一下，这两个SQL返回的结果一样吗？

再次套用上面的四个步骤，会发现，这时候就不一样了。

因为，将过滤条件写在ON后面，它实际是在第二步起到的作用。而将过滤条件写在WHERE后面，它实际是在第四步起到的作用。

这就导致了下面的SQL会比上面的SQL被多过滤了一些数据。

多表连接

考虑下面这个查询需求：

（1）、如何同时查出学生编号、学生姓名、老师编号、老师姓名、班级编号、班级名称：

因为学生相关的信息是在学生表中，老师相关的信息在老师表中，而班级相关的信息在班级表中。如果我们想同时从这三个表中获取数据，那么就需要这三个表同时关联。

SELECT a.student_id,a.student_name,
    a.teacher_id,b.teacher_name,
    a.class_id,c.class_name
FROM student a
LEFT JOIN teacher b
ON a.teacher_id = b.teacher_id
LEFT JOIN class c
ON a.class_id = c.class_id;

虽然是多个表同时关联，其实它的结果也是依次执行出来的。

第一步：a表与b表做LEFT JOIN；
第二步：a表与b表LEFT JOIN的结果，再与c表做LEFT JOIN；

所以说，多表关联的时候，也不要怕。也就是多做了几次两张表的关联而已。

非唯一键连接

对于下面这个SQL：

SELECT a.student_id,a.student_name,
    a.teacher_id,b.teacher_name
FROM student a
LEFT JOIN teacher b
ON a.teacher_id = b.teacher_id;

学生表与老师表使用老师编号关联，因为老师表中的老师编号不会重复，所以经过上面介绍的四个步骤执行完成之后，结果的数据量是与学生表中的数据量是一致的。

但是，如果老师表中的数据出错了，导致老师编号有重复。这时候，这个SQL语句的返回的数据量，还是与学生表中的数据量是一致的吗？

大家可以再次使用上面的四个步骤执行一下这个SQL。

最后就会发现，老师表中的重复数据，会导致最后执行的结果也会有数据重复，从而导致这个SQL语句返回的数据量，比学生表中的数据量多。

这可能就不是我们想要的结果。

所以，表关联时，首先需要确认的一点，就是关联条件字段在关联表中是不是唯一。在绝大多数的情况下，关联条件字段都是关联表中的主键或能唯一确定一条记录的字段。如果不是，很可能是SQL的关联条件写的不对，需要仔细确认是否与需求相符。

课后习题：

1、对于第6课课后习题中的论坛注册用户表、第15课课后习题中的文章表、用户访问的文章记录表，请编写SQL完成以下查询。

（1）、请查询出今天所有用户访问的文章编号、文章标题、文章作者用户编号、文章作者用户名，并且访问日期、访问时间升序排序。

（2）、每个文章的总访问次数以及今天的访问次数、上月末的访问次数、上上月末的访问次数。

（3）、查询今天的访问记录，同时查询出文章标题、文章作者、访问用户名、访问用户手机号。

（4）、使用连接的方式，查询没有访问任何文章的用户。

（5）、查询哪个省市区的作者发表的文章最多。

（6）、查询从月初到现在，每个用户的总访问次数。

2、对于学生表中的所有同学的成绩，如何查出比该班平均成绩高的学生信息？（使用表关联实现）（题目来源：@Yeeshine）

picture loss