场景 老板问「都是哪些城市的人在买」。orders 里只有 user_id 一个编号，城市藏在 users 表里--单表查不出来，要把两张表按「同一个用户」拼起来。

讲解 JOIN 做的事一句话：对左表（orders）的每一行，去右表（users）里找 user_id 相等的那一行，两行并成一行。连接之后每一行既是订单也是用户，u.city 这种 users 侧的列就能直接用了。

select o.id, o.total_amount, u.name, u.city
from orders o
join users u on u.id = o.user_id
limit 5;

易错 先想清楚「谁找谁」：从订单出发找用户，和从用户出发找订单，INNER JOIN 结果一样，但你读 SQL 的方式完全不同。业务问题问的是订单，就让 orders 打头。

场景 动手写之前把语法拆干净：JOIN 发生在 FROM 里，ON 是拼接条件。

讲解 from orders o 里的 o 是表别名，写一次后面全用短名。on u.id = o.user_id 是拼接条件：两行满足它才并成一行。INNER 可以省略--光写 JOIN 就是 INNER JOIN，语义是「两边都匹配才保留」。

select o.id, u.city
from orders o
inner join users u on u.id = o.user_id;

易错 别名一旦起了，原表名就不能再用：from orders o 之后写 orders.id 直接报错。整个查询统一用别名。

场景 两张表里都有 id，你顺手写 on id = id--报错 column reference is ambiguous。这个报错逼你回答一个业务问题：这两个 id 分别是谁的？（本库两表没有同名的 user_id--写 on user_id = user_id 反而不报错，那是更糟的笛卡尔积，见下方 pitfall。）

讲解 ON 的左边是 orders.user_id（这单是谁下的），右边是 users.id（这个编号的用户是谁）。写连接条件前先用嘴说一遍业务关系：「订单的用户 = 用户的编号」。说不出来，就是还没理解这两张表的关系。

select count(*)
from orders o
join users u on o.user_id = u.id;   -- orders 找 users
-- 条件两边反过来写 u.id = o.user_id，结果一样，语义也是同一句业务关系

易错 ON 只要求值相等，不检查列名。on o.user_id = o.user_id（自己等于自己）语法合法且恒真，结果是笛卡尔积--数据库不会替你挡这个错。

场景 「上海用户的订单数」：先筛出上海用户再连，或连完再筛，两条路都通。

讲解 对 INNER JOIN，条件写 WHERE 还是往 ON 里塞，结果一样（优化器也常把两者变成同一个计划）。但习惯上先过滤再连接：数据先变小，读的人也先看到范围。今天先养成这个习惯--明天（D10）会看到它在 LEFT JOIN 上被打破的场景。

-- 习惯写法：子查询先把用户收窄到上海
select count(*) from orders o
where o.user_id in (select id from users where city = '上海');

-- 连接后过滤：结果相同
select count(*) from orders o
join users u on o.user_id = u.id
where u.city = '上海';

易错 「结果相同」只对 INNER JOIN 成立。LEFT JOIN 上 ON 和 WHERE 的过滤结果完全不同，是明天的主题。

场景 老板要看「每笔订单里都有什么商品」。明细表 order_items 两头各揣一个外键：order_id 指向订单，product_id 指向商品--它就是把三张表串起来的桥。

讲解 报表要的列分布在不同表里：单号在 orders、用户名在 users、商品名在 products、数量单价在 order_items。连接链按业务关系走：orders → order_items（一笔订单含多件商品）→ products（商品详情）。四表连起来，每行就是「某笔订单里的某件商品」。

select o.id        as 单号,
       u.name       as 用户,
       p.name       as 商品,
       i.qty        as 数量,
       i.unit_price as 单价
from orders o
join order_items i on i.order_id = o.id
join products   p on p.id = i.product_id
join users      u on u.id = o.user_id
limit 5;

易错 连接顺序按链条写、JOIN 一行一个，别把四张表挤在 FROM 里用逗号连接--老式逗号语法没有 ON，漏条件就是笛卡尔积且不报错。

场景 你连了 order_items 统计订单 GMV，总金额凭空翻了倍--数据没坏，是行数被放大了。

讲解 orders 和 order_items 是一对多：一笔订单平均挂 2-3 件商品，连接后同一笔订单变成 2-3 行，sum(o.total_amount) 就把它重复累加了。判断连接后该有多少行：沿连接链找「多」的那一端，结果行数 = 多端行数（前提都匹配上）。

