sql-cross-join

SQL CROSS JOIN（交叉联接）

SQL CROSS JOIN（交叉联接）

SQL CROSS JOIN 是一种联接类型，它返回联接中表的行的笛卡尔积。简单来说，它将第一个表中的每一行与第二个表中的每一行组合。此操作会生成一个包含所有可能的行配对的表。

理解笛卡尔积

想象您有一组衬衫和一组裤子。笛卡尔积就像将每件衬衫与每一条裤子配对，从而创建所有可能的服装。如果您有 3 件衬衫和 4 条裤子，最终将得到 3 * 4 = 12 种不同的服装。

警告：由于 CROSS JOIN 会生成 M x N 行（其中 M 是第一个表中的行数，N 是第二个表中的行数），它会非常迅速地产生极其庞大的结果集。在大型表上使用它可能会导致严重的性能问题甚至服务器崩溃。这通常是查询中意外遗漏 JOIN 条件的结果。

语法

CROSS JOIN 的基本语法明确清晰：

SELECT column_list
FROM table1
CROSS JOIN table2;

还存在一种较旧的隐式语法，但不推荐使用，因为它可读性较差，可能被误认为是错误：

-- 这种语法也会产生 CROSS JOIN，但不够清晰。
SELECT column_list
FROM table1, table2;

何时使用 CROSS JOIN

尽管有警告，CROSS JOIN 仍有其有效的用例。它对于生成组合数据特别有用。

示例：生成产品变体

假设我们有一个 products 表和一个 colors 表。我们想生成一份包含每种产品所有可用颜色的列表，以填充我们的库存。

-- 首先，我们创建带有现代数据类型的表。
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255) NOT NULL
);

CREATE TABLE colors (
    color_id INT PRIMARY KEY AUTO_INCREMENT,
    color_name VARCHAR(50) NOT NULL
);

-- 现在，插入一些示例数据。
INSERT INTO products (product_name) VALUES
    ('Classic T-Shirt'),
    ('Hoodie');

INSERT INTO colors (color_name) VALUES
    ('Red'),
    ('Blue'),
    ('Black');

现在，我们可以使用 CROSS JOIN 来生成所有可能的变体：

SELECT
    p.product_name,
    c.color_name
FROM
    products AS p
CROSS JOIN
    colors AS c
ORDER BY
    p.product_name, c.color_name;

输出

该查询生成了所有 2 * 3 = 6 种组合的完整列表：

产品名称	颜色名称
Classic T-Shirt	Black
Classic T-Shirt	Blue
Classic T-Shirt	Red
Hoodie	Black
Hoodie	Blue
Hoodie	Red

使用 CROSS JOIN 连接多个表

您可以链式使用 CROSS JOIN 来组合两个以上的表。结果中的行数将是所有涉及表的行数的乘积。

语法

SELECT column_list
FROM table1
CROSS JOIN table2
CROSS JOIN table3
...;

示例：向产品变体添加尺寸

让我们在之前的示例中添加一个 sizes 表，以生成完整的库存列表。

-- 创建并填充 sizes 表
CREATE TABLE sizes (
    size_id INT PRIMARY KEY AUTO_INCREMENT,
    size_code VARCHAR(10) NOT NULL
);

INSERT INTO sizes (size_code) VALUES ('S'), ('M'), ('L'), ('XL');

现在，我们联接所有三个表：

SELECT
    p.product_name,
    c.color_name,
    s.size_code
FROM
    products AS p
CROSS JOIN
    colors AS c
CROSS JOIN
    sizes AS s
ORDER BY
    p.product_name, c.color_name, s.size_code;

输出

此查询生成所有 2（产品）* 3（颜色）* 4（尺寸）= 24 种可能的组合。以下是结果的小样本：

产品名称	颜色名称	尺寸代码
Classic T-Shirt	Black	L
Classic T-Shirt	Black	M
Classic T-Shirt	Black	S
Classic T-Shirt	Black	XL
Classic T-Shirt	Blue	L
…	…	…

常见陷阱和最佳实践

• 意外的 CROSS JOIN：最常见的错误是忘记联接条件（例如 ON t1.id = t2.t1_id 或 WHERE 子句）。始终使用显式 JOIN 语法（INNER JOIN、LEFT JOIN 等）来避免这种情况。
• 性能：在处理非小型表时使用 CROSS JOIN 需极其谨慎。在运行查询之前，务必分析结果集的潜在大小。
• 与 WHERE 子句一起使用：有时 CROSS JOIN 用于生成一组潜在组合，然后通过 WHERE 子句进行筛选。这可以是解决复杂问题的强大技术。