纸上得来终觉浅，绝知此事要躬行。
看了好久理解理解的都不深刻，动手做了一下实验，发现好简单。
理解NULL拒绝含义，满足空值拒绝，则退化为内连接，可以消除外连接
1. prepare sql
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `t_1`;
CREATE TABLE `t_1` (
`t_1_id` int(11) DEFAULT NULL,
`t_1_col_1` int(11) DEFAULT NULL,
`t_1_col_2` varchar(10) DEFAULT NULL,
UNIQUE KEY `t_1_id` (`t_1_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_1` VALUES ('1', '11', 't_1_1');
INSERT INTO `t_1` VALUES ('2', '12', null);
INSERT INTO `t_1` VALUES ('3', null, 't_1_3');
INSERT INTO `t_1` VALUES ('4', '14', 't_1_4');
INSERT INTO `t_1` VALUES ('5', '15', null);
INSERT INTO `t_1` VALUES ('7', null, null);
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `t_2`;
CREATE TABLE `t_2` (
`t_2_id` int(11) DEFAULT NULL,
`t_2_col_1` int(11) DEFAULT NULL,
`t_2_col_2` varchar(10) DEFAULT NULL,
UNIQUE KEY `t_2_id` (`t_2_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_2` VALUES ('1', '11', 't_2_1');
INSERT INTO `t_2` VALUES ('2', null, 't_2_2');
INSERT INTO `t_2` VALUES ('3', '13', null);
INSERT INTO `t_2` VALUES ('4', '14', 't_2_4');
INSERT INTO `t_2` VALUES ('6', '16', 't_2_6');
INSERT INTO `t_2` VALUES ('7', null, null);
2. select 语句
当null比较时unknown会被处理成false
原则：当left join退化为内连接时，可以消除外连接，满足空值拒绝
2.1 条件（where条件）可以保证结果中排除外连接右侧（右表）生成的值为null的行（即条件确保应用的右表带有的空值的列对象上时，条件不成立，条件的结果为false或者unkown，这样游标就不会有值为null的行生成），退化成内连接。
1）
select * from t_1 left join t_2 on true where t_1_id = t_2_id;
explain extended select * from t_1 left join t_2 on true wheret_1_id = t_2_id;
2）
select * from t_1 left join t_2 on t_1_id = t_2_id;
explain extended select * from t_1 left join t_2 on t_1_id = t_2_id;
3）
select * from t_1 left join t_2 on t_1_id = t_2_id where t_1_id = t_2_id;
explain extended select * from t_1 left join t_2 on t_1_id = t_2_id where t_1_id = t_2_id;
PS:t_1_id = t_2_idunique 所以，不存在未null的行
2.2 外连接提供空值的一侧为另一侧的每行只返回一行，如果该条件为真，则不存在提供空值的行，退化为内连接。
1)
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 true wheret_1_col_1 = t_2_col_1;
2)
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;
3)
select * from t_1 left join t_2 on t_1_col_1 = t_2_col_1 wheret_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;
ps:t_1_col_1 = t_2_col_1 肯定为true，不存在为false的情况。null = x or x = null or null = null -> false
2.3
1)
explain extended select * from t_1 left join t_2 on true where t_1_id > 0 and t_2_id > 0;
ok : t_2_id > 0 为ture，退化为内连接，可以消除链接
2)
explain extended select * from t_1 left join t_2 on t_1_id = t_2_id where t_1_id > 0;
no : 提供空值的右表中，提供一些空值几率。没有消除t_1表中的id=5的记录
3）
explain extended select * from t_1 left join t_2 on t_1_id = t_2_id where t_2_id >0;
ok： t_2_id > 0会过滤到提供的空值的记录
4）
explain extended select * from t_1 left join t_2 on t_1_id = t_2_id where t_1_id > 0 or t_2_id >0;
no : or 表达式，是的where条件可能无法过滤提供空值的记录 t_1表中id=5的记录
网友评论