探讨 T-SQL 的 EXISTS、EXCEPT、INTERSECT 算符

浅浅的花香味﹌ 2022-06-05 08:51 316阅读 0赞

**---http://www.cnblogs.com/WizardWu/archive/2011/10/01/2197147.html**

**转载收藏**

**The EXISTS Operator**

EXISTS 可称之为运算符，有些书称它为关键词。 EXISTS 和 IN 关键词很类似，但仍有些不同，EXISTS 使用时不会返回数据，而是返回简单的 TRUE / FALSE。如下示例 \[1\]，即使子查询返回 NULL，用 EXISTS 也会得到 TRUE：

USE AdventureWorks2008 ;  
GO  
  
SELECT DepartmentID, Name  
FROM HumanResources.Department  
WHERE EXISTS ( SELECT NULL)

EXISTS 和 IN 在很多情况下可查到相同的结果 \[1\]，两个关键词的用途有些类似，因此网络上常有些 EXISTS 和 IN 谁效率较好的讨论串 \[2\]，此处不再赘述。 以下我们改探讨 EXISTS 和 JOIN 的比较，下方是书上的两个示例 \[7\]，两者的执行结果相同，第一个示例是 EXISTS 搭配「嵌套查询 (nested query)」，第二个示例语法看起来较简洁，是传统大家习惯用的 join 写法：

![复制代码][copycode.gif]

SELECT e.BusinessEntityID, FirstName, LastName  
FROM HumanResources.Employee e  
JOIN Person.Person pp  
ON e.BusinessEntityID = pp.BusinessEntityID  
WHERE EXISTS  
 ( SELECT  BusinessEntityID  
FROM  HumanResources.JobCandidate jc  
WHERE  e.BusinessEntityID  =  jc.BusinessEntityID);

![复制代码][copycode.gif]

上方示例、下方示例，执行结果皆为：  
EmployeeID　　FirstName　　LastName  
\-----------　　------------　　-------------  
　　212　　　　　Peng　　　　 Wu  
　　274　　　　　Stephen　　 Jiang

(2 row(s) affected)

SELECT e.BusinessEntityID, FirstName, LastName  
FROM HumanResources.Employee e  
JOIN Person.Person pp  
ON e.BusinessEntityID = pp.BusinessEntityID  
JOIN  HumanResources.JobCandidate jc  
ON  e.BusinessEntityID  =  jc.BusinessEntityID;

两个示例虽然结果相同，但 EXISTS 的性能会较好 (书上说的) \[7\]。 当我们使用 EXISTS 关键词时，SQL Server 不会用 full row-by-row join，而是在记录当中搜寻，当它找到第一个符合条件的记录时，就会立即停止后续搜寻的动作，并标示为 TRUE，表示不需要再往下找了；反观 inner join 则不会有此种标示的动作。

此外，NOT EXISTS 也有此种标示的功能。当 NOT EXISTS 找到符合条件的数据时，同样也会标示，但标示为 FALSE，表示不需要再往下找了。

除了此一性能差别外，EXISTS 和 join-based 在查询时，各方面并无二致。

<table> 
 <tbody> 
  <tr> 
   <td>Using EXISTS in Other Ways</td> 
  </tr> 
 </tbody> 
</table>

有时您会看到有些人执行 CREATE 前，会加一些古怪的语句，如下：

![复制代码][copycode.gif]

IF EXISTS ( SELECT \* FROM sysobjects WHERE id =  
object\_id(N ' \[Sales\].\[SalesOrderHeader\] ') AND OBJECTPROPERTY(id,N ' IsUserTable ') = **1**)  
DROP TABLE \[ Sales \]. \[ SalesOrderHeader \]  
GO  
  
