[mysql]子查询

子查询的分类：

分类角度

角度1：从内查询(子查询)返回的结果的条目数

区分：单行子查询 / 多行子查询

角度2：内查询是否被执行多次

区分：相关子查询(关联子查询) / 不相关子查询

---

关于单行子查询：

操作符:=, > ,>= ,< ,<= , <> (不相等)

关于多行子查询：

操作符	含义
IN	等于列表中的任意一个
ANY	需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL	需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME	实际上是ANY的别名，作用相同，一般常使用ANY

题型区别：
ALL:
返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary
SOME/ANY:
返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

关于相关(关联)子查询

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

题目示例：
不相关子查询：
查询员工中工资大于本公司平均工资的员工的last_name,salary和其department_id

相关子查询：
查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

PS：公司平均工资和该员工的个人信息没有关系，即不相关。
部门和该员工的个人部门有关，相关。

相关子查询代码：

SELECT employee_id,last_name
FROM employees e1
WHERE salary > (
		    SELECT AVG(salary)
			FROM employees e2
			WHERE (e2.)department_id = e1.department_id);

拓展：用多表查询代替相关子查询

SELECT last_name,salary,e1.department_id 
FROM employees e1,(SELECT department_id,
                   AVG(salary)  dept_avg_sal 
   #此时AVG(salary) 意义作为字段而非函数，需要起别名 来被引用
                   FROM employees 
                   GROUP BY department_id) e2 
   #创建一个虚拟表 e2 来进行多表查询
WHERE e1.`department_id` = e2.department_id 
AND e2.dept_avg_sal < e1.`salary`;

---

使用过程中的细节点

子查询位置

子查询除去 GROUP BY 和 LIMIT 关键字，都可以作为字段配合写入。

WHERE条件的另一种写法

WHERE(manager_id,department_id) = (
		  SELECT manager_id,department_id
			FROM employees
			WHERE employee_id = 141);

子查询中的空值问题

当子查询返回语句的情况不存在，即子查询没有返回任何行时，mysql报错，提示‘no rows selected’

子查询类型不匹配

示例：多行子查询 使用 单行比较符

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

子查询和自连接的选择

结论：尽量选自连接。

题目中可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。

可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化

子查询配合两个判断语句

EXISTS和子查询

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

• 如果在子查询中不存在满足条件的行：
  1. 条件返回 FALSE
  2. 继续在子查询中查找
• 如果在子查询中存在满足条件的行：
  1. 不在子查询中继续查找
  2. 条件返回 TRUE

PS：NOT EXISTS 同理取反

---

例题：查询公司管理者的employee_id，last_name，job_id，department_id信息

1. 子查询

SELECT employee_id, last_name, job_id, department_id 
FROM employees e1 
WHERE EXISTS ( SELECT * 
               FROM employees e2 
               WHERE e2.manager_id = e1.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. 自连接

SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id FROM employees e1 
JOIN employees e2 
WHERE e1.employee_id = e2.manager_id;

case配合子查询

例题：找到员工的employee_id,last_name和location。
（其中，若员工department_id与location_id为1800 的department_id相同，则location为’Canada’，其余则为’USA’）

SELECT employee_id,last_name,
                  (case department_id
                   WHEN (SELECT department_id 
                         FROM departments
                         WHERE location_id = 1800)
                   THEN 'Canada'  ELSE 'USA' END)
FROM employees;

CASE能够实现判断语句功能。

---

THE END