select count(*) from orders;        -- 5 万
select count(*) from order_items;    -- ≈12 万
select count(*)
from orders o
join order_items i on i.order_id = o.id;   -- ≈12 万，等于明细行数

易错 「连了明细表之后聚合数字变大」首先怀疑一对多放大。修法：先按 order_id 聚合明细、再连接回主表（D14 周测专门考）。

场景 漏写一个 ON，笔记本风扇狂转--你正在计算两表所有行的组合。

讲解 没有 ON 的 JOIN 退化为 CROSS JOIN：结果行数 = 两表行数之积。orders 5 万行 × order_items 12 万行 = 60 亿行，数据库真的会一行行算出来。这是新手把库跑挂的第一名原因。

-- 惰性：LIMIT 几行就停，可以看到组合的样子
select o.id, i.id
from orders o cross join order_items i
limit 5;

-- 危险：count(*) 要把 60 亿行数完才返回，别在脑子里跑
-- select count(*) from orders cross join order_items;

易错 EXPLAIN 里看到两侧都没有过滤条件、估行数 = 两表行数之积，就是在算笛卡尔积--赶紧 Ctrl+C。

场景 PG 允许 using (...) 简写，natural join 甚至自动按同名列连接--看起来更省事。

讲解 using (列) 要求两边的连接列同名；本库里 orders.user_id 对 users.id，名字不同，USING 根本写不了。natural join 更激进：把所有同名列全当连接条件。省几个字的代价是赌表结构永远不变--哪天加了一个同名列，natural join 的连接条件悄悄变了，结果变了，不报任何错。

-- 本库写不了 USING（连接列不同名），只能显式 ON：
select o.id, u.city
from orders o
join users u on o.user_id = u.id;

易错 生产代码里见到 natural join 就重构；显式 ON 多打几个字，换来的是改动安全。

场景 月报里「未支付订单」整段消失。你用的 INNER JOIN：右边没有匹配行时，左边这行整个被丢掉。

讲解 一句话记四种：INNER=两边都匹配才留；LEFT=左边全留，右边匹配不上补 NULL；RIGHT 反过来；FULL=两边都全留。业务里 90% 是前两种：只看匹配上的用 INNER，左边的行一行都不能丢用 LEFT。

-- 各 3 行小表，四种 JOIN 各跑一遍，结果抄进 notes.md
create table a (id int, label text);
insert into a values (1,'一'), (2,'二'), (3,'三');
create table b (id int, val int);
insert into b values (2,20), (3,30), (4,40);

select a.label, b.val from a join  b on a.id=b.id;  -- (二,20)(三,30)
select a.label, b.val from a left  join b on a.id=b.id;  -- 多出 (一,NULL)
select a.label, b.val from a right join b on a.id=b.id;  -- 多出 (NULL,40)
select a.label, b.val from a full  join b on a.id=b.id;  -- 全部 4 行

易错 LEFT JOIN 补出来的 NULL 行，右边所有列都是 NULL--这正是下面反连接（IS NULL）能工作的前提。

场景 同样一个条件，写在 ON 里月报没丢行，写在 WHERE 里未支付订单又消失了。今天最重要的一节。

讲解 执行顺序决定的：ON 在连接发生时判断--不合格的右表行不参与连接，但左表这行还在，右边补 NULL；WHERE 在连接完成后过滤--此刻 NULL 行也在候选里，任何对右表列的比较都得 UNKNOWN，行被丢弃。所以对 LEFT JOIN：右表的条件写 ON = 「匹配不上的当 NULL 留着」，写 WHERE = 「必须匹配上才要」。

-- 条件 b.val > 25 放 ON：a 全保留，匹配不上的 b 侧补 NULL
select a.label, b.val
from a left join b on a.id = b.id and b.val > 25;

-- 同一条件放 WHERE：NULL 行被过滤，行数退化成 INNER
select a.label, b.val
from a left join b on a.id = b.id
where b.val > 25;

易错 右表过滤条件放 WHERE 还是 ON，是 LEFT JOIN 最大的坑、面试最爱问的一条。结论先背下来：右表条件放 ON。

场景 代码评审时前辈指着你一句 where 说：这行一加，你的 LEFT JOIN 就白写了。

