SQL 中的笛卡尔积：全面指南

笛卡尔积是 SQL 中一个既基础又复杂的概念。若能正确理解并熟练运用，它将大幅提升您的数据处理能力。本文将全面解析笛卡尔积的来龙去脉，并提供实用见解与示例。

如果您刚接触 SQL，建议先从我们的 Intermediate SQL 课程开始，夯实基础。此外，可下载的 SQL Basics Cheat Sheet 也是很好的参考资料，涵盖最常用的 SQL 函数。

什么是笛卡尔积？

SQL 中的笛卡尔积，是将一张表中的每一行与另一张表中的每一行进行组合的结果。最常见的实现方式是使用 CROSS JOIN，会生成包含两张表所有可能行对的结果集。此操作功能强大，但在行数较多的表上会产生非常庞大的结果集，因此需谨慎使用。事实上，很多开发者第一次“认识”笛卡尔积，往往是因为不小心漏写了某个连接条件，最终得到一个巨大的结果集。

数学背景

在数学中，笛卡尔积（记作 A × B）是由集合 A 的每个元素与集合 B 的每个元素配对而成的所有有序对的集合。在 SQL 中，这对应于将一张表的每一行与另一张表的每一行配对。

笛卡尔积示例。图片作者自制。

SQL cross join

SQL 中的 cross join 操作，会将第一张表的每一行与第二张表的每一行连接，生成两表之间的笛卡尔积。因此，cross join 会为数据生成所有可能的行组合。下面通过一个示例演示如何在 SQL 中进行 cross join。

假设您有两张表，Students 和 Courses：

Students 表

name	grade
Alice	10
Bob	11
Charlie	12

Courses 表

course	level
Math	Basic
Science	Advanced
History	Intermediate

在如下查询中，每位学生都会与每门课程配对，同时展示其年级与课程难度等级。

-- Perform the cross join to list each student with every course
SELECT Students.name, Students.grade, Courses.course, Courses.level
FROM Students
CROSS JOIN Courses;技术支持

由 cross join 导致的 SQL 中笛卡尔积示例。图片作者自制。

何时应当使用或避免在 SQL 中使用笛卡尔积

只要理解其行为并有意识地使用，SQL 中的笛卡尔积就会非常有用。下面我们来看一下使用笛卡尔积的典型场景（以及潜在的陷阱）。

适用场景

在以下情形中，使用笛卡尔积可能会很有帮助：

生成组合：笛卡尔积非常适合生成两个取值集合的所有组合。例如，您可能希望将产品与折扣配对、为员工匹配班次，或在不同类别之间创建所有可能的配对。
测试场景：在运行测试用例时，笛卡尔积可用于通过匹配各种参数集或配置来模拟每一种可能的情形。
填补缺失数据：笛卡尔积还可通过生成所有可能的组合并进行左连接来查找缺口，例如在时间序列分析中识别缺失的日期。

潜在陷阱

尽管笛卡尔积在某些场景下很有用，但也存在一些风险，包括：

性能问题：由于笛卡尔积会将一张表的行数与另一张表的行数相乘，结果规模可能呈指数级增长。若数据集过大，可能导致查询执行缓慢、内存占用过高，甚至数据库宕机。
意外的数据重复：如果没有谨慎过滤，笛卡尔积会生成看起来重复或与分析无关的行；若未预期或未妥善处理这些重复，可能造成数据解读偏差或结果错误。
调试困难：例如忘记指定连接条件等非预期的笛卡尔积，会把本该是内连接或外连接的查询变成 cross join，从而产生意料之外的输出，且难以回溯并修正。

笛卡尔积的实际应用

尽管由于潜在风险而应谨慎使用笛卡尔积，但在需要将每一对数据元素都进行组合的情境中，它依然非常有用。

数据组合场景

笛卡尔积适用于计算相对绩效指标、分析兼容性或构建排名体系。当您需要比较两个数据集中的所有元素时，可以使用笛卡尔积将每个项与其他所有项配对。

例如，若要将每位员工的绩效与其他每位员工进行比较，笛卡尔积将创建所有可能的员工配对：

