SQL 子查询全面详解

1. 子查询概念

子查询（Subquery）是指嵌套在另一个 SQL 查询中的查询，也称为内部查询或嵌套查询。子查询的结果会被外部查询使用。

-- 基本结构
SELECT 列名
FROM 表名
WHERE 列名 操作符 (SELECT 列名 FROM 表名 WHERE 条件);

2. 子查询分类

2.1 按位置分类

• SELECT 子句中的子查询

-- 显示每个员工的工资及其与平均工资的差值
SELECT 
    name,
    salary,
    (SELECT AVG(salary) FROM employees) as avg_salary,
    salary - (SELECT AVG(salary) FROM employees) as difference
FROM employees;

• FROM 子句中的子查询（派生表）

-- 统计每个部门的平均工资
SELECT dept_name, ROUND(avg_salary, 2) as avg_salary
FROM (
    SELECT d.name as dept_name, AVG(e.salary) as avg_salary
    FROM departments d
    LEFT JOIN employees e ON d.id = e.department_id
    GROUP BY d.name
) as dept_avg;

• WHERE 子句中的子查询

-- 查询工资第二高的员工
SELECT name, salary
FROM employees
WHERE salary = (
    SELECT DISTINCT salary
    FROM employees
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1
);

        上述代码详解：

                 步骤一：子查询执行过程：

                        SELECT DISTINCT salary - 选择不重复的工资

                        ORDER BY salary DESC - 按工资降序排列

                        LIMIT 1 OFFSET 1 - 跳过第1条，取1条

                步骤二：执行主查询

                        SELECT name, salary
                        FROM employees
                        WHERE salary =           -- 子查询的结果

• HAVING 子句中的子查询

-- 查询平均工资高于公司平均工资的部门
SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

2.2 按返回值分类

• 标量子查询：返回单个值

-- 查询工资高于平均工资的员工
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

• 列子查询：返回单列多行

        示例：查询在'IT'或'Sales'部门工作的员工

SELECT name, department_id
FROM employees
WHERE department_id IN (
    SELECT id FROM departments 
    WHERE name IN ('IT', 'Sales')
);

        详解：

                departments表：

                employees表：

执行步骤分解

     1.先执行子查询：

SELECT id FROM departments WHERE name IN ('IT', 'Sales')

        结果：【1,2】（IT部门id=1，Sales部门id=2）

     2.再执行主查询：

SELECT name, department_id 
FROM employees 
WHERE department_id IN (1, 2)

最终结果：

理解要点：子查询先找出部门ID,然后主查询用这些ID来筛选员工。

• 行子查询：返回单行多列

-- 查询与张三工资和部门都相同的员工
SELECT name, salary, department_id
FROM employees
WHERE (salary, department_id) = (
    SELECT salary, department_id
    FROM employees
    WHERE name = '张三'
);

• 表子查询：返回多行多列

        找出平均工资超过5000的部门及其平均工资

SELECT dept_name, avg_salary
FROM (
    SELECT d.name as dept_name, AVG(e.salary) as avg_salary
    FROM departments d
    JOIN employees e ON d.id = e.department_id
    GROUP BY d.name
) as dept_stats
WHERE avg_salary > 5000;

        详解：

                departments表：

               employees表：

        执行分解

                步骤一：先执行子查询（创建临时表）

SELECT d.name as dept_name, AVG(e.salary) as avg_salary
FROM departments d
JOIN employees e ON d.id = e.department_id
GROUP BY d.name

        子查询执行过程：

                1.连接两个表：departments和employees

                2.按部门名称分组

                3.计算每个部门的平均工资

        子查询结果（临时表dept_stats）：

        步骤二：主查询筛选临时表

SELECT dept_name, avg_salary
FROM dept_stats  -- 这里的dept_stats就是上一步的临时表
WHERE avg_salary > 5000

        最终结果：

相关子查询

        相关子查询是指子查询的执行依赖于外部查询的值的查询。也就是说，子查询需要外部查询的每一行数据来执行。

核心特征：

• 子查询不能独立执行，必须依赖外部查询

• 子查询会为外部查询的每一行数据执行一次

• 通常使用表别名来建立关联

经典例子详解

例子：查询每个部门中工资高于该部门平均工资的员工

SELECT name, salary, department_id
FROM employees e1  -- 外部查询
WHERE salary > (
    -- 这个子查询依赖于外部查询的e1.department_id
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id  -- 关键关联！
);

3.子查询操作符详解

3.1  IN / NOT IN

-- 查询有订单的客户
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- 查询没有订单的客户
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

3.2  EXISTS / NOT EXISTS

-- 使用EXISTS查询有订单的客户（性能更好）
SELECT name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- 查询没有订单的客户
SELECT name FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

3.3   ANY / SOME

-- 查询工资高于任意一个'IT'部门员工的员工
SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary FROM employees 
    WHERE department_id = (SELECT id FROM departments WHERE name = 'IT')
);

3.4   ALL

-- 查询工资高于所有'IT'部门员工的员工
SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees 
    WHERE department_id = (SELECT id FROM departments WHERE name = 'IT')
);

4.力扣实战题目

题目一：1965.丢失信息的雇员

编写解决方案，找到所有 丢失信息 的雇员 id。当满足下面一个条件时，就被认为是雇员的信息丢失：

• 雇员的 姓名 丢失了，或者
• 雇员的 薪水信息 丢失了

返回这些雇员的 id  employee_id ， 从小到大排序 。

SELECT employee_id 
FROM Employees
WHERE
employee_id NOT IN (SELECT employee_id FROM Salaries)
UNION
SELECT employee_id
FROM Salaries
WHERE
employee_id NOT IN (SELECT employee_id FROM Employees)
ORDER BY employee_id;

  注：UNION 用于合并两个或多个SELECT语句的结果集，并自动去除重复行。

题目二：1978.上级经理已离职的员工

查找这些员工的id，他们的薪水严格少于$30000 并且他们的上级经理已离职。当一个经理离开公司时，他们的信息需要从员工表中删除掉，但是表中的员工的manager_id  这一列还是设置的离职经理的id 。

返回的结果按照employee_id 从小到大排序。

SELECT employee_id
FROM Employees
WHERE manager_id NOT IN (
    SELECT employee_id FROM Employees
) AND salary < 30000
ORDER BY employee_id;

题目三：511.游戏玩法分析

查询每位玩家 第一次登录平台的日期。

SELECT player_id, MIN(event_date) as first_login
FROM Activity
GROUP BY player_id;