含义：出现在其他语句内部的 SELECT 语句，称为子查询或内查询。内部嵌套了其他 SELECT 语句的查询，称为外查询或主查询。

特点

  • 子查询放在小括号内。
  • 子查询一般放在条件的右侧。
  • 子查询的执行优于主查询执行，主查询的条件用到了子查询的结果。


分类

出现位置分类

位置 支持情况
SELECT 后面 标量子查询
FROM 后面 表子查询
WHERE 或 HAVING 后面 标量子查询/列子查询/行子查询（比较少用）
EXISTS 后面 表子查询

结果集行列数分类

类型 结果集
标量子查询 一行一列
列子查询 一列多行
行子查询 一行多列
表子查询 多行多列

案例

WHERE 或 HAVING 后面

标量子查询 

  1. -- 谁的工资币 Abel 高。
  3. SELECT
  4.     * 
  5. FROM
  6.     employees 
  7. WHERE
  8.     salary > ( SELECT salary FROM employees WHERE last_name = 'Abel' );
  10.  -- 返回job_id 与 141 号员工相同，salary 比 143 号员工多的员工 姓名、job_id 和 工资。
  12. SELECT
  13.     last_name,
  14.     job_id,
  15.     salary 
  16. FROM
  17.     employees 
  18. WHERE
  19.     job_id = ( SELECT job_id FROM employees WHERE employee_id = 141 ) 
  20.     AND salary > ( SELECT salary FROM employees WHERE employee_id = 143 );
  22.  -- 返回公司工资最少的员工的 last_name、job_id、salary。
  24. SELECT last_name, job_id, salary FROM employees WHERE salary =( SELECT MIN( salary ) FROM employees );
  26. -- 查询最低工资 > 50 号部门最低工资的部门和其最低工资。
  28. SELECT
  29.     MIN( salary ),
  30.     department_id 
  31. FROM
  32.     employees 
  33. GROUP BY
  34.     department_id 
  35. HAVING
  36.     MIN( salary ) > ( SELECT MIN( salary ) FROM employees WHERE department_id = 50 );

列子查询

一般会使用到几个关键字。

关键字 含义
IN/NOT IN 等于列表中的任意一个
ANY/SOME 和子查询返回的某一个值比较
ALL 和子查询返回的所有值比较 
  1. -- 返回 location_id 是 1400 或 1700 的部门中的所有员工姓名。
  3. SELECT
  4.     last_name 
  5. FROM
  6.     employees 
  7. WHERE
  8.     department_id IN ( SELECT DISTINCT department_id FROM departments WHERE location_id IN ( 1400, 1700 ) );
  10. SELECT
  11.     last_name 
  12. FROM
  13.     employees 
  14. WHERE
  15.     department_id = any( SELECT DISTINCT department_id FROM departments WHERE location_id IN ( 1400, 1700 ) );
  18. -- 返回其他工种中比 job_id 为 IT_PROG 任一工资低的员工名、姓名、job_id、salary。
  20. SELECT
  21.     last_name,    
  22.     employee_id,
  23.     job_id,
  24.     salary 
  25. FROM
  26.     employees 
  27. WHERE
  28.     salary < ALL ( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' ) 
  29.     AND job_id <> 'IT_PROG';
  31. SELECT
  32.     last_name,
  33.     employee_id,
  34.     job_id,
  35.     salary 
  36. FROM
  37.     employees 
  38. WHERE
  39.     salary < ( SELECT MIN(salary) FROM employees WHERE job_id = 'IT_PROG' ) 
  40.     AND job_id <> 'IT_PROG';

行子查询

  1. -- 查询员工编号最小并且工资最最高的员工信息。
  3. SELECT
  4.     * 
  5. FROM
  6.     employees 
  7. WHERE
  8.     employee_id = ( SELECT MIN( employee_id ) FROM employees ) 
  9.     AND salary = ( SELECT MAX( salary ) FROM employees );
  11. SELECT
  12.     * 
  13. FROM
  14.     employees 
  15. WHERE
  16.     ( employee_id, salary ) = ( SELECT MIN( employee_id ), MAX( salary ) FROM employees );

SELECT 后面

仅支持标量子查询。

  1. -- 查询每个部门的员工个数。
  3. SELECT
  4.     d.*,
  5.     ( SELECT COUNT(*) FROM employees AS e WHERE e.department_id = d.department_id ) AS count 
  6. FROM
  7.     departments AS d;
  9. -- 查询员工号 = 102 的部门名。
  11. SELECT
  12.     ( SELECT department_name FROM departments AS d INNER JOIN employees AS e ON d.department_id = e.department_id WHERE e.employee_id = 102 ) AS NAME;

FROM 后面

  1. -- 查询每个部门的平均工资的工资等级。
  3. SELECT
  4.     ag_dep.*,
  5.     g.grade_level 
  6. FROM
  7.     ( SELECT AVG( salary ) AS ag, department_id FROM employees GROUP BY department_id ) AS ag_dep
  8.     INNER JOIN job_grades AS g ON ag_dep.ag BETWEEN lowest_sal 
  9.     AND highest_sal;

EXISTS 后面

语法：EXISTS(完整的查询语句)。

  1. -- 查询有员工的部门名。
  3. SELECT
  4.     department_name 
  5. FROM
  6.     departments AS d 
  7. WHERE
  8.     EXISTS ( SELECT * FROM employees AS e WHERE d.department_id = e.department_id );
  11. -- 查询没有女朋友的男生信息。
  13. SELECT
  14.     bo.*
  15. FROM
  16.     boys AS bo 
  17. WHERE
  18.     bo.id NOT IN ( SELECT boyfriend_id FROM beauty );
  20. SELECT
  21.     bo.* 
  22. FROM
  23.     boys AS bo 
  24. WHERE
  25.     NOT EXISTS ( SELECT boyfriend_id FROM beauty AS b WHERE bo.id = b.boyfriend_id );

参考：