SQL中WHERE和ON的区别

WHERE和ON的区别

在探讨WHERE和ON这两个关键字的区别之前，我们先来复习一下连接查询。

SQL JOIN
SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

JOIN方式概述：

先定义两个简单的表格
表A tb_student

student_id	student_name	class_id
1	张三	1
2	李四	1
3	王二	2
4	赵五	4

表B tb_class

class_id	class_name
1	一年一班
2	一年二班
3	一年三班

1.内连接 inner join（等同于join）,如果表中有至少一个匹配，则返回行

执行SQL：SELECT a.student_name,b.class_name FROM tb_student a
INNER JOIN tb_class b ON a.class_id = b.class_id;

返回结果如下

student_name	class_name
张三	一年一班
李四	一年一班
王二	一年二班

2.外连接 outer join 主要分为三种

1）全外连接 FULL OUTER JOIN ，不管匹配与否，都会返回所有的行，MySQL不支持全外连接，这里不多赘述

2）左外连接 LEFT OUTER JOIN （等同于 LEFT JOIN）, 从左表返回所有的行，即使右表中没有匹配。如果右表中没有匹配，则结果为 NULL。

执行SQL：SELECT a.student_name,b.class_name FROM tb_student a LEFT OUTER JOIN tb_class b ON a.class_id = b.class_id

返回结果如下

student_name	class_name
张三	一年一班
李四	一年一班
王二	一年二班
赵五	NULL

3）右外连接 RIGHT OUTER JOIN （等同于 RIGHT JOIN）, 从右表返回所有的行，即使左表中没有匹配。如果左表中没有匹配，则结果为 NULL。

执行SQL：SELECT a.student_name,b.class_name FROM tb_student a RIGHT OUTER JOIN tb_class b ON a.class_id = b.class_id

返回结果如下

student_name	class_name
张三	一年一班
李四	一年一班
王二	一年二班
NULL	一年三班

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。复习完了连接查询，我们现在来看一下关键字WHERE和ON的区别，这里以LEFT JOIN举例。

1. on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录（注意这里是拿的LEFT JOIN举例）。
2. where 条件是在临时表生成好后，再对临时表进行过滤的条件，条件不为真的就全部过滤掉。

依然用上面的那两个简单的表来举例

表A tb_student

student_id	student_name	class_id
1	张三	1
2	李四	1
3	王二	2
4	赵五	4

表B tb_class

class_id	class_name
1	一年一班
2	一年二班
3	一年三班

我们来分析两条SQL：

1 . SELECT a.student_name,b.class_name FROM tb_student a LEFT JOIN tb_class b ON a.class_id = b.class_id AND b.class_name = '一年一班’
2.SELECT a.student_name,b.class_name FROM tb_student a LEFT JOIN tb_class b ON a.class_id = b.class_id WHERE b.class_name = '一年一班’

第一条SQL执行的过程：

生成临时表, ON 条件为 a.class_id = b.class_id AND b.class_name = ‘一年一班’,
即使条件不为True，也会返回左表记录

student_name	class_name
张三	一年一班
李四	一年一班
王二	NULL
赵五	NULL

第二条SQL执行的过程：

1.生成临时表，ON 条件为 a.class_id = b.class_id，即使条件不为True，也会返回左表记录

student_name	class_name
张三	一年一班
李四	一年一班
王二	一年二班
赵五	NULL

2.再对临时表进行过滤(WHERE语义), WHERE 条件为 b.class_name = ‘一年一班’,所有不为True的全部过滤掉。

student_name	class_name
张三	一年一班
李四	一年一班

其实以上结果的关键原因就是 left join、right join、full join 的特殊性，不管 on 上的条件是否为真都会返回 left 或 right 表中的记录，full 则具有 left 和 right 的特性的并集。 而 inner jion 没这个特殊性，则条件放在 on 中和 where 中，返回的结果集是相同的。