含义:
出现在其他语句中的select语句,称为子查询或内查询外部的查询语句,称为主查询或外查询
分类
按子查询出现的位置:
select后面: 仅仅支持标量子查询
from后面: 支持表子查询
where或having后面:★ 标量子查询(单行)列子查询(多行)√行子查询
exists后面(相关子查询) 表子查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列>
列子查询(结果集只有一列多行
行子查询(结果集有一行多列)
表子查询<结果集一般为多行多列>
一.where或having后面
1.标量子查询
案例一:谁的工资比Abel高
SELECT last_name,salary
from employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name= 'Abel'
)
案例二.查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT MIN(SAlary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id=50
)
2.列子查询
in / not in :等于列表中的任意一个
any/some 和子查询返回的一个值比较
all 和子查询返回的值比较
案例一:返回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)
)
3.行子查询
查询员工编号最小并且工资最高的员工信息
SELECT *
from employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
from employees
)
或者
SELECT *
from employees
WHERE (employee_id)=(
SELECT MIN(employee_id)
from employees
)
and salary=(
SELECT MAX(salary)
FROM employees
)
二.Select后面的子查询
案例.查询每个部门的员工个数
SELECT d.department_name,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.department_id
)
from departments d
三,exists后的(相关子查询)
exists 即 存在
语法:
exists(完整的查询语句)
结果:1或0
案例:查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.department_id = e.department_id
)