PHP小码农

Appearance

交叉连接（CROSS JOIN） ​

本节讲解交叉连接（Cross Join）的笛卡尔积语义、与 LATERAL 的关系、行数爆炸的风险，以及它在"生成全组合""补零报表""展开集合返回函数"等场景中的合理用途。学完本节，你将能辨认什么场合该用 CROSS JOIN，什么场合它是灾难。

前置知识

阅读本节前，建议先了解：外连接（LEFT / RIGHT / FULL JOIN）

基础概念 ​

交叉连接（Cross Join） 把左右表的每一行两两配对，结果行数等于两表行数之积，这个运算叫笛卡尔积（Cartesian Product）。

概念术语说明
交叉连接CROSS JOIN无条件配对，行数 = 左行数 × 右行数
笛卡尔积Cartesian Product集合论中的全组合运算
生成序列generate_series返回整数/时间戳序列的集合返回函数
集合返回函数Set-Returning Function (SRF)一次返回多行的函数
全组合矩阵Full Combination Matrix每个维度取值两两组合形成的网格
行数爆炸Row Explosion乘积过大导致内存/磁盘耗尽

交叉连接的等价写法有三种，结果完全相同：

写法语法说明
显式 CROSS JOINA CROSS JOIN B✅ 推荐，意图明确
逗号连接A, B⚠️ 可读性一般，容易被误认为漏写条件
JOIN ... ON TRUEA JOIN B ON TRUE⚠️ 写法冗余，不推荐

CROSS JOIN 在 PostgreSQL 中有个特殊能力：它是唯一允许右侧是集合返回函数（SRF）的连接形式，且隐式带有 LATERAL 语义。

text
A CROSS JOIN B  ←→  A JOIN B ON TRUE

理解这一点后就能明白：CROSS JOIN LATERAL 中的 LATERAL 其实是可省略的，因为 CROSS JOIN 本身就允许右表引用左表。

语法与用法 ​

交叉连接的语法：

text
SELECT 列列表
FROM 左表
CROSS JOIN 右表
[WHERE 过滤条件];

准备本节贯穿使用的示例表：

sql
-- 门店表
CREATE TABLE stores (
  id      INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code    TEXT    NOT NULL UNIQUE,
  name    TEXT    NOT NULL,
  region  TEXT    NOT NULL
);

-- 商品分类表
CREATE TABLE categories (
  id     INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code   TEXT NOT NULL UNIQUE,
  name   TEXT NOT NULL
);

-- 销售表：只记录"有销量"的 (日期, 门店, 分类) 组合
CREATE TABLE sales (
  id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  stat_date   DATE    NOT NULL,
  store_id    INTEGER NOT NULL,
  category_id INTEGER NOT NULL,
  amount      NUMERIC(10, 2) NOT NULL DEFAULT 0
);

INSERT INTO stores (code, name, region) VALUES
  ('S-01', '北京朝阳店', 'north'),
  ('S-02', '上海静安店', 'east'),
  ('S-03', '广州天河店', 'south');

INSERT INTO categories (code, name) VALUES
  ('C-01', '数码'),
  ('C-02', '家电');

INSERT INTO sales (stat_date, store_id, category_id, amount) VALUES
  (DATE '2026-01-01', 1, 1, 1200.00),
  (DATE '2026-01-01', 2, 1,  800.00),
  (DATE '2026-01-02', 1, 2,  500.00);
-- 注意：并不存在 2026-01-01 门店 3 的记录，也不存在 2026-01-02 分类 1 的记录

三种等价写法：

sql
-- 写法一：显式 CROSS JOIN（推荐）
SELECT s.name, c.name
FROM stores AS s
CROSS JOIN categories AS c
ORDER BY s.id, c.id;

-- 写法二：逗号连接
SELECT s.name, c.name
FROM stores AS s, categories AS c
ORDER BY s.id, c.id;

-- 写法三：JOIN ... ON TRUE
SELECT s.name, c.name
FROM stores AS s
JOIN categories AS c ON TRUE
ORDER BY s.id, c.id;
text
    name     | name
-------------+------
 北京朝阳店   | 数码
 北京朝阳店   | 家电
 上海静安店   | 数码
 上海静安店   | 家电
 广州天河店   | 数码
 广州天河店   | 家电
(6 rows)

3 个门店 × 2 个分类 = 6 行。

详细说明 ​

与 INNER JOIN ON TRUE 的等价性 ​

CROSS JOIN 与 JOIN ... ON TRUE 在计划层面完全一致，但语义表达力不同：