-- Select two columns with employee names from the Employees table
SELECT 
    A.employee_name AS Employee1,
    B.employee_name AS Employee2
FROM Employees AS A
-- Cross join to create all possible pairs of employees
CROSS JOIN Employees AS B;技术支持

笛卡尔积在电商分析中同样重要，可用于探索产品与折扣配对的全量空间。假设您有 Products 与 Discounts 两张表，cross join 可生成所有产品-折扣组合的列表：

-- Select product names and discount types
SELECT 
    Products.product_name,
    Discounts.discount_type
FROM Products
-- Generate all product and discount combinations
CROSS JOIN Discounts;技术支持

测试与验证

在测试环境中，笛卡尔积可以创建所有潜在的参数组合，有助于进行全面的情景测试。例如，在测试包含多种操作系统、设备和浏览器配置的系统时，使用 cross-join 可确保覆盖每一种可能的配置。

-- Select combinations of operating systems, devices, and browsers
SELECT 
    OS.name AS OperatingSystem,
    Device.name AS Device,
    Browser.name AS Browser
FROM OperatingSystems AS OS
-- All combinations with devices
CROSS JOIN Devices AS Device
-- All combinations with browsers           
CROSS JOIN Browsers AS Browser;技术支持

笛卡尔积还能帮助识别缺失记录，确保数据完整性。例如，在分析日度销售数据并确认每个产品每天都有数据时，您可以对 Products 与 Dates 表进行笛卡尔积，然后左连接真实销售数据，以识别缺失的日期-产品组合。

-- Select dates and products with no sales
SELECT 
    Dates.date,
    Products.product_name,
    Sales.sales_amount
FROM Dates
-- Create all date and product combinations
CROSS JOIN Products                          
-- Match sales by date and product
LEFT JOIN 
    Sales ON Dates.date = Sales.date 
           AND Products.product_name = Sales.product_name  
-- Filter for combinations with no sales
WHERE Sales.sales_amount IS NULL;技术支持

一些性能方面的建议

由于笛卡尔积会在两张表之间生成所有可能的数据组合，当涉及大型表时可能引发显著的性能问题。下面是我对查询优化与大数据集管理的一些建议，以提升数据库性能。

查询优化

在编写涉及笛卡尔积的高效查询时，可考虑以下方法。

在连接前限制行数：为减少参与笛卡尔积的行数，先对每张表应用筛选再进行 cross join。例如，使用 WHERE 子句按相关条件过滤各表，尽量缩小结果集规模。
仅选择所需列：在执行笛卡尔积时，在 SELECT 子句中仅指定需要的列，以降低内存使用并提升性能。避免使用 SELECT *，否则会返回所有列，可能导致结果集庞大难控。
通过条件将 cross join 转为 inner join：若仅关心部分行组合，可添加类似 INNER JOIN 的额外筛选条件，限制输出范围，明确应配对的行。
批处理：对于大型数据集，将查询拆分为可管理的小批量进行处理。这有助于防止内存溢出并改善响应时间，尤其是在查询超大表时。

管理大型数据集

以下技巧可用于管理由笛卡尔积产生的大型数据集。

索引：在经常用于连接的列上添加索引，可在过滤或配对大型表时提升性能。虽然索引不会直接加速 cross join，但当后续需要对笛卡尔积结果进行过滤或与其他表连接时，可帮助数据库更快访问行。
对大型表进行分区：对表进行分区可提升涉及笛卡尔积查询的性能。分区会基于指定列将表拆分为更小、更易管理的部分，使数据库在执行查询时仅读取相关分区。
限制结果行数：在仅需从笛卡尔积中抽样用于测试或验证时，可使用 LIMIT 子句限制返回的行数。
使用临时表存储中间结果：当需要多次复用某个笛卡尔积结果时，可将中间结果写入临时表，减少重复计算，加速后续操作。

其他可助力的 SQL 操作

在不适合生成完整笛卡尔积的场景中，也有替代方法可用。下面的方法能帮助您在不过度压垮数据库的情况下达成预期目标。

内连接与外连接

