Mysql的joinon上的过滤和在where上过滤的区别
测试如下：
（1）创建两张表，并插入数据，sql语句如下：
a表：
CREATE TABLE `a` ( `id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT "",
`grade` int(11) DEFAULT NULL,
`dept` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8mb4
insert into `a` (`id`, `name`, `grade`, `dept`) values("1","LIJIE1","100","10");
insert into `a` (`id`, `name`, `grade`, `dept`) values("2","LIJIE2","90","20");
insert into `a` (`id`, `name`, `grade`, `dept`) values("3","LIJIE3","60","10");
insert into `a` (`id`, `name`, `grade`, `dept`) values("4","LIJIE4","80","10");
insert into `a` (`id`, `name`, `grade`, `dept`) values("5","LIJIE5","70","20");
b表：
CREATE TABLE `b` ( `id` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8mb4
insert into `b` (`id`, `NAME`) values("10","IT");
insert into `b` (`id`, `NAME`) values("20","IT2");
表数据显示：
a表：
id name grade dept
1 lijie1 100 10
2 lijie2 90 20
3 lijie3 60 10
4 lijie4 80 10
5 lijie5 70 20
b表：
id name
10 IT1
20 IT2
(2)left join 中on和where条件的对比
1.两张表join并且筛选分数大于等于80的，条件放在join on上面
select A.id,A.name,A.grade,A.dept,B.id,B.name
from
A left outer join B
on
A.dept = B.id
and
A.grade >=80
查询结果：
2.两张表join并且筛选分数大于等于80的，条件放在where上面
select A.id,A.name,A.grade,A.dept,B.id,B.name
from
A left outer join B
on
A.dept = B.id
where
A.grade >=80
查询结果：
- 结论
当把过滤条件写在left join on上面会让基表所有数据都能显示(不管是否在on条件中如何限定了基表，最多只对基表进行分组，而不是过滤，即不论on条件中如果限制基表，基表的记录都会显示，但是on中会过滤非基表的记录)不满足条件的右表会以null填充，当过滤条件写在where上只会让符合筛选条件的数据显示。
多表join时条件写在where和on的区别(总结篇)
在开发过程中经常遇到这种情况：
多表关联join时，到底限制的条件是写在where后面效率高还是写在on后面，又或者是先对表过滤使表的数据量减少，到底这三种效率哪种更高，看了一堆网上说的，都没有说到具体点上，现在对这三种情况专门做以下详细说明，你就会明白到底是怎么回事了
干货总结：(以下只适用于left join，right join，full join，不适合inner join)
1、left join where + 基表过滤条件：先对基表执行过滤，然后进行left join；
2、left join where + 被关联表过滤条件：先执行left join，然后执行关联表的过滤条件；
3、left join on+基表过滤条件：满足过滤的基表记录执行left join，不满足的基表记录后面补null，然后两集合并一起；
4、left join on+被关联表过滤条件：先执行被关联表的过滤条件，然后执行left join；
示例：
sql:
CREATE TABLE app_test_01 ( id INT AUTO_INCREMENT PRIMARY KEY,
city VARCHAR(50) DEFAULT ""
)
INSERT INTO app_test_01 VALUES
(NULL,"北京"),(NULL,"上海"),(NULL,"深圳"),(NULL,"上海"),(NULL,"湖南"),(NULL,"湖北"),(NULL,"武汉");
CREATE TABLE app_test_02 (
id INT AUTO_INCREMENT PRIMARY KEY,
stu VARCHAR(50) DEFAULT "",
city VARCHAR(50) DEFAULT ""
)
INSERT INTO app_test_02 VALUES
(NULL,"一","北京"),(NULL,"二","北京"),
(NULL,"三","上海"),(NULL,"四","北京"),
(NULL,"五","深圳"),(NULL,"六","深圳"),
(NULL,"七","湖南"),(NULL,"八","湖北");
on和where对比：
一、第一种情况：
（1）表条件写在where后面:
SELECT a.id,
b.stu
FROM
app_test_01 a
LEFT JOIN app_test_02 b
ON a.id = b.id
WHERE a.`city` = "深圳" ;
结果：
（2）先对基表进行过滤，然后关联
SELECT a.id,
b.stu
FROM
(SELECT
id
FROM
app_test_01
WHERE city = "深圳") a
LEFT JOIN app_test_02 b
ON a.id = b.id ;
结果：
这两种写法的执行顺序是一样的，都是先执行过滤，然后执行关联；所以运行效率是一样的！
二、第二种情况：
(1)where条件放基表
SELECT a.id,
b.stu
FROM
app_test_01 a
LEFT JOIN app_test_02 b
ON a.id = b.id
WHERE a.city = "深圳";
结果：
(2)where条件放关联表
SELECT a.id,
b.stu
FROM
app_test_01 a
LEFT JOIN app_test_02 b
ON a.id = b.id
WHERE b.city = "深圳" ;
结果：
第一种执行顺序：<1>先对a表进行where过滤，<2>再对过滤后的a表与b表进行关联
第二种执行顺序：<1>先a表和b表进行关联，<2>再对关联的结果执行where后面b表的条件
三、第三种情况
(1)第一种：基表过滤条件写where后面
SELECT a.id,
b.stu
FROM
app_test_01 a
LEFT JOIN app_test_02 b
ON a.id = b.id
WHERE a.city = "深圳" ;
结果：
(2)第二种：基表条件写on后面
SELECT a.id,
b.stu
FROM
app_test_01 a
LEFT JOIN app_test_02 b
ON a.id = b.id
AND a.city = "深圳" ;
结果：
（3）第三种情况：基表过滤条件和被关联表的过滤条件都写在on后面
SELECT a.id,
b.stu
FROM
app_test_01 a
LEFT JOIN app_test_02 b
ON a.id = b.id
AND a.city = "深圳"
AND b.`city` = "深圳"
结果：
第一种执行顺序是：
<1>先对a表执行过滤条件，
<2>然后过滤后的a表和b表进行关联；
第二种执行顺序：
<1>先使用a.city="深圳"的过滤条件将a表分为两部分，一部分满足过滤条件，一部分不满足过滤条件(即on后面基表的条件只是用来和被关联表进行关联)，
<2>对满足条件的与b表关联，不满足条件的后面字段补null，然后将满足和不满足的两部分集union起来成最后结果集；
第三种执行顺序：
<1>先对b表进行b.city=‘上海’条件对b表进行过滤，
<2>使用a.city="深圳"条件将a表分为满足和不满足条件的两部分集
<3>对满足集合与过滤后的b表进行关联，不满足集后面字段直接补null，最后将两个集合union起来成最终结果集
以上都是经过查看执行计划并且经过具体测试得出的结论，所以针对不同的业务场景可以选择不同的写法来提高执行效率。
我相信有了上面几种情况的掌握，无论在怎么添加条件，都能很快的判断出代码的执行顺序！！！
以上是 Mysql的joinon上的过滤和在where上过滤的区别 的全部内容， 来源链接： utcz.com/z/533844.html
