Mysql中on与where关系

Question

目前有三个表：student 学生信息表 ，course选课课程信息表，exame选课情况信息表。其中 stduent 和course 是exame子表。

我们先用外连接看一下标的基本信息，其中1-4都有选课。5号只插入了学生表的信息。

mysql> select s.*,e.* from student s left join exame e on s.uid = e.uid;
+-----+--------+-----+--------+------+------+------------+-------+
| uid | name   | age | sex    | uid  | cid  | time       | score |
+-----+--------+-----+--------+------+------+------------+-------+
|   1 | Alice  |  20 | female |    1 |    1 | 2023-08-25 |    85 |
|   1 | Alice  |  20 | female |    1 |    2 | 2023-08-26 |    92 |
|   2 | Bob    |  22 | male   |    2 |    1 | 2023-08-25 |    78 |
|   2 | Bob    |  22 | male   |    2 |    3 | 2023-08-27 |    88 |
|   3 | Claire |  21 | female |    3 |    2 | 2023-08-26 |    95 |
|   3 | Claire |  21 | female |    3 |    4 | 2023-08-28 |    90 |
|   4 | David  |  23 | male   |    4 |    1 | 2023-08-25 |    82 |
|   4 | David  |  23 | male   |    4 |    4 | 2023-08-28 |    91 |
|   5 | Eeboy  |  25 | male   | NULL | NULL | NULL       |  NULL |
+-----+--------+-----+--------+------+------+------------+-------+
9 rows in set (0.00 sec)

#我们对e.uid进行筛选一下。

mysql> select s.*,e.* from student s left join exame e on s.uid = e.uid and e.uid is NULL;
+-----+--------+-----+--------+------+------+------+-------+
| uid | name   | age | sex    | uid  | cid  | time | score |
+-----+--------+-----+--------+------+------+------+-------+
|   1 | Alice  |  20 | female | NULL | NULL | NULL |  NULL |
|   2 | Bob    |  22 | male   | NULL | NULL | NULL |  NULL |
|   3 | Claire |  21 | female | NULL | NULL | NULL |  NULL |
|   4 | David  |  23 | male   | NULL | NULL | NULL |  NULL |
|   5 | Eeboy  |  25 | male   | NULL | NULL | NULL |  NULL |
+-----+--------+-----+--------+------+------+------+-------+
5 rows in set (0.01 sec)

#引出了问题 为什么在exame信息成null了？
#换成where就好了。
mysql> select s.*,e.* from student s left join exame e on s.uid = e.uid where e.uid is NULL;
+-----+-------+-----+------+------+------+------+-------+
| uid | name  | age | sex  | uid  | cid  | time | score |
+-----+-------+-----+------+------+------+------+-------+
|   5 | Eeboy |  25 | male | NULL | NULL | NULL |  NULL |
+-----+-------+-----+------+------+------+------+-------+
1 row in set (0.00 sec)

Answer 1

区别在内连接时，会先把左边/右边的所有记录先记录下来，然后再根据on找到另一张表的所有记录，若不满足所有的on，那么直接赋值为NULL。而where是在两张表链接后进行筛选，若不匹配直接过滤掉。