本节要点
连接表的执行步骤
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)