sql
-- 验证两种写法执行计划相同
EXPLAIN
SELECT s.name, c.name FROM stores AS s CROSS JOIN categories AS c;

EXPLAIN
SELECT s.name, c.name FROM stores AS s JOIN categories AS c ON TRUE;
text
 Nested Loop  (cost=0.00..1.18 rows=6 width=44)
   ->  Seq Scan on stores s  (cost=0.00..1.03 rows=3 width=28)
   ->  Materialize  (cost=0.00..1.04 rows=2 width=28)
         ->  Seq Scan on categories c  (cost=0.00..1.03 rows=2 width=28)

两者都产生 Nested Loop 且内表被 Materialize（物化一次反复使用）。区别只在可读性：ON TRUE 让人怀疑"是不是忘了写条件"，而 CROSS JOIN 一眼就能看出"这是故意的全组合"。

行数爆炸风险与预警 ​

笛卡尔积的增长是指数级的，这是 CROSS JOIN 最大的风险：

左表行数右表行数结果行数备注
1010100无感
1,0001,0001,000,000开始变慢
100,000100,00010,000,000,000必然 OOM
1,000,0001,0001,000,000,000磁盘打满
sql
-- 危险！在生产库上执行前务必先用 count(*) 估算行数
SELECT count(*) FROM orders CROSS JOIN users;       -- 行数 = orders × users

-- 更危险！缺少连接条件时，本意是 INNER JOIN 却得到笛卡尔积
SELECT * FROM orders, users WHERE orders.status = 'paid';   -- 错误！漏了 users.id = orders.user_id

预警公式：

sql
-- 执行任何 CROSS JOIN 前，先用这条语句估算结果行数
SELECT
  (SELECT count(*) FROM stores)     AS left_rows,
  (SELECT count(*) FROM categories) AS right_rows,
  (SELECT count(*) FROM stores) * (SELECT count(*) FROM categories) AS product_rows;
text
 left_rows | right_rows | product_rows
-----------+------------+--------------
         3 |          2 |            6

合理用途一：生成全组合网格（补零报表） ​

这是 CROSS JOIN 最经典、最有价值的用途。报表常要求"即使某天某个门店没有销售，也要显示 0"（而不是缺行）。做法是先用 CROSS JOIN 生成完整的维度网格，再左连接实际数据。

sql
-- 生成 日期 × 门店 × 分类 的完整网格
SELECT d.stat_date, s.id AS store_id, s.name AS store_name, c.id AS category_id, c.name AS category_name
FROM generate_series(DATE '2026-01-01', DATE '2026-01-03', INTERVAL '1 day')::date AS d (stat_date)
CROSS JOIN stores AS s
CROSS JOIN categories AS c
ORDER BY d.stat_date, s.id, c.id;
text
 stat_date  | store_id | store_name | category_id | category_name
------------+----------+------------+-------------+---------------
 2026-01-01 |        1 | 北京朝阳店  |           1 | 数码
 2026-01-01 |        1 | 北京朝阳店  |           2 | 家电
 2026-01-01 |        2 | 上海静安店  |           1 | 数码
 2026-01-01 |        2 | 上海静安店  |           2 | 家电
 2026-01-01 |        3 | 广州天河店  |           1 | 数码
 2026-01-01 |        3 | 广州天河店  |           2 | 家电
 2026-01-02 |        1 | 北京朝阳店  |           1 | 数码
 ...（共 18 行）

合理用途二：日期 × 门店矩阵补零 ​

把上面的网格与实际销售左连接，用 coalesce 补 0，就得到标准报表：

sql
WITH date_grid AS (
  SELECT d::date AS stat_date
  FROM generate_series(DATE '2026-01-01', DATE '2026-01-03', INTERVAL '1 day') AS d
),
full_grid AS (
  SELECT g.stat_date, s.id AS store_id, s.name AS store_name,
         c.id AS category_id, c.name AS category_name
  FROM date_grid AS g
  CROSS JOIN stores AS s
  CROSS JOIN categories AS c
)
SELECT
  f.stat_date,
  f.store_name,
  f.category_name,
  coalesce(sum(sa.amount), 0)  AS amount
FROM full_grid AS f
LEFT JOIN sales AS sa
  ON sa.stat_date   = f.stat_date
 AND sa.store_id    = f.store_id
 AND sa.category_id = f.category_id
GROUP BY f.stat_date, f.store_name, f.category_name
ORDER BY f.stat_date, f.store_name, f.category_name;
text
 stat_date  | store_name | category_name | amount
