文章目录
一、基本使用
1.1 基本语法
SELECT select_list
FROM table
WHERE expr operator
( SELECT select_list
FROM TABLE);
- 子查询(内查询)在主查询之前一次执行完成。
- 子查询的结果被主查询(外查询)使用 。
- 子查询要包含在括号内
- 将子查询放在比较条件的右侧
- 单行操作符对应单行子查询,多行操作符对应多行子查询
1.2 子查询分类
- 我们按内查询的结果返回一条还是多条记录,将子查询分为 单行子查询 、 多行子查询 。
- 我们按内查询是否被执行多次,将子查询划分为 相关(或关联)子查询 和 不相关(或非关联)子查询 。
二、单行子查询
2.1 单行比较运算符
单行子查询含义: 子查询返回结果为单行数据,主查询中通过比较运算符对子查询中的数据进行比较,进而进行主查询。
#:查询工资大于149号员工工资的员工的信息
SELECT last_name
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE employee_id = '149');
#: 返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资
SELECT last_name,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');
2.2 聚合函数中的子查询
- 首先执行子查询。
- 向主查询中的HAVING 子句返回结果。
# 查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary)
FROM employees
WHERE department_id = '50');
2.3 CASE中的子查询
# 显式员工的employee_id,last_name和location。其中,若员工department_id与location_id为1800的department_id相同,则location为’Canada’,其余则为’USA’。
SELECT employee_id, last_name,
(CASE department_id
WHEN
(SELECT department_id FROM departments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM employees;
2.4 子查询中的空值问题
子查询为空时,不返回任何值,主查询获取不到数据。
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');
三、多行子查询
- 也称为集合比较子查询
- 内查询返回多行
- 使用多行比较操作符
3.1 多行比较操作符
# 返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY (SELECT salary
FROM employees
WHERE job_id ='IT_PROG')
AND job_id <> 'IT_PROG';
# 返回其它job_id中比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')
AND job_id <> 'IT_PROG';
3.2 聚合函数中的多行子查询
# 查询平均工资最低的部门id
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
)
3.3 空值问题
NOT IN
解决
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
SELECT manager_id
FROM employees
);
四、相关子查询
解析图:
4.1 代码示例
# 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (SELECT AVG(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id );
#查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
SELECT department_name
FROM departments d
WHERE e.`department_id` = d.`department_id`
);
4.2 EXISTS 与 NOT EXISTS关键字
- 关联子查询通常也会和 EXISTS操作符一起来使用,用来检查在子查询中是否存在满足条件的行。
- 如果在子查询中不存在满足条件的行:
- 条件返回 FALSE
- 继续在子查询中查找
- 如果在子查询中存在满足条件的行:
- 不在子查询中继续查找
- 条件返回 TRUE
- NOT EXISTS关键字表示如果不存在某种条件,则返回TRUE,否则返回FALSE。
#查询公司管理者的employee_id,last_name,job_id,department_id信息
SELECT employee_id,last_name,job_id,department_id
FROM employees e
WHERE EXISTS (SELECT *
FROM employees e1
HERE e1.manager_id = e.employee_id);
#自连接
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;
#查询departments表中,不存在于employees表中的部门的department_id和department_name
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
FROM employees
WHERE department_id = d.department_id);