CREATE TABLE \[ Sales \]. \[ SalesOrderHeader \] (  
...  
...

![复制代码][copycode.gif]

他们使用 sys.objects、sys.databases，或 INFORMATION\_SCHEMA 检视 (view)，目的都一样，想在执行 CREATE 前先看看某个对象是否已存在。 但同样是用 EXISTS 做事先的判断，我有更好的写法，请参考以下创建数据库的示例：

![复制代码][copycode.gif]

USE master  
GO  
  
IF NOT EXISTS ( SELECT ' True ' FROM sys.databases WHERE name = ' DBCreateTest ')  
BEGIN  
CREATE DATABASE DBCreateTest  
END  
ELSE  
BEGIN  
PRINT ' 此数据库已经存在。跳过了 CREATE DATABASE 语句 '  
END  
GO

![复制代码][copycode.gif]

第一次执行此语句时，若没有 DBCreateTest 数据库，则创建它；第二次执行时，由于该数据库已存在，因此会印出自定义的提示信息。 因此，仅用一点小技巧，可避免掉不必要的 DROP 动作被执行，这会让您公司的产品在被安装时更有效率。

EXISTS 是很方便的关键词，有时可让查询语句执行得更有效率，有时则可简化 SQL 语句。

<table> 
 <tbody> 
  <tr> 
   <td><strong>The INTERSECT and EXCEPT Operators</strong></td> 
  </tr> 
 </tbody> 
</table>

接下来介绍的 INTERSECT 和 EXCEPT 关键词，在 SQL Server 和其他厂牌数据库多半都支持。 INTERSECT 和 EXCEPT 在处理两个 result set 时，和 UNION 关键词很类似。 在 MSDN、TechNet 上虽然有对这两个关键词作解释 \[3\], \[6\]，但半机器的翻译有些不易理解，建议参考下列取自书上的图文示例  \[7\]，可让人一目了然：

*  UNION： 将多个「结果集 (result set)」的「行 (row)」合并，作为单个结果集返回，并移除重复的行。若有重复的行，只留下一个。
 *  UNION ALL： 将多个「结果集 (result set)」所有的行合并，不论是否有重复的行。
 *  EXCEPT： 提取只在 EXCEPT 左侧存在，但右侧不存在的行，参考下图 1。用更口语化的说法：「只给我 A 里才有，但 B 里没有的行」。
 *  INTERSECT： 只提取两个结果集里，都存在的行。 INTERSECT 很类似 inner join，但 INTERSECT 并不会对特定的「列 (column)」去做处理。

由于上述关键词，不会对特定的「列」去做处理，因此在使用上必须符合某些原则 \[3\]，例如：所有查询中的列数和列的顺序必须相同、数据类型必须兼容。  
  
![111001_01.jpg][]  
图 1　UNION 中若有重复的行，会被移除，只留下一个

在看本文最后的完整比较示例前，我们先了解 EXCEPT 和 INTERSECT 的基本语法。

<table> 
 <tbody> 
  <tr> 
   <td>EXCEPT</td> 
  </tr> 
 </tbody> 
</table>

EXCEPT 用法如下，简单易懂：

*<table or tabular result>*  
EXCEPT  
*<table or tabular result with same number of columns and type as top query>*

同样的查询需求，我们改用 NOT EXISTS 的写法，也能得到和 EXCEPT 写法相同的结果，用法如下  \[1\], \[7\]。 本文最后会有完整的示例。

<base query >  
WHERE NOT EXISTS  
( SELECT **1**  
FROM < table or result with same number of columns and type as top query >  
WHERE <base query first column > = <comparison table first column > \[ , ... \])

<table> 
 <tbody> 
  <tr> 
   <td>INTERSECT</td> 
  </tr> 
 </tbody> 
</table>

至于 INTERSECT 的语法也一样简单易懂，用法和 EXCEPT、UNION 也都类似，如下：

*<table or tabular result>*  
INTERSECT  
*<table or tabular result with same number of columns and type as top query>*

同样的查询需求，我们改用 EXISTS 的写法，也能得到和 INTERSECT 写法相同的结果，用法如下  \[1\], \[7\]。 本文最后会有完整的示例。

<base query >  
WHERE EXISTS  
( SELECT **1**  
FROM < table or result with same number of columns and type as top query >  
WHERE <base query first column > = <comparison table first column > \[ , ... \])

此外，INTERSECT 和 EXCEPT 可同时混搭使用，但 INTERSECT 运算符会优先于 EXCEPT \[6\]：

SELECT \* FROM TableA  
EXCEPT  
SELECT \* FROM TableB  
INTERSECT  
SELECT \* FROM TableC

<table> 
 <tbody> 
  <tr> 
   <td><strong>Comparing EXCEPT and INTERSECT with Their EXISTS and NOT EXISTS Equivalents</strong></td> 
  </tr> 
 </tbody> 
</table>

以下的示例，我们将演示本帖前述的所有内容，先看 EXCEPT、INTERSECT 的示例，再看与他们俩对等的 NOT EXISTS、EXISTS 写法示例 (不同写法，但会返回相同的结果集)。

![ExpandedBlockStart.gif][] 完整示例

![复制代码][copycode.gif]

1 \--  Create our test tables and populate them with a few relevant rows   
  2  CREATE TABLE UnionTest1  
 3 (  
 4 idcol int IDENTITY,  
 5 col2 char( **3**),  
 6 );  
 7  
 8 CREATE TABLE UnionTest2  
 9 (  
10 idcol int IDENTITY,  
11 col4 char( **3**),  
12 );  
13  
14 INSERT INTO UnionTest1 VALUES ( ' AAA '),( ' BBB '),( ' CCC ');  
15 INSERT INTO UnionTest2 VALUES ( ' CCC '),( ' DDD '),( ' EEE ');  
16  
17  
18 PRINT ' 测试 1, Source and content of both tables: ';  
19  
20 SELECT **1** AS SourceTable, col2 AS Value FROM UnionTest1  
21 UNION ALL  
22 SELECT **2**, col4 FROM UnionTest2;  
23  
24  
25 PRINT ' 测试 2, Results with classic UNION ';  
26  
27 SELECT col2 FROM UnionTest1  
28 UNION  
29 SELECT col4 FROM UnionTest2;  
30  
31  
32 PRINT ' 测试 3-1, Results with EXCEPT ';  
33  
34 SELECT col2 FROM UnionTest1  
35 EXCEPT  
36 SELECT col4 FROM UnionTest2;  
37  
38  
39 PRINT ' 测试 3-2, Equivilent of EXCEPT but using NOT EXISTS ';  
40  
41 SELECT col2 FROM UnionTest1 ut1  
42 WHERE NOT EXISTS  
43 ( SELECT col4 FROM UnionTest2 WHERE col4 = ut1.col2);  
44  
45  
46 PRINT ' 测试 4-1, Results with INTERSECT ';  
47  
48 SELECT col2 FROM UnionTest1  
49 INTERSECT  
50 SELECT col4 FROM UnionTest2;  
51  
52  
53 PRINT ' 测试 4-2, Equivilent of INTERSECT but using EXISTS ';  
54  
55 SELECT col2 FROM UnionTest1 ut1  
56 WHERE EXISTS  
57 ( SELECT col4 FROM UnionTest2 WHERE col4 = ut1.col2);  
58  
59  
60 PRINT ' 测试 5, INTERSECT 运算符优先于 EXCEPT ';  
61 SELECT col2 FROM UnionTest1  
62 EXCEPT  
63 SELECT col2 FROM UnionTest1  
64 INTERSECT  
65 SELECT col4 FROM UnionTest2;  
66  
67  
68 \--  Clean up after ourselves   
 69  DROP TABLE UnionTest1;  
70 DROP TABLE UnionTest2;

![复制代码][copycode.gif]

执行结果：

如您所见，测试 3-1、3-2 的结果相同，测试 4-1、4-2 的结果也相同，但 SQL Server 的「评估的执行计划」很不同，且 EXCEPT / INTERSECT 的「查询开销」会比 NOT EXISTS / EXISTS 大很多，如同上例中，测试 3-1 比 3-2 的性能差，测试 4-1 比 4-2 性能差。而且经我测试 (书籍作者 \[7\])，大多数的情形，用 EXISTS 的写法，性能都比 EXCEPT / INTERSECT 要好。

虽然如此，但我们不该完全舍弃 EXCEPT / INTERSECT 的使用，因为他们的语法简洁、容易阅读。因此在您的 SQL 语句中，除非两种写法的性能差距很大，不然我仍建议使用 EXCEPT / INTERSECT 写法，以便项目后续的维护。若论 EXISTS 和 EXCEPT / INTERSECT 哪一种写法较好，则属见仁见智的问题，端视您的数据量、执行环境、实测两种写法的效率差距，来决定要用哪一种写法。

[copycode.gif]: /images/20220605/9046b4d041de4887a31d34cbb2279cdf.png
[111001_01.jpg]: /images/20220605/ebef31044c8a40d1a9e7dbd53b7a3442.png
[ExpandedBlockStart.gif]: /images/20220605/c9d306c5c34e4852a353242c10eeef06.png