如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表， 并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的 子查询就称为关联子查询。

SELECT column1, column2, ...
FROM table1, outer
WHERE column1 operator (
  SELECT column1, column2
  FROM table2
  WHERE expr1 = outer.expr2
);

# 除了 GROUP BY 和 LIMIT 外，其它位置都可以声明子查询。
SELECT ...
FROM ... 
(LEFT / RIGHT) JOIN ...
ON ...
(LEFT / RIGHT) JOIN
ON...
WHERE 
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT

# 查询员工中工资大于本部门平均工资的员工的 last_name, salary 和 department_id。
SELECT last_name, salary, department_id
FROM employees e1
WHERE salary > (
  # 求出该部门的平均工资
  SELECT AVG(salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

# 非相关子查询实现
SELECT t1.last_name, t1.salary, t1.department_id
FROM employees t1, (
  SELECT AVG(salary) avg_sal, department_id
  FROM employees
  GROUP BY department_id
) t2
WHERE t1.department_id = t2.department_id
AND t1.salary > t2.avg_sal;

# 查询员工的 last_name, salary，按照 department_name 排序。
SELECT t1.last_name, t1.salary
FROM employees t1
ORDER BY (
  SELECT department_name
  FROM departments t2
  WHERE t1.department_id = t2.department_id
);

# 若 employees 表中 employee_id 与 job_history 表中 employee_id 相同的数目不
# 小于 1，输出这些相同 id 的员工的 employee_id，last_name，job_id。
SELECT t1.employee_id, t1.last_name, t1.job_id
FROM employees t1
WHERE 2 <= (
  SELECT COUNT(*)
  FROM job_history t2
  WHERE t1.employee_id = t2.employee_id
);

EXIST & NOT EXIST

# 关联子查询通常也会和 EXIST 操作符一起使用，用来检查在子查询中是否存在满足条件
# 的行，如果子查询中不存在满足条件的行，条件返回 false，继续在子查询中找，如果
# 存在满足条件的行，不在子查询中继续找，条件返回 true。

# 查询公司管理者的 last_name，job_id 信息
# 自连接
SELECT DISTINCT t2.last_name, t2.job_id
FROM employees t1, employees t2 
WHERE t1.manager_id = t2.employee_id;

# 子查询
SELECT t1.last_name, t1.job_id
FROM employees t1
WHERE t1.employee_id IN (
  SELECT DISTINCT manager_id
  FROM employees
);

SELECT t1.last_name, t1.job_id
FROM employees t1
WHERE EXISTS (
  # 选择什么不重要
  SELECT *
  FROM employees t2
  WHERE t1.employee_id = t2.manager_id
);

# 查询 departments 表中，不存在 employees 表中的部门的 department_name。
SELECT t1.department_name
FROM departments t1
LEFT JOIN employees t2
ON t1.department_id = t2.department_id
WHERE t2.department_id IS NULL;

SELECT t1.department_name
FROM departments t1
WHERE NOT EXISTS (
  SELECT *
  FROM employees t2
  WHERE t1.department_id = t2.department_id
);