mysql中left join on多条件与where的区别

为了测试建立两张表
A、B表

CREATE TABLE `b`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a_id` int(11) NULL DEFAULT NULL,
  `is_delete` int(1) NULL DEFAULT NULL,
  `a_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `asf`(`a_id`) USING BTREE,
  INDEX `dada`(`a_id`, `is_delete`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of b
-- ----------------------------
INSERT INTO `b` VALUES (1, 1, 1, '张三2');
INSERT INTO `b` VALUES (2, 1, 1, '张三2');
INSERT INTO `b` VALUES (3, 3, 1, '王五');
INSERT INTO `b` VALUES (4, 4, 1, NULL);
INSERT INTO `b` VALUES (5, 5, 0, NULL);
INSERT INTO `b` VALUES (6, 6, 0, NULL);
INSERT INTO `b` VALUES (7, 7, 0, NULL);

CREATE TABLE `a`  (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `isdelete` int(2) NULL DEFAULT NULL,
  `special` int(2) NULL DEFAULT NULL,
  `sex` enum('男','女') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_nameSp`(`special`, `name`, `isdelete`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 778 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of a
-- ----------------------------
INSERT INTO `a` VALUES (1, '张三2', 1, 1, '男');
INSERT INTO `a` VALUES (2, '李1', 1, 1, '女');
INSERT INTO `a` VALUES (3, '王五', 0, 3, '女');
INSERT INTO `a` VALUES (4, NULL, 0, 4, NULL);

查询结果
SELECT count(*) from b LEFT JOIN a on b.a_id = a.id and a.sex = 1

结果：7

SELECT count(*) from b LEFT JOIN a on b.a_id = a.id where a.sex = 1

结果：2

SELECT count(*) from b LEFT JOIN a on b.a_id = a.id and b.id = 1

结果：7，这种方式只有b表中id为1的会进行连表匹配，不满足条件的直接输出

结论：
join关联时，根据B表中的a_id，和sex字段去先根据扫描A表数据