Introduction to the usage of exists and except in SQL Server

1. exists

1.1 Description
1.2 Examples
1.3 intersect/2017-07-21

2. except

2.1 Description
2.2 Examples

3. Test Data

1. exists

1.1 Description

The return value of the EXISTS (including NOT EXISTS) clause is a BOOL value. There is a subquery statement inside EXISTS (SELECT ... FROM ...), which I call the inner query statement of EXIST. The query statement returns a result set.

The EXISTS clause returns a Boolean value depending on whether the result set of the query statement is empty or not.

exists: emphasizes whether a result set is returned, not what is returned. For example: select name from student where sex = 'm' and mark exists(select 1 from grade where ...) . As long as the clause introduced by exists returns a result set, the exists condition is met. Please note that the returned field is always 1. If it is changed select 2 from grade where ... , the returned field is 2. This number is meaningless. So the exists clause does not care what is returned, but whether there is a result set returned. EXISTS = IN, the meaning is the same but the syntax is slightly different. It seems that using IN is less efficient, probably because the index will not be executed.

Compared with inner join, exists has better performance. When it finds the first record that meets the conditions, it will immediately stop searching and return TRUE.

1.2 Examples

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

1.3 intersect/2017-07-21

intersect works similarly to exists.

--intersect
--SQL:
select name from family_member where group_level > 0
intersect
select name from family_grade where grade > 90

--result:
name
cherrie

2. except

2.1 Description

In the query results, EXCEPT = NOT EXISTS, INTERSECT = EXISTS, but the "query overhead" of EXCEPT/INTERSECT is much greater than that of NOT EXISTS/EXISTS.

Except automatically removes duplicates, but not in/not exists does not.

2.2 Examples

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

3. Test Data

When verifying the except deduplication function, a rabbit is added to family_member.

-- ----------------------------
-- 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'father')
GO
GO
INSERT INTO [mazeytop].[family_part] ([id], [group], [group_name]) VALUES (N'2', N'2', N'mother')
GO
GO
INSERT INTO [mazeytop].[family_part] ([id], [group], [group_name]) VALUES (N'3', N'3', N'daughter')
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

This is the end of this article about the usage of exists and except in SQL Server. For more relevant content about exists and except in SQL Server, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: