Introduction to the usage of exists and except in SQL Server

Introduction to the usage of exists and except in SQL Server

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:
  • Usage of sqlserver exists, not exists

<<:  Service management of source package installation under Linux

>>:  Detailed explanation of how to dynamically set the browser title in Vue

Recommend

The corresponding attributes and usage of XHTML tags in CSS

When I first started designing web pages using XH...

TCP third handshake data transmission process diagram

The process packets with the SYN flag in the RFC7...

Summary of knowledge points about events module in Node.js

Through the study and application of Node, we kno...

Vue implements the countdown component for second kills

This article shares the specific code of Vue to i...

Sample code for implementing the Olympic rings with pure HTML+CSS

Rendering Code - Take the blue and yellow rings a...

Analysis of the Linux input subsystem framework principle

Input subsystem framework The linux input subsyst...

Vue installation and use

Table of contents 1. Vue installation Method 1: C...

Introduction to who command examples in Linux

About who Displays users logged into the system. ...

This article will show you how to use SQL CASE WHEN in detail

Table of contents Simple CASEWHEN function: This ...

About the solution record of the page unresponsiveness when using window.print() in React

Table of contents 1. Background of the problem: 2...

Pure HTML+CSS to achieve typing effect

This article mainly introduces the typing effect ...

How to bind Docker container to external IP and port

Docker allows network services to be provided by ...

Introduction to the differences between HTML name, id, class (format/application scenario/features), etc.

In a page, there are many controls (elements or ta...

SQL ROW_NUMBER() and OVER() method case study

Syntax format: row_number() over(partition by gro...

Detailed explanation of the differences and applications of {{}}, v-text and v-html in Vue

{ {}} Get the value, the original content of the ...