------------+------------+---------------+---------
 2026-01-01 | 上海静安店  | 家电          |    0.00
 2026-01-01 | 上海静安店  | 数码          |  800.00
 2026-01-01 | 北京朝阳店  | 家电          |    0.00
 2026-01-01 | 北京朝阳店  | 数码          | 1200.00
 2026-01-01 | 广州天河店  | 家电          |    0.00
 2026-01-01 | 广州天河店  | 数码          |    0.00
 2026-01-02 | 上海静安店  | 家电          |    0.00
 2026-01-02 | 上海静安店  | 数码          |    0.00
 2026-01-02 | 北京朝阳店  | 家电          |  500.00
 2026-01-02 | 北京朝阳店  | 数码          |    0.00
 2026-01-02 | 广州天河店  | 家电          |    0.00
 2026-01-02 | 广州天河店  | 数码          |    0.00
 2026-01-03 | 上海静安店  | 家电          |    0.00
 2026-01-03 | 上海静安店  | 数码          |    0.00
 2026-01-03 | 北京朝阳店  | 家电          |    0.00
 2026-01-03 | 北京朝阳店  | 数码          |    0.00
 2026-01-03 | 广州天河店  | 家电          |    0.00
 2026-01-03 | 广州天河店  | 数码          |    0.00
(18 rows)

合理用途三：展开集合返回函数（SRF） ​

CROSS JOIN LATERAL 是展开数组、JSON、序列的常用手段。因为 CROSS JOIN 隐式带有 LATERAL 语义，LATERAL 关键字可以省略。

sql
-- 把每个用户的标签数组展开成多行
SELECT u.name, tag
FROM users AS u
CROSS JOIN LATERAL unnest(ARRAY['new', 'vip']) AS tag;
text
 name  | tag
-------+-----
 alice | new
 alice | vip
 bob   | new
 bob   | vip
 carol | new
 carol | vip
 dave  | new
 dave  | vip
(8 rows)
sql
-- 展开 jsonb 数组
SELECT u.name, item->>'sku' AS sku
FROM users AS u
CROSS JOIN LATERAL jsonb_array_elements('[{"sku":"A1"},{"sku":"B2"}]'::jsonb) AS item;

-- 与 generate_series 组合：为每个用户生成 3 个编号
SELECT u.name, n
FROM users AS u
CROSS JOIN generate_series(1, 3) AS n
ORDER BY u.name, n;

CROSS JOIN 与 LATERAL 的关系 ​

严格来说，CROSS JOIN 与 JOIN LATERAL ... ON TRUE 是等价的，而 LEFT JOIN LATERAL ... ON TRUE 多保留"右侧无结果时也保留左行"的行为。

写法右侧是否可引用左表右侧无结果时典型用途
CROSS JOIN B仅当 B 是 SRF 或含 SRF左行被丢弃全组合、展开 SRF
CROSS JOIN LATERAL (SELECT ...) B是左行被丢弃每组 TopN（内连接版）
LEFT JOIN LATERAL (SELECT ...) B ON TRUE是左行保留，补 NULL每组 TopN（保留空组）
sql
-- 对比：CROSS JOIN LATERAL 会丢弃无结果的左行
SELECT u.name, o.order_no
FROM users AS u
CROSS JOIN LATERAL (
  SELECT order_no FROM orders AS o
  WHERE o.user_id = u.id AND o.status = 'paid'
  LIMIT 1
) AS o
ORDER BY u.name;
text
 name  | order_no
-------+----------
 alice | SO-1001
 bob   | SO-1002
(2 rows)

carol、dave 没有已付款订单，被 CROSS JOIN LATERAL 丢弃。若希望保留它们，应改用 LEFT JOIN LATERAL：

sql
SELECT u.name, o.order_no
FROM users AS u
LEFT JOIN LATERAL (
  SELECT order_no FROM orders AS o
  WHERE o.user_id = u.id AND o.status = 'paid'
  LIMIT 1
) AS o ON TRUE
ORDER BY u.name;
text
 name  | order_no
-------+----------
 alice | SO-1001
 bob   | SO-1002
 carol | (null)
 dave  | (null)
(4 rows)

实战示例 ​

完整实现"生成完整日期 × 分类矩阵补零报表"。

sql
-- 步骤 1：查看原始数据（注意缺哪些组合）
SELECT stat_date, store_id, category_id, amount
FROM sales
ORDER BY stat_date, store_id, category_id;
text
 stat_date  | store_id | category_id | amount
