SQL 基本功：查询、连接与聚合

本文是 MySQL 系统学习系列的 L1 入门篇，学习篇第一篇，前置：无。 学完可以配合面试题食用：MySQL 8.0 窗口函数与 CTE 实战

先搞清楚：一条 SELECT 是按什么顺序执行的

SQL 写多了，容易默认它按书写顺序执行。其实 SQL 是声明式语言：你只描述要什么结果，怎么执行由优化器决定。但有一条逻辑执行顺序基本不变，理解了它，一半的"灵异现象"都能自己解释：

flowchart LR
    A["FROM / JOIN<br/>确定数据集"] --> B["WHERE<br/>逐行过滤"]
    B --> C["GROUP BY<br/>分组"]
    C --> D["HAVING<br/>过滤组"]
    D --> E["SELECT<br/>计算列与别名"]
    E --> F["ORDER BY<br/>排序"]
    F --> G["LIMIT<br/>截断"]

这条流水线直接解释一个高频报错：WHERE 里不能用 SELECT 定义的别名——执行到 WHERE 时 SELECT 还没跑，别名还不存在；而 ORDER BY 排在 SELECT 之后，用别名没问题。

-- 报错：ERROR 1054 (42S22): Unknown column 'annual' in 'where clause'
SELECT id, salary * 12 AS annual FROM emp WHERE annual > 100000;

-- 修正：WHERE 里写原始表达式
SELECT id, salary * 12 AS annual FROM emp WHERE salary * 12 > 100000;

深分页为什么慢（LIMIT 100000, 10 要先产出前 100010 行再丢掉前面 10 万行）、GROUP BY 之后为什么不能随便 SELECT 没分组的列，答案都在这条流水线上。

JOIN 家族：拼表，以及 ON 与 WHERE 的经典坑

INNER JOIN 只保留两表都匹配上的行；LEFT JOIN 保留左表全部，右表匹配不上的补 NULL；RIGHT JOIN 是它的镜像；FULL OUTER JOIN 两边都保留——MySQL 没实现，惯用写法是 LEFT JOIN UNION RIGHT JOIN 模拟，注意 UNION 有去重开销，大表慎用。

真正容易翻车的是外连接里 ON 和 WHERE 的分工：

• 条件写 ON：先按条件匹配，匹配不上的左表行照样保留，右侧补 NULL，外连接语义完整
• 条件写 WHERE：先连接再过滤，右侧为 NULL 的行参与 NULL 比较，结果既不是 TRUE 也不是 FALSE，整行被过滤掉——外连接悄悄退化成了内连接

记一条规则：对右表（被连接表）的过滤条件放 ON，对左表的放 WHERE，各管各的。INNER JOIN 时两者等价，放哪都行。

聚合与分组：COUNT 的 NULL 语义和 ONLY_FULL_GROUP_BY

COUNT 是最容易用错的聚合函数。COUNT() 数行数，不关心任何列的值，InnoDB 对它有专门优化；COUNT(col) 只数 col 非 NULL 的行；COUNT(1) 与 COUNT() 语义相同。orders 表 10 行、remark 列有 2 行 NULL，则 COUNT(*) 是 10、COUNT(remark) 是 8——拿后者当行数用就会少数。SUM、AVG 同样忽略 NULL，而且 AVG 的分母只算非 NULL 行，想按总行数平均要写 AVG(IFNULL(col, 0))。

GROUP BY 的规矩由 sql_mode 中的 ONLY_FULL_GROUP_BY 把关（5.7 起默认开启）：SELECT 里的非聚合列必须出现在 GROUP BY 里，否则报 ERROR 1055。原因很直接：按 user_id 分组后每组有多行，SELECT remark 到底展示哪一行的？标准 SQL 不允许这种歧义。修法两种：把 remark 加进 GROUP BY；或确认业务上取哪行都无所谓时，用 ANY_VALUE(remark) 显式声明。

子查询：相关与非相关，NOT IN 的 NULL 陷阱

子查询分两种。非相关子查询不依赖外层，整个只执行一次，结果当常数用；相关子查询引用了外层的列，外层每处理一行都要执行一遍，EXISTS 是典型写法。性能直觉：外表大、子查询表小用 IN，反过来用 EXISTS。不过 8.0 优化器会在很多场景自动做半连接改写，别死记口诀，拿 EXPLAIN 验证。

比性能更要紧的是正确性：NOT IN 遇上 NULL 直接返回空集。x NOT IN (1, NULL) 展开是 x <> 1 AND x <> NULL，NULL 参与比较的结果既不是 TRUE 也不是 FALSE，AND 出来整条都不满足，一行都查不出来。它不报错、只给 0 行，排查起来最费时间。修法：子查询里加 IS NOT NULL，或改写 NOT EXISTS——EXISTS 按行判断存在性，不受集合里 NULL 的影响。

窗口函数：不压扁行的聚合

GROUP BY 聚合后每组只剩一行，窗口函数在保留每行的前提下另开一列做计算。三个排名函数一张表分清（分数 100、90、90、80）：