讲解 退化路径：LEFT JOIN 补出的 NULL 行，一到 WHERE 就活不过任何对右表列的判断（NULL 参与比较得 UNKNOWN，被丢弃）。判别法：把 WHERE 里提到右表列的条件挪进 ON，行数变多了，说明原来的写法已经退化。

-- 每个用户及其已支付订单数，没下过单的也要（显示 0）：
select u.name, count(o.id) as 单数
from users u
left join orders o on o.user_id = u.id and o.status = 2
group by u.name;
-- 把 and o.status = 2 挪到 where：没下过单的用户整行消失 = 退化

易错 where b.id is not null 是最常见的隐性退化写法--除非你就是要反连接，否则别这么过滤。

场景 增长同事问：「有多少用户注册了却一单没下？」--本质是集合的减法。

讲解 反连接 = LEFT JOIN + 右表主键 IS NULL：左行在右边找不到匹配时右表列全是 NULL，用 IS NULL 精确挑出这批行。它和 NOT IN / NOT EXISTS 语义等价、各有性能适用场景，先把 LEFT JOIN 版练熟（D12 会见另外两种）。

select u.name
from users u
left join orders o on o.user_id = u.id
where o.id is null;   -- 从未下过单的用户

易错 IS NULL 判断的列必须是右表的主键（或必然非空的列），否则右表本身的 NULL 会冒充「没匹配上」。

场景 统计「每个用户的订单数」，没下过单的用户显示 1 而不是 0--你数的是行，不是订单。

讲解 LEFT JOIN 补出来的 NULL 行也被 count(*) 数到了；count(o.id) 只数非空的 o.id，NULL 行自动跳过，正好实现「没下过单 = 0」。一句话：LEFT JOIN 之后聚合右表，用 count(右表.非空列)，别用 count(*)。

select u.name,
       count(*)   as 错误_NULL行也数,
       count(o.id) as 正确_没下单记0
from users u
left join orders o on o.user_id = u.id
group by u.name;

易错 sum(o.total_amount) 遇 NULL 行会自动跳过、结果没错，但展示层记得 coalesce 补 0。

场景 「按城市统计订单数」你顺手 select 了 u.city, o.total_amount, count(*)--报错：column must appear in the GROUP BY clause。

讲解 分组之后每组只剩一行，SELECT 的每一列要么进过 GROUP BY（组内相同，有唯一值），要么被聚合函数包裹（压成单值）。o.total_amount 组内有几百个值，一个格子放不下，PG 拒绝猜。MySQL 关掉 only_full_group_by 时会随便取一个值不报错--著名的坑。

-- 报错：total_amount 既不在 GROUP BY 也没被聚合
select u.city, o.total_amount, count(*)
from orders o join users u on o.user_id = u.id
group by u.city;

-- 修法：聚合它
select u.city, sum(o.total_amount) as gmv, count(*) as 单数
from orders o join users u on o.user_id = u.id
group by u.city;

易错 PG 的严格检查是帮你挡错的；看到 MySQL「不报错但结果怪」，先查 GROUP BY 列全不全。

场景 老板要「城市 × 状态」交叉表：同一张 orders，维度从一列变两列。

讲解 group by u.city, o.status 先按城市分、城市内再按状态分，每组一行。粒度 = GROUP BY 列的全体：多加一列，组数变多、每组的度量变小。写任何报表前先问自己「一行代表什么」--答案就是你的 GROUP BY。

select u.city, o.status,
       count(*) as 单数,
       sum(o.total_amount) as gmv
from orders o join users u on o.user_id = u.id
group by u.city, o.status
order by u.city, o.status;

易错 少写一个分组列，是报表「数字看起来对、粒度不对」的头号原因。

场景 老板要在一张表里同时看到总单、已付、取消。三个数字三条 SQL、三个结果对着粘？不用。

讲解 count(*) filter (where 条件) 在聚合内部做条件计数：一次扫表，不同条件各数各的。比 sum(case when ...) 少一层嵌套、条件直读。这是 PG 特有语法（MySQL 没有），面试里写出来是加分项。

select u.city,
       count(*)                             as 总单,
       count(*) filter (where o.status = 2) as 已付,
       count(*) filter (where o.status = 3) as 取消,
       round(100.0 * count(*) filter (where o.status = 2) / count(*), 1) as 支付率
from orders o join users u on o.user_id = u.id
group by u.city;

易错 filter 里只能写行级条件，不能引用别的聚合结果（那个要子查询或窗口函数）。

