前言
假如我们想要知道哪些员工的额薪水大于所有员工的平均薪水值
表结构如下：
CREATE TABLE employee (
id int NOT NULL,
first_name varchar(50) NULL DEFAULT NULL COMMENT '名字',
last_name varchar(50) NULL DEFAULT NULL COMMENT '姓',
salary decimal(10, 2) NULL DEFAULT NULL COMMENT '薪水',
job_id varchar(50) NULL DEFAULT NULL COMMENT '职位id',
organization_id varchar(50) NULL DEFAULT NULL COMMENT '部门id',
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB
首先我们的解决思路是先查询所有员工的平均值
select avg(salary) from employee; -- 比如算出来的平均工资是5000
然后再select * from employee where salary > 5000按照这样的思路来查询。
虽然两步也可以达到想要的效果，但是实际却不会这么做，分两次请求不说，也增加了代码的复杂度，可以用子查询来解决。
子查询
- 定义
在SQL中，子查询(subquery) 是指嵌套在其他语句(select、insert、update、delete、merge) 中的select语句；子查询中也可以嵌套另外一个子查询。
如果用子查询实现上面的查询：
select * from employee where salary > (select avg(salary) from employee);
分类1
根据子查询的返回可以分成三类子查询：
标量子查询、行子查询、表子查询标量子查询：返回单个值(单行单列)的子查询，子查询可以替换成一个标量，比如上面的员工薪水就可以把平均值替换子查询语句。
select 后面可以使用标量子查询，比如我们想看看每个员工与平均工资之间的差距，只要是一个数值可以出现的地方，标量子查询都是可以使用的。
select first_name,
last_name,
salary,
salary - (select ROUND(AVG(salary),0) from employee) as difference
from employee;
行子查询：返回包含一个或者多个列的单行结果(一行多列)，标量子查询是行子查询的特例。
表子查询：返回的是虚拟的表(多行多列)，行子查询是表子查询的特例。表子查询可以当成一个数据表使用，from中的子查询，相当于创建了一个临时表，可以作为查询的一个数据源使用，mysql称之为派生表(derived table)。
比如下面的子查询：
-- 查询每个部门最高的工资
select o.name, -- 部门名称
e.salary
from organization o
join (
SELECT organization_id,
max(salary) as salary
from employee
group by organization_id
) e
on o.id = e.organization_id;
- 分类2
子查询也可以根据子查询会不会引用外部查询的列分为 关联子查询 和 非关联子查询。关联子查询查询时会引用外部查询的列，也就是里面的子查询不能单独执行，依赖于外部查询。没有引用外部查询的列就成为非关联子查询。
select job_id,
first_name,
last_name,
salary
from employee e
where salary = (
select max(salary)
from employee i
where i.job_id = e.job_id
);
比如上面的子查询 查询每个职位最高的薪水的员工信息。
这个子查询的查询顺序是：
- 先取employ的第一条数据，取出job_id，first_name，last_name，salary字段
- 用取出的job_id字段作为条件，在去找employ中job_id等于刚取出的job_id中最大的salary，如果主查询中的salary不满足条件则舍去，满足则返回，这时的查询可以用上索引。
- 重复12，直至所有的记录都遍历完毕。
注意：在不考虑数据库对这个查询进行优化的前提下，里面的这个子句是每次都要执行的。
select max(salary)
from employee i
where i.job_id = e.job_id
也可以在select中用关联子查询，比如 查询每个部门的所有员工薪水总和
select o.name,
(
select sum(salary)
from employee e
where e.organization_id = o.id
) as total_salary
from organization o;
说到关联子查询也就不得不说 EXISTS 关键字，比如我们想查看部门里包含员工名字叫做David的部门。
select o.name
from organization o
where exists (
select * from employee e
where e.organization_id = o.id
and e.first_name = 'David'
)
EXISTS只做存在性判断，如果里面的子查询有记录则外部的查询结果返回，否则舍去。NOT EXISTS和EXISTS相反，这里就不举例了。
