--观察查询执行计划,看哪个真正执行的是外连接,哪个被从外连接优化为了内连接
语句十:EXPLAIN EXTENDED SELECT * FROM t_1 LEFT JOIN t_2 ON true WHERE t_1_col_1 = t_2_col_1;
语句十一:EXPLAIN EXTENDED SELECT * FROM t_1 LEFT JOIN t_2 ON t_1_col_1 = t_2_col_1;
语句十二:EXPLAIN EXTENDED SELECT * FROM t_1 LEFT JOIN t_2 ON t_1_col_1 = t_2_col_1 WHERE t_1_col_1 = t_2_col_1;
语句 | 查询语句优化后的结果 |
语句十、语句十二 | /* select#1 */ select `test`.`t_1`.`t_1_id` AS `t_1_id`, `test`.`t_1`.`t_1_col_1` AS `t_1_col_1`, `test`.`t_1`.`t_1_col_2` AS `t_1_col_2`, `test`.`t_2`.`t_2_id` AS `t_2_id`, `test`.`t_2`.`t_2_col_1` AS `t_2_col_1`, `test`.`t_2`.`t_2_col_2` AS `t_2_col_2` from `test`.`t_1` join `test`.`t_2` where (`test`.`t_2`.`t_2_col_1` = `test`.`t_1`.`t_1_col_1`) |
语句十一 | /* select#1 */ select `test`.`t_1`.`t_1_id` AS `t_1_id`, `test`.`t_1`.`t_1_col_1` AS `t_1_col_1`, `test`.`t_1`.`t_1_col_2` AS `t_1_col_2`, `test`.`t_2`.`t_2_id` AS `t_2_id`, `test`.`t_2`.`t_2_col_1` AS `t_2_col_1`, `test`.`t_2`.`t_2_col_2` AS `t_2_col_2` from `test`.`t_1` left join `test`.`t_2` on((`test`.`t_1`.`t_1_col_1` = `test`.`t_2`.`t_2_col_1`)) where 1 |
对比四 | 语句十一,多了“left”,表明最后得到的查询执行计划还是按照左连接的语义执行,没有被优化为内连接;而语句十、十二,执行两表连接时,只是“join”,没有外连接存在,表明最后得到的查询执行计划是按照内连接的语义执行的,外连接被优化为了內连接 |
--第三组
--观察WHERE条件是非连接条件,且条件分别在左表和右表上,查询执行计划,看哪个真正执行的是外连接,哪个被从外连接优化为了内连接
语句十三:EXPLAIN EXTENDED SELECT * FROM t_1 LEFT JOIN t_2 ON true WHERE t_1_id>0 and t_2_id>0;
语句十四:EXPLAIN EXTENDED SELECT * FROM t_1 LEFT JOIN t_2 ON t_1_id = t_2_id WHERE t_1_id>0;
语句十五:EXPLAIN EXTENDED SELECT * FROM t_1 LEFT JOIN t_2 ON t_1_id = t_2_id WHERE t_2_id>0;
语句十六:EXPLAIN EXTENDED SELECT * FROM t_1 LEFT JOIN t_2 ON true WHERE t_1_col_1>0 OR t_2_col_1>0;
语句 | 查询语句优化后的结果 |
语句十三 | /* select#1 */ select `test`.`t_1`.`t_1_id` AS `t_1_id`, `test`.`t_1`.`t_1_col_1` AS `t_1_col_1`, `test`.`t_1`.`t_1_col_2` AS `t_1_col_2`, `test`.`t_2`.`t_2_id` AS `t_2_id`, `test`.`t_2`.`t_2_col_1` AS `t_2_col_1`, `test`.`t_2`.`t_2_col_2` AS `t_2_col_2` from `test`.`t_1` join `test`.`t_2` where ((`test`.`t_1`.`t_1_id` > 0) and (`test`.`t_2`.`t_2_id` > 0)) |
语句十四 | /* select#1 */ select `test`.`t_1`.`t_1_id` AS `t_1_id`, `test`.`t_1`.`t_1_col_1` AS `t_1_col_1`, `test`.`t_1`.`t_1_col_2` AS `t_1_col_2`, `test`.`t_2`.`t_2_id` AS `t_2_id`, `test`.`t_2`.`t_2_col_1` AS `t_2_col_1`, `test`.`t_2`.`t_2_col_2` AS `t_2_col_2` from `test`.`t_1` left join `test`.`t_2` on((`test`.`t_2`.`t_2_id` = `test`.`t_1`.`t_1_id`)) where (`test`.`t_1`.`t_1_id` > 0) | |
语句十五 | /* select#1 */ select `test`.`t_1`.`t_1_id` AS `t_1_id`, `test`.`t_1`.`t_1_col_1` AS `t_1_col_1`, `test`.`t_1`.`t_1_col_2` AS `t_1_col_2`, `test`.`t_2`.`t_2_id` AS `t_2_id`, `test`.`t_2`.`t_2_col_1` AS `t_2_col_1`, `test`.`t_2`.`t_2_col_2` AS `t_2_col_2` from `test`.`t_1` join `test`.`t_2` where ((`test`.`t_2`.`t_2_id` = `test`.`t_1`.`t_1_id`) and (`test`.`t_1`.`t_1_id` > 0)) |
语句十六 | /* select#1 */ select `test`.`t_1`.`t_1_id` AS `t_1_id`, `test`.`t_1`.`t_1_col_1` AS `t_1_col_1`, `test`.`t_1`.`t_1_col_2` AS `t_1_col_ 2`,`test`.`t_2`.`t_2_id` AS `t_2_id`, `test`.`t_2`.`t_2_col_1` AS `t_2_col_1`, `test`.`t_2`.`t_2_col_2` AS `t_2_col_2` from `test`.`t_1` left join `test`.`t_2` on(1) where ((`test`.`t_1`.`t_1_col_1` > 0) or (`test`.`t_2`.`t_2_col_1` > 0)) |
对比五 | 语句十三,WHERE条件是“t_1_id>0 and t_2_id>0”,满足如下四种情况: 1) t_1_id = NULL,t_2_id = NULL:则NULL>0为UNKOWEN, UNKOWEN and UNKOWEN值为UNKOWEN 2) t_1_id = NULL,t_2_id != NULL:则NULL>0为UNKOWEN,t_2_id>0且t_2_id非NULL值为TRUE或FALSE,所以“UNKOWEN and TRUE”或者“UNKOWEN and FALSE”值为UNKOWEN 3) t_1_id != NULL,t_2_id = NULL: 同上 4) t_1_id != NULL,t_2_id !=NULL:“t_1_id>0 and t_2_id>0”值要么是TRUE要么是FALSE,但毕竟可能为“TRUE”,这不符合“空值拒绝”的条件一,但满足条件二“外连接的提供空值的一侧为另一侧的每行只返回一行”(t_1_id和t_2_id都是QUIQUE键至多能为连接后的结果集返回一行) 所以,四种情况都是满足“空值拒绝”的,所以可以把外连接转换为內连接
语句十四,WHERE条件是“t_1_id>0”,有如下两种情况: 1) t_1_id = NULL:则NULL>0为UNKOWEN 2) t_1_id != NULL:非NULL值与零比较,可为TRUE或FALSE,当为FALSE时满足“空值拒绝”条件一,当值为TRUE时不满足“空值拒绝”条件二,所以外连接不能转换为內连接
语句十五,WHERE条件是“t_2_id>0”,有如下两种情况: 3) t_2_id = NULL:则NULL>0为UNKOWEN 4) t_2_id != NULL:非NULL值与零比较,可为TRUE或FALSE,当为FALSE时满足“空值拒绝”条件一,当值为TRUE时满足“空值拒绝”条件二,所以可以把外连接转换为內连接
语句十六,WHERE条件是“t_1_col_1>0 or t_2_col_1>0”,类似语句十四、十五的组合,只是列不同(UNIQUE列和普通列的差别),“t_2_col_1>0”能够像语句十五一样,保证满足“空值拒绝”,但“t_1_col_1>0”如同语句十四,不能够保证满足“空值拒绝”,所以经过“or”操作,只能以“left Join”执行两表连接 |