INNER JOIN 仅返回两表中指定列存在匹配的行。该技术可将结果限定为相关的组合，避免笛卡尔积带来的多余行。当您只需要满足两表中特定条件的行时，内连接是合适的选择。

在下例中，INNER JOIN 仅返回属于同一类别的产品与折扣。

-- Select products and corresponding discounts by category
SELECT 
    A.product_name,
    B.discount_type
FROM Products AS A
-- Match products and discounts by category
INNER JOIN Discounts AS B ON A.category_id = B.category_id;技术支持

外连接（LEFT JOIN、RIGHT JOIN 和 FULL JOIN）会返回匹配和不匹配的行。该技术适用于从一张表取回所有行，并带回另一张表中与之匹配的行，但不会生成所有可能的组合。当您希望纳入未匹配数据进行分析、但又不需要完整的笛卡尔配对时，外连接是合适的。

下面查询中的 LEFT JOIN 会返回所有产品，包括没有对应折扣的产品；未匹配的折扣列将为 NULL。

-- Select products with corresponding discounts, (NULL if no matching discount)
SELECT 
    A.product_name,
    B.discount_type
FROM Products AS A
-- Match products with discounts by category
LEFT JOIN Discounts AS B ON A.category_id = B.category_id;  技术支持

我建议学习 DataCamp 的 Introduction to SQL Server 课程，进一步了解如何连接表并从多张表中检索数据。

子查询与 CTE

子查询允许您先从一张表中选出特定数据，再用于主查询的结果过滤。这样可避免不必要的行组合，仅保留相关记录。子查询适用于需要预先过滤数据或计算特定值以供主查询使用、而无需完整笛卡尔连接的情形。

下面的子查询会先筛出折扣值大于 10 的记录，再进行连接，从而减少处理的数据量并避免不必要的 cross join。

-- Select products with discounts greater than 10 by category
SELECT 
    A.product_name,
    B.discount
FROM Products AS A
-- Subquery to filter discounts over 10
INNER JOIN 
    (SELECT discount, category_id   
     FROM Discounts 
     WHERE discount > 10) AS B
-- Match products with filtered discounts by category
ON A.category_id = B.category_id;   技术支持

CTE（WITH 子句）可以将复杂查询拆解为多个步骤。在连接前先过滤或汇总数据，能有效避免笛卡尔积。CTE 适用于简化多步骤查询，尤其是在需要预聚合或进行多次连接、但又想避免完整笛卡尔结果时。

下方的查询使用公共表表达式（CTE）在与产品按类别连接前，先筛选出折扣大于 10 的记录。

-- Filter discounts and join with products by category
WITH FilteredDiscounts AS (
    SELECT discount, category_id
    FROM Discounts
    WHERE discount > 10
)
SELECT 
    A.product_name,
    FD.discount
FROM Products AS A
-- Join with filtered discounts by category
INNER JOIN 
    FilteredDiscounts AS FD ON A.category_id = FD.category_id;技术支持

笛卡尔积的创新应用

尽管笛卡尔积广泛用于基础的 SQL 操作，它的应用并不局限于传统数据库场景。下面我们看看笛卡尔积如何用于机器学习数据准备以及为仿真生成合成数据集。

机器学习数据准备

在为机器学习准备数据时，数据的多样性与全面性至关重要，以训练出健壮的模型。笛卡尔积可以创建覆盖所有特征取值组合的完整数据集，帮助模型学习更广泛的情景。

例如，在构建反欺诈模型时，您可以将交易类型、地区与设备类型进行组合，以模拟各种条件，帮助模型在不同案例中实现更好的泛化。

合成数据生成

合成数据常用于在广泛条件下测试系统，或在不使用真实数据的情况下保护隐私。笛卡尔积有助于生成多样化的合成数据集，适用于从仿真到机器学习数据增强等各类应用。

例如，在金融仿真中，您可以组合不同的市场状况、利率与客户画像，以测试模型或算法在这些变量下的表现。

下面是一个如何在 SQL 中“白手起家”创建合成数据并使用 CROSS JOIN 将其扩展为更大数据集的简单示例。思路是：每个小表代表测试数据的一个维度，然后通过 cross join 获取所有可能的组合。

