SQLServer中exists和except用法

一、exists

1.1 說(shuō)明

EXISTS(包括 NOT EXISTS)子句的返回值是一個(gè)BOOL值。EXISTS內(nèi)部有一個(gè)子查詢(xún)語(yǔ)句(SELECT ... FROM...)，我將其稱(chēng)為EXIST的內(nèi)查詢(xún)語(yǔ)句。其內(nèi)查詢(xún)語(yǔ)句返回一個(gè)結(jié)果集。EXISTS子句根據(jù)其內(nèi)查詢(xún)語(yǔ)句的結(jié)果集空或者非空，返回一個(gè)布爾值。Link

exists：強(qiáng)調(diào)的是是否返回結(jié)果集，不要求知道返回什么，比如：select name from student where sex = 'm' and mark exists(select 1 from grade where ...) ，只要exists引導(dǎo)的子句有結(jié)果集返回，那么exists這個(gè)條件就算成立了，大家注意返回的字段始終為1，如果改成“select 2 from grade where ...”，那么返回的字段就是2，這個(gè)數(shù)字沒(méi)有意義。所以exists子句不在乎返回什么，而是在乎是不是有結(jié)果集返回。EXISTS = IN，意思相同不過(guò)語(yǔ)法上有點(diǎn)點(diǎn)區(qū)別，好像使用IN效率要差點(diǎn)，應(yīng)該是不會(huì)執(zhí)行索引的原因。Link

相對(duì)于inner join，exists性能要好一些，當(dāng)她找到第一個(gè)符合條件的記錄時(shí)，就會(huì)立即停止搜索返回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 說(shuō)明

查詢(xún)結(jié)果上EXCEPT = NOT EXISTS，INTERSECT = EXISTS，但是EXCEPT / INTERSECT的「查詢(xún)開(kāi)銷(xiāo)」會(huì)比NOT EXISTS / EXISTS大很多。

except自動(dòng)去重復(fù)，not in / not exists不會(huì)。

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

三、測(cè)試數(shù)據(jù)

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

SQLServer中exists和except用法