------------+----------+-------------+---------
 2026-01-01 |        1 |           1 | 1200.00
 2026-01-01 |        2 |           1 |  800.00
 2026-01-02 |        1 |           2 |  500.00
(3 rows)
sql
-- 步骤 2：生成网格并补零
WITH date_grid AS (
  SELECT g::date AS stat_date
  FROM generate_series(DATE '2026-01-01', DATE '2026-01-02', INTERVAL '1 day') AS g
),
grid AS (
  SELECT d.stat_date, s.id AS store_id, c.id AS category_id
  FROM date_grid AS d
  CROSS JOIN stores     AS s
  CROSS JOIN categories AS c
)
SELECT
  g.stat_date,
  s.name                       AS store_name,
  c.name                       AS category_name,
  coalesce(sa.amount, 0)       AS amount
FROM grid AS g
JOIN stores     AS s ON s.id = g.store_id
JOIN categories AS c ON c.id = g.category_id
LEFT JOIN sales AS sa
  ON sa.stat_date   = g.stat_date
 AND sa.store_id    = g.store_id
 AND sa.category_id = g.category_id
ORDER BY g.stat_date, s.id, c.id;
text
 stat_date  | store_name | category_name | amount
------------+------------+---------------+---------
 2026-01-01 | 北京朝阳店  | 数码          | 1200.00
 2026-01-01 | 北京朝阳店  | 家电          |    0.00
 2026-01-01 | 上海静安店  | 数码          |  800.00
 2026-01-01 | 上海静安店  | 家电          |    0.00
 2026-01-01 | 广州天河店  | 数码          |    0.00
 2026-01-01 | 广州天河店  | 家电          |    0.00
 2026-01-02 | 北京朝阳店  | 数码          |    0.00
 2026-01-02 | 北京朝阳店  | 家电          |  500.00
 2026-01-02 | 上海静安店  | 数码          |    0.00
 2026-01-02 | 上海静安店  | 家电          |    0.00
 2026-01-02 | 广州天河店  | 数码          |    0.00
 2026-01-02 | 广州天河店  | 家电          |    0.00
(12 rows)

2 天 × 3 门店 × 2 分类 = 12 行，完整无缺。

sql
-- 步骤 3：行列透视 —— 用 CROSS JOIN 构造表头，再用条件聚合转列
WITH grid AS (
  SELECT g::date AS stat_date
  FROM generate_series(DATE '2026-01-01', DATE '2026-01-02', INTERVAL '1 day') AS g
),
full_grid AS (
  SELECT g.stat_date, s.id AS store_id, c.id AS category_id
  FROM grid AS g
  CROSS JOIN stores AS s
  CROSS JOIN categories AS c
)
SELECT
  s.name AS store_name,
  sum(CASE WHEN c.code = 'C-01' AND g.stat_date = DATE '2026-01-01' THEN coalesce(sa.amount, 0) ELSE 0 END) AS "数码_0101",
  sum(CASE WHEN c.code = 'C-02' AND g.stat_date = DATE '2026-01-01' THEN coalesce(sa.amount, 0) ELSE 0 END) AS "家电_0101",
  sum(CASE WHEN c.code = 'C-01' AND g.stat_date = DATE '2026-01-02' THEN coalesce(sa.amount, 0) ELSE 0 END) AS "数码_0102",
  sum(CASE WHEN c.code = 'C-02' AND g.stat_date = DATE '2026-01-02' THEN coalesce(sa.amount, 0) ELSE 0 END) AS "家电_0102"
FROM full_grid AS g
JOIN stores     AS s ON s.id = g.store_id
JOIN categories AS c ON c.id = g.category_id
LEFT JOIN sales AS sa
  ON sa.stat_date   = g.stat_date
 AND sa.store_id    = g.store_id
 AND sa.category_id = g.category_id
GROUP BY s.id, s.name
ORDER BY s.id;
text
 store_name | 数码_0101 | 家电_0101 | 数码_0102 | 家电_0102
------------+-----------+-----------+-----------+-----------
 北京朝阳店  |   1200.00 |      0.00 |      0.00 |    500.00
 上海静安店  |    800.00 |      0.00 |      0.00 |      0.00
 广州天河店  |      0.00 |      0.00 |      0.00 |      0.00
(3 rows)

注意事项 ​

  • CROSS JOIN 最容易由"漏写连接条件"意外产生。 内连接漏写 ON（或逗号连接漏写 WHERE 条件）就会静默变成笛卡尔积。
sql
-- 错误！本意是内连接，实际是笛卡尔积
SELECT o.order_no, u.name FROM orders AS o, users AS u WHERE o.status = 'paid';

