MySQL运维实战教程（5）子查询概述与基本使用

一、MySQL子查询介绍
使用子查询可以实现SELECT语句的嵌套查询，即一个SELECT的查询结果作为另一个SELECT的查询条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作，比如要查询企业中哪些员工的工资比张三高，那就需要先查询张三的工资。

在整个查询语句结构中，最外层的查询称为主查询，其余嵌套查询都作为它的子查询语句。虽然子查询是一项重要的功能，但是它的查询性能并不高，因为执行子查询时会将内层语句的查询结果存放在内存临时表中，主查询再通过临时表进行查询，最后删除这个临时表，这些过程都会导致CPU和IO资源的消耗。并且子查询结果集存放的临时表不会存在索引，查询性能会再一次受到影响。所以在进行复杂查询时可以考虑尽量用多表连接代替子查询。

子查询的相关特性：

· 子查询主要解决AVG等聚合函数不能写在WHERE条件中的问题

· 通常要对聚合函数进行嵌套的话就可以考虑使用子查询（Oracle中聚合函数可以嵌套，如min(avg()）

· 单行子查询：子查询的查询结果只有1条数据，对于单行子查询可以使用的操作符有=、!=、>、<、>=、<=

· 多行子查询：子查询的查询结果大于1条数据，对于多行子查询可以使用的操作符有IN（等于列表中的任意一个）、ANY（需要和单行操作符一起使用，和子查询返回的其中一个值进行比较）、ALL（需要和单行操作符一起使用，和子查询返回的其中所有值进行比较）。如果主查询中使用了像=、!=这样的关联条件就会返回subquery return more than 1row的错误

· 相关子查询：如果子查询中的数据是动态变化的，一般为相关子查询。比如需要查询工资大于部门平均工资的员工，这个时候内层查询的数据会随着主查询中员工信息发生变化，比如A员工对于SALES部门、B员工对应IT部门

· 如果子查询并没有返回数据，那么整个查询结果为空而不报错

二、MySQL子查询示例

1、查询工资大于149号员工的其他员工信息

SELECT employee_id,last_name,salary FROM employees
WHERE salary > (
SELECT salary FROM employees WHERE employee_id = 149
);

#查询工资大于张三的员工
SELECT last_name,salary FROM employees WHERE salary > ( SELECT salary FROM employees WHERE last_name='zhangsan');

2、查询工号与141号员工相同、工资比143号员工多的其他员工的姓名、job_id和工资

SELECT last_name,job_id,salary 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、查询最低工资大于110号部门中最低工资的部门ID和该部门最低工资（在HAVING中使用子查询）

SELECT department_id,MIN(salary) FROM employees 
WHERE department_id IS NOT NULL 
GROUP BY department_id
HAVING MIN(salary) > (
  SELECT MIN(salary) FROM employees WHERE department_id = 110
)

4、查询结果显示employee_id，last_name和location三个字段，其中location字段的值需要进行判断，如果员工department_id和location_id为1800的department_id相同则location显示CANADA，否则显示USA（在CASE中使用子查询）

SELECT employee_id,last_name,CASE department_id WHEN (SELECT department_id FROM departments WHERE location_id=1800)
                                                ELSE 'USA' END "location"
FROM employees;

5、查询除IT部门外其他所有部门中工资比IT部门所有人都要低的员工信息（多行子查询）

SELECT employee_id,last_name,job_ib,salary FROM employees WHERE job_id <> 'IT'
AND salary < ALL (
  SELECT salary FROM employees WHERE job_id = 'IT'
)

6、查询平均工资最低的部门ID（把子查询的结果作为一张表，解决聚合函数嵌套问题）

SELECT MIN(avg_salary) FROM ( 
  SELECT AVG(salary) avg_salary 
  FROM employees 
  GROUP BY department_id                             
) t_dept_avg_salary      #必须为子查询起一个表别名