SQL 左连接中on后面加where和加and的区别

阅读目录

1、首先来谈谈什么是left join

2、再来谈谈on后面使用and和where的区别

3、总结

---

今天的最佳表现应该作为明天的最低要求

1、首先来谈谈什么是left join

     left join即左连接，比如表1左连接表2，以左表为主，表示以表1为主，关联上表2的数据，查询出来的结果显示左表的所有数据，然后右边显示的是和左表有交集的数据

     比如下面有学生表(student)和教师表(teacher)，将教师表的主键作为学生表的外键，此外建可为空，即有的学生可能暂时还没有分配到教师。表结构如下所示

     学生表(student)

     

     教师表(teacher)

     

     现在假设需要查询出所有学生的aid、name以及教师的name,有没有分配教师的学生都需要查询出来，以便给没有分配教师的学生进行教师分配，这个SQL该怎么写呢？如下所示

SELECT s.aid,s.name, t.name teacher_name
FROM student s
LEFT JOIN teacher t ON s.teacher_aid = t.aid

    这样就查询出了所有学生的信息，包括未分配教师的学生，student表作为左表，teacher作为右表，查询出student表的所有数据以及和teacher表有交集的数据，这就是左连接(left join)

---

2、再来谈谈on后面使用and和where的区别

    假设现在student表里面的数据如下

    

    现在有一个接口的要求是查询出目前在读(is_read  = 1)的学生的aid、name和教师的name，这个SQL该怎么写呢？如下所示

    (1)、第一种情况，在on后面使用and，SQL 如下所示

SELECT s.aid,s.name, t.name teacher_name
FROM student s
LEFT JOIN teacher t ON s.teacher_aid = t.aid
AND s.is_read = 1

    查询结果是这样的

    

     很显然根据上面student的数据来看，这个SQL语句查询的结果中包含了is_read = 0的数据，而接口要求的是查询出is_read  = 1的数据，这个SQL无法查询出正确的数据。

    (2)、第二种情况，在on后面使用where，SQL 如下所示

SELECT s.aid,s.name, t.name teacher_name
FROM student s
LEFT JOIN teacher t ON s.teacher_aid = t.aid
WHERE s.is_read = 1

   查询结果是这样的

   

   很显然根据上面的student表的数据来看，这个SQL查询出来的数据正是is_read = 1的数据，完全符合接口的要求，所以这个接口查询的数据是正确的。

---

3、总结

  根据上面的例子相信有的人已经猜出了这两者的区别，我这里做个总结

  在on后面使用and

•   如果and语句是对左表进行过滤，那么无论这个过滤条件是真是假都不起作用，左表的记录会全部返回
•   如果and是对右表进行过滤，那么左表会与过滤后的右表进行左连接，左表数据依旧全部返回

  在on后面使用where

•   where是对连接后的结果集(临时表)进行过滤的，此时的left join已经不起作用，条件不为真的数据就全部过滤掉