on 子句的谓词和 where 谓词的区别

数据准备

CREATE TABLE `test1`  (
  `id` int(11) NOT NULL,
  `name1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test1
-- ----------------------------
INSERT INTO `test1` VALUES (1, 'n1');
INSERT INTO `test1` VALUES (2, 'n2');
INSERT INTO `test1` VALUES (3, 'n3');

CREATE TABLE `test2`  (
  `id` int(11) NOT NULL,
  `name2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test2
-- ----------------------------
INSERT INTO `test2` VALUES (1, 'm1');
INSERT INTO `test2` VALUES (2, 'm2');

条件作用于右表

on

SELECT  * FROM  test1 LEFT join test2 ON  test1.id = test2.id  AND  test2.name2 !='m2';

对应的结果

where

SELECT  * FROM  test1 LEFT join test2 ON  test1.id = test2.id  WHERE  test2.name2 !='m2';

条件作用于左表

SELECT  * FROM  test1 LEFT join test2 ON  test1.id = test2.id  WHERE  test1.name1 ='n3';

SELECT  * FROM  test1 LEFT join test2 ON  test1.id = test2.id  AND  test1.name1 ='n3';

分别对应的结果

Why

因为outer join 的执行过程分为4步
1、先对两个表执行交叉连接(笛卡尔积)
2、应用on筛选器
3、添加外部行
4、应用where筛选器

问题在第三步
outer join有一个特点就是以一侧的表为基，假如另一侧的表没有符合on筛选条件的记录，则以null替代。这一步的作用就是将原本应该被过滤掉的记录给添加了回来。