1. 定义
嵌套在其他语句中的select语句,称为子查询或内查询
外查询:
嵌套select语句的外部的查询语句,称为主查询或外查询
2. 分类
* 按子查询结果集的行列数:
标量子查询:一行一列
列子查询:一列多行
行子查询:一行多列(不常用)
* 要求多个判断条件的单行操作符一样
* 查询的结果可以被看做一个虚拟的字段
表子查询:多行多列
3. 特点
* 子查询一般放在小括号内
* 子查询一般放在条件的右侧
* 标量子查询一般搭配着单行操作符使用:> < >= <= = <>
* 列子查询一般搭配着多行操作符使用:IN ANY SOME ALL
IN/NOT IN:等于列表中的任意一个
ANY/SOME:和子查询返回的某一个值比较 >MIN <MAX
ALL:和子查询返回的所有值比较 >MAX <MIN
* 子查询的执行先于主查询,主查询用到了子查询的结果
4. 案例
-- 查询job_id与141号员工相同、工资比143号员工高的,员工的姓名、工资、job_id
select last_name,salary,job_id
from employees
where job_id = (
select job_id
from employees
where employee_id=141
) and salary >(
select salary
from employees
where employee_id=143
);
-- 查询location_id是1400或1700的部门的所有员工的姓名
select last_name
from employees
where department_id in (
select distinct department_id
from departments
where location_id in (1400,1700)
);
-- 查询其他工种中比工种IT_PROG中任一工资低的员工的员工号,姓名,工种,薪水
select employee_id,last_name,job_id,salary
from employees
where job_id<>'IT_PROG'
and salary < any(
select diatinct salary
from employees
where job_id='IT_PROG'
);
-- 查询其他工种中比工种IT_PROG中所有工资低的员工的员工号,姓名,工种,薪水
select employee_id,last_name,job_id,salary
from employees
where job_id<>'IT_PROG'
and salary < all(
select diatinct salary
from employees
where job_id='IT_PROG'
);
-- 查询员工编号最小并且工资最高的员工的信息
select *
from employees
where (employee_id,salary)=(
select min(employee_id),max(salary)
from employees
);
-- 查询每个部门的员工个数
select d.*,(
select count(*)
from employees e
where e.department_id=d.department_id
)
from department d;
-- 查询每个部门的平均工资的工资等级
select g.grande_level,a.department_id,a.平均工资
from job_grades g
inner join(
select avg(salary) 平均工资,department_id
from employees
group by department_id
) a
on a.平均工资 between g.lowest_sal and highset_sal;
-- 查询没有女朋友的男生信息
select bo.*
from boys bo
where not exists(
select *
from beatuy b
where b.boyfriend_id=bo.id
);