-- 正确
SELECT o.order_no, u.name FROM orders AS o JOIN users AS u ON u.id = o.user_id WHERE o.status = 'paid';
  • 执行前必须估算行数。 乘积增长极快，在大表上一条 CROSS JOIN 就能耗尽内存。
sql
-- 先估算，再决定是否执行
SELECT
  (SELECT count(*) FROM big_table_a) * (SELECT count(*) FROM big_table_b) AS estimated_rows;
  • CROSS JOIN 的过滤条件写在 WHERE 中不会改变"先做笛卡尔积"的事实。 优化器可能把条件下推，但若条件过滤性差，仍会先生成巨量中间结果。
sql
-- 不推荐：形式上正确，但中间结果可能极大
SELECT s.name, c.name
FROM stores AS s CROSS JOIN categories AS c
WHERE c.code = 'C-01';

-- 更好：先过滤再交叉连接，减小中间结果
SELECT s.name, c.name
FROM stores AS s
CROSS JOIN (SELECT * FROM categories WHERE code = 'C-01') AS c;

  • CROSS JOIN 的书写顺序影响中间结果规模。 若一侧已过滤到很小，把它放后面并配合 EXPLAIN 确认内表被物化。

  • JOIN ... ON TRUE 与 CROSS JOIN 等价，但不推荐使用。 它让人误以为漏写了条件。

  • CROSS JOIN 不适合表达"每组取 N 行"。 那种场景应使用 LATERAL 或窗口函数，因为 CROSS JOIN LATERAL 会丢弃无匹配的左行。

  • 含 CROSS JOIN 的查询不要让用户直接控制输入规模。 若某一侧来自用户输入的行数，需在应用层限制上下界。

最佳实践 ​

  1. 只在"确实需要全组合"时使用 CROSS JOIN。 典型场景：日期补全、维度网格、展开 SRF。常规的关联查询一律用 INNER JOIN / LEFT JOIN。

  2. 先在纸上算出结果行数。 若乘积超过百万量级，重新审视需求，考虑用 generate_series 限定范围或用聚合替代。

sql
-- 用 generate_series 精确控制网格规模
SELECT count(*)
FROM generate_series(DATE '2026-01-01', DATE '2026-12-31', INTERVAL '1 day') AS d
CROSS JOIN stores
CROSS JOIN categories;
-- 365 天 × 门店数 × 分类数，先看清规模再执行
  1. 把 CROSS JOIN 放在 CTE 中，明确它是"生成网格"这一步。
sql
-- 语义清晰的分层写法
WITH date_grid AS (
  SELECT d::date AS stat_date
  FROM generate_series(DATE '2026-01-01', DATE '2026-01-03', INTERVAL '1 day') AS d
),
full_grid AS (
  SELECT g.stat_date, s.id AS store_id, c.id AS category_id
  FROM date_grid AS g
  CROSS JOIN stores     AS s
  CROSS JOIN categories AS c
)
SELECT ...
FROM full_grid ...
  1. 展开数组/JSON 时优先用 CROSS JOIN LATERAL 或直接 CROSS JOIN SRF。 后者更简洁，但要注意它丢弃无结果的左行。
sql
-- 展开数组：CROSS JOIN 隐式 LATERAL，写法最简
SELECT u.name, tag
FROM users AS u
CROSS JOIN unnest(ARRAY['new', 'vip']) AS tag;
  1. 补零报表用"网格 + LEFT JOIN + coalesce"三段式。 这是最稳妥、最易维护的模板。
sql
-- 补零报表模板
WITH grid AS (
  SELECT ... FROM 维度表1 CROSS JOIN 维度表2
)
SELECT g.维度, coalesce(f.指标, 0)
FROM grid AS g
LEFT JOIN fact_table AS f ON <所有维度列相等>
ORDER BY ...;
  1. 为交叉连接后的左连接条件建复合索引。 补零报表的性能瓶颈通常在 LEFT JOIN fact_table 那一步。
sql
-- 覆盖 (日期, 门店, 分类) 三个连接列的复合索引
CREATE UNIQUE INDEX idx_sales_dims ON sales (stat_date, store_id, category_id);
  1. 用 EXPLAIN 确认没有意外的 Nested Loop 无 Join Filter。 若看到 Nested Loop 且节点上没有 Join Filter/Hash Cond/Merge Cond，说明这是交叉连接。
sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT s.name, c.name
FROM stores AS s
CROSS JOIN categories AS c;

下一节

继续学习：自然连接与 USING

参考链接 ​