阿源的知识库

切换日光/暗黑模式

连接、子查询与集合查询 ​

学习目标 ​

  • 能根据结果粒度选择 JOIN、EXISTS 或聚合。
  • 能判断 ON、WHERE 对 LEFT JOIN 的不同影响。
  • 能避免标量子查询多行错误与 NOT IN 的 NULL 陷阱。

核心知识点 ​

本节基于 MySQL 8.0 / InnoDB 的 learning_lab.users/orders 种子数据：林同学有 101/25、102/15 两单，陈同学有 103/40 一单。连接是组合行：一对多关系让用户行重复，这不是数据库错误。子查询按结果形态分标量、行、表；相关子查询引用外层列，优化器可能改写执行方式，不能断言一定“每行执行一次”。

以下例子均限制种子 id，避免第四篇额外用户影响推演。明确 SELECT 字段和别名，先决定“一行代表用户还是订单”，再选择查询写法。JOIN 官方语法。

常用用法 ​

SELECT INNER JOIN：组合有匹配的用户与订单 ​

用于返回双方连接键匹配的明细行。

sql
-- 初始：用户 1 两单，用户 2 一单。
SELECT u.id AS uid,u.username,o.id AS oid,o.amount
FROM learning_lab.users u INNER JOIN learning_lab.orders o ON o.user_id=u.id
WHERE u.id IN (1,2) AND o.id BETWEEN 101 AND 103 ORDER BY o.id;
-- 关键变化：用户 1 返回两行，结果共三行。
-- 输出：1/林同学/101/25；1/林同学/102/15；2/陈同学/103/40。

JOIN 条件缺失或错误会扩大结果；不要用 DISTINCT 掩盖错误关系。INNER JOIN 中很多条件放 ON 或 WHERE 结果相同，但不适用于外连接。

SELECT LEFT JOIN：保留没有符合条件订单的用户 ​

用于保留左表用户，同时只匹配金额大于 30 的订单。

sql
-- 初始：用户 1 最大订单25，用户2订单40。
SELECT u.id,o.id AS oid
FROM learning_lab.users u LEFT JOIN learning_lab.orders o
 ON o.user_id=u.id AND o.amount>30 AND o.id BETWEEN 101 AND 103
WHERE u.id IN (1,2) ORDER BY u.id;
SELECT u.id,o.id AS oid
FROM learning_lab.users u LEFT JOIN learning_lab.orders o ON o.user_id=u.id
WHERE u.id IN (1,2) AND o.amount>30 AND o.id BETWEEN 101 AND 103;
-- 关键变化：ON 过滤保留用户1的 NULL 补行；WHERE 过滤排除该行。
-- 输出：第一组 (1,NULL),(2,103)；第二组只有 (2,103)。

ON 决定能否匹配，WHERE 对连接后的行过滤。识别无匹配应检查右表非空主键 o.id IS NULL，不要检查本身可空的业务列。

SELECT CROSS JOIN：生成笛卡尔积 ​

用于有意生成两个小集合的所有组合，例如报表维度补全。

sql
-- 初始：用户集合1、2，状态集合 NEW、PAID。
SELECT u.id,s.status FROM learning_lab.users u
CROSS JOIN (SELECT 'NEW' AS status UNION ALL SELECT 'PAID') s
WHERE u.id IN (1,2) ORDER BY u.id,s.status;
-- 关键变化：2乘2返回4行。
-- 输出：1/NEW、1/PAID、2/NEW、2/PAID。

大集合交叉会产生乘积规模，必须先估算结果量。MySQL 的 INNER/CROSS 语法较宽松，本节用 CROSS JOIN 强调有意不写连接条件。

SELECT self JOIN：对同一表比较两组行 ​

用于通过不同别名表达同一实体之间的关系。

sql
-- 初始：用户1余额100、用户2余额50。
SELECT a.id AS higher,b.id AS lower FROM learning_lab.users a
JOIN learning_lab.users b ON a.balance>b.balance
WHERE a.id IN (1,2) AND b.id IN (1,2);
-- 关键变化：两个别名比较余额100与50的用户，返回高余额id=1。
-- 输出：higher=1,lower=2。

自连接并不是另一种存储结构；别名只是区分同一表的两次引用。树状管理关系可用自连接一层查询，任意深度另用递归 CTE。

SELECT scalar subquery：在列中返回一个值 ​

用于在用户结果中附带单个聚合金额。

sql
-- 初始：用户1订单金额25+15=40。
SELECT id,(SELECT SUM(amount) FROM learning_lab.orders WHERE user_id=1 AND id IN (101,102)) AS total
FROM learning_lab.users WHERE id=1;
-- 关键变化：聚合子查询把25与15合并成单值40。
-- 输出：1/40.00。

标量子查询零行返回 NULL，多行报 Subquery returns more than 1 row。不能靠无 ORDER BY 的 LIMIT 1 随机消除错误；应先定义唯一条件或聚合规则。

SELECT correlated subquery：引用外层用户 ​

用于给每位用户计算与其连接键对应的订单数量。

sql
-- 初始：用户1两单、用户2一单。
SELECT u.id,(SELECT COUNT(*) FROM learning_lab.orders o
 WHERE o.user_id=u.id AND o.id BETWEEN 101 AND 103) AS order_count
FROM learning_lab.users u WHERE u.id IN (1,2) ORDER BY u.id;
-- 关键变化：子查询按外层id关联。
-- 输出：1/2、2/1。

