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

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

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。临时表中数据条件不为真的就全部过滤掉。之后再返回对临时表中过滤后的结果

假设现在有两张表：
表1 tab1：

| id | size |
|----|------|
| 1  | 10   |
| 2  | 20   |
| 3  | 30   |

表2 tab2：

| size | name |
|------|------|
| 10   | AAA  |
| 20   | BBB  |
| 20   | CCC  |

用如下两条SQL语句进行查询:

--1、left join on where
select * from tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’

--2、left join on and
select * from tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

第一条SQL语句left join on where的执行过程为：
1、left join on根据on条件: tab1.size = tab2.size得到临时表

|  tab1.id |  tab1.size |  tab2.size |  tab2.name |
|----------|------------|------------|------------|
| 1        | 10         | 10         | AAA        |
| 2        | 20         | 20         | BBB        |
| 2        | 20         | 20         | CCC        |
| 3        | 30         | NULL       | NULL       |

2、再通过where 条件：tab2.name=’AAA’对中间表过滤

|  tab1.id |  tab1.size |  tab2.size |  tab2.name |
|----------|------------|------------|------------|
| 1        | 10         | 10         | AAA        |

第二条SQL语句left join on and的执行过程为：：

1、left join on根据on条件:tab1.size = tab2.size and tab2.name=’AAA’得到临时表

|  tab1.id |  tab1.size |  tab2.size |  tab2.name |
|----------|------------|------------|------------|
| 1        | 10         | 10         | AAA        |
| 2        | 20         | NULL       | NULL       |
| 3        | 30         | NULL       | NULL       |

2.结果中返回上一步得到的临时表

其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。
所以实际的过程中一定要注意left join on and是无法获取只含有符合条件的结果集的。我们可以看下面这个例子：

有如下两张表，执行语句：select count(s_id) from students left join information on s_id=id and province in ('山东','湖南')的结果集的行数为？
students表：

|  s_id  |  name  |
|--------| ------ |
| 123585 | 张小飞 |
| 123586 | 程小英 |
| 123587 | 吴天   |
| 123588 | 张仙   |
| 123589 | 刘成   |
| 123590 | 何梅   |
| 123591 | 王程杰 |
| 123592 | 李成浩 |
| 123593 | 李静   |
| 123594 | 刘敏敏 |

information表：

| id     | name | 省份 | 年龄 | 性别 |
|--------|------|----|----|----|
| 123585 | 张小飞  | 湖南 | 22 | 男  |
| 123586 | 程小英  | 河北 | 20 | 女  |
| 123587 | 吴天   | 河南 | 22 | 男  |
| 123589 | 刘成   | 河南 | 21 | 男  |
| 123590 | 何梅   | 河南 | 22 | 女  |
| 123591 | 王程杰  | 苏州 | 20 | 男  |
| 123593 | 李静   | 湖南 | 23 | 女  |
| 123594 | 刘敏敏  | 湖南 | 20 | 女  |
| 123595 | 王志峰  | 山东 | 21 | 男  |
| 123596 | 赵罗生  | 江西 | 21 | 男  |
| 123597 | 李静   | 山东 | 20 | 女  |
| 123598 | 程丽   | 湖南 | 21 | 女  |

根据上面的原理讲解， left join 后的表会和左表的个数一致，语句执行的结果为10.
如果执行：select count(s_id) from students left join information on s_id=id where province in ('山东','湖南'),则语句执行的结果为3.

赏