示例 1：组合多个维度

假设您要基于市场状况、利率与客户画像来模拟不同的金融场景。我们可以使用公共表表达式（CTE）或临时表，以字面值来定义每个维度。接着，通过 CROSS JOIN 将它们合并为所有可能的组合：

WITH MarketConditions AS (
    SELECT 'Bull' AS condition
    UNION ALL
    SELECT 'Bear'
    UNION ALL
    SELECT 'Sideways'
),
InterestRates AS (
    SELECT 1.5 AS rate
    UNION ALL
    SELECT 2.0
    UNION ALL
    SELECT 3.25
),
CustomerProfiles AS (
    SELECT 'Younger' AS profile
    UNION ALL
    SELECT 'Mid-career'
    UNION ALL
    SELECT 'Retired'
)
SELECT 
    mc.condition,
    ir.rate,
    cp.profile
FROM 
    MarketConditions mc
CROSS JOIN 
    InterestRates ir
CROSS JOIN 
    CustomerProfiles cp;技术支持

结果集将包含 3×3×3 = 27 行。每行对应一个唯一的市场状况、利率与客户画像组合。

使用笛卡尔积生成合成数据的示例。图片作者自制。

示例 2：使用序列或简单 VALUES 生成行

另一种技巧是通过将小表与自身进行 cross join 来生成一系列数字或时间区间。这可以用于快速生成大量合成行用于测试。例如，我们可以使用以下查询生成 1 到 10 的数字。

WITH
Nums AS (
    SELECT 1 AS n
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
    UNION ALL SELECT 10
)
SELECT n
FROM Nums;技术支持

使用笛卡尔积结合序列生成行。图片作者自制。

然后可将这个 10 行集合与自身进行 cross join，得到 10×10=100：

WITH
Nums AS (
    SELECT 1 AS n
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
    UNION ALL SELECT 10
)
SELECT A.n AS n1, B.n AS n2
FROM Nums A
CROSS JOIN Nums B
ORDER BY A.n, B.n;技术支持

结果会得到 100 行，每行包含一对值（n1, n2）。您可以将此逻辑扩展到数百、数千甚至数百万行——但请注意性能与存储。

通过这些方法——使用 CTE（或 VALUES 子句）定义维度，配合 CROSS JOIN 进行组合——您可以在不依赖现有表的情况下，快速在 SQL 中创建合成数据集。

结语

理解 SQL 中的笛卡尔积将为数据处理与分析带来更多可能。掌握这一概念能够增强您处理复杂数据集与优化数据库查询的能力。若想进一步进阶，不妨了解我们的 SQL Server Fundamentals 技能路径，深化您的专业知识。

根据您的职业方向，我建议报名 DataCamp 的Associate Data Analyst in SQL 职业路径，学习成为数据分析师所需的行业技能；或者尝试我们的Associate Date Engineer in SQL 职业路径，迈向数据工程师之路。

作者

Allan Ouko

数据科学技术写作者，具备数据分析、商业智能和数据科学的一线实践经验。我撰写以行业为导向的实用内容，涵盖 SQL、Python、Power BI、Databricks 和数据工程，并以真实的分析工作为基础。我的写作兼顾技术深度与业务影响，帮助专业人士将数据转化为自信的决策。

笛卡尔积常见问题

SQL 中的笛卡尔积是什么？

SQL 中的笛卡尔积源自 cross join 操作，即将一张表的每一行与另一张表的每一行组合。

笛卡尔积与其他 SQL 连接有何不同？

与其他连接不同，笛卡尔积会组合参与表中所有可能的行对，通常会产生体量很大的数据集。

我应该在何时使用笛卡尔积？

当需要从两张表生成所有行组合时使用笛卡尔积，例如在测试或特定分析场景中。

使用笛卡尔积时需要考虑哪些性能问题？

笛卡尔积可能导致结果集过大，从而影响性能。为缓解此问题，请优化查询并谨慎管理数据集。

在 SQL 中是否有替代笛卡尔积的方法？

是的，视具体用例而定，替代方案包括使用内连接或外连接、子查询以及公共表表达式。

主题

SQL 数据工程

主题

技术