MySql 关联查询时 on条件过滤和where条件过滤的区别

在学习mysql的连接查询时容易忽略的细节，当左连接时就以左表为主，有连接就以由表为主，但是过滤条件为on和where时时常被我们忽略。

首先建两张表并导入数据：

-- ----------------------------
-- Table structure for sys_dept
-- ----------------------------
DROP TABLE IF EXISTS `sys_dept`;
CREATE TABLE `sys_dept`  (
  `dept_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id，自增',
  `dept_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '部门名称',
  PRIMARY KEY (`dept_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sys_dept
-- ----------------------------
INSERT INTO `sys_dept` VALUES (1, '研发部');
INSERT INTO `sys_dept` VALUES (2, '测试部');
INSERT INTO `sys_dept` VALUES (3, '财务部');
INSERT INTO `sys_dept` VALUES (4, '人事部');

-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user`  (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id，自增',
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `dept_id` int(11) NULL DEFAULT NULL COMMENT '部门id',
  `is_disable` int(11) NULL DEFAULT NULL COMMENT '是否禁用：1-启用，0-禁用',
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sys_user
-- ----------------------------
INSERT INTO `sys_user` VALUES (1, '张三', 1, 0);
INSERT INTO `sys_user` VALUES (2, '李四', 4, 1);
INSERT INTO `sys_user` VALUES (3, '王五', 1, 1);
INSERT INTO `sys_user` VALUES (4, '赵六', 3, 1);
INSERT INTO `sys_user` VALUES (5, '田七', 2, 1);
INSERT INTO `sys_user` VALUES (6, '刘八', 4, 0);

SET FOREIGN_KEY_CHECKS = 1;

一共两张表，用户表和部门表，用户表中的dept_id和部门表的dept_id关联，下面的对两表进行关联查询，查出用户名和用户对应的部门名称，分别采用 where 和 on 进行条件过滤。

where 过滤

SELECT
	u.user_name,
	d.dept_name 
FROM
	sys_user u
	LEFT JOIN sys_dept d ON u.dept_id = d.dept_id 
WHERE
	u.is_disable = 1

条件

on 过滤

SELECT
	u.user_name,
	d.dept_name 
FROM
	sys_user u
	LEFT JOIN sys_dept d ON u.dept_id = d.dept_id 
AND
	u.is_disable = 1

上述两个查询中都是加了is_disable = 1这个条件，本来左连接要以左表为主，但是where后加了is_disable = 1后过滤掉了左表中的两条数据，而 and 后加is_disable = 1却仍然以坐标为主，并没有过滤掉左表中的数据，这是为什么呢？

mysql在进行关联查询时会生成一个临时表，当过滤条件为on时mysql会不管on后的条件是否为真，都会以左表为主（这里指左连接）；而过滤条件为where时，mysql会在临时表生成之后再对临时表进行条件过滤，所以第二条sql中仍然以左表为主，并没有将数据过滤掉。