MySQL子查询

子查询概述

　　子查询指一个查询语句嵌套在另一个查询语句内部的查询。

子查询的基本语法结构

SELECT 字段名
FROM 表名
WHERE 字段名1 = (
SELECT 字段名1
FROM 表名
WHERE 过滤条件);

• 子查询（内查询）在主查询之前一次执行完成，子查询的结果被主查询（外查询）使用

• 子查询要包含在括号内，建议将子查询放在比较条件的右侧单行操作符对应单行子查询，多行操作符对应多行子查询

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

单行子查询

• 单行比较操作符

  -- 查询与141号或174号员工的manager_id和department_id相同的其它员工的employee_id，manager_id，department_id
  SELECT employee_id,manager_id,department_id
      FROM employees
      WHERE manager_id = (
          SELECT manager_id
          FROM employees
      WHERE employee_id = 141) AND (
          SELECT department_id
          FROM employees
          WHERE employee_id = 141) AND employee_id <> 141;
  -- 方式2
  SELECT employee_id,manager_id,department_id
      FROM employees
      WHERE (manager_id,department_id) = (
      SELECT manager_id,department_id
      FROM employees
      WHERE employee_id = 141) AND employee_id <> 141;            

• HAVING 中的子查询

  SELECT 字段名1, 分组函数(字段名)
      FROM 表名
      GROUP BY 字段名1
      HAVING 分组函数1(字段名) = (
      SELECT 分组函数1(字段名)
      FROM 表名
  WHERE 过滤条件);
  -- 首先执行子查询。
  -- 向主查询中的 HAVING 子句返回结果。

  -- 查询最低工资大于118号部门最低工资的部门id和其最低工资
  SELECT department_id,MIN(salary)
      FROM employees
      GROUP BY department_id
      HAVING MIN(salary) > (
      SELECT MIN(salary)
      FROM employees
  WHERE department_id = 118
  );

• CASE中的子查询

  SELECT 字段名
  CASE WHEN (
      SELECT 字段名1
      FROM 表名
      WHERE 过滤条件)
      THEN 结果1
      ELSE 结果2 
  END AS 别名 FROM 表名;

  -- 显示员工的employee_id，last_name和location
  -- 其中，若员工的department_id与location_id为1800的department_id相同，则location为'Canada'，其余为'USA'
  SELECT employee_id,last_name,
      CASE WHEN department_id = (
          SELECT department_id
          FROM departments d JOIN locations l
          ON d.location_id = l.location_id
          WHERE l.location_id = 1800
  ) THEN 'Canada'
  ELSE 'USA' 
  END AS "location" FROM employees;

多行子查询

• 多行子查询的基本语法结构

  SELECT 字段名 FROM 表名
      WHERE 字段名1 IN (
          SELECT 字段名1
          FROM 表名
  WHERE 过滤条件);
  -- 也称为集合比较子查询,内查询返回多行,使用多行比较操作符    

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

• 多行比较操作符

  -- 返回其它job_id中比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';
  -- 方式2
  SELECT employee_id,last_name,job_id,salary FROM employees
      WHERE salary < (
          SELECT MIN(salary)
          FROM employees
      WHERE job_id = 'IT_PROG'
  ) AND job_id <> 'IT_PROG';
  
  -- 查新平均工资最低的部门id
  SELECT department_id FROM employees
      GROUP BY department_id
      HAVING AVG(salary) = (
      SELECT MIN(avg_sal)
          FROM (
          SELECT AVG(salary) AS "avg_sal"
      FROM employees
  GROUP BY department_id
  ) AS t_dept_avg_sal
  );
  -- 方式2
  SELECT department_id
  FROM employees
      GROUP BY department_id
      HAVING AVG(salary) <= ALL(
      SELECT AVG(salary)
      FROM employees
  GROUP BY department_id
  );
  -- 方式3
  SELECT department_id
      FROM employees
          GROUP BY department_id
  ORDER BY AVG(salary) ASC
  LIMIT 1;    

相关子查询

• 相关子查询的基本语法结构

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

  -- 查询员工中工资大于本部门平均工资的员工的last_name，salary和其department_id
  -- 方式1：相关子查询
  SELECT last_name,salary,department_id
      FROM employees e1
      WHERE salary > (
      SELECT AVG(salary)
      FROM employees e2
  WHERE department_id = e1.department_id
  );
  -- 方式2：FROM中声明子查询
  SELECT e.last_name,e.salary,e.department_id
      FROM employees e,(
      SELECT department_id,AVG(salary) AS "avg_sal"
      FROM employees
      GROUP BY department_id
  ) AS t_dept_avg_sal
  WHERE e.department_id = t_dept_avg_sal.department_id
  AND e.salary > t_dept_avg_sal.avg_sal;

EXISTS 与 NOT EXISTS关键字

• 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行
• 如果在子查询中不存在满足条件的行,条件返回 FALSE,继续在子查询中查找
• 如果在子查询中存在满足条件的行,不在子查询中继续查找条件返回 TRUE
• NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE

  -- 查询公司管理者的employee_id，last_name，job_id，department_id信息
  -- 方式1：自连接
  SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_iFROM employees emp
  JOIN employees mgr ON emp.manager_id = mgr.employee_id;
  -- 方式2：子查询
  SELECT employee_id,last_name,job_id,department_id
      FROM employees
      WHERE employee_id IN (
          SELECT DISTINCT manager_id
          FROM employees
  );
  -- 方式3：EXISTS
  SELECT employee_id,last_name,job_id,department_id
      FROM employees e1
      WHERE EXISTS (
          SELECT *
          FROM employees e2
  WHERE e1.employee_id = e2.manager_id
  );    

  -- 查询departments表中，不存在与employees表中的部门的department_id和department_name
  -- 方式1
  SELECT d.department_id,department_name
      FROM employees e
      RIGHT JOIN departments d ON d.department_id = e.department_id
      WHERE e.department_id IS NULL;
  -- 方式2
  SELECT department_id,department_name
      FROM departments d
      WHERE NOT EXISTS (
          SELECT * FROM employees e
  WHERE d.department_id = e.department_id
  );

子查询的编写技巧

如果子查询相对较简单，建议从外往内写。一旦子查询结构较复杂，则建议从里往外写
如果是相关子查询的话，通常都是从外往里写