子查询分类
-
放在SELECT后面
- 仅支持标量子查询 -
放在FROM后面
- 支持表子查询 -
放在WHERE或HAVING后面
- 支持标量子查询、列子查询、行子查询
一、WHERE或HAVING后面
1、标量子查询(单行单列子查询)
案例1: 查询谁的工资比Abel高?
SELECT last_name
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = "Abel"
);
案例2:返回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
);
案例3:返回公司工资最少的员工的last_name, job_id和salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM empoyees
);
案例4:查询最低工资大于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、列子查询
特点:返回多行,所以要使用多行比较操作符(IN/NOT IN、ANY|SOME、ALL)
案例1:返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id IN (1400, 1700)
);
案例2:返回其它工种中比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";
案例3:返回其它部门中比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、行子查询
案例:查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employess
WHERE employee_id = (
SELECT MIN(employee_id)
FROM employees
) AND salary = (
SELECT MAX(salary)
FROM employees
);
SELECT *
FROM employees
WHERE (employee_id, salary) = (
SELECT MIN(employee_id), MAX(salary)
FROM employees
);
二、SELECT后面
案例1:查询每个部门的员工个数
SELECT D.*, (
SELECT COUNT(*)
FROM employees AS E
WHERE E.department_id = D.department_id
GROUP BY D.department_id
)
FROM departments AS D;
当然也可以用内连接查询来做:
SELECT D.*, COUNT(*)
FROM employees AS E, departments AS D
WHERE E.department_id = D.department_id
GROUP BY D.department_id;
案例2:查询员工号为102的部门名
SELECT (
SELECT department_name
FROM departments AS D
WHERE D.department_id = E.department_id
)
FROM employees AS E
WHERE E.employee_id = 102;
三、FROM后面
案例:查询每个部门的平均工资的工资等级
SELECT J.grade_level
FROM job_grades AS J, (
SELECT AVG(salary) AS avg
FROM employees AS E
GROUP BY E.department_id
) AS avg_dep
WHERE avg_dep.avg BETWEEN J.lowest_salary AND J.highest_salary;
四、exists后面
案例1:查询有员工的部门名
SELECT department_name
FROM departments AS D
WHERE EXISTS(
SELECT *
FROM employees AS E
WHERE E.department_id = D.department_id
);
案例2:查询没有女朋友的男神信息
SELECT Bo.*
FROM girls.boys AS Bo
WHERE NOT EXISTS(
SELECT *
FROM girls.beauty AS B
WHERE B.boyfriend_id = Bo.id
);