[Mysql] EXISTS | NOT EXISTS_mysql 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后的执行顺序，注意，是先执行外查询再执行内查询 

上述代码详细步骤:

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

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

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

4.外查询的所有数据逐行查询匹对(重复上述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:部门编号 

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

-- 解法1
SELECT * 
FROM employees AS a
WHERE NOT EXISTS(SELECT emp_no
                 FROM dept_emp AS b
                 WHERE a.emp_no = b.emp_no);
 
-- 解法2
SELECT *
FROM employees
WHERE 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)不成立的记录