首页 / MySQL 入门教程 / 派生表与 EXISTS

← 上一篇 下一篇 →
MySQL 入门教程

派生表与 EXISTS

本教程共 46 篇 · 第 25 篇 · 更新于 2026-07-30 · 约 10 分钟阅读

MySQLMySQL 入门教程派生表EXISTSDerived TableNOT EXISTS子查询

25. 派生表与 EXISTS

本节目标：理解派生表的概念和用法，掌握 EXISTS/NOT EXISTS 的存在性判断，学会在 EXISTS、IN、JOIN 之间选择最合适的方案，理解派生表的限制和优化器的处理方式。

25.1 派生表（Derived Table）

上一章提到过，子查询放在 FROM 子句里就变成了派生表。说白了，派生表就是把一个查询的结果当作一张临时表来用。

SELECT t.user_id, t.总消费
FROM (
    SELECT user_id, SUM(amount) AS 总消费
    FROM orders
    GROUP BY user_id
) AS t
WHERE t.总消费 > 1000;

这里的子查询 (SELECT user_id, SUM(amount) ... GROUP BY user_id) 就是派生表，起了别名 t。外层查询把 t 当作一张普通表来查。

为什么需要派生表

有些查询逻辑没法一步写完，需要先算出中间结果，再基于中间结果做过滤。派生表就是干这个的。

比如”查消费总额超过 1000 的用户详情”：

-- 不用派生表：HAVING 可以做到
SELECT u.*, SUM(o.amount) AS 总消费
FROM users u
INNER JOIN orders o ON o.user_id = u.id
GROUP BY u.id
HAVING SUM(o.amount) > 1000;

但如果是”查消费总额超过 1000 的用户的订单明细”，HAVING 就不够用了，因为订单明细是逐行的，而总额是聚合的：

-- 用派生表先算出每个用户的总消费，再关联订单明细
SELECT o.*
FROM orders o
INNER JOIN (
    SELECT user_id
    FROM orders
    GROUP BY user_id
    HAVING SUM(amount) > 1000
) AS t ON o.user_id = t.user_id;

派生表的使用规则

  1. 必须起别名：派生表必须有别名，不然报错。
-- 报错：Every derived table must have its own alias
SELECT * FROM (SELECT * FROM users WHERE age > 20);
-- 正确
SELECT * FROM (SELECT * FROM users WHERE age > 20) AS t;
  1. 列名必须唯一：派生表的列名不能重复。如果子查询有同名列，需要起别名区分。
-- 报错：两个 user_id 列名冲突
SELECT * FROM (
    SELECT u.id AS user_id, o.user_id
    FROM users u JOIN orders o ON o.user_id = u.id
) AS t;

-- 正确：起不同别名
SELECT * FROM (
    SELECT u.id AS uid, o.user_id AS oid
    FROM users u JOIN orders o ON o.user_id = u.id
) AS t;
  1. 不能引用外层查询的列：派生表是非相关的，不能像相关子查询那样引用外层。
Note

MySQL 5.7+ 优化器会尝试把派生表”合并”到外层查询中（叫 derived_merge 优化），避免实际创建临时表。但不是所有派生表都能合并，比如带 GROUP BY、DISTINCT、LIMIT 的通常不能合并，会生成临时表。8.0+ 的 derived_merge 策略更智能，但基本原则相同。

25.2 EXISTS：存在性判断

EXISTS 用来判断子查询是否返回了行。返回了至少一行就为 true，一行都没有就为 false。

-- 查有订单的用户
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
);

执行逻辑：外层每取一行用户，就执行一次子查询看这个用户有没有订单。有就保留，没有就跳过。

EXISTS 的特点

EXISTS 不关心子查询返回什么值，只关心有没有行返回。所以子查询的 SELECT 列写什么都行：

-- 这三种写法效果完全一样
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = u.id);
WHERE EXISTS (SELECT * FROM orders WHERE user_id = u.id);
WHERE EXISTS (SELECT 100 FROM orders WHERE user_id = u.id);

习惯上写 SELECT 1，表示”我只关心有没有行，不关心值”。

NOT EXISTS

NOT EXISTS 取反，判断”不存在”：

-- 查没有订单的用户
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
);
Tip

NOT EXISTS 比 NOT IN 更安全。上一章讲过 NOT IN 碰到 NULL 会返回空结果，而 NOT EXISTS 不受 NULL 影响。需要判断”不存在”时，优先用 NOT EXISTS。

25.3 EXISTS vs IN vs JOIN

这三个都能实现”查有/没有关联数据的行”，但原理和性能不同。

同一个需求的三种写法

