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项目的人
以前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)
现在只需要用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'