[Mysql] EXISTS | NOT EXISTS

EXISTS运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False

从英文字面含义理解:EXISTS(存在)、NOT EXISTS(不存在)

语法结构

-- condition用于连接A_table_name与B_table_name

SELECT column_name(s)
FROM A_table_name
WHERE EXISTS (SELECT column_name FROM B_table_name WHERE condition);

带有EXISTS的子查询不返回任何记录的数据，只返回逻辑值True或False

SELECT * FROM A WHERE EXISTS(SELECT * FROM B WHERE A.id = B.id);

首先我们要知道sql语句使用了EXISTS或NOT EXISTS后的执行顺序，注意，是先执行外查询再执行内查询 

上述代码详细步骤:

首先执行外查询SELECT * FROM A，然后从外查询的数据中取出一条数据传给内查询

内查询执行SELECT * FROM B，外查询传入的数据和内查询获得数据根据WHERE后面的条件做匹对，如果存在数据满足A.id = B.id则返回True，如果一条都不满足则返回False

内查询返回True，则外查询的这行数据保留，反之内查询返回False，则外查询的这行数据不显示

外查询的所有数据逐行查询匹对(重复上述1-3步骤，直至所有外查询数据匹对完毕)

NOT EXISTS和EXISTS的用法相反

练习案例1

数据导入

DROP TABLE IF EXISTS `WEBsites`;CREATE TABLE `websites` (  `id` int(11) NOT NULL,  `name` varchar(255) NOT NULL,  `url` varchar(255) DEFAULT NULL,  `country` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO `websites` VALUES ('1', 'Google', 'https://www.google.cm/ ', 'USA');INSERT INTO `websites` VALUES ('2', '淘宝', 'Https://www.taobao.com/', 'CN');INSERT INTO `websites` VALUES ('3', '百度', 'https://www.baidu.com/', 'CN');INSERT INTO `websites` VALUES ('4', '微博', 'http://weibo.com/', 'CN');INSERT INTO `websites` VALUES ('5', 'Facebook', 'https://www.facebook.com/', 'USA');DROP TABLE IF EXISTS `access_log`;CREATE TABLE `access_log` (  `a_id` int(11) NOT NULL,  `site_id` int(11) DEFAULT NULL,  `count` int(11) DEFAULT NULL,  `date` date DEFAULT NULL,  PRIMARY KEY (`a_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO `access_log` VALUES ('1', '1', '45', '2022-05-10');INSERT INTO `access_log` VALUES ('2', '3', '100', '2022-05-13');INSERT INTO `access_log` VALUES ('3', '1', '230', '2022-05-14');INSERT INTO `access_log` VALUES ('4', '2', '10', '2022-05-14');INSERT INTO `access_log` VALUES ('5', '5', '205', '2022-05-14');INSERT INTO `access_log` VALUES ('6', '4', '13', '2022-05-14');INSERT INTO `access_log` VALUES ('7', '3', '220', '2022-05-15');INSERT INTO `access_log` VALUES ('8', '5', '545', '2022-05-16');INSERT INTO `access_log` VALUES ('9', '3', '201', '2022-05-17');

websites表

access_log表(网站访问记录表) 

问题1:查找总访问量(count 字段)大于200的网站是否存在

SELECT a.name, a.url FROM websites AS a WHERE EXISTS (SELECT b.count               FROM access_log AS b               WHERE a.id = b.site_id AND b.count > 200);

结果展示:

 问题2:查找总访问量(count 字段)小于等于200的网站是否存在

SELECT a.name, a.url FROM websites AS a WHERE NOT EXISTS (SELECT b.count                   FROM access_log AS b                   WHERE a.id = b.site_id AND b.count > 200);

结果展示:

练习案例2

数据导入

DROP TABLE IF EXISTS user_info; CREATE TABLE user_info( user_id VARCHAR(8), age INT, gender VARCHAR(8) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; INSERT INTO user_info (user_id,age,gender) VALUE ('u001',18,'male') ,('u002',20,'male') ,('u003',34,'female') ,('u004',23,'female') ,('u005',28,'male'); DROP TABLE IF EXISTS order_info; CREATE TABLE order_info( order_id VARCHAR(8), order_amount INT, user_id VARCHAR(8) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; INSERT INTO order_info (order_id,order_amount,user_id) VALUE ('o001',2000,'u001') ,('o002',1300,'u001') ,('o003',180,'u003') ,('o004',340,'u005') ,('o005',670,'u003');

user_info表(用户信息表)

order_info表(订单信息表) 

问题:查询未购买过商品的用户信息(即在用户信息表中出现，但没在订单信息表中出现的用户信息) 

SELECT * FROM user_info AS u WHERE NOT EXISTS (SELECT *                   FROM order_info AS o                   WHERE o.user_id = u.user_id);

结果展示:

如果用户信息表的某条记录不在订单信息表中，就可以使用NOT EXISTS将该条记录检索出来，由查询结果可以发现，u002和u004两位用户没有购买过商品 

EXISTS的子查询不返回具体的数据，只返回所查询数据的行数。所以一般EXISTS后面的查询通常都用 * 表示，给出具体的列名没有实际意义

练习案例3

数据导入

drop table if exists employees;drop table if exists dept_emp;CREATE TABLE `employees` (  `emp_no` int(11) NOT NULL,  `birth_date` date NOT NULL,  `first_name` varchar(14) NOT NULL,  `last_name` varchar(16) NOT NULL,  `gender` char(1) NOT NULL,  `hire_date` date NOT NULL,  PRIMARY KEY (`emp_no`));CREATE TABLE `dept_emp` (`emp_no` int(11) NOT NULL,`dept_no` char(4) NOT NULL,`from_date` date NOT NULL,`to_date` date NOT NULL,PRIMARY KEY (`emp_no`,`dept_no`));INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');INSERT INTO dept_emp VALUES(10003,'d004','1995-12-03','9999-01-01');INSERT INTO dept_emp VALUES(10004,'d004','1986-12-01','9999-01-01');INSERT INTO dept_emp VALUES(10005,'d003','1989-09-12','9999-01-01');INSERT INTO dept_emp VALUES(10006,'d002','1990-08-05','9999-01-01');INSERT INTO dept_emp VALUES(10007,'d005','1989-02-10','9999-01-01');INSERT INTO dept_emp VALUES(10008,'d005','1998-03-11','2000-07-31');INSERT INTO dept_emp VALUES(10009,'d006','1985-02-18','9999-01-01');INSERT INTO dept_emp VALUES(10010,'d005','1996-11-24','2000-06-26');INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','9999-01-01');

employees表

emp_no:员工编号

dept_emp表 

emp_no:员工编号  dept_no:部门编号 

问题:查找未分配具体部门的员工的所有信息 

-- 解法1SELECT * FROM employees AS aWHERE NOT EXISTS(SELECT emp_no                 FROM dept_emp AS b                 WHERE a.emp_no = b.emp_no);-- 解法2SELECT *FROM employeesWHERE emp_no NOT IN(SELECT emp_no                    FROM dept_emp);

结果展示:

本题解法1中EXISTS用法旨在从employees表中挑选出令(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)不成立的记录 

来源地址：https://blog.csdn.net/Hudas/article/details/124816473