-- 方法一：EXISTS
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 方法二：IN
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

-- 方法三：JOIN（注意去重）
SELECT DISTINCT u.*
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

性能对比

方式执行方式NULL 安全需要 DISTINCT
EXISTS外层驱动，逐行判断安全不需要
IN先算子查询，再匹配NOT IN 不安全不需要
JOIN两表关联安全需要（防重复）
Note

MySQL 8.0+ 的优化器很聪明，会自动把 IN 子查询优化成类似 EXISTS 的半连接（Semi-Join）。所以 IN 和 EXISTS 在 8.0+ 性能差异很小。但 NOT IN 和 NOT EXISTS 的差异仍然存在（NULL 问题）。

选择建议

场景推荐
判断”存在”关联数据EXISTS 或 IN，看可读性
判断”不存在”关联数据NOT EXISTS（比 NOT IN 安全）
需要同时显示关联表的数据JOIN
子查询结果集大EXISTS（逐行判断，不生成大结果集）
子查询结果集小IN（先算小集合再匹配）
Tip

经验法则：子查询表比外层表大用 EXISTS，子查询表比外层表小用 IN。但这只是倾向，实际性能还是得以 EXPLAIN 为准。我之前优化过一个查询，把 IN 改成 EXISTS 快了 5 倍，但另一个查询反着改才快。没有绝对最优，测了才知道。

25.4 派生表 vs CTE

派生表和 CTE（公用表表达式）功能类似，都是把子查询命名后引用。区别在于：

特性派生表CTE（WITH 子句）
语法嵌在 FROM 里写在语句开头
能否多次引用不能能
可读性嵌套深时差好，像变量
支持递归不支持支持
版本要求所有版本8.0+
-- 派生表：嵌在 FROM 里，只能用一次
SELECT * FROM (
    SELECT user_id, SUM(amount) AS total
    FROM orders GROUP BY user_id
) AS t WHERE total > 1000;

-- CTE：写在开头，可多次引用（下章详讲）
WITH user_totals AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders GROUP BY user_id
)
SELECT * FROM user_totals WHERE total > 1000;
Note

CTE 是 8.0+ 特性，5.7 不支持。如果你的 MySQL 版本是 5.7，只能用派生表。26.7 当然支持 CTE，新项目推荐用 CTE 替代派生表，可读性好太多。CTE 在下一章详细讲。

25.5 派生表的性能注意

派生表虽然方便，但有性能陷阱：

无法走索引

派生表是临时表，没有索引（除非优化器能合并到外层）。在派生表上做 WHERE 过滤或 JOIN，可能全表扫描临时表。

-- 派生表上没有索引，WHERE 过滤慢
SELECT * FROM (
    SELECT * FROM orders  -- 假设有 100 万行
) AS t
WHERE t.status = 'paid';

这种写法不如直接在外层 WHERE：

-- 直接过滤，能走索引
SELECT * FROM orders WHERE status = 'paid';

物化开销

不能被优化器合并的派生表会被”物化”—实际创建一张临时表存中间结果。如果中间结果很大，消耗内存和磁盘。

Warning

别把派生表当万能工具。简单的查询不需要包一层派生表。只有确实需要中间步骤时才用，而且尽量让派生表的结果集小一些（在子查询内部就做好过滤）。

25.6 实用示例

示例一：查每个用户最新的一条订单

SELECT o.*
FROM orders o
INNER JOIN (
    SELECT user_id, MAX(created_at) AS latest_time
    FROM orders
    GROUP BY user_id
) AS t ON o.user_id = t.user_id AND o.created_at = t.latest_time;

先在派生表里算出每个用户的最新订单时间，再关联回 orders 表取完整数据。

示例二：查消费排名前 3 的用户

SELECT u.username, t.总消费
FROM users u
INNER JOIN (
    SELECT user_id, SUM(amount) AS 总消费
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
    ORDER BY 总消费 DESC
    LIMIT 3
) AS t ON u.id = t.user_id;

示例三：EXISTS 判断用户是否有大额订单

SELECT u.username
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.amount > 500
);

25.7 小结

派生表是把子查询当临时表用，适合需要中间步骤的复杂查询。EXISTS 判断”有没有”关联数据，NOT EXISTS 比 NOT IN 更安全。三者选择原则：要显示关联数据用 JOIN、只判断存在性用 EXISTS/IN、需要中间结果用派生表或 CTE。派生表要注意性能—能合并到外层就合并，不能合并的注意临时表开销。下一章学集合操作 UNION 和 INTERSECT。

上一篇
子查询
下一篇
集合操作 UNION 与 INTERSECT