【SQL进阶篇】征服子查询！完全掌握SELECT中的SELECT

“我只想知道那些工资高于公司平均工资的员工…”
“每个部门里工资最高的员工是谁？”

作为一名Web开发者，在与数据库打交道的过程中，你是否也曾想过“把一个查询的结果用作另一个查询的条件”？这种一次查询无法完成、稍微有些复杂的数据提取，用我们这次要介绍的子查询（Subquery）就能漂亮地解决。

子查询，一言以蔽之，就是“查询中的查询”。它就像俄罗斯套娃一样，是在一个SELECT语句中嵌套另一个SELECT语句的技巧。如果能熟练运用，你就可以把原本需要多个步骤的数据提取整合到一个查询中，你的SQL技能也会因此大幅提升！

本文准备了大量即使是初学者也能直接复制粘贴运行的示例代码，希望通过“代码成功运行”的亲身体验，让你感受到子查询的强大与便捷。

准备：本次练习用的数据表

为了最大限度地体验子查询的威力，这次我们准备了“员工(employees)”和“部门(departments)”两张表。请复制下方的SQL语句，在你自己的数据库环境中执行。

当然，如果手边没有可以立刻尝试的环境也不用担心！文章后半部分我们准备了仅用浏览器就能体验SQL的“SQL执行环境”。


-- 如果表已存在则删除
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

-- 创建部门表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

-- 插入部门数据
INSERT INTO departments (id, department_name) VALUES
(1, '営業部'),
(2, '開発部'),
(3, '人事部');

-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    salary INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- 插入员工数据
INSERT INTO employees (id, name, department_id, salary) VALUES
(1, '山田 太郎', 1, 600000), -- 销售部
(2, '鈴木 花子', 2, 550000), -- 开发部
(3, '佐藤 次郎', 1, 400000), -- 销售部
(4, '高橋 三郎', 3, 650000), -- 人事部
(5, '田中 恵子', 2, 500000), -- 开发部
(6, '伊藤 四郎', 1, 700000), -- 销售部
(7, '渡辺 久美', 2, 750000), -- 开发部
(8, '山本 五郎', 3, 380000); -- 人事部

准备好了吗？那么，就让我们踏入子查询的强大世界吧！

1. 基础中的基础：在WHERE子句中使用子查询

子查询最基本、最直观的用法，就是在WHERE子句中作为条件使用。你可以把它想象成用内部查询（子查询）动态地为外部查询（主查询）生成筛选条件。

规则是子查询必须用()（括号）括起来。SQL会先执行括号里的子查询，然后将执行结果用作主查询的条件。

示例1：提取工资高于平均工资的员工

我们来试试文章开头提到的“找出工资高于全体员工平均工资的员工”。要实现这个目标，需要两个步骤：

首先，计算出“全体员工的平均工资”。
然后，以这个平均工资为条件，提取员工信息。

使用子查询，就可以用一个查询完成这个任务。


SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

怎么样？首先，(SELECT AVG(salary) FROM employees)被执行，计算出平均工资（在本例中是547500），然后这个结果被用作主查询的条件，即WHERE salary > 547500。漂亮地用一个查询就达成了目标！

示例2：提取特定部门的员工 (与IN运算符联动)

子查询不仅可以返回单个值，还可以返回一个多值的列表。将这个列表与IN运算符结合使用，可以实现非常灵活的条件指定。

例如，我们想提取所有隶属于“销售部”或“人事部”的员工。我们可以用子查询从部门名称中获取部门ID的列表，然后将其用作主查询的条件。


SELECT *
FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE department_name IN ('営業部', '人事部')
);

在这个查询中，子查询会先被执行，返回'営業部'(销售部)和'人事部'(人事部)的ID列表，即(1, 3)。然后，主查询被解析为WHERE department_id IN (1, 3)，并提取相应员工的数据。这样做非常方便，因为即使部门名称变更，也无需修改SQL。

2. 进阶用法：在SELECT和FROM子句中使用子查询

子查询除了在WHERE子句中大放异彩，在SELECT和FROM子句中也同样作用巨大，能极大地拓宽数据分析的范围。

SELECT子句中的子查询 (标量子查询)

在SELECT子句中使用子查询，可以为主查询的每一行获取一个相关的值，并将其作为一个新列显示。但是，这种子查询必须返回单行单列的单个值（标量值）。

举个例子，我们在显示每个员工信息的同时，也列出“该员工所属部门的平均工资”。


SELECT
    name,
    salary,
    (SELECT department_name FROM departments WHERE id = e.department_id) AS department_name,
    (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) AS department_avg_salary
FROM
    employees AS e;

看起来有点复杂吧。这被称为相关子查询，子查询（内侧）会引用主查询（外侧）每一行的数据（本例中是e.department_id）来执行。主查询每前进一行，子查询就会利用那一行的部门ID来计算部门名称和部门平均工资，并添加到结果的列中。

FROM子句中的子查询 (内联视图)

在FROM子句中使用子查询，可以将其结果视为一个“临时的虚拟表（内联视图）”。这种方式对于分两步处理的场景非常方便：先用子查询对数据进行汇总或加工，然后再对这个结果执行进一步的查询。

例如，我们想“计算各部门的平均工资，然后只显示其中平均工资在55万以上的部门”。


SELECT
    d.department_name,
    dept_avg.avg_salary
FROM
    (
        SELECT department_id, AVG(salary) AS avg_salary
        FROM employees
        GROUP BY department_id
    ) AS dept_avg
JOIN
    departments AS d ON dept_avg.department_id = d.id
WHERE
    dept_avg.avg_salary >= 550000;

