SQL EXISTS子查询

我们介绍一个与子查询相关的操作符：EXISTS。

EXISTS 操作符

EXISTS 操作符用于判断子查询结果的存在性。如果子查询存在任何结果，EXISTS 返回真；否则，返回假。

以下语句查找存在女性员工的部门：

SELECT d.dept_name
  FROM department d
 WHERE EXISTS ( SELECT 1
                  FROM employee e
                 WHERE e.sex = '女'
                   AND e.dept_id = d.dept_id)
 ORDER BY dept_name;

dept_name|
---------|
研发部    |
财务部    |

EXISTS 之后是一个关联子查询，先执行外查询找到 d.dept_id；然后依次将 d.dept_id 传递给子查询，判断该部门是否存在女性员工；子查询一旦找到任何数据立即返回结果。EXISTS 只判断结果的存在性，因此子查询的 SELECT 列表中的内容无所谓，通常使用一个常量值。该查询的结果表明“研发部”和“财务部”存在女性员工。

EXISTS 只要找到任何数据，立即终止子查询的执行，因此可以提高查询的性能。

另外，NOT EXISTS 执行相反的操作。如果想要查找不存在女性员工的部门，可以将上例中的 EXISTS 替换成 NOT EXISTS。

现在，我们知道 [NOT] EXISTS 和 [NOT] IN 都可以用于判断子查询返回的结果。但是它们之间存在一个重要的区别：[NOT] EXISTS 只检查存在性，[NOT] IN 需要比较实际的值是否相等。因此，当子查询的结果包含 NULL 值时，EXISTS 仍然返回结果，NOT EXISTS 不返回结果；但是此时 IN 和 NOT IN 都不会返回结果，因为 (X = NULL) 和 NOT (X = NULL) 的结果都是未知。

以下示例用于查找没有员工的部门，演示了这两者之间的区别：

SELECT d.dept_name
  FROM department d
 WHERE NOT EXISTS ( SELECT NULL
                      FROM employee e
                     WHERE e.dept_id = d.dept_id)
 ORDER BY dept_name;

dept_name|
---------|
保卫部    |

SELECT d.dept_name
  FROM department d
 WHERE d.dept_id NOT IN ( SELECT NULL
                            FROM employee e)
 ORDER BY dept_name;

dept_name|
---------|

第一个查询使用了 NOT EXISTS，子查询中除了“保卫部”之外的部门都有返回结果（NULL 也是结果），所以外查询只返回“保卫部”。第二个查询使用了 NOT IN，子查询中返回的都是 NULL 值；d.dept_id = NULL 的结果是未知，加上 NOT 之后仍然未知，所以查询没有返回任何结果。

通常来说，[NOT] EXISTS 的性能比 [NOT] IN 更好，尽量使用 [NOT] EXISTS。

我们还可以在子查询中包含其他的子查询，实现嵌套子查询。

EXISTS 子查询场景问题

问题描述

不太理解哪种情况下应该使用EXISTS，哪种情况应该用IN。选择的标准是看能否使用表的索引吗？

解答

索引是个前提，其实选择与否还是要看表的大小。你可以将选择的标准理解为小表驱动大表。在这种方式下效率是最高的。

比如下面这样：

 SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
 SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc=A.cc)

当A小于B时，用EXISTS。因为EXISTS的实现，相当于外表循环，实现的逻辑类似于：

 for i in A
     for j in B
         if j.cc == i.cc then ...

当B小于A时用IN，因为实现的逻辑类似于：

 for i in B
     for j in A
         if j.cc == i.cc then ...

哪个表小就用哪个表来驱动，A表小就用EXISTS，B表小就用IN。