38. SQL--cross join：交叉连接

1. 前言

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 集合元素个数。

2. 语法

笛卡尔连接有两种语法，可以使用 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 是一样的。

3. 示例

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

表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;

4. 性能问题

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

如非必要，请慎用 cross join 连接！