MySQL九： 子查询

子查询

一、概念

1. 子查询就是一个查询语句嵌套在另一个查询语句内部的查询
2. SQL中子查询的使用大大增强了SELECT查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较

二、单行子查询

1. 单行比较操作符

   = 、 > 、 >= 、 < 、 <= 、 <>

2. WHERE中的子查询

   # 返回job_id与141号员工相同，salary比143号员工多的员工姓名
   SELECT last_name
   FROM employees
   WHERE job_id = (
       			SELECT job_id 
       			FROM employees
       			WHERE  employee_id = 141
   				)
   AND  salary > (
       			SELECT salary
       			FROM  employees
       			WHERE employee_id = 143
   				);
   

3. HAVING中的子查询

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

4. CASE中的子查询

   /* 
     显示员工的employee_id,last_name和location。其中，若员工deartyment_id与location_id为1800的   department_id相同，则location为‘Canada’,其余则为‘USA’
   */
   
   SELECT employee_id,last_name,(
       						CASE deparyment_id
       						WHEN (
                                   	SELECT department_id 
                                   	FROM departments
                                   	WHERE location_id = 1800
                               		)
       						THEN 'Canada'
       						ELSE 'USA' END
   							) location
   FROM employees;
   
   

5. 空值问题

   如果内查询的返回结果中包含一个NULL值，那么外查询的结果也会为空

   # NOT IN 的结果集中有一个NULL值，所以查询结果为空
   SELECT last_name
   FROM employees
   WHERE employee_id NOT IN (
       					SELECT manager_id
       					FROM employees    					
   						)
   

三、多行子查询

运算符	作用
IN	等于列表中的任意一个
ANY/SOME	需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL	需要和单行比较操作符一起使用，和子查询返回的所有值比较

四、相关子查询

1. 定义

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

2. 子查询执行流程

   相关子查询是按照一行接一行的顺序执行，主查询的每一行都执行一次子查询

   

3. 应用实例：查询员工中工资大于本部门平均工资的员工的last_name,salary

   # 方式一 在括号中使用的外查询中的引用
   SELECT last_name,salary
   FROM employees e1
   WHERE salary > (
       			SELECT AVG(salary)
       			FROM employees e2
       			WHERE e2.department_id = e1.department_id
   				);
   				
   # 方式二 在FROM中声明子查询，利用虚拟表
   SELECT last_name,salary
   FROM employees e,(
       			SELECT department_id,AVG(salary) avg_sal
       			FROM employees
       			GROUP BY department_id
   				)  dept
   WHERE e.department_id = dept.department_id
   AND e.salary > dept.avg_sal
   

4. 结论

   在查询结构中，除了GROUP BY 和 LIMIT之外，其它位置都可以声明子查询。

5. EXISTS / NOT EXISTS

   关联子查询通常会和EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行

   5.1 如果在子查询中不存在满足条件的行

   ​ ① 返回FALSE

   ​ ② 继续在子查询中查找

   5.2 如果在在查询中存在满足条件的行

   ​ ① 不继续在子查询中查找

   ​ ② 返回TURE

   # 查询departments表中，不存在于employees表中的部门的department_id和department_name
   SELECT department_id, department_name
   FROM departments d
   WHERE NOT EXISTS (
       			  SELECT *
       			  FROM employees
       			  WHERE department_id = d.department_id
   				  );
   

五、子查询的编写技巧

1. 从里往外 / 从外往里

   如果子查询相对简单，建议从外往里；一旦子查询结构复杂，建议从里往外

   如果是相关子查询，通常都是从外往里。

2. 最小、最大的三种求解方式

   ① 用MAX / MIN 聚合函数

   ② 用ORDER BY . . . ASC / DESC

   ​ LIMIT 0,1

   ③ ≥ / ≤ ALL（虚拟表）

3. 提升效率

   使用虚拟表与表之间的连接，可以显著减少查询层数，极大提高查询效率

   在SELECT处使用子查询（深度理解SELECT语句的执行顺序）

4. 建议

   在子查询中，= 最好改用 IN ，SELECT 后最好使用 DISTINCT去重