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

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

1. 创建测试数据库：

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);

2. 测试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适合于外表小而内表大的情况。

3. 测试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 要快。