SQLServer中exists、except、intersect用法
一、exists
1.1 说明
EXISTS(包括 NOT EXISTS)子句的返回值是一个BOOL值。EXISTS内部有一个子查询语句(SELECT ... FROM...)，我将其称为EXIST的内查询语句。其内查询语句返回一个结果集。EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。
exists：强调的是是否返回结果集，不要求知道返回什么，比如：select name from student where sex = 'm' and mark exists(select 1 from grade where ...) ，只要exists引导的子句有结果集返回，那么exists这个条件就算成立了，大家注意返回的字段始终为1，如果改成“select 2 from grade where ...”，那么返回的字段就是2，这个数字没有意义。所以exists子句不在乎返回什么，而是在乎是不是有结果集返回。EXISTS = IN，意思相同不过语法上有点点区别，好像使用IN效率要差点，应该是不会执行索引的原因。
相对于inner join，exists性能要好一些，当她找到第一个符合条件的记录时，就会立即停止搜索返回TRUE。
1.2 示例
--EXISTS --SQL: select name from family_member where group_level > 0 and exists(select 1 from family_grade where family_member.name = family_grade.name and grade > 90) --result: name cherrie --NOT EXISTS --SQL: select name from family_member where group_level > 0 and not exists(select 1 from family_grade where family_member.name = family_grade.name and grade > 90) --result: name mazey rabbit
二、except
2.1 说明
查询结果上EXCEPT = NOT EXISTS，INTERSECT = EXISTS，但是EXCEPT / INTERSECT的「查询开销」会比NOT EXISTS / EXISTS大很多。
except自动去重复，not in / not exists不会。
2.2 示例
--except --SQL: select name from family_member where group_level > 0 except(select name from family_grade) --result: name rabbit --NOT EXISTS --SQL: select name from family_member where group_level > 0 and not exists(select name from family_grade where family_member.name = family_grade.name) --result: name rabbit rabbit
三、测试数据
-- ---------------------------- -- Table structure for family_grade -- ---------------------------- DROP TABLE [mazeytop].[family_grade] GO CREATE TABLE [mazeytop].[family_grade] ( [id] int NOT NULL , [name] varchar(20) NULL , [grade] int NULL ) GO -- ---------------------------- -- Records of family_grade -- ---------------------------- INSERT INTO [mazeytop].[family_grade] ([id], [name], [grade]) VALUES (N'1', N'mazey', N'70') GO GO INSERT INTO [mazeytop].[family_grade] ([id], [name], [grade]) VALUES (N'2', N'cherrie', N'93') GO GO -- ---------------------------- -- Table structure for family_member -- ---------------------------- DROP TABLE [mazeytop].[family_member] GO CREATE TABLE [mazeytop].[family_member] ( [id] int NOT NULL , [name] varchar(20) NULL , [sex] varchar(20) NULL , [age] int NULL , [group_level] int NULL ) GO -- ---------------------------- -- Records of family_member -- ---------------------------- INSERT INTO [mazeytop].[family_member] ([id], [name], [sex], [age], [group_level]) VALUES (N'1', N'mazey', N'male', N'23', N'1') GO GO INSERT INTO [mazeytop].[family_member] ([id], [name], [sex], [age], [group_level]) VALUES (N'2', N'cherrie', N'female', N'22', N'2') GO GO INSERT INTO [mazeytop].[family_member] ([id], [name], [sex], [age], [group_level]) VALUES (N'3', N'rabbit', N'female', N'15', N'3') GO GO INSERT INTO [mazeytop].[family_member] ([id], [name], [sex], [age], [group_level]) VALUES (N'4', N'rabbit', N'female', N'15', N'3') GO GO -- ---------------------------- -- Table structure for family_part -- ---------------------------- DROP TABLE [mazeytop].[family_part] GO CREATE TABLE [mazeytop].[family_part] ( [id] int NOT NULL , [group] int NULL , [group_name] varchar(20) NULL ) GO -- ---------------------------- -- Records of family_part -- ---------------------------- INSERT INTO [mazeytop].[family_part] ([id], [group], [group_name]) VALUES (N'1', N'1', N'父亲') GO GO INSERT INTO [mazeytop].[family_part] ([id], [group], [group_name]) VALUES (N'2', N'2', N'母亲') GO GO INSERT INTO [mazeytop].[family_part] ([id], [group], [group_name]) VALUES (N'3', N'3', N'女儿') GO GO -- ---------------------------- -- Indexes structure for table family_grade -- ---------------------------- -- ---------------------------- -- Primary Key structure for table family_grade -- ---------------------------- ALTER TABLE [mazeytop].[family_grade] ADD PRIMARY KEY ([id]) GO -- ---------------------------- -- Indexes structure for table family_member -- ---------------------------- -- ---------------------------- -- Primary Key structure for table family_member -- ---------------------------- ALTER TABLE [mazeytop].[family_member] ADD PRIMARY KEY ([id]) GO -- ---------------------------- -- Indexes structure for table family_part -- ---------------------------- -- ---------------------------- -- Primary Key structure for table family_part -- ---------------------------- ALTER TABLE [mazeytop].[family_part] ADD PRIMARY KEY ([id]) GO
三、EXCEPT、INTERSECT
EXCEPT是指在第一个集合中存在，但是不存在于第二个集合中的数据。
INTERSECT是指在两个集合中都存在的数据。
示例：
create table t1(id int,mark char(2)) go create table t2(id int,mark char(2)) go insert into t1 select 1,'t1' union all select 2,'t2' union all select 3,'t3' union all select 4,'t4' go insert into t2 select 2,'t2' union all select 3,'m3' union all select 5,'m5' union all select 6,'t6' go select * from t1 EXCEPT select * from t2 go select * from t1 INTERSECT select * from t2 go --EXCEPT结果集为 --1 t1 --3 t3 --4 t4 --INTERSECT结果集为 --2 t2
EXCEPT和INTERSECT的优先级：
为了测试它们之间的优先级，运行下面的测试代码：
create table t3(int id,mark char(2)) go insert into t3 select 3,'t3' union all select 3,'r3' union all select 5,'m5' union all select 5,'r5' union all select 7,'b7' union all select 8,'b8' go select * from t1 EXCEPT select * from t2 INTERSECT select * from t3 --运行结果 --1 t1 --2 t2 --3 t3 --4 t4
为什么会出现如上结果呢，请看下面的执行计划：
原来t2和t3先进行的INTERSECT运算，得出5m5结果集，再和t1进行EXCEPT运算。
原文博主比较专业，一来例子看来简单易懂，二来描述比较全面，不光讲了两个关键字的用法，也讲到了它们的优先级。
『引申』
也许，你会说我完全可以设置条件，达到类似的过滤效果，例如：简单的一个exists：
select * from table1 where not exists (
select 1 from table2 where table1.ID=table2.ID
)
以上语句就实现了except的效果。
select * from table1 inner join table2 on table1.ID=table2.ID
这就达到了intersect的效果，那么，它们的价值在哪里呢？
- - ，也许已经有人看出了问题所在，是的！使用条件过滤，比较的是表中的ID，而使用这两个关键字，比较的是整行记录，而如果我们要自己写sql比较整行记录，相信这是非常令人头疼的事情，并且，有可能写出来的sp具有很大的性能问题。
我相信，它们会是合并结果集，过滤结果集的一把利器。
