mysql之left join、join的on、where区别看这篇就懂 |
您所在的位置：网站首页 › in和on的使用 › mysql之left join、join的on、where区别看这篇就懂 |
mysql之left join、join的on、where区别看这篇就懂
|
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表数据
sql如下： SELECT * FROM `t_students` ts JOIN `t_classes` tc ON ts.`class_id` = tc.`id`;执行 explain extended + sql; 命令 select_typetabletypepossible_keyskeykey_lenrefrowsExtraSIMPLEtcALL(NULL)(NULL)(NULL)(NULL)11SIMPLEtsALLPRIMARY(NULL)(NULL)(NULL)5Using 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_typetabletypepossible_keyskeykey_lenrefrowsExtraSIMPLEtsALL(NULL)(NULL)(NULL)(NULL)11SIMPLEtceq_refPRIMARYPRIMARY4mytest.ts.class_id1执行 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_typetabletypepossible_keyskeykey_lenrefrowsExtraSIMPLEtsALL(NULL)(NULL)(NULL)(NULL)11Using whereSIMPLEtceq_refPRIMARYPRIMARY4mytest.ts.class_id1可以看出，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_typetabletypepossible_keyskeykey_lenrefrowsExtraSIMPLEtsALL(NULL)(NULL)(NULL)(NULL)11SIMPLEtceq_refPRIMARYPRIMARY4mytest.ts.class_id1执行 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_typetabletypepossible_keyskeykey_lenrefrowsExtraSIMPLEtsALL(NULL)(NULL)(NULL)(NULL)11SIMPLEtcALLPRIMARY(NULL)(NULL)(NULL)5Using 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`结果集如下：
生成结果集，应该如下：
然后我们执行这条sql 生成的结果集却是如下所示：
原因：left join 被优化成了 join。 |
【本文地址】
今日新闻 |
推荐新闻 |