27. 多表连接 JOIN

到目前为止，我们都是在单张表里折腾数据。但真实世界里，数据往往是”分散”在多张表里的：用户信息放在 users，订单信息放在 orders，它们通过一个”外键”字段（这里是 orders.user_id）串起来。当你既想知道”谁下了单”、又想知道”下了多少金额的订单”时，就必须把两张表横向拼到一起——这件事在 SQL 里就叫 JOIN（连接）。

回忆一下我们全局统一的示例表结构：

-- 用户表
CREATE TABLE users (
  id    INTEGER PRIMARY KEY,
  name  TEXT,
  age   INTEGER,
  email TEXT
);

-- 订单表，user_id 指向 users.id
CREATE TABLE orders (
  id        INTEGER PRIMARY KEY,
  user_id   INTEGER,
  amount    REAL,
  created_at TEXT
);

注意 orders.user_id 这一列。从语义上说它”应该”指向 users.id，但 SQLite 并不会因为你”心里这么想”就自动保证这一点——这正是后面 WARNING 要强调的事。先记住这个表关系：一个用户（users 一行）可以对应多笔订单（orders 多行），这是典型的”一对多”关系。

为什么需要 JOIN，而不是两次查询

你当然可以先查 users 拿到一批用户，再逐个人去 orders 里找他的订单。但这样做有两个麻烦：第一，要在程序里写循环、做匹配，代码啰嗦；第二，数据库本来就是为”集合运算”而生的，它在底层对连接做了大量优化，自己拼不如交给它拼。JOIN 的本质，就是告诉 SQLite：“请把 A 表和 B 表按某个条件对齐，凡是能对上的，就拼成一行返回给我。”

类比一下：两张纸，一张写”人名 + 身份证号”，一张写”订单号 + 身份证号 + 金额”。JOIN 就是按”身份证号”这个共同字段，把两张纸并排贴在一起。贴的规则不同，得到的结果也不同——这正是 INNER、LEFT、CROSS 三种连接的区别。

INNER JOIN：只保留能对上的行

INNER JOIN（内连接） 是最常用的连接。它的语义是：左表和右表都取，但结果里只保留两边都能对上连接条件的行；对不上的，直接丢弃。

sqlite> SELECT users.name, orders.amount
   ...> FROM users
   ...> INNER JOIN orders ON orders.user_id = users.id;

这条语句的含义：从 users 和 orders 里，凡是 orders.user_id 能在 users.id 中找到匹配的那一行，就把 users.name 和 orders.amount 拼成一行输出。如果一个用户从来没下过单（orders 里没有他的记录），他在 INNER JOIN 的结果里不会出现；反过来，如果一笔订单的 user_id 在 users 里找不到对应的人（脏数据），这笔订单也不会出现。

ON 后面跟的就是”连接条件”，它决定了两张表怎么对齐。除了 ON，当两个表的连接列名完全相同时，还可以用更短的 USING 写法：

sqlite> SELECT users.name, orders.amount
   ...> FROM users
   ...> INNER JOIN orders USING (id);  -- 仅当连接列都叫 id 时可用，本例不适用，演示语法

要让结果更易读，可以给表起别名（alias），尤其是表名较长时：

sqlite> SELECT u.name, o.amount, o.created_at
   ...> FROM users AS u
   ...> INNER JOIN orders AS o ON o.user_id = u.id
   ...> ORDER BY o.amount DESC;

AS u、AS o 只是给表取的小名，本句之内有效，纯属为了方便。

LEFT JOIN：左表全保留，右表对不上就补 NULL

LEFT JOIN（左连接） 的语义是：左表（FROM 后面那张）的每一行都一定出现在结果里；至于右表，能对上的就拼过来，对不上的那一列用 NULL 补齐。

sqlite> SELECT u.name, o.amount
   ...> FROM users AS u
   ...> LEFT JOIN orders AS o ON o.user_id = u.id;

这下差别就出来了：哪怕某个用户一笔订单都没有，他依然会出现在结果里，只是 o.amount 那一侧是 NULL。这个特性非常实用——比如你想统计”每个用户下了多少单”，包括那些 0 单的用户，就必须用 LEFT JOIN，否则 INNER JOIN 会把 0 单用户直接过滤掉。

LEFT JOIN 还能用来找”孤儿数据”，也就是”在左表存在、但在右表找不到对应”的行。方法是接一个 WHERE 右表主键 IS NULL：

sqlite> SELECT u.name
   ...> FROM users AS u
   ...> LEFT JOIN orders AS o ON o.user_id = u.id
   ...> WHERE o.id IS NULL;   -- 找出从未下过单的用户

LEFT JOIN 和 LEFT OUTER JOIN 是完全等价的写法，加不加 OUTER 都行。

sqlite> SELECT u.name, COUNT(o.id) AS order_count
   ...> FROM users AS u
   ...> LEFT JOIN orders AS o ON o.user_id = u.id
   ...> GROUP BY u.id, u.name;

CROSS JOIN：笛卡尔积，谨慎使用

CROSS JOIN（交叉连接） 最特别：它没有任何连接条件，直接把左表的每一行和右表的每一行两两配对。如果左表有 n 行、右表有 m 行，结果就是 n × m 行。这种”全配对”的结果在数学上叫笛卡尔积（Cartesian product）。

sqlite> SELECT u.name, o.id
   ...> FROM users AS u
   ...> CROSS JOIN orders AS o;

什么时候需要它？典型场景是”生成组合”。比如你有一张”产品表”和一张”月份表”，想生成”每个产品 × 每个月”的销售计划基底，CROSS JOIN 一次就搞定。但要注意：如果不小心在普通 JOIN 上漏写了 ON 条件，SQLite 也可能退化为笛卡尔积，行数会爆炸式增长，所以写连接时务必确认连接条件没丢。

三种连接怎么选

• 只要”两边都对得上”的数据 → INNER JOIN（最常用，也是默认的 JOIN）。
• 要”左表全保留，右表可选匹配”（如统计含 0 的单、找孤儿记录）→ LEFT JOIN。
• 要”穷举所有两两组合”（如产品 × 月份） → CROSS JOIN，且一定要想清楚结果行数会不会太大。

还有一个常被问到的问题：SQLite 支持 RIGHT JOIN 和 FULL OUTER JOIN 吗？答案是：SQLite 不支持 RIGHT JOIN 和 FULL OUTER JOIN。如果你”想要右连接的效果”，标准做法是把两张表调换左右位置，改用 LEFT JOIN 即可；想要”全外连接”（左右都没匹配的行都要），则需要用 LEFT JOIN 与 RIGHT 思路的 UNION 组合来模拟，这部分我们留到 UNION 章节再展开。

类比小结

把 JOIN 想象成”按共同字段把两张纸并排贴”：

• INNER JOIN = 只贴两边都有的；
• LEFT JOIN = 左边那张纸每行都留着，右边贴不上的空着（NULL）；
• CROSS JOIN = 左边每行和右边每行全部两两配对，不管有没有共同字段。

掌握这三种，日常 90% 的多表查询都能拿下。它们可以和前面学过的 WHERE、GROUP BY、ORDER BY、LIMIT 自由组合——连接先把数据拼好，后面的过滤、分组、排序照常生效。下一章我们再看另一种”嵌套”获取数据的方式：子查询与自连接。