子查询
概念：
出现在其他查询里的查询称为子查询或内查询
内部嵌套其他select语句的查询，叫做主查询或外查询
分类：
按子查询出现的位置:
select 后面：
仅支持标量子查询
from后面
支持表子查询
where或having后面（★）
标量子查询 ★(也叫单行子查询)
列子查询 ★（也叫多行子查询）
行子查询
exists后面（称为相关子查询）
表子查询
按结果集的行列数不同
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有多行多列）
表子查询（结果集随意，一般为多行多列）
特点：
在where和having后面
1 子查询放在小括号里
2 子查询放在条件的右侧
3 标量子查询，一般搭配着单行操作符使用（> < >= 一般搭建条件运算符）
4 列子查询 一般搭配着多行操作符使用（IN、ANY/SOME\ALL）
5 子查询的执行优先主查询
where后面的标量子查询
案例1：
查询工资比某位员工工资高的员工 1首先查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel';
2然后查询员工的信息，满足salary大于1的结果
SELECT *
FROM employees
WHERE salary>(SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
案例2
返回job_id与141员工相同，salary比143员工多的员工姓名和工资
步骤：
先查询141员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141;
查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143;
合在一起查询
SELECT job_id,salary
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE employee_id = 141
)AND salary>(
SELECT salary
FROM employees
WHERE employee_id = 143
);
案例3：
返回公司工资最少的员工的名字，job_id和salary
1 首先查询工资最少的工资
SELECT MIN(salary)
FROM employees;
2 根据最低工资的工资来匹配输出员工的名字，工作和工资得到自己需要的东西
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
);
having后面的标量子查询
案例1：
查询最低工资大于50号部门最低工资的部门id和其最低工资
1 先查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
2 查询每个部门的最低工资
SELECT MIN(salary)
FROM employees
GROUP BY department_id;
3 筛选2中大于1的结果
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
非法使用标量子查询：
单行操作符只能搭建标量子查询
必须对应一行一列，且不能是NULL
多行子查询
返回多行
使用多行比较操作符
| 操作符 | 含义 |
|---|---|
| IN/NOT IN★ | 等于列表中的任意一个 |
| ANY|SOME | 和子查询返回的某一个值比较 |
| ALL | 和子查询返回的所有值比较 |
案例1
返回location_id是1400或1700部门中的所有员工姓名
1 首先查询location_id是1400或者1700的部门
SELECT department_id
FROM departments
WHERE location_id IN (1400,1700)
2 在1的基础上查询员工的姓名
SELECT last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id IN (1400,1700)
);
案例2
返回其它部门中比job_id为'IT_PROG'部门任一工资低的员工的：工号，姓名，job_id，和salary
1 首先查询job_id为'IT_PROG'的部门信息的员工工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
2 再any函数来小于任意一个来处理
SELECT last_name
FROM employees
WHERE salary < ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
)AND job_id <>'IT_PROG' ;
案例3
返回其他部门中比job_id为'IT_PROG'部门所有工资都低的员工的工号、姓名、job_id和salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary >ALL(
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
);
行子查询
结果集为一行多列
案例：
查询员工编号最小并且工资最高的员工信息
方法1：
按照变量子查询来处理：
SELECT MIN(employee_id)
FROM employees
#获取编号最小的员工id
#获得最高工资
SELECT MAX(salary)
FROM employees
#合在一起处理
SELECT *
FROM employees
WHERE employee_id = (
SELECT MIN(employee_id)
FROM employees
)AND salary = (
SELECT MAX(salary)
FROM employees
);
方法2：
采用行子查询
SELECT *
FROM employees
WHERE (employee_id,salary) = (
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
select后面的子查询
查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
)
FROM departments d;
