MySQL高级查询技巧：子查询、派生表与联合查询实战

子查询、派生表和联合查询是MySQL中处理复杂数据逻辑的核心工具。子查询可嵌套在查询中，用于动态提供条件或字段值，适用于先计算后过滤的场景；派生表作为FROM子句中的子查询，能将复杂逻辑封装为临时表，便于多层聚合与分析；联合查询（UNION/UNION ALL）则用于合并多个结果集，UNION ALL因不去重而性能更高，适合大数据量合并。选择子查询或JOIN需权衡可读性与性能，关联子查询可能带来性能瓶颈，而JOIN通常更高效；派生表在分步处理、窗口函数结果重用和避免重复计算方面优势显著。实际应用中应根据数据量、索引情况和业务需求灵活选择，结合EXPLAIN优化执行计划，提升查询效率。

在MySQL的世界里，当你发现简单的SELECT、WHERE和JOIN已经无法满足你对数据挖掘的渴望时，子查询、派生表和联合查询这些“高级武器”就该登场了。它们不仅仅是语法上的扩展，更是一种思维模式的转变，让你能以更灵活、更强大的方式去组织和处理复杂的数据逻辑，从而深入洞察数据背后的故事。

子查询：查询中的查询，灵活的战术部署

子查询，简单来说，就是嵌套在另一个查询内部的查询。它的结果可以作为外部查询的条件、字段，甚至是一张临时表。在我日常工作中，子查询就像是那些灵活的侦察兵，能帮我先行获取一些关键信息，再供主查询使用。

比如，我想找出那些订单总金额超过公司平均订单金额的客户。这要是没有子查询，我得先算平均值，再写另一个查询，多麻烦。但有了子查询，事情就简单多了：

SELECT
    c.customer_name,
    SUM(o.amount) AS total_spent
FROM
    customers c
JOIN
    orders o ON c.customer_id = o.customer_id
GROUP BY
    c.customer_name
HAVING
    SUM(o.amount) > (SELECT AVG(amount) FROM orders);

这里

(SELECT AVG(amount) FROM orders)

WHERE

IN

EXISTS

FROM

SELECT

派生表：临时的工作台，结构化复杂逻辑

派生表，其实就是

FROM

假设我们需要分析每个部门销售额排名前三的员工。这事儿如果只用JOIN，可能得绕好几个弯，而且逻辑会变得很臃肿。但用派生表，我们可以先在子查询中给每个部门的员工销售额排名，然后在外层查询中筛选出前三名：

SELECT
    d.department_name,
    ranked_sales.employee_name,
    ranked_sales.sales_amount
FROM
    departments d
JOIN
    (SELECT
        e.employee_id,
        e.employee_name,
        e.department_id,
        SUM(s.amount) AS sales_amount,
        ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY SUM(s.amount) DESC) AS rn
    FROM
        employees e
    JOIN
        sales s ON e.employee_id = s.employee_id
    GROUP BY
        e.employee_id, e.employee_name, e.department_id
    ) AS ranked_sales ON d.department_id = ranked_sales.department_id
WHERE
    ranked_sales.rn <= 3;

这里的

ranked_sales

联合查询：合并同类项，拓宽数据视野

联合查询（

UNION

UNION ALL

我经常用它来合并那些在逻辑上相关，但物理上分散在不同表或不同分区的数据。比如，某个电商平台，今年的订单数据放在一张表，去年的又在另一张归档表里。如果我想统计近两年的总销售额，UNION就派上用场了：

SELECT
    order_date,
    amount
FROM
    orders_2023
WHERE
    order_status = 'completed'
UNION ALL
SELECT
    order_date,
    amount
FROM
    orders_2022
WHERE
    order_status = 'completed';

这里的

UNION ALL

UNION

UNION ALL

子查询与JOIN：性能考量与选择策略

这确实是个老生常谈的问题，但它背后的权衡艺术，却值得我们反复琢磨。在我看来，子查询和JOIN并非水火不容，它们各有擅长，关键在于“何时”以及“如何”使用。

