一个sql问题深入理解左连接

数据准备

• 1.新建一个学生表t_student 如下 del_falg = ‘1’ ,’表示逻辑删除’
  

• 2.新建课程表 t_course
  

• 3.新建 学生选课表 r_course_student
  

• 4.上面的表结构并不复杂，需要关注的一个点是 4号学生已经被删除了，当然他的选课关系表也删除了

SELECT t.name 学生姓名  ,cs.cid 课程id , t.del_flag 学生删除标志,cs.del_flag 学生课程关系删除标志 FROM t_student  t
LEFT JOIN r_course_student cs ON t.id = cs.sid AND cs.del_flag = '0'
WHERE t.del_flag = '0' 

SELECT t.name 学生姓名  ,cs.cid 课程id , t.del_flag 学生删除标志,cs.del_flag 学生课程关系删除标志 FROM t_student  t
LEFT JOIN r_course_student cs ON t.id = cs.sid AND cs.del_flag = '0' AND t.del_flag = '0'

发现错误

可以发现，已经被删除的学生 和选课信息被查询出来了，为什么?
就是主表的条件写在on后面导致的。

左连接遵循的一个原则是，主表的信息一定会显示出来，而从表可能没有主表所对应的记录，相应的列就置为null ，第一条sql满足我们预期的结果，下面分析一下 两条sql的执行过程。

过程分析

• 第一条：

  • 1.t.id = cs.sid AND cs.del_flag = ‘0’通过这个条件生成中间临时表
  • 2.通过WHERE t.del_flag = ‘0’ 对临时表进行筛选，返回正确结果

• 第二条：

  • ON t.id = cs.sid AND cs.del_flag = ‘0’ AND t.del_flag = ‘0’生成 临时表，但是连接原则是主表的记录一定会被查询出来，所以不论on后面的条件对主表怎样过滤，都是无效的，无效的，无效的，所以造成了错误的结果。。

结论

• 1.使用连接时，on后面一定不要接过滤主表的条件，否则可能造成难以预料的后果，过滤主表的条件应该放在where后面，对生成的中间结果集进行过滤。当生成中间结果集后，就已经没有连接的概念了，这个中间数据就像是一张全新的表一样。
• 2.过滤从表的条件（也包括连接条件）可以尽量写在on后面，这样在生成中间表时，就少了很多不必要的数据、