第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)

【版权说明:仅允许非商业转载且请注明出处:Mac私塾 网址:http://macsishu.com】

发表评论

邮箱地址不会被公开。

Captcha Code