MySQL的联合查询中on和where的区别

前言

on和where都可以用来过滤数据，但是作用的场景不一样：

在联合查询时，MySQL会返回一个临时表：

下面的讨论都基于以下两个表s_user, s_cat：

s_user表

s_cat表

假设想要通过左连接查询用户以及宠物信息，那么SQL语句如下：

SELECT * FROM s_user t1 LEFT JOIN s_cat t2 
	ON t1.id = t2.user_id

查询结果如下：

可以看到，左表s_user中的数据都有返回，此时如果没有在右表s_cat中找到符合条件（on筛选条件）的数据，就会以null填充；

这里on的作用就是：在左右表中，找到符合条件的数据，然后返回到临时表，如果右表没匹配的数据，则以null填充；

下面我们加一个where条件，如下所示：

SELECT * FROM s_user t1 LEFT JOIN s_cat t2 
	ON t1.id = t2.user_id
	WHERE t1.id = 1

查询结果如下：

可以看到，只有一条数据，这就是where的功劳；

这里where的作用就是：在返回的临时表中，筛选出t1.id=1的数据；

下面简单总结下on和where的区别：

可以说：在左连接中，on和where互为补充；

左连接中，on条件必须存在，否则会报语法错误；右连接同理；

假设要查询有宠物的用户信息，那么就需要用到内连接：内连接返回的是左右表的交集，即如果用户没有宠物信息，那么该用户信息也不会返回；

SQL语句如下所示：JOIN 是 INNER JOIN 的缩写

SELECT * FROM s_user t1 JOIN s_cat t2 
	ON t1.id = t2.user_id

查询结果如下：

此时如果用where替换on，也是一样的效果：

SELECT * FROM s_user t1 JOIN s_cat t2 
	WHERE t1.id = t2.user_id

这是因为内连接查询时，返回的是左右表数据的交集，而不是单纯地以左表或右表数据为准；

此时就不存在左/右表数据为null的情况，所以on和where的作用是类似的，都可以用来筛选返回数据

只是作用阶段不一样，on是作为在临时表的生成条件，而where是作为返回数据的生成条件

而在前面的左连接查询中，因为以左表为主，所以on条件中的真假都不会影响左表中数据的返回；

但是where条件会影响左表数据的返回，所以左连接中的on和where的效果不一样；

可以说：在内连接中，on和where可以互为替换

联合查询时，会根据on条件，返回一个临时表
左连接中，on和where互为补充
- on用来从右表中找出符合on条件的数据，并返回到临时表，不符合on条件的，就以null填充；（即不管on条件真假，左表的数据都会返回到临时表）
- where用来从临时表中找出符合where条件的数据，并返回给用户；
内连接中，on和where可以互为替换；不过为了保证SQL语句的可读性，还是建议on和where搭配使用，即on负责联合条件，where负责具体的过滤条件