Sql 简明教程
SQL - Cross Join
The SQL Cross Join
SQL Cross Join 是用于检索两个单独表的笛卡尔积（或交叉积）的基本类型内连接。也就是说，此连接会将第一张表中的每一行与第二张表中的每一行组合在一起（即排列）。
An SQL Cross Join is a basic type of inner join that is used to retrieve the Cartesian product (or cross product) of two individual tables. That means, this join will combine each row of the first table with each row of second table (i.e. permutations).
以下示例图以简单的方式说明了交叉连接。
The sample figure below illustrates the cross join in a simple manner.
如你所见，我们考虑了两个表格列：发型和发质。这些列中的每一个都包含一些需要匹配的记录。因此，使用交叉连接，我们将“发型”列中的每条记录与“发质”列中的所有记录组合在一起。所得结果表被认为是笛卡尔积或连接表。
As you can see, we considered two table columns: Hair Style and Hair Type. Each of these columns contain some records that need to be matched. Hence, using cross join, we combine each record in the "Hair Style" column with all records in the "Hair Type" column. The resultant table obtained is considered as the Cartesian product or Joined table.
Syntax
以下是 SQL 中交叉连接查询的基本语法 −
Following is the basic syntax of the Cross Join query in SQL −
SELECT column_name(s)
FROM table1
CROSS JOIN table2;Example
假设我们使用以下查询创建了一张名为 CUSTOMERS 的表，其中包含客户的个人详细信息，包括他们的姓名、年龄、地址和薪水等：
Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc., using the following query −
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 语句向此表中插入值：
Now, insert values into this table using the INSERT statement as follows −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 );该表将被创建为：
The table will be created as −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
2 |
Khilan |
25 |
Delhi |
1500.00 |
让我们创建另一个名为 ORDERS 的表，其中包含所做的订单和下单日期的详细信息。
Let us create another table ORDERS, containing the details of orders made and the date they are made on.
CREATE TABLE ORDERS (
OID INT NOT NULL,
DATE VARCHAR (20) NOT NULL,
CUSTOMER_ID INT NOT NULL,
AMOUNT DECIMAL (18, 2)
);使用 INSERT 语句像下面这样向该表中插入值：
Using the INSERT statement, insert values into this table as follows −
INSERT INTO ORDERS VALUES
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00);该表显示如下：
The table is displayed as follows −
OID |
DATE |
CUSTOMER_ID |
AMOUNT |
100 |
2009-10-08 00:00:00 |
3 |
1500.00 |
101 |
2009-11-20 00:00:00 |
2 |
1560.00 |
现在，如果我们对上面给出的这两个表执行以下 Cross Join query ，交叉连接将会将 CUSTOMERS 表中的每一行与 ORDERS 表中的每一行组合在一起。
Now, if we execute the following Cross Join query on these two tables given above, the cross join combines each row in CUSTOMERS table with each row in ORDERS table.
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
CROSS JOIN ORDERS;Joining Multiple Tables with Cross Join
我们也可以使用交叉连接连接多于两张表。在这种情况下，将显示多路排列，结果表预计将比单独的表包含更多的记录。
We can also join more than two tables using cross join. In this case, multiple-way permutations are displayed and the resultant table is expected to contain way more records than the individual tables.
Syntax
以下是如何在 SQL 中使用交叉连接联接多个表的语法：
Following is the syntax to join multiple tables using cross join in SQL −
SELECT column_name(s)
FROM table1
CROSS JOIN table2
CROSS JOIN table3
CROSS JOIN table4
....
....
....
CROSS JOIN tableN;Example
假设我们使用以下查询创建了一个名为 ORDER_RANGE 的其他表：
Assume we have created another table named ORDER_RANGE using the following query −
CREATE TABLE ORDER_RANGE (
SNO INT NOT NULL,
ORDER_RANGE VARCHAR (20) NOT NULL
);现在，我们可以使用 INSERT 语句将值插入到此空表中，如下所示：
Now, we can insert values into this empty tables using the INSERT statement as follows −
INSERT INTO ORDER_RANGE VALUES
(1, '1-100'),
(2, '100-200'),
(3, '200-300');ORDER_RANGE 表被创建如下：
The ORDER_RANGE table is created as follows −
SNO |
ORDER_RANGE |
1 |
1-100 |
2 |
100-200 |
3 |
200-300 |
以下查询使用交叉连接将三个表 CUSTOMERS、ORDERS 和 ORDER_RANGE 组合起来：
Following query combines the three tables CUSTOMERS, ORDERS and ORDER_RANGE, using cross join −
SELECT ID, NAME, AMOUNT, DATE, ORDER_RANGE
FROM CUSTOMERS
CROSS JOIN ORDERS
CROSS JOIN ORDER_RANGE;Output
生成的表如下：
The resultant table is given below −
ID |
NAME |
AMOUNT |
DATE |
ORDER_RANGE |
2 |
Khilan |
1560 |
2009-11-20 00:00:00 |
1-100 |
1 |
Ramesh |
1560 |
2009-11-20 00:00:00 |
1-100 |
2 |
Khilan |
1500.00 |
2009-10-08 00:00:00 |
1-100 |
1 |
Ramesh |
1500.00 |
2009-10-08 00:00:00 |
1-100 |
2 |
Khilan |
1560 |
2009-11-20 00:00:00 |
100-200 |
1 |
Ramesh |
1560 |
2009-11-20 00:00:00 |
100-200 |
2 |
Khilan |
1500.00 |
2009-10-08 00:00:00 |
100-200 |
1 |
Ramesh |
1500.00 |
2009-10-08 00:00:00 |
100-200 |
2 |
Khilan |
1560 |
2009-11-20 00:00:00 |
200-300 |
1 |
Ramesh |
1560 |
2009-11-20 00:00:00 |
200-300 |
2 |
Khilan |
1500.00 |
2009-10-08 00:00:00 |
200-300 |
1 |
Ramesh |
1500.00 |
2009-10-08 00:00:00 |
200-300 |