mysql之left join、join的on、where区别
- 1.准备工作
- 2. Join 连接on、where区别
- 3. left join之on、where区别
- 3.1 驱动表之on、where区别
- 3.2 被驱动表之on、where区别
- 4. 附加
前言：
对于外连接查询，我们都知道驱动表和被驱动表的关联关系条件我们放在 on后面，如果额外增加对驱动表过滤条件、被驱动表过滤条件，放 on 或者 where 好像都不会报错，但是得到的结果集确是不一样的。
网上大量关于left join、join的on、where区别其实很多都是错误，本文开始揭晓其中区别所在，该如何使用。
1.准备工作
建表语句
CREATE TABLE `t_students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`class_id` int(11) NOT NULL,
`name` varchar(10) NOT NULL,
`gender` char(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_class_id` (`class_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
CREATE TABLE `t_classes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8表数据
2. Join 连接on、where区别
sql如下：
SELECT * FROM `t_students` ts JOIN `t_classes` tc ON ts.`class_id` = tc.`id`; 执行 explain extended + sql; 命令
select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
SIMPLE | tc | ALL | (NULL) | (NULL) | (NULL) | (NULL) | 11 | |
SIMPLE | ts | ALL | PRIMARY | (NULL) | (NULL) | (NULL) | 5 | Using where; Using join buffer |
执行 show warnings; (该命令可以查看优化器优化后真正执行的sql语句)
select `mytest`.`ts`.`id` AS `id`,`mytest`.`ts`.`class_id` AS `class_id`,`mytest`.`ts`.`name` AS `name`,`mytest`.`ts`.`gender` AS `gender`,`mytest`.`tc`.`id` AS `id`,`mytest`.`tc`.`name` AS `name`
from `mytest`.`t_students` `ts` join `mytest`.`t_classes` `tc`
where (`mytest`.`tc`.`id` = `mytest`.`ts`.`class_id`) 分析：show warnings展示了优化后的语句，可以发现 on 连接条件被转化为 where 过滤条件。更多案例，可以自己去测试，on 都会转化为 where
结论：对于Join连接，on和where其实是一样的，经过InnoDB优化后，on连接条件会转化为where。
3. left join之on、where区别
3.1 驱动表之on、where区别
sql如下：
SELECT * FROM `t_students` ts
LEFT JOIN `t_classes` tc ON ts.`class_id` = tc.`id` AND ts.`gender` = 'M'; 执行 explain extended + sql;
select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
SIMPLE | ts | ALL | (NULL) | (NULL) | (NULL) | (NULL) | 11 | |
SIMPLE | tc | eq_ref | PRIMARY | PRIMARY | 4 | mytest.ts.class_id | 1 |
执行 show warnings;
select `mytest`.`ts`.`id` AS `id`,`mytest`.`ts`.`class_id` AS `class_id`,`mytest`.`ts`.`name` AS `name`,`mytest`.`ts`.`gender` AS `gender`,`mytest`.`tc`.`id` AS `id`,`mytest`.`tc`.`name` AS `name`
from `mytest`.`t_students` `ts` left join `mytest`.`t_classes` `tc`
on(((`mytest`.`ts`.`class_id` = `mytest`.`tc`.`id`) and (`mytest`.`ts`.`gender` = 'M')))
where 1结果集：
分析：从结果集来看，ts.gender = ‘M’ 并未生效。为什么？
从 explian 分析看出，ts 作为驱动表，做全表扫描，然后把查询到的每条记录的 ts.class_id、ts.gender= 'M' （也就是 on 条件里面的）作为条件让被驱动表tc 做单表查询（ts有多少条记录，单表查询多少次）得到结果集。
结论：left join 连接 on连接条件是给被驱动表用的，ts.gender = 'M' 放在 on连接条件里面，对驱动表查询是无效的，仅在连接被驱动表时生效，这不是我们想要的结果。
那我们应该怎么改sql，让 ts.gender = 'M' 对驱动表生效呢？
修改后的 sql 如下：
SELECT * FROM `t_students` ts LEFT JOIN `t_classes` tc
ON ts.`class_id` = tc.`id` WHERE ts.`gender` = 'M'; 执行explain extended + sql;
select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
SIMPLE | ts | ALL | (NULL) | (NULL) | (NULL) | (NULL) | 11 | Using where |
SIMPLE | tc | eq_ref | PRIMARY | PRIMARY | 4 | mytest.ts.class_id | 1 |
可以看出，ts表的 Extra 使用了 Using where
执行 show warnings;
select `mytest`.`ts`.`id` AS `id`,`mytest`.`ts`.`class_id` AS `class_id`,`mytest`.`ts`.`name` AS `name`,`mytest`.`ts`.`gender` AS `gender`,`mytest`.`tc`.`id` AS `id`,`mytest`.`tc`.`name` AS `name`
from `mytest`.`t_students` `ts` left join `mytest`.`t_classes` `tc`
on((`mytest`.`tc`.`id` = `mytest`.`ts`.`class_id`))
where (`mytest`.`ts`.`gender` = 'M')结果集：
分析：
从 explian 分析看出，ts作为驱动表，把 ts.gender = 'M' 作为条件做全表扫描，然后把查询到的每条记录的 ts.class_id（也就是 on连接条件）作为条件让被驱动表tc 做单表查询（ts有多少条记录，单表查询多少次）得到结果集。
可以看出， ts.gender = 'M' 放在where条件里面，驱动表做全表扫描时会带上where条件。
结论：对于驱动表，需要加针对驱动表的过滤条件，我们应该放在 where条件而不是 on条件
3.2 被驱动表之on、where区别
sql如下：
SELECT * FROM `t_students` ts LEFT JOIN `t_classes` tc
ON ts.`class_id` = tc.`id` AND tc.`name` IN ( '二班', '三班'); 执行 explain extended + sql;
select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
SIMPLE | ts | ALL | (NULL) | (NULL) | (NULL) | (NULL) | 11 | |
SIMPLE | tc | eq_ref | PRIMARY | PRIMARY | 4 | mytest.ts.class_id | 1 |
执行 show warnings;
select `mytest`.`ts`.`id` AS `id`,`mytest`.`ts`.`class_id` AS `class_id`,`mytest`.`ts`.`name` AS `name`,`mytest`.`ts`.`gender` AS `gender`,`mytest`.`tc`.`id` AS `id`,`mytest`.`tc`.`name` AS `name`
from `mytest`.`t_students` `ts` left join `mytest`.`t_classes` `tc`
on(((`mytest`.`ts`.`class_id` = `mytest`.`tc`.`id`) and (`mytest`.`tc`.`name` in ('二班','三班'))))
where 1结果集：
分析：
从 explian 分析看出，ts 作为驱动表，做全表扫描，然后把查询到的每条记录的 ts.class_id、 tc.name in ('二班','三班') （也就是 on条件）作为条件让被驱动表tc 做单表查询（ts有多少条记录，单表查询多少次）得到结果集。
假如：被驱动表的过滤条件放在 where 而不是 on呢，请看如下sql：
SELECT * FROM `t_students` ts LEFT JOIN `t_classes` tc
ON ts.`class_id` = tc.`id` WHERE tc.`name` IN ( '二班', '三班'); 执行 explain extended + sql;
select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
SIMPLE | ts | ALL | (NULL) | (NULL) | (NULL) | (NULL) | 11 | |
SIMPLE | tc | ALL | PRIMARY | (NULL) | (NULL) | (NULL) | 5 | Using where; Using join buffer |
执行 show warnings;
select `mytest`.`ts`.`id` AS `id`,`mytest`.`ts`.`class_id` AS `class_id`,`mytest`.`ts`.`name` AS `name`,`mytest`.`ts`.`gender` AS `gender`,`mytest`.`tc`.`id` AS `id`,`mytest`.`tc`.`name` AS `name`
from `mytest`.`t_students` `ts` join `mytest`.`t_classes` `tc`
where ((`mytest`.`tc`.`id` = `mytest`.`ts`.`class_id`) and (`mytest`.`tc`.`name` in ('二班','三班')))仔细看这里，left join连接变成了 join连接
结果集：
分析：
从 show warnings 分析看出，如果被驱动表有过滤条件在 where，那么 left join 会失效，会被优化成 join 连接。所以，被驱动表的过滤条件应该放在 on而不是 where
4. 附加
网上有种说法：left join连接 on会先生成虚拟表，然后再经过where条件过滤生成结果集。
这种说法是错误的！
验证：
sql如下：
SELECT * FROM `t_classes` tc LEFT JOIN `t_students` ts
ON ts.`class_id` = tc.`id` WHERE ts.id = NULL- 虚拟表生成：
SELECT * FROM `t_classes` tc LEFT JOIN `t_students` ts
ON ts.`class_id` = tc.`id`结果集如下：
- 再经过
WHERE ts.id = NULL生成结果集，应该如下：
然后我们执行这条sql 生成的结果集却是如下所示：
原因：left join 被优化成了 join。
