DAY 08 W2 · 第二批数据来了：多表世界 高频

用户表来了：第一次 JOIN

运营发来 users 表（1 万用户）：「老板想知道都是哪些城市的人在买。」订单表里只有 user_id，你的第一次表连接。

学 40 min
练 65 min
盘 15 min
共 120 分钟

学 · 40 min

  1. 01跑 seed.sql「W2」段：导入 users / products / order_items 三张表，count(*) 核对行数

  2. 02JOIN 的直觉：按 user_id 把两张表的行拼起来

    场景老板问「都是哪些城市的人在买」。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 打头。

  3. 03INNER JOIN ... ON 的语法与表别名

    场景动手写之前把语法拆干净：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 直接报错。整个查询统一用别名。

  4. 04连接条件表达的是业务关系，不是「列名相同」

    场景两张表里都有 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（自己等于自己）语法合法且恒真，结果是笛卡尔积--数据库不会替你挡这个错。

  5. 05连接前过滤 vs 连接后过滤：结果相同，习惯先过滤

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

    对 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 的过滤结果完全不同，是明天的主题。

练 · 65 min

  1. 跑 seed.sql「W2」段导入三张新表，count(*) 核对行数
    参考答案

    答案不是抄 SQL，是把 §B 的五条语句按顺序跑掉：④⑤ 依赖 ③ 的结果，乱序跑数字对不上。行数不对先怀疑自己重复灌过。

    -- seed.sql 的 §B 段（页面文案里的「W2」段）共五条：
-- ①users ②products ③order_items 三段 INSERT，
-- ④ 把 total_amount 对齐成明细之和 ⑤ 重新埋 100 单负金额
select (select count(*) from users)       as 用户数,    -- 10000
       (select count(*) from products)    as 商品数,    -- 500
       (select count(*) from order_items) as 明细行数;  -- ≈12 万
  2. orders 连 users，让每笔订单带上用户名和城市
    参考答案

    ON 表达的是业务关系：「订单的用户 = 用户的编号」。业务问题问的是订单，就让 orders 打头。

    select o.id, o.total_amount, u.name, u.city
from orders o
join users u on o.user_id = u.id
limit 10;
  3. 查「上海用户」的订单数：先过滤再连、连了再过滤各写一遍，对比结果
    参考答案

    INNER JOIN 上两种写法结果相同（优化器常生成同一个计划）；习惯先过滤再连接，数据先变小。这条规律到明天的 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 = '上海';
  4. 各城市的订单数和 GMV
    参考答案

    约 2% 用户的 city 是 NULL，这批订单自己成一组（city 显示 NULL），报表里别悄悄丢掉，展示时再 coalesce 成「未知」。

    select u.city, count(*) as 单数, sum(o.total_amount) as gmv
from orders o
join users u on o.user_id = u.id
group by u.city
order by gmv desc;
  5. 故意把连接条件写成 user_id = user_id 不加表名限定，记下报错
    参考答案

    user_id = user_id 两边都解析成 orders.user_id，语法合法且恒真，结果 = 5 万 × 1 万 = 5 亿行（LIMIT 救了你）。ambiguous 报错只在两表真有同名列时出现（如 id = id）——报错反而是在帮你逼问：这个列到底属于谁。

    -- 本库 user_id 只有 orders 有，所以这行不报错——恒真，退化成 5 亿行笛卡尔积
select o.id, u.id
from orders o
join users u on user_id = user_id
limit 5;

-- 真正的 ambiguous 报错要用两边同名的列：
select * from orders o join users u on id = id;
-- ERROR:  column reference "id" is ambiguous
过关标准 能说清 JOIN ON 的条件在业务上表示什么、两个 user_id 分别属于谁。