什么是子查询
子查询，是指一个查询语句嵌套在另一个查询语句内的查询，因此也称为“嵌套查询”。
比如， 要查询谁的工资比Abel高？
# 谁的工资比Abel高
# 方式1：两次查询
SELECT salary
FROM employees
WHERE last_name = 'Abel'; -- 返回11000
SELECT last_name,salary
FROM employees
WHERE salary > 11000; -- 返回10行记录
# 方式2：自连接
SELECT e1.salary,e1.last_name
FROM employees e1,employees e2
WHERE e1.salary > e2.salary
AND e2.last_name = 'Abel'; -- 返回10行记录
# 方式3：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
) -- 返回10行记录
单行子查询和多行子查询
按照子查询返回的记录行数，分为单行子查询和多行子查询。
单行子查询
单行子查询，即子查询返回单行记录。
单行子查询使用的比较操作符有：>，>=，<，<=，=，<>。
- 查询工资大于149号员工工资的员工信息。
# 查询工资大于149号员工工资的员工信息
SELECT last_name,employee_id,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE employee_id = 149
)
- 返回job_id与141号员工相同，salary比143号员工高的员工姓名、job_id和工资。
# 返回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
);
- 返回公司工资最少的员工的last_name、job_id和salary。
# 返回公司工资最少的员工的last_name、job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
)
- 查询与141号的manager_id和department_id相同的其他员工的employee_id、manager_id、department_id。
# 查询与141号的manager_id和department_id相同的其他员工的employee_id、manager_id、department_id
# 第一种写法
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id = (
SELECT manager_id
FROM employees
WHERE employee_id = 141
)
AND manager_id = (
SELECT manager_id
FROM employees
WHERE employee_id = 141
)
AND employee_id <> 141; -- 返回7行记录
# 第二种写法
SELECT employee_id,manager_id,department_id
FROM employees
WHERE(manager_id,department_id) = (
SELECT manager_id,department_id
FROM employees
WHERE employee_id = 141
)
AND employee_id <> 141; -- 返回7行记录
- 查询最低工资大于50号部门最低工资的部门id和其最低工资。
# 查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT MIN(salary),department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
) -- 返回10行记录
- 显示员工的employee_id、last_name和location。其中，如果员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。
#显示员工的employee_id、last_name和location。其中，如果员工department_id与location_id为1800的department_id相同，则location为'Canada'，其余则为'USA'。
# 第一步
SELECT department_id
FROM departments
WHERE location_id = 1800; -- 返回20
# 第二步
SELECT employee_id,last_name,CASE department_id WHEN 20 THEN 'Canada'
ELSE 'USA' END "location"
FROM employees; -- 返回107行记录
# 汇总
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; -- 返回107行记录
多行子查询
多行子查询，即子查询返回多行记录。
多行子查询使用的比较操作符有：IN、ANY、ALL、SOME。
-
IN，等于列表中的任意一个。
-
ANY，需要和单行比较操作符一起使用，和子查询返回的某一个值比较。
-
ALL，需要和单行比较操作符一起使用，和子查询返回的所有值比较。
-
SOME，ANY的别名，作用相同，但常用ANY。
- 返回其他job_id中比job_id为’IT_PROG’部门任意一工资低的employee_id、last_name、job_id、salary。
# 返回其他job_id中比job_id为'IT_PROG'部门任意一工资低的employee_id、last_name、job_id、salary。
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
); -- 返回76行记录
- 返回其他job_id中比job_id为’IT_PROG’部门所有工资低的employee_id、last_name、job_id、salary。
# 返回其他job_id中比job_id为'IT_PROG'部门所有工资低的employee_id、last_name、job_id、salary。
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
); -- 返回44行记录
- 查询平均工资最低的部门。
# 查询平均工资最低的部门
# 方式1
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(avg_sal)
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) t_dep_avg_sal
);
/*
返回
department_id AVG(salary)
50 3475.555556
*/
# 方式2
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
SELECT AVG(salary)
FROM employees
GROUP BY department_id
);
/*
返回
department_id AVG(salary)
50 3475.555556
*/
# 方式3
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC
LIMIT 1;
/*
返回
department_id avg_sal
50 3475.555556
*/
- 空值问题
SELECT employee_id
FROM employees; -- 返回107行记录
SELECT employee_id
FROM employees
WHERE employee_id IN (
SELECT manager_id
FROM employees
); -- 返回18行记录
SELECT employee_id
FROM employees
WHERE employee_id NOT IN (
SELECT manager_id
FROM employees
); -- 返回0行记录，因为SELECT manager_id FROM employees中有空值（NULL）
SELECT employee_id
FROM employees
WHERE employee_id NOT IN (
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
); -- 返回89行记录
