多行子查询（集合比较子查询）

子查询返回多条记录

操作符

• IN

  等于列表中的任意一个

• ANY

  需要和单行子查询一起使用，和子查询返回的某一个值比较。

• ALL

  需要和单行子查询一起使用，和子查询返回的所有值比较。

• SOME

  ANY 的别名

# 查询各个部门的最低工资，找出所有和最低工资相等的员工。
SELECT last_name, salary
FROM employees
WHERE salary IN (
  SELECT MIN(salary)
  FROM employees
  GROUP BY department_id
);

# 查询其它 job_id 中比 job_id 为 IT_PROG 部门任意一个工资低的员工的
# last_name，job_id 和 salary
SELECT last_name, job_id, salary
FROM employees
WHERE job_id != 'IT_PROG'
AND salary < ANY (
  SELECT salary
  FROM employees
  WHERE job_id = 'IT_PROG'
);

# 查询其它 job_id 中比 job_id 为 IT_PROG 部门所有一个工资低的员工的
# last_name，job_id 和 salary
SELECT last_name, job_id, salary
FROM employees
WHERE job_id != 'IT_PROG'
AND salary < ALL (
  SELECT salary
  FROM employees
  WHERE job_id = 'IT_PROG'
);

# 查询平均工资最低的部门 id
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 # 别名是必须的
);

# 方式二
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
  SELECT AVG(salary)
  FROM employees
  GROUP BY department_id
);

# 空值问题
# 正常情况，有 18 条记录。
SELECT last_name, manager_id
FROM employees
WHERE employee_id IN (
  SELECT manager_id
  FROM employees
);

# 按理说有 107 - 18 条记录，但是没有记录。
SELECT last_name, manager_id
FROM employees
WHERE employee_id NOT IN (
  SELECT manager_id
  FROM employees
  # 多行子查询结果中出现 NULL，NOT IN 会让外层查询不出结果。
  # WHERE manager_id IS NOT NULL
);