和 UNION 指令类似, INTERSECT 也是对两个 SQL 语句所产生的结果做处理的。不同的地方是, UNION 基本上是一个 OR (如果这个值存在于第一句或是第二句,它就会被选出),而 INTERSECT 则比较像 AND ( 这个值要存在于第一句和第二句才会被选出)。 UNION 是联集,而 INTERSECT 是交集

INTERSECT 的语法如下: 

[SQL语句 1]
INTERSECT
[SQL语句 2] 

假设我们有以下的两个表格, 

A表格
Place  Price Date  
Los Angeles  $1500  Jan-05-1999  
San Diego  $250  Jan-07-1999  
Los Angeles  $300  Jan-08-1999  
Boston  $700  Jan-08-1999  

B表格 
Date  Price  
Jan-07-1999  $250  
Jan-10-1999  $535  
Jan-11-1999  $320  
Jan-12-1999  $750  

而我们要找出哪几天有店面交易和网络交易。要达到这个目的,我们用以下的 SQL 语句: 

SELECT Date FROM A
INTERSECT
SELECT Date FROM B

结果: 

Date 
Jan-07-1999 

请注意,在 INTERSECT 指令下,不同的值只会被列出一次。

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 写法:

ITesseract 的语言 intersect语句_ITesseract 的语言

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

ITesseract 的语言 intersect语句_ITesseract 的语言

上方示例、下方示例,执行结果皆为:


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 在查询时,各方面并无二致。

Using EXISTS in Other Ways

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

ITesseract 的语言 intersect语句_ITesseract 的语言

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] (
...
...

ITesseract 的语言 intersect语句_ITesseract 的语言

 

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

ITesseract 的语言 intersect语句_ITesseract 的语言

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

ITesseract 的语言 intersect语句_ITesseract 的语言

 

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

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

The INTERSECT and EXCEPT Operators

 接下来介绍的 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],例如:所有查询中的列数和列的顺序必须相同、数据类型必须兼容。


ITesseract 的语言 intersect语句_ITesseract 的语言_07


图 1 UNION 中若有重复的行,会被移除,只留下一个

 

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

EXCEPT

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> [, ...])

 

INTERSECT

至于 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


Comparing EXCEPT and INTERSECT with Their EXISTS and NOT EXISTS Equivalents

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

ITesseract 的语言 intersect语句_结果集_08

ITesseract 的语言 intersect语句_ITesseract 的语言

 

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;

ITesseract 的语言 intersect语句_ITesseract 的语言

 

执行结果:


ITesseract 的语言 intersect语句_ITesseract 的语言_11


测试 1 结果          

ITesseract 的语言 intersect语句_结果集_12


测试 2 结果       

ITesseract 的语言 intersect语句_ITesseract 的语言_13


测试 3-1、3-2 结果     

ITesseract 的语言 intersect语句_ITesseract 的语言_14


测试 4-1、4-2 结果     

ITesseract 的语言 intersect语句_ITesseract 的语言_15


测试 5 结果

如您所见,测试 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 哪一种写法较好,则属见仁见智的问题,端视您的数据量、执行环境、实测两种写法的效率差距,来决定要用哪一种写法。