性能差异的本质： 很多时候，我们会听到“子查询性能不如JOIN”的说法。这在特定场景下是成立的。当子查询作为

WHERE

JOIN操作则不同，它通常会通过索引扫描、哈希连接等方式，高效地将两张表的数据进行匹配。优化器在处理JOIN时，有更多的策略和算法可以选择，比如调整表的连接顺序，利用索引等等。

选择策略：

Tana

“节点式”AI智能笔记工具，支持超级标签。

下载

1. 可读性与复杂性：
   • 子查询在某些情况下能让逻辑更清晰，尤其是当你需要先计算一个聚合值或筛选一个特定集合，再用这个结果去过滤主查询时。比如，找出那些购买了特定商品的用户，

     WHERE user_id IN (SELECT user_id FROM order_items WHERE product_id = X)

     ，这比写一个复杂的JOIN再GROUP BY可能更直观。
   • JOIN在处理多表关联、一对多或多对多关系时，其结构通常更紧凑，也更容易理解数据流向。当你的查询涉及多个表的连接条件时，JOIN是首选。
2. 数据量与索引：
   • 当子查询返回的结果集非常小，或者子查询本身可以高效地利用索引时，其性能并不会比JOIN差。
   • 如果子查询返回的结果集很大，或者子查询无法有效利用索引，那么转换成JOIN，尤其是

     LEFT JOIN

     或

     INNER JOIN

     ，往往能获得更好的性能。例如，将

     WHERE EXISTS (SELECT 1 FROM other_table WHERE ...)

     转换为

     INNER JOIN other_table ON ...

     。
3. 特定功能需求：
   • 有些场景，比如需要进行集合操作（

     INTERSECT

     ，尽管MySQL没有直接的

     INTERSECT

     关键字，但可以通过

     INNER JOIN

     或

     IN

     子查询模拟），子查询是更自然的选择。
   • 需要对每个分组计算一个聚合值并基于此进行筛选时，

     HAVING

     子句中的子查询非常方便。

我个人的经验是，如果一个子查询能清晰地表达你的意图，并且经过测试发现性能尚可，那就用它。但如果发现查询变慢，或者逻辑变得过于复杂，第一时间就应该考虑将其重构为JOIN。很多时候，通过

EXPLAIN

派生表在数据分析和聚合中的独特优势

派生表，在我看来，是数据分析师和数据库开发者手中的一把利器，尤其在处理多层聚合和复杂报表生成时，它的优势简直是压倒性的。它提供了一种“分阶段处理”数据的能力，让复杂的逻辑变得可管理。

1. 简化复杂聚合逻辑： 想象一下，你需要计算每个用户的平均订单价值，然后找出那些平均订单价值高于整体平均水平的用户。这在没有派生表的情况下，你可能需要写两个独立的查询，或者一个非常复杂的嵌套子查询。但有了派生表，你可以：

• 第一步（派生表内）： 计算每个用户的平均订单价值。
• 第二步（派生表外）： 将这个结果集作为一张“临时表”，再与整体平均值进行比较。

SELECT
    u.user_name,
    user_avg_order.avg_value
FROM
    users u
JOIN
    (SELECT
        o.user_id,
        AVG(o.amount) AS avg_value
    FROM
        orders o
    GROUP BY
        o.user_id
    ) AS user_avg_order ON u.user_id = user_avg_order.user_id
WHERE
    user_avg_order.avg_value > (SELECT AVG(amount) FROM orders);

这种分步处理的方式，让代码逻辑清晰，每一步的目的都非常明确。

2. 实现窗口函数的结果重用： 虽然MySQL 8.0以后引入了窗口函数（

ROW_NUMBER()

RANK()

SUM() OVER()

WHERE

