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

MySQL sorting using index scan

Table of contents Install sakila Index Scan Sort ...

Introduction to the use of form OnSubmit and input type=image

If there is an <input type="image">...

VirtualBox CentOS7.7.1908 Python3.8 build Scrapy development environment [graphic tutorial]

Table of contents environment Install CentOS Conf...

How to use vue3 to build a material library

Table of contents Why do we need a material libra...

React implements dynamic pop-up window component

When we write some UI components, if we don't...

How to assign a public IP address to an instance in Linux

describe When calling this interface, you need to...

Detailed explanation of redo log and undo log in MySQL

The most important logs in the MySQL log system a...

10 content-related principles to improve website performance

<br />English address: http://developer.yaho...

How to use type enhancement without typingscript

Preface Due to the weak typing of JS, loose writi...

Two ways to introduce svg icons in Vue

How to introduce svg icons in Vue Method 1 of int...

MySQL and MySQL Workbench Installation Tutorial under Ubuntu

Ubuntu install jdk: [link] Install Eclipse on Ubu...

Web page custom selection box Select

Everyone may be familiar with the select drop-dow...

Some points on using standard HTML codes in web page creation

The most common mistake made by many website desi...

Windows Server 2008 R2 Multi-User Remote Desktop Connection Licensing

At work, we often need remote servers and often e...

Application of dynamic image loading technology and use case of jquery.lazyload plug-in

Application example website http://www.uhuigou.net...