Document

🚀 个人主页 极客小俊
✍🏻 作者简介：程序猿、设计师、技术分享
🐋 希望大家多多支持, 我们一起学习和进步！
🏅 欢迎评论 ❤️点赞💬评论 📂收藏 📂加关注

NOT EXISTS的定义

NOT EXISTS专门是用来检测一些子查询的操作符，主要是检查一个子查询是否不返回任何行

通俗的说，NOT EXISTS用于确定一个集合是否为空吗，

如果子查询的结果集为空，那么NOT EXISTS返回TRUE，否则返回FALSE,

应用场景

这里我们简单先看一个案例，假设我们有两个表：Students(学生表)和Enrollments(选课表)

表字段设计

学生表：包含学生ID、姓名等信息

选课表：包含学生ID和他们所选课程

创建表结构

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName NVARCHAR(50),
    Status NVARCHAR(20) -- 假设有一个状态字段，用于标记学生是否活跃
);

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseName NVARCHAR(50),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

如图

添加数据

-- 插入学生数据
INSERT INTO Students (StudentID, StudentName, Status) VALUES (1, '张三', 'yes');
INSERT INTO Students (StudentID, StudentName, Status) VALUES (2, '李四', 'yes');
INSERT INTO Students (StudentID, StudentName, Status) VALUES (3, '王五', 'yes');
INSERT INTO Students (StudentID, StudentName, Status) VALUES (4, '赵六', 'no'); 


-- 插入选课数据
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseName) VALUES (1, 1, '数学');
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseName) VALUES (2, 1, '英语');
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseName) VALUES (3, 2, '物理');
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseName) VALUES (4, 3, '英语');

如图

查询

我们现在先用JOIN查询一下，哪些学生对应选修了哪些课程!

如下

SELECT * FROM Students AS s LEFT JOIN Enrollments AS e ON s.StudentID = e.StudentID;

如图

问题需求:

现在我们想要找出哪些没有选修任何课程的学生, 那么这个时候NOT EXISTS语句就很有用了!

如下

SELECT s.StudentID, s.StudentName FROM Students s WHERE NOT EXISTS (SELECT * FROM Enrollments as e WHERE e.StudentID = s.StudentID);

如图

查询分析

首先我们来逐一拆解一下~~

主查询

SELECT s.StudentID, s.StudentName FROM Students AS s

我们的目的就是要从Students表中选择StudentID和StudentName字段显示, 并且这里我给Students表起了一个别名s，方便后续在查询中引用这个表! 这肯定大家都没什么问题!

子查询

那么重点就在于我们的子查询WHERE NOT EXISTS (...) 这里!

如下

SELECT * FROM Enrollments AS e WHERE e.StudentID = s.StudentID

子查询是针对Enrollments表的一个查询，目的是找出所有选课记录中，学生ID（StudentID）与主查询中当前处理的学生ID相匹配的记录

这里的关键是WHERE e.StudentID = s.StudentID的条件, 这里保证了子查询只查找与主查询当前处理的学生相关的选课记录!

当主查询开始处理Students表中的第一行（或第一个学生）时，它会暂停，并执行子查询，看看这个学生是否有选课记录

并且子查询会遍历Enrollments表，查找所有StudentID与当前``学生ID`相匹配的记录!

如果子查询找到了至少一条匹配的记录，那么NOT EXISTS条件就会评估为FALSE，那么当前这个学生就不会被包括在最终的结果集中!

如果子查询没有找到任何匹配的记录（即这个学生没有选课），那么NOT EXISTS条件就会评估为TRUE，当前这个学生就会被包括在最终的结果集中!

如图

也就是说把这些对不上号的放入到结果集中!

注意

在EXISTS或NOT EXISTS子查询中的SELECT子句后面的内容并不重要，所以这两个运算符只关心子查询是否返回了行，而不关心这些行具体包含了什么数据。

所以子查询中使用SELECT *、SELECT 1或SELECT NULL在功能上是一样的!

在这里我用的SELECT *但实际上，使用SELECT 1或SELECT NULL也会得到相同的结果！

其实我们用IS NULL也一样可以查询到同样的结果:

SELECT s.StudentID,s.StudentName FROM Students AS s LEFT JOIN Enrollments AS e ON s.StudentID = e.StudentID WHERE  e.StudentID IS NULL;

如图

所以换句话说，NOT EXISTS 用于筛选出那些不存在满足特定条件的相关记录的数据!

这里我说的特定条件通常是通过子查询的 WHERE 子句来确定的，而且这个条件通常涉及到与外层查询的字段进行关联!

更新

NOT EXISTS还可以用于更新那些不满足某个子查询条件的记录

例如

将所有没有选课学生的状态标记为no

比如现在数据如下:

修改语句

UPDATE Students SET Status = 'no'
WHERE NOT EXISTS ( SELECT * FROM Enrollments WHERE Enrollments.StudentID = Students.StudentID);

如图

结果如下

删除

比如我们现在可以使用NOT EXISTS删除那些不满足某个子查询条件的记录

例如 删除没有选课的学生记录!!

DELETE FROM Students WHERE NOT EXISTS (SELECT * FROM Enrollments WHERE Enrollments.StudentID = Students.StudentID);

如图

再举个例子

比如说现在有这样一个数据表如下

如图

问题

现在我们要查询都有哪些国家的人年龄在30~50岁之间?
其中还有一个附带条件,如果某个国家里面有一个人不满足30~50岁之间这个条件这个国家就要排除!

SQL如下

SELECT distinct address,lastName FROM Persons AS tmp1 
WHERE NOT EXISTS(SELECT * FROM Persons AS tmp2
WHERE tmp1.address = tmp2.address AND (tmp2.age<=30 OR tmp2.age>=50));

结果如下

我们来简单分析一下，首先这是一个单表查询!

我们要查的是满足条件的国家有哪些!

重点还是这个子查询

如下

NOT EXISTS(SELECT * FROM Persons AS tmp2 WHERE tmp1.address = tmp2.address AND (tmp2.age<=30 OR tmp2.age>=50));

因为查询的逻辑要求整个地区内不能有任何人的年龄小于等于30岁或大于等于50岁，
如果有一个人的年龄满足这些条件，那么整个地区就会被排除在查询结果之外。

所以你要把下面看成一个整体

WHERE tmp1.address = tmp2.address AND (tmp2.age<=30 OR tmp2.age>=50));

其实也就是把下面查询出来的信息要过滤掉

SELECT * FROM Persons WHERE (age<=30 OR age>=50)

所以道理是一样的! 都是要满足这个NOT EXISTS(不存在)的情况下~

同理EXISTS正好和NOT EXISTS相反，意思差不多，这里就过多赘述了!