mysql left join on过滤_Mysql 的join on上的过滤和在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起来成最终结果集

以上都是经过查看执行计划并且经过具体测试得出的结论，所以针对不同的业务场景可以选择不同的写法来提高执行效率。

我相信有了上面几种情况的掌握，无论在怎么添加条件，都能很快的判断出代码的执行顺序！！！