在这个查询中，首先会执行FROM子句中的子查询，创建一个计算了每个department_id平均工资的临时表dept_avg。然后，主查询将这个dept_avg表与departments表连接，并显示符合条件的部门名称和平均工资。当你想对GROUP BY的结果再使用WHERE子句时，这是一个非常有效的技巧。

实践！在浏览器中执行SQL吧

久等了！这里是一个能让你亲手实践所学子查询的环境。

请把下面的代码完整地复制下来，保存成一个如subquery_practice.html这样的文件，然后在浏览器中打开它。试试本文中介绍的各种子查询，亲身体验它们的运作方式吧！


<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <title>SQL子查询练习场</title>
    <script src="https://cdn.jsdelivr.net/npm/alasql@4"></script>
    <style>
        body { font-family: sans-serif; padding: 2rem; background-color: #f9f9f9; }
        .container { max-width: 800px; margin: auto; background: white; padding: 2rem; border-radius: 8px; box-shadow: 0 4px 8px rgba(0,0,0,0.1); }
        h1 { color: #333; }
        textarea { width: 100%; height: 180px; font-family: monospace; font-size: 16px; padding: 10px; border: 1px solid #ccc; border-radius: 4px; box-sizing: border-box; margin-bottom: 1rem; }
        button { background-color: #007bff; color: white; padding: 10px 20px; border: none; border-radius: 4px; cursor: pointer; font-size: 16px; }
        button:hover { background-color: #0056b3; }
        #result-area { margin-top: 2rem; }
        table { width: 100%; border-collapse: collapse; margin-top: 1rem; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background-color: #f2f2f2; }
        .error { color: red; font-weight: bold; }
    </style>
</head>
<body>
    <div class="container">
        <h1>来试试子查询吧！</h1>
        <p>在下方的文本框中输入SQL语句，然后点击“执行SQL”按钮。</p>
        <textarea id="sql-input">-- 在这里写下你的SQL
SELECT
    d.department_name,
    dept_avg.avg_salary
FROM
    (
        SELECT department_id, AVG(salary) AS avg_salary
        FROM employees
        GROUP BY department_id
    ) AS dept_avg
JOIN
    departments AS d ON dept_avg.department_id = d.id
WHERE
    dept_avg.avg_salary >= 550000;</textarea>
        <button onclick="executeSQL()">执行SQL</button>
        <div id="result-area"></div>
    </div>

    <script>
        // 初始化数据库并准备数据
        const db = new alasql.Database();
        db.exec(`
            CREATE TABLE departments (id INT PRIMARY KEY, department_name VARCHAR(50));
            INSERT INTO departments VALUES (1, '営業部'), (2, '開発部'), (3, '人事部');

            CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), department_id INT, salary INT);
            INSERT INTO employees VALUES
            (1, '山田 太郎', 1, 600000), (2, '鈴木 花子', 2, 550000), (3, '佐藤 次郎', 1, 400000),
            (4, '高橋 三郎', 3, 650000), (5, '田中 恵子', 2, 500000), (6, '伊藤 四郎', 1, 700000),
            (7, '渡辺 久美', 2, 750000), (8, '山本 五郎', 3, 380000);
        `);

        function executeSQL() {
            const sql = document.getElementById('sql-input').value;
            const resultArea = document.getElementById('result-area');
            resultArea.innerHTML = '';

            try {
                const result = db.exec(sql);
                if (result.length > 0) {
                    resultArea.appendChild(createTable(result));
                } else {
                    resultArea.innerHTML = '<p>查询结果为0条。</p>';
                }
            } catch (e) {
                resultArea.innerHTML = `<p class="error">错误: ${e.message}</p>`;
            }
        }

        function createTable(data) {
            const table = document.createElement('table');
            const thead = table.createTHead();
            const tbody = table.createTBody();
            const headerRow = thead.insertRow();
            for (const key in data[0]) {
                const th = document.createElement('th');
                th.textContent = key;
                headerRow.appendChild(th);
            }
            data.forEach(rowData => {
                const row = tbody.insertRow();
                for (const key in rowData) {
                    const cell = row.insertCell();
                    cell.textContent = rowData[key];
                }
            });
            return table;
        }

        // 初始显示
        executeSQL();
    </script>
</body>
</html>

注意事项与相关技巧

子查询非常强大，但也有几个需要注意的地方。了解这些能帮助你写出更优雅的查询。

性能问题

子查询，特别是刚才介绍的相关子查询，因为需要对主查询的每一行都执行一次，所以当数据量变大时，性能可能会急剧下降。在很多情况下，子查询可以用JOIN来改写，并且后者通常运行得更快。

子查询的返回值

子查询返回的数据形式是有规则的。

当与=, >, <等比较运算符一起使用时，子查询必须返回单个值（标量值）。
当与IN运算符一起使用时，可以返回一个单列的列表。
当在SELECT子句中使用时，也必须是单个值。

违反这些规则会导致错误，因此时刻清楚你的子查询会返回什么样的结果非常重要。

总结

辛苦了！这次我们深入探讨了SQL中一个强大的技巧——子查询的用法。

WHERE子句中的子查询：动态生成查询条件的最基本用法。
SELECT子句中的子查询：为每一行获取相关值，并作为新列添加（相关子查询）。
FROM子句中的子查询：将查询结果作为临时表，进行更复杂的查询。
性能：子查询虽然方便，但时刻考虑用JOIN进行改写也很重要。

熟练运用子查询后，过去需要分好几步才能完成的数据提取现在可以一次搞定，工作效率将得到飞跃性的提升。请务必活用本次介绍的SQL执行环境，多多尝试各种模式，将子查询化为己用吧！