子查询

• 子查询 (内查询) 在主查询之前一次执行完成。
• 子查询的结果被主查询(外查询)使用 。

SELECT last_name
FROM employees
WHERE salary >
(SELECT salary
FROM employees
WHERE last_name = 'Abel');

非法使用子查询

SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);

在多行子查询中使用 ANY 操作符
题目：返回其它部门中比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';

在多行子查询中使用 ALL 操作符
题目：返回其它部门中比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';