列子查询(多行子查询)
子查询返回的是多行单列的数据,就是一列数据。多行子查询也称为集合比较子查询
在使用多行子查询需要使用多行比较操作符:
操作符 | 含义 |
IN | 等于列表中的任意一个,在某个集合中找到了就是成立的,IN和=ANY是一样的 |
ANY | 需要和单行比较操作符一起使用(>、<、=、<>...),与子查询结果中任何一个值比较,一个成立就成立 |
ALL | 需要和单行比较操作符一起使用(>、<、=、<>...),和子查询返回的所有值比较,所有条件同时成立才成立 |
SOME | 实际上是ANY的别名,作用与ANY相同,所以一般使用ANY |
IN操作符
MySQL --- 数据库查询 - 条件查询 IN的使用
MySQL --- 多表查询 - 笛卡尔积和正确的多表查询、等值连接和不等值连接、内连接和外连接 ON一般与JOIN一起使用
MySQL --- 多表查询 - 七种JOINS实现、集合运算、多表查询练习
IN 运算符用来判断表达式的值是否位于给出的列表中;如果是,返回值为 1,否则返回值为 0。
NOT IN 的作用和 IN 恰好相反,NOT IN 用来判断表达式的值是否不存在于给出的列表中;如果不是,返回值为 1,否则返回值为 0。
- 查询出与每个部门中最低工资相同的员工信息
- 按照部门分组,统计每个部门的最低工资
- 根据最低工资查询出员工信息
报错,子查询返回最多只有一行,但是需求要实现多行,需要把等于5000的、等于800的、等于950的、等于1300的薪资输出
使用IN操作符,当工资在这个集合里面的时候,把对应的员工进行输出
-- 查询出每个员工信息
-- SELECT * FROM emp WHERE sal=();
-- 得到整个部门的最低工资之后按部门编号进行分组
SELECT MIN(sal) FROM emp GROUP BY deptno;
-- 查询出与每个部门中最低工资相同的员工信息
SELECT * FROM emp WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno);
把部门编号为NULL的员工信息去掉
-- 查询出每个员工信息
-- SELECT * FROM emp WHERE sal=();
-- 得到整个部门的最低工资之后按部门编号进行分组
SELECT MIN(sal) FROM emp GROUP BY deptno;
-- 查询出与每个部门中最低工资相同的员工信息
SELECT * FROM emp WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno HAVING deptno IS NOT NULL);
查询出不与每个部门中最低工资相同的员工信息
SELECT * FROM emp WHERE sal NOT IN(SELECT MIN(sal) FROM emp GROUP BY deptno HAVING deptno IS NOT NULL);
ANY操作符
ANY关键字是一个MySQL运算符,如果子查询条件中ANY的比较结果为TRUE,则它会返回布尔值TRUE
- 查询工资比任何管理工资都要高的员工信息
- 查找出每个管理的薪资
- 每个员工的薪资与每个管理的薪资比较
-- 查询出每个员工信息
-- SELECT * FROM emp WHERE sal=();
-- 得到所有管理的工资
SELECT sal FROM emp WHERE job='MANAGER';
-- 查询工资比任何一个管理工资高的员工信息
报错,子查询最多应该要返回一行,最多使用一个值进行比较,一个值不能与多个值进行比较,不知道是与2975比较、还是与2850比较、还是与2450比较
-- 查询出每个员工信息
-- SELECT * FROM emp WHERE sal=();
SELECT sal FROM emp WHERE job='MANAGER';
-- 查询工资比任何一个管理工资高的员工信息
SELECT * FROM emp WHERE sal>=ANY(SELECT sal FROM emp WHERE job='MANAGER');
ALL操作符
ALL关键字是一个MySQL运算符,如果子查询条件中ALL的比较结果为TRUE,则它会返回布尔值TRUE
- 案例同ANY操作符
不存在一个员工的薪资等于2975、等于2850、等于2450
-- 查询出每个员工信息
-- SELECT * FROM emp WHERE sal=();
SELECT sal FROM emp WHERE job='MANAGER';
-- 查询工资等于所有管理工资的员工信息
SELECT * FROM emp WHERE sal=ALL(SELECT sal FROM emp WHERE job='MANAGER');
总结
IN 判断是否在集合内,在集合内返回真,不在集合内返回假
ANY 判断是否在集合内(和IN一样)
>ANY 大于集合中值最小的那一个
<ANY 小于集合中值最大的那一个
<>ANY 与集合中任意一个不等都成立
= ALL 与集合中每个值都相等才成立
> ALL 比集合中最大的那个值还大
< ALL 比集合中最小的那个值还小
<> ALL 与集合中每个值都不相等