进阶7子查询

定义

出现在其他语句内部的select语句，称作子查询/内查询
内部嵌套其他select语句的查询，称作主查询/外查询

分类

1）按子查询出现位置：

select后面：
标量子查询
from后面：
表子查询
where/having后面：⭐️
标量子查询 （单行）✅
列子查询 （多行）✅

行子查询（较少）
exists后面（相关子查询）：
都可以

2）按结果集的行列数不同：

标量子查询（结果集只有一行一列）
列子查询 （一列多行）
行子查询 （允许多行多列，但主要用的是一行多列）
表子查询（无所谓，一般为多行多列）

案例讲解

1.where/having后面

特点：
1.子查询放在小括号内
2.子查询一般放在条件的右侧
3.标量子查询，一般搭配单行操作符使用：> < >= <= <>
列子查询，一般搭配多行操作符使用：in、any/some、all
4.子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

标量子查询
案例1:
谁的工资比abel高？
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:返回和141号员工job_id一致，并且salary大于143号员工的员工信息
1.查询141员工的job_id
select job_id
from employees
where employee_id=141
2.查询143号员工的salary
select salary
from employees
where employee_id=143
3.查询员工信息，要求job_id=结果1 and salary>结果2
select *
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：返回工资最少的员工的last_name,job_id 和salary
select last_name,job_id,salary
from employees
where salary=(
select min(salary)
from employees
);
select last_name,job_id,salary
from employees
group by employee_id
having salary=min(salary);

案例4:
查询最低工资大于50号部门最低工资的部门id和最低工资
1.50号部门最低工资
select min(salary)
from employees
where department_id=50;
2.查询每个部门的最低工资
select min(salary),department_id
from employees
group by department_id;
3.在结果2基础上筛选，满足min(salary)>结果1
select min(salary),department_id
from employees
group by department_id
having min(salary)>(
select min(salary)
from employees
where department_id=50
);

非法使用标量子查询：子查询结果不是一行一列
情况1:子查询为一列多行，而操作符为><>=<=<>单个值
select min(salary),department_id
from employees
group by department_id
having min(salary)>(
select salary
from employees
where department_id=50
);
情况2:子查询结果为空，不存在department_id=250
select min(salary),department_id
from employees
group by department_id
having min(salary)>(
select salary
from employees
where department_id=250
);

列子查询

操作符关系
a> any(10,20,30) 大于数组中任意一个即可 = a>min(10,20,30)
a> all(10,20,30) 大于数组中所有值 = a>max(10,20,30)
=any（）== in（）
<>all（）== not in（）

案例1:
返回loction_id是1400或者1700的部门中的所有员工姓名
select last_name
from employees
where department_id in (
select distinct department_id
from departments
where location_id in (1400, 1700)
);

select last_name
from employees
where department_id =any (
select distinct department_id
from departments
where location_id in (1400, 1700)
);
案例2:
返回其他工种中比job_id为’IT_PROG’部门任一工资低的员工的员工号、姓名
job_id以及salary
select last_name,employee_id,job_id,salary
from employees
where salary< any (
select distinct salary
from employees
where job_id=’IT_PROG’
) and job_id=’IT_PROG’;

行子查询
案例1:
select
from employees
where employee_id=(
select min(employee_id)
from employees
) and salary=(
select max(salary)
from emplyees
);
select
from employees
where (employee_id,salary)=(
select min(employee_id), max(salary)
from employees
);

2.select 后面

只能是标量子查询
案例1:
查询每个部门的员工个数
select d.,(
select count()
from employees
where e.department_id=d.department_id
)个数
from department d；

案例2:
查询员工号=102的部门名
select department_name
from departments d
join employees e
on d.department_id=e.department_id
where e.employee_id=102;

3.from 后面

表子查询：将子查询结果充当一张表，必须起别名

案例1:🌟
查询每个部门的平均工资的工资等级
1.查询每个部门的平均工资
select avg(salary) avg ,department_id
from employees
group by department_id
2.连接结果1和job_grade表，筛选条件平均工资betwwen lowest_sal and highest_sal
select avg_dep., g.grade_level
from (
select avg(salary) avg,department_id
from employees
group by department_id
)avg_dep 必须给表起名
join job_grade g
on avg_dep.avg between lowest_sal and highest_sal

4.exists 后面（相关子查询）🌟

表子查询

语法：
exists（完整查询语句）
结果：1或0

select exists (select employee_id from employees where salary=30000);

案例1:查询有员工的部门名
方案1🌟
select department_name
from departments d
where exists(
select *
from emplyees e
where d.department_id=e.department_id
);
方案2🌟
select department_name
from departments d
where d.department_id in (
select department_id
from employees
);

案例2:查询没有女朋友的男神信息
方案1
select bo.
from boys bo
where not exists(
select boyfriend_id
from beauty b
where bo.id=b.boyfriend_id
);
方案2
select bo.
from boys bo
where bo.id not in (
select boyfriend_id
from beauty b
);