SQLite INNER JOIN：合并多张表中的行

连接把两张表拼在一起

关系型数据库有意把数据拆分到多张表中：客户在一张表，订单在另一张表，商品在第三张表。这样每条事实只存放在一个地方。但当你想回答一个实际的问题（“哪些客户订了什么？”）时，就需要把这些碎片重新拼起来。这正是连接的作用。

INNER JOIN 是最常用的连接。只要条件匹配，它就把两张表中的行配成一对，其余的全部丢弃。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name) VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Chen');
INSERT INTO orders (customer_id, total) VALUES (1, 49.99), (1, 12.50), (2, 80.00);

SELECT customers.name, orders.total
FROM customers
INNER JOIN orders ON orders.customer_id = customers.id;

三个客户，三个订单，但 Chen 没有订单，所以 Chen 不会出现。这就是“内”连接的含义：只有匹配的行才会留下。

思维模型：先配对，再过滤

可以这样理解 INNER JOIN：取第一张表的每一行，与第二张表的每一行逐一比对，只有当 ON 条件为真时才保留这一对。从概念上讲，它是先做一个巨大的笛卡尔积，再进行过滤。SQLite 实际上并不是这样执行的（能用索引时它会用索引），但用这个模型来预测输出结果是正确的。

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 1, 70), (12, 2, 15);

SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id;

这里有几个值得养成的习惯：

• 如果一张表要被提到不止一次，就给它起别名（customers AS c）。这样能减少干扰。
• 当两张表都可能有某一列时，给列名加上限定（c.name、o.total）。
• ON o.customer_id = c.id 中的顺序无关紧要：c.id = o.customer_id 效果一样。

INNER JOIN 与 JOIN

在 SQLite（以及标准 SQL）中，单独写 JOIN 就表示 INNER JOIN。INNER 关键字是可选的。

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);

SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

两种写法产生相同的执行计划和相同的行。在混用多种连接的代码中，写全 INNER JOIN 能稍微提高可读性：和几行之后的 LEFT JOIN 放在一起时，意图一目了然。

ON 与 USING

当连接列在两张表中同名时，USING (column) 比 ON a.col = b.col 更简短：

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);

SELECT name, total
FROM customers
INNER JOIN orders USING (customer_id);

USING (customer_id) 做了两件事：按 customer_id 相等进行匹配，并把这一列合并，让它在结果中只出现一次。当两边确实使用同一个列名时就用它。当列名不同（orders.customer_id = customers.id），或者条件不只是一个等式时，继续使用 ON。

连接三张表

再添加更多的 JOIN ... ON ... 子句，就能把连接串起来。每一个子句都把当前的结果和另一张表关联起来。

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
CREATE TABLE order_items (
    order_id INTEGER,
    product TEXT,
    quantity INTEGER
);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);
INSERT INTO order_items VALUES (10, 'Notebook', 2), (10, 'Pen', 5), (11, 'Mug', 1);

SELECT c.name, o.id AS order_id, i.product, i.quantity
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
INNER JOIN order_items i ON i.order_id = o.id
ORDER BY c.name, o.id;

从上往下读：客户连接到订单，订单连接到订单项。输出中的每一行代表一个“客户、订单、订单项”的组合。链条上任何一处缺少匹配的行都会被丢弃：这就是内连接规则在每一步的应用。

用 WHERE 过滤

ON 说明如何配对行，WHERE 过滤配对后的结果。具体到内连接，把额外的条件放在 ON 还是 WHERE 中，得到的行是一样的；但惯例是把连接条件放在 ON 中，把行过滤条件放在 WHERE 中。

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, country TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada', 'UK'), (2, 'Boris', 'FR'), (3, 'Chen', 'UK');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 80), (12, 3, 12);

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'UK' AND o.total > 20;

它读起来就是：“连接客户和订单，然后只保留订单金额超过 20 的英国客户。”两种职责，两个子句，将来的你会感谢现在的自己。（等你开始写 LEFT JOIN 时，ON/WHERE 的区别就不再只是写法问题了，不过那是下一页的内容。）

ON 中的多个条件

ON 可以包含任意布尔表达式，而不只是一个等式。当关系涉及不止一列，或者你想在连接时就过滤右表时，这很有用。

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL, status TEXT);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES
    (10, 1, 30, 'paid'),
    (11, 1, 50, 'cancelled'),
    (12, 2, 15, 'paid');

SELECT c.name, o.id, o.total
FROM customers c
INNER JOIN orders o
    ON o.customer_id = c.id
   AND o.status = 'paid';

已取消的订单消失了，因为第二个条件不成立。对于内连接，你也可以写成 WHERE o.status = 'paid'，结果相同。ON 的写法让“什么算匹配”的逻辑紧挨着连接本身。

常见的坑

有几件事经常让人出错：

• 忘了写 ON 子句。 在 SQLite 中，没有 ON 的 FROM a INNER JOIN b 是语法错误。（只用逗号的 FROM a, b 能通过编译，会得到一个交叉连接，而这几乎从来都不是你想要的。）
• 意料之外的重复。 如果一个客户有三个订单，这个客户的名字就会在结果中出现三次。这是正确的连接行为，不是 bug。如果你想每个客户只占一行，就用 GROUP BY 做聚合。
• 缺失的行。 如果某个客户本该出现却没有出现，说明连接条件没有匹配上：检查连接列中是否有 NULL，或者改用 LEFT JOIN。
• 列名有歧义。 SELECT id FROM customers JOIN orders ON ... 会报错，因为两张表都有 id。给它加上限定：c.id 或 o.id。

下一篇：LEFT JOIN

当缺少匹配意味着“跳过这一行”时，INNER JOIN 非常好用。但有时你想列出每一个客户，即使是没有订单的客户，并用 NULL 代替缺失的数据。这就是接下来要讲的 LEFT JOIN。