侧边栏壁纸
  • 累计撰写 83 篇文章
  • 累计创建 87 个标签
  • 累计收到 9 条评论

目 录CONTENT

文章目录

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

汤圆学Java
2022-05-22 / 0 评论 / 0 点赞 / 79 阅读 / 1,589 字
温馨提示:
本文最后更新于 2022-05-22,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

前言

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

在联合查询时,MySQL会返回一个临时表:

  • 这个临时表就是通过on条件过滤返回的;

  • 然后再通过where条件,在临时表中筛选出符合条件的数据,返回给用户;

目录

  1. 联合查询的类型

  2. 左连接中on和where

  3. 内连接中on和where

正文

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

s_user表

image-20220506111935679

s_cat表

image-20220506111950654

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

查询结果如下:

image-20220506112523495

可以看到,左表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

查询结果如下:

image-20220506113320396

可以看到,只有一条数据,这就是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

查询结果如下:

image-20220506114008061

此时如果用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负责具体的过滤条件

0

评论区