浅析sql中的in与exists,not in与not exists的区别

在我们平时开发中书写SQL语句时，in、not in、exists、not exists都是可能会用到的，那么它们之间有什么区别呢，有没有什么可能潜在的坑呢？

创建测试数据库：

CREATE TABLE `testa`  (
  `id` int(11) NULL DEFAULT NULL
);
INSERT INTO `testa` VALUES (1);
INSERT INTO `testa` VALUES (2);
INSERT INTO `testa` VALUES (NULL);
INSERT INTO `testa` VALUES (NULL);
INSERT INTO `testa` VALUES (3);

CREATE TABLE `testb`  (
  `id` int(11) NULL DEFAULT NULL
);
INSERT INTO `testb` VALUES (1);
INSERT INTO `testb` VALUES (2);
INSERT INTO `testb` VALUES (NULL);
INSERT INTO `testb` VALUES (NULL);
INSERT INTO `testb` VALUES (4);

测试in、exists：

测试 IN：

SELECT	t1.id FROM	testa t1 WHERE	t1.id IN ( SELECT t2.id FROM testb t2 );

运行结果：
在这里插入图片描述
测试 EXISTS：

SELECT	t1.id FROM testa t1 WHERE EXISTS ( SELECT t2.id FROM testb t2 WHERE t1.id = t2.id );

运行结果：
在这里插入图片描述
可以看到两个SQL语句的运行结果是一样的，下面简单解析一下IN和EXISTS的区别。

对于IN来说，上面的SQL语句：

SELECT	t1.id FROM	testa t1 WHERE	t1.id IN ( SELECT t2.id FROM testb t2 );

可以看做等同于下面的SQL语句：

SELECT	t1.id FROM	testa t1 WHERE	t1.id = 1 OR  t1.id = 2 OR t1.id = 4 OR t1.id = null;

由于t1.id = null的结果肯定是false，上面的SQL语句可以进一步简化成：

SELECT	t1.id FROM	testa t1 WHERE	t1.id = 1 OR  t1.id = 2 OR t1.id = 4;

对于EXISTS来说，上面的SQL语句：

SELECT	t1.id FROM testa t1 WHERE EXISTS ( SELECT t2.id FROM testb t2 WHERE t1.id = t2.id );

等同于：

SELECT	t1.id FROM testa t1 WHERE EXISTS ( SELECT 1 FROM testb t2 WHERE t1.id = t2.id );

会发现运行结果是一样的，这是为什么呢？这是因为EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS内部有一个子查询语句(SELECT … FROM…)，我将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。简单理解就是，使用EXISTS时，将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。

IN和EXISTS区别：

a. IN：in()后面的子查询是返回结果集的，换句话说执行次序和exists()不一样。子查询先产生结果集，然后主查询再去结果集里去找符合要求的字段列表去。符合要求的输出,反之则不输出。由上面的SQL可以看出来，in()中有null值时会被忽略。

b. EXISTS：exists()后面一般都是子查询，当子查询返回行数时，exists返回true，后面的子查询被称做相关子查询, 他是不返回列表的值的，只是返回一个ture或false的结果。也就是它只在乎括号里的数据能不能查找出来，是否存在这样的记录。其运行方式是先运行主查询一次再去子查询里查询与其对应的结果如果存在，返回ture则输出,反之返回false则不输出。

IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

测试NOT IN和NOT EXISTS：

测试 NOT IN：

SELECT	t1.id FROM	testa t1 WHERE	t1.id NOT IN ( SELECT t2.id FROM testb t2 );

运行结果：
在这里插入图片描述
可以看到并没有查询出结果集，这是为什么呢？其实上面的SQL等同于：

SELECT	t1.id FROM	testa t1 WHERE	t1.id != 1 AND  t1.id != 2 AND t1.id != 4 AND t1.id != null;

where后面的条件运算，t1.id != null的运算结果为false，导致整个where条件语句运算结果为false，所以什么都没有查询出来。

如果想要查询出结果，需要修改一下SQL语句，去除子查询中的NULL值：

SELECT	t1.id FROM	testa t1 WHERE	t1.id NOT IN ( SELECT t2.id FROM testb t2 where t2.id IS NOT null );

运行结果：
在这里插入图片描述
测试NOT EXISTS：

SELECT	t1.id FROM testa t1 WHERE NOT EXISTS ( SELECT t2.id FROM testb t2 WHERE t1.id = t2.id );

运行结果：
在这里插入图片描述
去除id为NULL的结果：

SELECT	t1.id FROM testa t1 WHERE NOT EXISTS ( SELECT t2.id FROM testb t2 WHERE t1.id = t2.id ) AND t1.id IS NOT null;

运行结果：
在这里插入图片描述
NOT IN和NOT EXISTS区别：

a. NOT IN：如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录。如果子查询字段有非空限制，这时可以使用not in。

b. NOT EXISTS：相当于把前表的每条记录带入后面的表达式，看是否有记录返回，即使存在NULL也可以正常查询。

如果查询语句使用了not in，那么对内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in 要快。

一颗安安静静的小韭菜。文中如果有什么错误，欢迎指出。