SQL Server 2005 EXCEPT和INTERSECT运算符

--> Title : SQL Server 2005EXCEPT和INTERSECT运算符

--> Author : wufeng4552

--> Date : 2009-10-30

(一)概念

EXCEPT和INTERSECT运算符使您可以比较两个或多个SELECT语句的结果并返回非重复值。

(二)區別

EXCEPT运算符返回由EXCEPT运算符左侧的查询返回、而又不包含在右侧查询所返回的值中的所有非重复值。

INTERSECT返回由INTERSECT运算符左侧和右侧的查询都返回的所有非重复值。

(三)注意點

(3.1) 使用EXCEPT或INTERSECT比较的结果集必须具有相同的结构。它们的列数必须相同，并且相应的结果集列的数据类型必须兼容

(3.2) INTERSECT运算符优先于EXCEPT

(3.3) SELECT INTO必须是包含INTERSECT或EXCEPT运算符的语句中的第一个查询，用来创建容纳最终结果集的表

(3.4)ORDER BY子句中的列名或别名必须引用左侧查询返回的列名

(四)實例

SET NOCOUNT ON

--建立測試數據

DECLARE @T1 TABLE(ID INT,[NAME] VARCHAR(10))

INSERT @T1 SELECT 1,'A'

INSERT @T1 SELECT 1,'A'

INSERT @T1 SELECT 2,'B'

INSERT @T1 SELECT 2,'C'

INSERT @T1 SELECT 3,NULL

INSERT @T1 SELECT NULL,'D'

INSERT @T1 SELECT NULL,NULL

DECLARE @T2 TABLE(ID INT,[NAME] VARCHAR(10))

INSERT @T2 SELECT 1,'A'

INSERT @T2 SELECT 2,'E'

INSERT @T2 SELECT 3,'F'

DECLARE @T3 TABLE(ID INT,[NAME] VARCHAR(10))

INSERT @T3 SELECT 4,'G'

INSERT @T3 SELECT 3,NULL

INSERT @T3 SELECT 5,'H'

--1 EXCEPT从左查询中返回右查询沒有找到的所有非重复值。

--找出@T1表的中不存在@T2表的所有非重复值

SELECT * FROM @T1

EXCEPT

SELECT * FROM @T2

/*

ID NAME

----------- ----------

NULL NULL

NULL D

2 B

2 C

3 NULL

*/

--MSSQL2000的版本用NOT EXISTS实现EXCEPT的功能

SELECT DISTINCT * FROM @T1 T

WHERE NOT EXISTS(SELECT 1 FROM @T2 WHERE ID=T.ID AND [NAME]=T.[NAME])

/*

ID NAME

----------- ----------

NULL NULL

NULL D

2 B

2 C

3 NULL

*/

/*

--sql 2000,not in是得不到上述结果的

--空值表示值未知。空值不同于空白或零值。没有两个相等的空值。

--比较两个空值或将空值与任何其他值相比均返回未知，这是因为每个空值均为未知。

--使用IN或NOT IN比较后返回的所有空值都将返回UNKNOWN。

--将空值与IN或NOT IN一起使用会产生意外结果。

*/

--看以下實例

SELECT ID FROM @T1

EXCEPT

SELECT ID FROM @T2

/*

ID

-----------

NULL

*/

SELECT DISTINCT ID FROM @T1 T

WHERE NOT EXISTS(SELECT 1 FROM @T2 WHERE ID=T.ID)

/*

ID

-----------

NULL

*/

SELECT * FROM @T1 WHERE ID NOT IN(SELECT ID FROM @T2)

/*

ID NAME

----------- ----------

*/

--2 INTERSECT 返回 INTERSECT 操作数左右两边的两个查询都返回的所有非重复值

SELECT * FROM @T1

INTERSECT

SELECT * FROM @T2

/*

ID NAME

----------- ----------

1 A

*/

-- 3INTERSECT运算符优先于EXCEPT

SELECT * FROM @T1

EXCEPT

SELECT * FROM @T2

INTERSECT

SELECT * FROM @T3

/*

ID NAME

----------- ----------

NULL NULL

NULL D

1 A

2 B

2 C

3 NULL

*/

(SELECT * FROM @T1

EXCEPT

SELECT * FROM @T2)

INTERSECT

SELECT * FROM @T3

/*

ID NAME

----------- ----------

3 NULL

*/