SQL - 交叉连接

SQL Cross Join

SQL CROSS JOIN 用于返回两个表的 笛卡尔积。这意味着第一个表中的每一行都会与第二个表中的每一行组合，产生所有可能的行组合。

笛卡尔积是将两个集合相乘的结果，即第一个集合的每个元素与第二个集合的每个元素配对。

SQL CROSS JOIN 的维恩图

以下维恩图展示了 SQL CROSS JOIN 中所有可能的行组合：

如你所见，我们有两个列：Hair Style 和 Hair Type，每个列包含多条记录。使用 CROSS JOIN，"Hair Style" 列中的每条记录都会与 "Hair Type" 列中的所有记录组合。

结果表包含所有可能的组合，被称为 笛卡尔积 或连接表。

语法

以下是 SQL 中 Cross Join 查询的基本语法：

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

示例

假设我们使用以下查询创建了一个名为 CUSTOMERS 的表，其中包含客户的个人信息，如姓名、年龄、地址和薪资等：

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

现在，使用 INSERT 语句向该表插入值，如下所示：

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 );

表将创建如下：

让我们创建另一个表 ORDERS，其中包含订单详情和订单日期。

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

使用 INSERT 语句向该表插入值，如下所示：

INSERT INTO ORDERS VALUES 
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00);

表显示如下：

现在，如果我们对上述两个表执行以下 CROSS JOIN 查询，cross join 会将 CUSTOMERS 表中的每一行与 ORDERS 表中的每一行组合。

SELECT  ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
CROSS JOIN ORDERS;

结果表如下所示：

使用 Cross Join 连接多个表

CROSS JOIN 可用于连接两个以上的表，生成所有相关表的笛卡尔积。第一表中的每一行都会与第二表的每一行组合，然后与第三表的每一行组合，依此类推。这会产生连接表中所有可能的行组合。

要小心，因为使用 CROSS JOIN 连接多个大表可能会产生极大的结果集。

语法

以下是在 SQL 中使用 cross join 连接多个表的语法：

SELECT column_name(s)
FROM table1
CROSS JOIN table2
CROSS JOIN table3
CROSS JOIN table4
....
....
....
CROSS JOIN tableN;

示例

假设我们使用以下查询创建了另一个名为 ORDER_RANGE 的表：

CREATE TABLE ORDER_RANGE (
   SNO INT NOT NULL,
   ORDER_RANGE VARCHAR (20) NOT NULL
);

现在，我们可以使用 INSERT 语句向这个空表插入值，如下所示：

INSERT INTO ORDER_RANGE VALUES
(1, '1-100'),
(2, '100-200'),
(3, '200-300');

ORDER_RANGE 表创建如下：

以下查询使用 cross join 组合了三个表 CUSTOMERS、ORDERS 和 ORDER_RANGE：

SELECT ID, NAME, AMOUNT, DATE, ORDER_RANGE
FROM CUSTOMERS
CROSS JOIN ORDERS
CROSS JOIN ORDER_RANGE;

结果表如下所示：

SQL CROSS JOIN 与内联表

SQL CROSS JOIN 也可以与 inline tables（内联表）或子查询一起使用。这在您想快速组合两组值而无需创建永久表时非常有用。

内联表是在 SQL 查询中直接定义的临时表，通常使用 VALUES 命令或子查询，而不创建永久表。它们允许您快速为 join 或计算提供数据。

示例

以下示例创建了两个包含示例数据的内联表，并对它们执行 CROSS JOIN：

SELECT A.Name, B.Product
FROM (SELECT 'Ramesh' AS Name UNION ALL SELECT 'Khilan') AS A
CROSS JOIN (SELECT 'Laptop' AS Product UNION ALL SELECT 'Mobile') AS B;

此查询的结果将产生两个内联表的笛卡尔积：

CROSS JOIN 与 INNER JOIN 的区别

CROSS JOIN 和 INNER JOIN 都用于组合表，但它们的工作方式截然不同：

CROSS JOIN 的重要要点

以下是使用 SQL 中的 CROSS JOIN 时你应该了解的一些重要要点：

• CROSS JOIN 返回所涉及表的笛卡尔积。
• 它不需要WHERE 子句或连接条件。
• 使用 CROSS JOIN 处理大表时要小心，因为它可能会产生非常多的行。
• CROSS JOIN 可以与内联表或子查询结合使用，用于快速测试或数据生成。
• 当使用 CROSS JOIN 连接多个表时，总行数是每个表行数的乘积。