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/', 'CN');
INSERT INTO `websites` VALUES ('3', '百度', 'https://www.baidu/', 'CN');
INSERT INTO `websites` VALUES ('4', '微博', 'http://weibo/', 'CN');
INSERT INTO `websites` VALUES ('5', 'Facebook', 'https://www.facebook/', '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)不成立的记录
更多推荐
[Mysql] EXISTS | NOT EXISTS
发布评论