关注关联条件索引与 EXPLAIN，不能把语义层的相关性直接等同于物理执行次数。子查询。

SELECT EXISTS：只判断是否有匹配行 ​

用于筛选有符合条件订单的用户而不展开订单明细。

sql
-- 初始：只有用户2的103订单金额大于30。
SELECT u.id FROM learning_lab.users u WHERE u.id IN (1,2)
AND EXISTS (SELECT 1 FROM learning_lab.orders o WHERE o.user_id=u.id AND o.amount>30 AND o.id BETWEEN 101 AND 103);
-- 关键变化：匹配存在条件后返回用户2。
-- 输出：2。

EXISTS 判断行存在，不关注 SELECT 1 的值；NOT EXISTS 适用于反向存在判断。IN 子查询则比较某一列的成员资格；不能简单说 EXISTS 永远更快。

SELECT IN subquery：按子查询集合筛选 ​

用于将子查询返回的用户编号作为外层用户的候选集合。

sql
-- 初始：103属于用户2且金额40，另外两单低于30。
SELECT id FROM learning_lab.users WHERE id IN (1,2)
 AND id IN (SELECT user_id FROM learning_lab.orders WHERE id BETWEEN 101 AND 103 AND amount>30);
-- 关键变化：子查询返回2，外层仅保留用户2。
-- 输出：id=2。

子查询必须返回一列；重复候选值不会让外层用户重复返回。它与 JOIN 明细展开的结果粒度不同；性能通过实际计划判断。

SELECT NOT IN：观察 NULL 陷阱 ​

用于验证未知候选值如何影响反向成员过滤。

sql
-- 输入：候选用户1与NULL，外层是用户1、2。
SELECT id FROM learning_lab.users WHERE id IN (1,2) AND id NOT IN (1,NULL);
SELECT u.id FROM learning_lab.users u WHERE u.id IN (1,2)
AND NOT EXISTS (SELECT 1 FROM (SELECT 1 AS uid UNION ALL SELECT NULL) blocked WHERE blocked.uid=u.id);
-- 关键变化：NOT IN 对2得到UNKNOWN；NOT EXISTS 只排除匹配的1。
-- 输出：第一组空集；第二组 id=2。

NOT IN 子查询候选列可能含 NULL 时，先明确 NULL 的业务含义；按“无等值匹配”语义可用 NOT EXISTS。若外层值也可空，两种写法仍可能有差别，不能机械替换。比较运算符。

SELECT UNION：合并并去重 ​

用于合并列数与类型兼容的查询结果并去除重复行。

sql
-- 输入：第一组用户1，第二组用户1、2。
SELECT id FROM learning_lab.users WHERE id=1
UNION SELECT id FROM learning_lab.users WHERE id IN (1,2) ORDER BY id;
-- 关键变化：重复id=1被去重。
-- 输出：1、2。

UNION 去重整个结果行，列名取第一查询；最终 ORDER BY 作用于组合结果。分支内部限制需按语法加括号，且不能替代最终排序。

SELECT UNION ALL：保留重复结果 ​

用于合并明确允许重复的结果并避免不必要的去重工作。

sql
-- 输入：第一组用户1，第二组用户1、2。
SELECT id FROM learning_lab.users WHERE id=1
UNION ALL SELECT id FROM learning_lab.users WHERE id IN (1,2) ORDER BY id;
-- 关键变化：两组均保留id=1。
-- 输出：1、1、2。

UNION ALL 不承诺分支输出顺序；仍用最终 ORDER BY。MySQL 8.0.31 新增 INTERSECT/EXCEPT，不应当成整个 8.0 系列都有的能力。UNION。

SELECT derived table：把中间结果作为表 ​

用于先按用户汇总再与用户实体连接，明确结果粒度。

sql
-- 初始：用户1订单总额40、用户2总额40。
SELECT u.id,t.total FROM learning_lab.users u
JOIN (SELECT user_id,SUM(amount) AS total FROM learning_lab.orders
 WHERE id BETWEEN 101 AND 103 GROUP BY user_id) t ON t.user_id=u.id
WHERE u.id IN (1,2) ORDER BY u.id;
-- 关键变化：3条明细归并为用户1、2的两行汇总。
-- 输出：1/40、2/40。

派生表需要别名；它可能被合并或物化，并不天然提高性能。LATERAL 派生表从 8.0.14 起支持，本例不依赖它。

易混点 ​

一对多 JOIN 返回多行是结果粒度决定的；EXISTS 保留用户粒度。LEFT JOIN 右表条件在 ON 与 WHERE 不可随意互换。UNION 和 JOIN 分别纵向合并结果与横向组合行。

课后小问 ​

  1. 查“有订单的用户”为什么可以选 EXISTS？

    答：只判断匹配存在，不需要为每张订单重复返回用户。

  2. 2 NOT IN (1,NULL) 为什么不为 TRUE？

    答：与 NULL 的比较未知，整个反向条件不能确定成立，WHERE 会排除它。

本节小结 ​

先决定每行代表什么，再选连接、存在判断或聚合；所有子查询都要检查行数、NULL 与列类型边界。

快速回顾 ​

  • LEFT JOIN 的 ON 负责匹配，WHERE 负责结果过滤。
  • 标量子查询最多一行；EXISTS 关心存在。
  • UNION 去重；UNION ALL 保留重复；两者都需显式最终排序。