SQL CROSS JOIN：行的所有组合

第 5.6 课 · 阅读时间：约 5 分钟 · 练习题 ↓

SQL CROSS JOIN 把一张表的每一行与另一张表的每一行组合起来，结果称为笛卡尔积。在本课中，你将学习 CROSS JOIN 的语法、它的结果会有多大，以及如何用它构建报表网格，让缺失的组合显示为零而不是消失。

到目前为止，所有连接都是按条件配对行的。CROSS JOIN 没有条件：第一张表的每一行都会与第二张表的每一行相遇。对于相互关联的数据，这很少是我们想要的，但要生成所有组合时它恰好合适：每个门店配每个类别、每种商品配每个尺码、每一天配每位员工。

CROSS JOIN 返回什么

如果第一张表有 m 行，第二张表有 n 行，结果就有 m × n 行。没有 ON 子句。

基本语法

SELECT table1.column1, table2.column2
FROM table1
CROSS JOIN table2;

旧的逗号语法 FROM table1, table2（不带条件）含义相同。CROSS JOIN 能明确表达意图，因此更推荐。

示例：每个门店配每个类别

SELECT s.store_id, c.name AS category
FROM store AS s
CROSS JOIN category AS c
ORDER BY s.store_id, c.name;

结果：32 行：2 个门店 × 16 个类别，每个门店配每个类别。

构建报表网格

CROSS JOIN 最实用的场景是构建报表的“骨架”。假设你需要按门店和类别统计电影数量，包括电影数量为零的组合。普通的连接加 GROUP BY 只会返回数据中存在的组合。CROSS JOIN 先生成所有组合，然后再用 LEFT JOIN 附加数据：

SELECT s.store_id,
       c.name AS category,
       COUNT(i.inventory_id) AS copies
FROM store AS s
CROSS JOIN category AS c
LEFT JOIN film_category AS fc ON fc.category_id = c.category_id
LEFT JOIN inventory AS i ON i.film_id = fc.film_id
                        AND i.store_id = s.store_id
GROUP BY s.store_id, c.name
ORDER BY s.store_id, c.name;

结果：32 行，显示每个门店中每个类别电影的拷贝数量。没有拷贝的组合会显示 0，而不会消失。

COUNT(i.inventory_id) 只统计匹配到的拷贝，因此没有拷贝的组合得到 0。同样的技巧可以用来填补时间序列中的空缺：把日期列表与产品列表做交叉连接，就能得到每种产品每一天的一行。

意外的笛卡尔积

笛卡尔积常常是由于漏写连接条件而意外产生的：

-- 没有连接条件：每位演员与每部电影组合
SELECT COUNT(*) AS row_count
FROM actor, film;

结果：200 000 行：200 位演员 × 1000 部电影。

如果查询突然返回过多的行，或者行以奇怪的组合重复出现，请检查每张被连接的表是否都有连接条件。两张各有 100 000 行的表会产生 100 亿种组合，这可能会让数据库停止响应。

要点：

• CROSS JOIN 把一张表的每一行与另一张表的每一行组合。
• 结果有 m × n 行，没有 ON 条件。
• 不带条件的 FROM a, b 也是笛卡尔积，而且往往是意外产生的。
• CROSS JOIN 加 LEFT JOIN 可以构建完整的报表网格，缺失的组合显示为零。
• 在对大表做交叉连接之前，请先估算结果的规模。

常见问题

SQL 中的笛卡尔积是什么？

两张表中行的所有组合的集合。如果一张表有 2 行，另一张有 16 行，笛卡尔积就有 32 行。CROSS JOIN 显式地返回它。

CROSS JOIN 和 INNER JOIN 有什么区别？

INNER JOIN 只保留满足 ON 条件的组合。CROSS JOIN 保留所有组合。条件始终为真的 INNER JOIN 的行为与 CROSS JOIN 相同。

什么时候应该使用 CROSS JOIN？

需要所有组合时：报表网格、商品规格（颜色 × 尺码）、日历（日期 × 员工），或者与 LEFT JOIN 一起填补时间序列中的空缺。