SQL SERVER 2008的EXCEPT和INTERSECT

S QL SERVER 2008的EXCEPT和INTERSECT

EXCEPT：用第一个集合与第二个集合比较，返回只存在于第一个集合，但不在第二个集合中的值。

INTERSECT:比较两个集合，返回两个集合都有的行。

我们新建一个表

CREATE TABLE projectPerson

(

　　personId varchar(10),

　　projectId varchar(10),

　　PRIMARY KEY (personId,projectId)

)

GO


INSERT INTO projectPerson Values('joeb','projBlg')
INSERT INTO projectPerson Values('joeb','projLitter')
INSERT INTO projectPerson Values('fredf','projBlg')
INSERT INTO projectPerson Values('homerr','projLitter')
INSERT INTO projectPerson Values('stevegr','projBlg')
INSERT INTO projectPerson Values('stevegr','projLitter')

GO

--查看谁参与了其中一个项目或两个项目都参加

SELECT personId
FROM projectPerson
WHERE projectId='projBig'
UNION
SELECT personId
FROM projectPerson
WHERE projectId='projLitter'

--查看只参加projLittle项目但不参与projBig项目的人

1.以前sql server 2000的写法

SELECT personId
FROM projectPerson AS projLittle
WHERE projectId='projLitter'
  AND NOT EXISTS
    (SELECT *
     FROM projectPerson AS projBig
     WHERE projBig.projectId='projBig'
       AND projBig.personId=projLittle.personId)

2.现在只需要用except即可

SELECT personId
FROM projectPerson
WHERE projectId='projLitter' EXCEPT
  SELECT personId
  FROM projectPerson WHERE projectId='projBig'

--两个项目都参加的人的信息可用intersect:

SELECT personId
FROM projectPerson
WHERE projectId='projLitter' INTERSECT
  SELECT personId
  FROM projectPerson WHERE projectId='projBig'