SQL CROSS JOIN

精华

小牛编辑

114浏览

2023-03-14

CROSS JOIN 称为“交叉连接”或者“笛卡尔连接”。SQL CROSS JOIN 连接用于从两个或者多个连接表中返回记录集的笛卡尔积，即将左表的每一行与右表的每一行合并。

什么是笛卡尔积？

笛卡尔积（Cartesian product）是指两个集合 A 和 B 的乘积。

例如，A 集合和 B 集合分别包含如下的值：

A = {1,2}
B = {3,4,5}

A×B 和 B×A 的结果集分别表示为：

A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };

A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。

从以上结果可以看出：

笛卡尔积不满足交换率，即 A×B≠B×A。
笛卡尔积的元素个数 = A 集合元素个数 × B 集合元素个数。

语法

笛卡尔连接有两种语法，可以使用 CROSS JOIN 关键字，也可以使用不带 WHERE 子句的 SELECT FROM 命令，如下所示：

#第一种写法
SELECT table1.column1, table2.column2...
FROM table1 CROSS JOIN table2

#第二种写法
SELECT table1.column1, table2.column2...
FROM  table1, table2

第一种写法见名知意，是 SQL 标准推荐的写法。

之所以会产生笛卡尔积，是因为以上两种写法既没有使用 WHERE 子句也没有使用 ON 子句，数据库引擎不知道根据什么条件来连接两个表，也不知道根据什么条件来筛选结果集，只能返回笛卡尔积。如果给 CROSS JOIN 加上 ON 子句或者 WHERE 子句，它返回的结果和 INNER JOIN 是一样的。

示例

现在有以下两个表，分别是客户表和订单表。

表1：CUSTOMERS 表

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

表2：ORDERS 表

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

下面使用 CROSS JOIN 语句连接连个表：

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

执行结果：

+----+----------+--------+---------------------+
| ID | NAME     | AMOUNT | DATE                |
+----+----------+--------+---------------------+
|  1 | Ramesh   |   3000 | 2009-10-08 00:00:00 |
|  1 | Ramesh   |   1500 | 2009-10-08 00:00:00 |
|  1 | Ramesh   |   1560 | 2009-11-20 00:00:00 |
|  1 | Ramesh   |   2060 | 2008-05-20 00:00:00 |
|  2 | Khilan   |   3000 | 2009-10-08 00:00:00 |
|  2 | Khilan   |   1500 | 2009-10-08 00:00:00 |
|  2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|  2 | Khilan   |   2060 | 2008-05-20 00:00:00 |
|  3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|  3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|  3 | kaushik  |   1560 | 2009-11-20 00:00:00 |
|  3 | kaushik  |   2060 | 2008-05-20 00:00:00 |
|  4 | Chaitali |   3000 | 2009-10-08 00:00:00 |
|  4 | Chaitali |   1500 | 2009-10-08 00:00:00 |
|  4 | Chaitali |   1560 | 2009-11-20 00:00:00 |
|  4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
|  5 | Hardik   |   3000 | 2009-10-08 00:00:00 |
|  5 | Hardik   |   1500 | 2009-10-08 00:00:00 |
|  5 | Hardik   |   1560 | 2009-11-20 00:00:00 |
|  5 | Hardik   |   2060 | 2008-05-20 00:00:00 |
|  6 | Komal    |   3000 | 2009-10-08 00:00:00 |
|  6 | Komal    |   1500 | 2009-10-08 00:00:00 |
|  6 | Komal    |   1560 | 2009-11-20 00:00:00 |
|  6 | Komal    |   2060 | 2008-05-20 00:00:00 |
|  7 | Muffy    |   3000 | 2009-10-08 00:00:00 |
|  7 | Muffy    |   1500 | 2009-10-08 00:00:00 |
|  7 | Muffy    |   1560 | 2009-11-20 00:00:00 |
|  7 | Muffy    |   2060 | 2008-05-20 00:00:00 |
+----+----------+--------+---------------------+

可以给 CROSS JOIN 加上 ON 子句或者 WHERE 子句，也就是写成：

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     CROSS JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

或者

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     CROSS JOIN ORDERS
     WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

执行结果：

+----+----------+--------+---------------------+
| ID | NAME     | AMOUNT | DATE                |
+----+----------+--------+---------------------+
|  3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|  3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|  2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|  4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
+----+----------+--------+---------------------+

您看，该结果和下面 INNER JOIN 的执行结果一样：

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     INNER JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

性能问题

CROSS JOIN 需要把两个表的每一行都一一合并，并产生一个结果集，这个结果集可能会非常巨大。在生产环境中，一个表有一万条记录非常普遍，两个这样的表进行 CROSS JOIN 连接，结果集就包含 10000 * 10000 = 10000 0000 = 1亿条记录，数据库引擎将花费大量的时间和资源去创建和处理这个结果集。

如非必要，请慎用 CROSS JOIN 连接！