子查询

含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

举例

SELECT first_name 
FROM employees 
WHERE department_id 
IN (SELECT department_id 
        FROM departments 
        WHERE location_id = 1700 
    )

子查询出现的位置:
select 后面
仅仅支持标量子查询
from 后面
支持表子查询
where或having后面 ☆
标量子查询（单行） √
列子查询 （多行）√
行子查询
exists后面
表子查询
按结果集的行列数不同：
标量子查询：（结果集只有一行一列）
列子查询 （结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般有多行多列）

放在where或者having 后面子查询

1.标量子查询
2.列子查询
3.行子查询

select last_name,job_id,salary from employees
where salary = (select min(salary) from employees)

select min(salary),department_id
from employees
group by department_id
having min(salary) > (
select min(salary) from employees
where department_id = 50
)

多行子查询

• 返回多行
• 使用多行比较操作符

in/not in : 等于列表中的任意一个
any/some: h和子查询中的某一个值比较
all:和子查询中所有值比较

行子查询

select * from employees e
where (e.employee_id,e.salary) = (select min(employee_id), max(salary)  from employees)

select后面的子查询

仅仅支持标量查询

select d.department_id,(select count(*) from employees where department_id = d.department_id) as count
from departments d

放在from后面的子查询

select * 
from job_grades j inner join
(select avg(salary) as salary,department_id from employees group by department_id) temp on temp.salary BETWEEN j.lowest_sal and j.highest_sal

exists后面的子查询

语法
exists(完整的查询语句)
结果1或0
举例

select depart_name
from departments d
where exists(
    select *
  from employees e
  where e.department_id = d.department_id
)