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。

赞(1)
未经允许不得转载:极客笔记 » SQL EXISTS子查询
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址