场景 老板：「各城市销售额，顺便给个城市小计，最后来个总计。」--要在一个结果里出三种粒度。

讲解 rollup (u.city) 在按城市分组的结果之外追加一行「所有城市合计」（该列显示 NULL）；rollup(a, b) 会出 a 小计、(a,b) 明细、总计多层。GROUPING SETS 是完全体：想要哪几种粒度自己点菜。

select coalesce(u.city, '【总计】') as 城市,
       count(*) as 单数,
       sum(o.total_amount) as gmv
from orders o join users u on o.user_id = u.id
group by rollup (u.city);

易错 小计行的 NULL 和业务 NULL 撞车：city 本身为 NULL 的用户会被 coalesce 吞进「总计」。用 grouping(city) 函数区分（返回 1 = 这是小计行）。

场景 正式需求下来了，先别碰键盘--这一节是今天所有查询的方法论。

讲解 任何报表口径 = 粒度（一行代表什么：一个用户？一个城市？）+ 过滤（哪些数据算进来：已支付才计 GMV？）+ 度量（算什么指标、分母是谁）。把这三行念给提需求的人确认过再写 SQL，能消灭一半返工。

易错 「支付率」的分母是全部订单还是排除已取消？两个口径都合理、数字差一截--不写清楚必被追问。

场景 老板要「上海和北京的用户名单合成一张表」。

讲解 UNION 把两个查询的结果上下叠起来并去重（内部要做排序或哈希）；UNION ALL 只叠不去重。去重有真实代价：数据量大时 UNION 明显更慢。上海和北京的用户本来就不重叠，用 UNION 等于白白多付一次去重。

select name, city from users where city = '上海'
union all
select name, city from users where city = '北京';

-- 换成 union 再跑一遍，对比 EXPLAIN 里多出来的 Unique/Sort 节点

易错 确认两段结果不可能重叠，就写 ALL；用它做「去重」是顺便的副作用，不是设计目标。

场景 「注册了但没下过单」「既买过商品 1 又买过商品 2」--听着就是集合的交与差。

讲解 INTERSECT 取两个查询都有的行；EXCEPT 取前者减去后者。配套铁律：两边列数、列序、对应类型必须一致（列名可以不同）。EXCEPT 天生去重：减完每行只留一份。

-- 注册了但从未下单（和 D10 的反连接对照着看）
select id from users
except
select user_id from orders;

-- 既买过「商品1」也买过「商品2」（明细表没有 user_id，经 orders 带出；商品 id 是 uuid，先按名字查出 id）
select o.user_id
from orders o join order_items i on i.order_id = o.id
where i.product_id = (select id from products where name = '商品1')
intersect
select o.user_id
from orders o join order_items i on i.order_id = o.id
where i.product_id = (select id from products where name = '商品2');

易错 集合运算的世界里没有「重复行」：两边都先去重再运算，和 JOIN 的世界规则不同。

场景 「每个用户最新的一单」：按用户分组、取组内 created_at 最大的那行。用 GROUP BY 写很别扭。

讲解 distinct on (user_id) 是 PG 特产：结果里每个 user_id 只保留一行，而保留哪一行由 ORDER BY 决定。固定三件套：SELECT DISTINCT ON (列)、ORDER BY 的第一组列与它一致、再排取行依据。

select distinct on (user_id)
       user_id, id, created_at, total_amount
from orders
order by user_id, created_at desc;   -- 每个用户时间最大的一单

易错 ORDER BY 打头的列必须和 DISTINCT ON 的列一致，否则报错。它和窗口函数 row_number() 的分工在 W3 会展开。

场景 月报日，八张报表排着队。每张动手前，先写三行字。

讲解 拿到需求先答三个问题再写 SQL：一行代表什么（粒度）、哪些数据算进来（过滤）、算什么指标（度量）。比如「复购率」：粒度 = 用户；过滤 = 统计期内下过单的用户；度量 = 其中下单 ≥ 2 次的占比。三行写出来，SQL 就是把它翻译成代码。

-- 「复购率」的三要素翻译成 SQL
select round(100.0 * count(*) filter (where 单数 >= 2) / count(*), 1) as 复购率
from (
  select user_id, count(*) as 单数
  from orders
  group by user_id
) t;

易错 八张报表最容易口径打架的是分母：GMV 按全部订单还是已支付订单？全月统一一个口径，并写进报表备注。