MySQL 筛选条件放 ON后 vs 放 WHERE 后的区别解析_Mysql

今天我们来讲讲数据库筛选条件放 on 后和放 where 后的区别。

on 决定如何 "连接" 表，where 决定连接后 "显示" 哪些行。这个根本区别导致了在 left join / right join 外连接中，条件放置位置会产生巨大影响；而在 inner join 中，效果通常等价。

on 条件匹配 被驱动表 的行，生成 "临时关联结果集"。left join 会保留 驱动表 所有行，匹配不上的 被驱动表 字段填充为 null。

where 会对 "临时关联结果集" 进行条件过滤，删除不满足的行。

接下来我们搞两张测试表，一目了然。

-- 用户表（驱动表，左表）
create table `ysjz_user` (
  `id` int(11) not null auto_increment,
  `name` varchar(20) default null,
  `age` int(11) default null,
  primary key (`id`)
) engine=innodb default charset=utf8mb4;
insert into `ysjz_user` values (1,'张三',18),(2,'李四',25),(3,'王五',30);
-- 订单表（被驱动表，右表）
create table `ysjz_order` (
  `id` int(11) not null auto_increment,
  `user_id` int(11) default null,
  `amount` decimal(10,2) default null,
  primary key (`id`)
) engine=innodb default charset=utf8mb4;
insert into `ysjz_order` values (1,1,100),(2,2,200),(3,2,300),(4,4,500); -- 注：user_id=4无对应用户

场景一：使用 inner join，查询年龄 > 20 的用户及其订单。

写法1：条件放 on 后

select u.*, o.* 
from `ysjz_user` u
inner join `ysjz_order` o on u.id = o.user_id and u.age > 20;

写法2：条件放 where 后

select u.*, o.* 
from `ysjz_user` u
inner join `ysjz_order` o on u.id = o.user_id 
where u.age > 20;

两种写法的 结果一致。写法1 更高效，因少关联了年龄 ≤ 20 的用户。

场景二：使用 left join，保留所有用户，同时显示年龄 > 20 的用户及其订单（≤ 20 的用户订单显示为 null）。

写法1：条件放 on 后（符合要求）

select u.*, o.* 
from `ysjz_user` u
left join `ysjz_order` o on u.id = o.user_id and u.age > 20;

写法2：条件放 where 后（跟要求不符）

select u.*, o.* 
from `ysjz_user` u
left join `ysjz_order` o on u.id = o.user_id
where u.age > 20;

写法2 将张三过滤了，并没有保留所有用户。

场景三：使用 left join，保留所有用户，同时显示订单金额 > 200 的订单（无符合条件订单的用户填充为 null）。

写法1：条件放 on 后（符合要求）

select u.*, o.* 
from `ysjz_user` u
left join `ysjz_order` o on u.id = o.user_id and o.amount > 200;

写法2：条件放 where 后（跟要求不符）

select u.*, o.* 
from `ysjz_user` u
left join `ysjz_order` o on u.id = o.user_id 
where o.amount > 200;

写法2 过滤了无符合条件订单的用户。

场景二和场景三其实相差不大，只是条件作用的表不一样。

on 后面优先放 "表之间的关联键"（如 u.id = o.user_id），非关联的筛选条件（如 u.age > 20）是否放 on 后，取决于是否要保留驱动表的行。

总结：on 管关联，where 管过滤；left join 用 on 保行，inner join 用 on 提效。

别人的嘴你堵不住，但自己的心却任由自己掌控。-- 烟沙九洲

到此这篇关于mysql 筛选条件放 on后 vs 放 where 后的区别解析的文章就介绍到这了,更多相关mysql on where筛选内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

MySQL 筛选条件放 ON后 vs 放 WHERE 后的区别解析

推荐阅读

深入理解MySQL中查询表的历史操作记录

MySQL进行查询结果求和的五种方法详解

Mysql的MHA实战指南

确认MySQL备份权限最小化的方法

MySQL 中只统计周一到周五的到访数据(案例演示)

MySQL中COUNT函数的使用小结

猜你喜欢

发表评论