🚀 个人主页 极客小俊
✍🏻 作者简介：程序猿、设计师、技术分享
🐋 希望大家多多支持, 我们一起学习和进步！
🏅 欢迎评论 ❤️点赞💬评论 📂收藏 📂加关注
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相反，意思差不多，这里就过多赘述了!
"👍点赞" "✍️评论" "收藏❤️"欢迎一起交流学习❤️❤️💛💛💚💚
好玩 好用 好看的干货教程可以点击下方关注❤️微信公众号❤️
说不定有意料之外的收获哦..🤗嘿嘿嘿、嘻嘻嘻🤗!
🌽🍓🍎🍍🍉🍇
扫一扫
1001
到【灌水乐园】发言