3. 避免重复计算，提高可读性： 当你需要在同一个查询中多次引用某个复杂的计算结果时，如果不用派生表，你可能需要重复写这段计算逻辑，这不仅增加了代码量，也让维护变得困难。将这个计算封装在派生表中，就像给它起了个临时的别名，后续可以直接引用，大大提高了代码的可读性和维护性。这就像是你在写一篇长论文，先把一些基础数据整理成一个附录，正文直接引用附录的结论，而不是每次都把原始数据和计算过程重复一遍。

4. 模拟视图，但更灵活： 派生表可以看作是一个临时的、仅在当前查询中有效的“视图”。它不需要像正式视图那样预先创建，也不占用持久存储空间。这使得它在进行探索性数据分析、快速原型验证或一次性复杂查询时非常方便。你可以随时调整派生表的内部逻辑，而不用担心影响到其他查询或数据库结构。

当然，派生表也不是万能药。过多的嵌套派生表可能会让查询变得难以理解和优化。我的建议是，当逻辑需要分层处理，或者需要对中间结果进行多次操作时，优先考虑派生表。但如果仅仅是简单的连接，直接使用JOIN往往更高效。

UNION与UNION ALL：如何巧妙合并异构数据源？

UNION

UNION ALL

核心区别：去重与否

• UNION

  ： 合并结果集，并自动去重。这意味着如果两个查询结果中有完全相同的行，

  UNION

  只会保留一份。这个去重过程需要额外的计算资源和时间，尤其当结果集非常大时，性能开销会比较明显。

• UNION ALL

  ： 合并结果集，保留所有重复行。它只是简单地将两个结果集堆叠在一起，没有去重过程，因此通常比

  UNION

  效率更高。

何时选择：

1. 需要去重时选择

   UNION

   ：
   • 合并客户列表： 假设你有来自线上商城和线下门店的客户数据，你想得到一个不重复的客户总列表。这时，

     UNION

     是理想选择，因为它会自动剔除那些同时在两边都注册过的客户。
   • 查找唯一事件： 如果你想合并来自不同日志表的事件，但只关心事件的唯一性，而不是事件发生的次数，那么

     UNION

     能帮你得到一个干净的唯一事件列表。
   • 示例：获取所有销售过商品的员工和所有管理过部门的员工的唯一列表。

     SELECT employee_id, employee_name FROM sales_team
     UNION
     SELECT employee_id, employee_name FROM management_team;

2. 不需要去重且追求性能时选择

   UNION ALL

   ：
   • 合并历史数据与当前数据： 就像前面提到的，将不同年份的订单数据合并起来进行统计分析。由于订单本身不太可能完全重复（即使有，也是不同年份的），

     UNION ALL

     是更高效的选择。
   • 分表查询： 很多大型系统会将数据分散到不同的物理表或分区中。如果需要对所有分表进行查询并汇总结果，且知道各分表之间的数据是独立的（无重复），

     UNION ALL

     能提供最佳性能。
   • 统计总数： 如果你只是想计算总行数，或者对合并后的所有数据进行聚合（如

     SUM

     、

     COUNT

     ），

     UNION ALL

     会更快，因为去重操作在这里是多余的。
   • 示例：合并近两年的销售额明细。

     SELECT order_id, customer_id, amount FROM sales_2023
     UNION ALL
     SELECT order_id, customer_id, amount FROM sales_2022;

使用注意事项：

• 列数和列类型匹配：

  UNION

  和

  UNION ALL

  要求所有

  SELECT

  语句的列数必须相同，并且对应列的数据类型必须兼容（MySQL会自动进行隐式转换，但最好保持一致，避免意外）。
• 列名： 最终结果集的列名通常以第一个

  SELECT

  语句的列名为准。如果你想指定合并后的列名，可以在第一个

  SELECT

  语句中给列设置别名。
• 排序： 如果需要对整个合并结果集进行排序，

  ORDER BY

  子句只能放在最后一个

  SELECT

  语句的后面，并且作用于整个合并后的结果。

我个人在工作中，倾向于优先使用

UNION ALL

UNION