SQL面试题2：join连接where和on的区别

遇到的一道面试题，题目很简单，但不仔细思考很容易出错，在平常的工作中也会出现这样的问题。考察join连接时，on和where的区别。题目如下：

员工表和部门表，表结构和数据如下：

员工表emp

+--------+------+---------+
| emp_no | name | dept_no |
+--------+------+---------+
|      1 | 张三 | A1      |
|      2 | 李四 | A2      |
|      3 | 王五 | A3      |
|      4 | 赵六 | B1      |
+--------+------+---------+

部门表dept

+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| A1      | 财务      |
| A2      | 人事      |
+---------+-----------+

问题一：请写出如下sql语句的结果

SELECT t1.name
FROM emp t1
LEFT JOIN dept t2
ON t1.dept_no = t2.dept_no
WHERE t2.dept_no IS NULL

问题二：请写出如下sql语句的结果

SELECT t1.name
FROM emp t1
LEFT JOIN dept t2
ON t1.dept_no = t2.dept_no
AND t2.dept_no IS NULL

问题三：请写出如下sql语句的结果

SELECT t1.name
FROM emp t1
INNER JOIN dept t2
ON t1.dept_no = t2.dept_no
WHERE t2.dept_no IS NULL

问题四：请写出如下sql语句的结果

SELECT t1.name
FROM emp t1
INNER JOIN dept t2
ON t1.dept_no = t2.dept_no
AND t2.dept_no IS NULL

建表语句：

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `dept_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `dept_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`dept_no`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('A1', '财务');
INSERT INTO `dept` VALUES ('A2', '人事');

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (
  `emp_no` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `dept_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`emp_no`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (1, '张三', 'A1');
INSERT INTO `emp` VALUES (2, '李四', 'A2');
INSERT INTO `emp` VALUES (3, '王五', 'A3');
INSERT INTO `emp` VALUES (4, '赵六', 'B1');

运行结果

一

+------+
| name |
+------+
| 王五 |
| 赵六 |
+------+

二

+------+
| name |
+------+
| 张三 |
| 李四 |
| 王五 |
| 赵六 |
+------+

三

Empty set (0.00 sec)

四

Empty set (0.00 sec)

总结：

1. 对于left join，不管on后面跟什么条件，左表的数据全部查出来，因此要想过滤需把条件放到where后面
2. 对于inner join，满足on后面的条件表的数据才能查出，可以起到过滤作用。也可以把条件放到where后面
3. 在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里