连接查询中的 on 和 where条件a表 id size 1 10 2 20 3 30 4 11 b表 size na

-- auto-generated definition
create table a
(
    id   varchar(10) null,
    size varchar(10) null
);

-- auto-generated definition
create table b
(
    size varchar(10) null,
    name varchar(10) null
);

a表

id	size
1	10
2	20
3	30
4	11

b表

size	name
10	aaa
20	bbb
20	ccc
100	ddd

语句1

select *
from a
left join b on a.size = b.size

返回结果

id	a.siz	b.size	name
1	10	10	aaa
2	20	20	bbb
2	20	20	ccc
3	30
4	11

语句2

select *
from a
left join b on a.size = b.size
where b.name = 'aaa';

返回结果

id	a.siz	b.size	name
1	10	10	aaa

执行过程

生成中间表

此时获取a.size = b.size符合该条件的数据，存放在中间表中，也就是on条件

id	a.siz	b.size	name
1	10	10	aaa
2	20	20	bbb
2	20	20	ccc
3	30
4	11

where条件

对中间表进行过滤，将获取符合b.name = 'aaa'的数据

id	a.siz	b.size	name
1	10	10	aaa

语句3

select * 
from a 
left join b on a.size = b.size and b.name='aaa';

返回结果

id	a.siz	b.size	name
1	10	10	aaa
2	20
3	30
4	11

执行过程

生成中间表

a.size = b.size and b.name='aaa'条件不为真的也会返回左表的记录

id	a.siz	b.size	name
1	10	10	aaa
2	20
3	30
4	11

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用 left jion 时，on 和 where 条件的区别如下：

on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。
where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

inner join 中on 和 where

因为内连接生成的临时表中只会保留符合on条件的数据，所以数据在 on 和 where 条件中过滤没区别。

在使用 INNER JOIN 时会产生一个结果集，WHERE 条件在这个结果集中再根据条件进行过滤，如果把条件都放在 ON 中，在 INNER JOIN 的时候就进行过滤了，比如

SELECT *
         FROM A
      INNER JOIN B
          ON B.ID = A.ID
          AND B.State = 1
      INNER JOIN C
          ON B.ID = C.ID

在联查 B 表时，就把状态不等于 1 的忽略掉了，这样对于状态不等于 1 的就不需要去联查 C 表了

而

SELECT *
         FROM A
      INNER JOIN B
          ON B.ID = A.ID
      INNER JOIN C
          ON B.ID = C.ID
         WHERE B.State = 1

则不管 B 的状态是否满足，都去联查 C，最后再将 B 状态满足的查出来。

这样一分析，得出的结论就是 inner join on 比直接 where 的查询效率要高。

inner join on 后面的条件已经把结果过滤了一遍，而 where 则是把限制条件放到最后，执行最后一次查询前结果里值变多了，查询起来变慢了，效率自然变低了。

然而，对于一般的内联接来说，就是没用例如上面的 b.state=1 这类的多一层限制，它和 where 的效率是一样的。而且 where 在写法上简单，因此对单表的查询一般都用 where 即可。