【大数据系列之MySQL】（二十五）：MySQL中的子查询

子查询：出现在其它语句内部的select语句，称为子查询或内查询，就是嵌套的查询结果集

子查询分类

按子查询出现的位置：

• select后面：仅仅支持标量子查询
• from后面：支持表子查询
• where或having后面：标量子查询、列子查询、行子查询
• exists后面（相关子查询）

按结果集的行列数不同：

• 标量子查询：结果集只有一行一列
• 列子查询：结果集只有一列多行
• 行子查询：结果集有一行多列，也可多行多列
• 表子查询：结果集一般为多行多列

特点：

• 子查询放在小括号内
• 子查询一般放在条件的右侧
• 标量子查询一般搭配单行操作符的使用（> < <= >= = <>）
• 列子查询一般搭配多行操作符的使用（in、any、all）

一、where后面子查询

1.标量子查询

案例1：谁的工资比Abel高？

首先需要查询出Abel的工资，查询结果为一个标量集，所以在where后面当一个常量值使用

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

案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名、job_id和工资

首先需要查询出141号的job_id，然后再查询出143号的salary，然后再主查询中进行条件筛选

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：返回公司工资最少的员工的last_name，job_id和salary

SELECT
    last_name,
    job_id,
    salary 
FROM
    employees 
WHERE
    salary =(
    SELECT
        min( salary ) 
FROM
    employees)

案例4：查询最低工资大于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)

2.列子查询

案例1：返回location_id是1400或1700的部门中的所有员工姓名

首先查询出location_id为1400或1700的部门编号

SELECT
    last_name 
FROM
    employees 
WHERE
    department_id IN (
    SELECT
        department_id 
    FROM
        departments 
    WHERE
    location_id IN ( 1400, 1700 ) 
    )

案例2：返回其他工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id以及salary

首先查询出工种为IT_PROG的工资列表，由于是小于任一，也就是小于其中任意一个就行，所以使用any关键字进行筛选

SELECT
    employee_id,
    last_name,
    job_id,
    salary 
FROM
    employees 
WHERE
    salary < ANY ( SELECT salary FROM employees WHERE job_id = 'IT_PROG' ) 
    AND job_id <> 'IT_PROG';

3.行子查询

案例：查询员工编号最小并且工资最高的员工信息

先把最小编号和最高工资查询出来，是一个行信息，然后用于之后的筛选

SELECT
    * 
FROM
    employees 
WHERE
    ( employee_id, salary ) = ( SELECT min( employee_id ), max( salary ) FROM employees );

二、select后面子查询

案例1：查询每个部门的员工个数及其部门信息

SELECT
    d.*,
    ( SELECT count(*) FROM employees WHERE department_id = d.department_id ) 
FROM
    departments d;

案例2：查询员工号为102的部门名

SELECT
    ( SELECT department_name FROM departments d JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id = 102 );

三、from后面子查询

案例：查询每个部门的平均工资的工资等级

首先查出每个部门的平均工资，相当于一张新的表，由于是中间数据新表，所以需要起别名，然后再使用sql99的非等值连接即可

SELECT
    avg,
    grade_level 
FROM
    ( SELECT avg( salary ) avg FROM employees GROUP BY department_id ) a
    JOIN job_grades j ON a.avg BETWEEN j.lowest_sal 
    AND j.highest_sal;

四、exists后面（相关子查询）

exist可以看成一个函数，该函数判断给定结果集中是否有数据，如果有则返回1，否则返回0，就是一个判断结果集是否为空的函数

案例1：查询有员工的部门名

可以看成是双重for循环，首先遍历部门表，拿着部门id去筛选，判断员工表中是否有员工的部门id等于该id，一旦有返回的集合就不为空，该部门名选中，如果没有则返回空。

第二种方式使用in，首先查询出员工表中是否有部门表中的部门id，然后使用in判断部门表的部门id是否在该子查询中

# 方式一
SELECT
    department_name 
FROM
    departments d 
WHERE
    EXISTS (
    SELECT
        * 
    FROM
        employees 
    WHERE
    department_id = d.department_id 
    )
    
# 方式二
SELECT
    department_name 
FROM
    departments d 
WHERE
    department_id IN (
    SELECT
        department_id 
    FROM
        employees 
WHERE
    department_id = d.department_id)

案例2：查询没有女朋友的男神信息

方式一使用exist，拿着男表中的id去查询女表中，一旦查询出结果，那么则返回1，所以使用notexist，不存在即结果集为空则筛选对该男信息

方式二使用not in，查询出女表中的所有boyid，然后判断男表的id是否在其中

# 方式一
SELECT
    * 
FROM
    boys 
WHERE
    NOT EXISTS (
    SELECT
        * 
    FROM
        beauty 
    WHERE
    boyfriend_id = boys.id 
    )
    
# 方式二
SELECT
    * 
FROM
    boys 
WHERE
    id NOT IN ( SELECT boyfriend_id FROM beauty );