前言
on和where都可以用来过滤数据,但是作用的场景不一样:
在联合查询时,MySQL会返回一个临时表:
-
这个临时表就是通过on条件过滤返回的;
-
然后再通过where条件,在临时表中筛选出符合条件的数据,返回给用户;
目录
-
联合查询的类型
-
左连接中on和where
-
内连接中on和where
正文
下面的讨论都基于以下两个表s_user, s_cat:
1. 联合查询的类型
- left join: 左连接,以左边的表为主,此时左表中符合where条件的数据会全部返回,不符合on条件的右表的数据会用null填充
- right join: 右连接,以右边的表为主,此时右表中符合where条件的数据会全部返回,不符合on条件的左表的数据会用null填充
- inner join: 内连接,以两边的表数据的交集为主,此时左右表中符合on条件和where条件的数据会全部返回
2. 左连接中on和where
假设想要通过左连接查询用户以及宠物信息,那么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用来从右表中找出符合on条件的数据,并返回到临时表,不符合on条件的,就以null填充;
- where用来筛选临时表中的数据,返回给用户;
可以说:在左连接中,on和where互为补充;
左连接中,on条件必须存在,否则会报语法错误;右连接同理;
3. 内连接中on和where
假设要查询有宠物的用户信息,那么就需要用到内连接:内连接返回的是左右表的交集,即如果用户没有宠物信息,那么该用户信息也不会返回;
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负责具体的过滤条件
评论区