函数	结果	特点
ROW_NUMBER()	1, 2, 3, 4	严格递增，并列也分先后
RANK()	1, 2, 2, 4	并列同名次，跳号
DENSE_RANK()	1, 2, 2, 3	并列不跳号

SUM(amount) OVER (PARTITION BY user_id ORDER BY id) 表示"每个用户按 id 累加金额"；去掉 ORDER BY 就退回每组总额。

窗口函数最实用的场景是每组取最新一条：ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id DESC) 筛出 rn = 1 的行。旧写法要么子查询 MAX(id) 再回表，要么自连接比大小，要取整行数据时都很别扭。

动手实操

一套脚本跑完上面所有知识点，含错误示例与修正：

-- 01 建表与数据
CREATE TABLE users (
  id   INT PRIMARY KEY,
  name VARCHAR(20)
);
CREATE TABLE orders (
  id      INT PRIMARY KEY,
  user_id INT,              -- 故意允许 NULL，给 NOT IN 的坑用
  amount  DECIMAL(10,2),
  remark  VARCHAR(50)
);
INSERT INTO users VALUES (1,'alice'),(2,'bob'),(3,'carol'),(4,'dave');
INSERT INTO orders VALUES
  (1, 1,  50.00, 'first'),
  (2, 1, 120.00, NULL),      -- remark 为 NULL，给 COUNT 用
  (3, 2, 200.00, 'vip'),
  (4, 2,  80.00, 'normal'),
  (5, 3,  30.00, NULL);
-- dave 没有任何订单，留给 LEFT JOIN 用

-- 02 LEFT JOIN：条件放 ON 还是 WHERE
-- 需求：列出所有用户及其大于 100 的订单，没有大额订单的用户也要出现
SELECT u.name, o.id, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.amount > 100;  -- 正确：4 行，carol/dave 侧补 NULL

SELECT u.name, o.id, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.amount > 100;                                        -- 错误：只剩 2 行，退化成 INNER JOIN

-- 03 COUNT 的 NULL 语义
SELECT COUNT(*), COUNT(remark) FROM orders;                  -- 5 / 3，不是 5 / 5

-- 04 ONLY_FULL_GROUP_BY
SELECT user_id, MAX(amount), remark FROM orders GROUP BY user_id;
-- ERROR 1055: expression #3 is not in GROUP BY ...
SELECT user_id, MAX(amount), ANY_VALUE(remark) AS remark     -- 修正 1：任意一行都行
FROM orders GROUP BY user_id;
SELECT user_id, remark, MAX(amount)                          -- 修正 2：真要按两列维度统计就都写进去
FROM orders GROUP BY user_id, remark;

-- 05 NOT IN 的 NULL 陷阱
INSERT INTO orders VALUES (6, NULL, 999.00, 'admin');        -- 构造 user_id 为 NULL 的行
-- 需求：找出从没下过单的用户，直觉写法：
SELECT name FROM users
WHERE id NOT IN (SELECT user_id FROM orders);
-- 返回 0 行！子查询含 NULL，NOT IN 永远不成立
SELECT name FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);  -- 修正 1
SELECT name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);        -- 修正 2：推荐
-- 两种写法都返回 dave

-- 06 窗口函数
SELECT id, amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn,
       RANK()       OVER (ORDER BY amount DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS drk
FROM orders;                                                 -- 三种排名对比

SELECT id, user_id, amount,
       SUM(amount) OVER (PARTITION BY user_id ORDER BY id) AS running_total
FROM orders WHERE user_id IS NOT NULL;                       -- 每用户累计消费

SELECT id, user_id, amount
FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id DESC) AS rn
  FROM orders o WHERE o.user_id IS NOT NULL
) t
WHERE rn = 1;                                                -- 每用户最新一条订单

常见误区与小结

• WHERE 里用 SELECT 别名：执行顺序决定它必然报错，要么把表达式重写进 WHERE，要么挪到 ORDER BY
• LEFT JOIN 后随手把右表条件写进 WHERE：外连接退化成内连接，数据悄悄变少还不报错
• COUNT(col) 当 COUNT() 用：col 有 NULL 就少数，数行数固定用 COUNT()
• NOT IN 子查询不带 IS NOT NULL：结果集含 NULL 时查出 0 行且不报错，首选 NOT EXISTS
• GROUP BY 少写列指望"随便取一行"：5.7+ 默认 ONLY_FULL_GROUP_BY 直接报 1055，语义明确要么补列要么 ANY_VALUE

小结：这篇是 MySQL 学习篇的地基，JOIN、聚合、子查询、窗口函数在后面索引原理、执行计划、慢查询优化各篇里都会反复出现。下一篇讲 MySQL 体系结构与一条 SQL 的执行旅程，把这篇的逻辑执行顺序落到 Server 层和 InnoDB 层的真实组件上。

参考

• MySQL 8.0 Reference Manual · SELECT Statement：子句处理顺序、JOIN 语法、窗口函数
• MySQL 8.0 Reference Manual · GROUP BY Handling（ONLY_FULL_GROUP_BY 与 ANY_VALUE()）
• 本系列面试题篇：MySQL 8.0 窗口函数与 CTE 实战