SQL - Cross Join

http://www.un.org。这是一种基本类型的内ner,用于检索两个表的 Car产品(或跨产品)。这意味着,这一合并将把第一个表格的每行与第二位(即每轮变动)的每行合并。

A Cartesian product, or a cross product, is the result achieved from multippcation of two sets. This is done by multiplying all the possible pairs from both the sets.

下面的样本数字以简单的方式说明了交叉。

各位可以看到,我们审议了两个表列: 奶类和奶类。这些栏目中的每一栏都有一些需要加以匹配的记录。因此,利用相互合并,我们把“奶类”一栏中的每一记录与“奶类”一栏的所有记录结合起来。所得表格被视为 Car产品或 Jo。

Syntax

The following is the basic syntax of the Cross Join query in -kou


SELECT column_name(s)
FROM table1
CROSS JOIN table2;

Example

让我们在现有数据库中考虑两个现有表格,并努力利用以下交叉点加入这些表格:

假设我们已经建立了一个名为“客户”的表格,其中载有客户的个人详情,包括姓名、年龄、地址和工资等,使用以下询问:


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 INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1,  Ramesh , 32,  Ahmedabad , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2,  Khilan , 25,  Delhi , 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3,  kaushik , 23,  Kota , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4,  Chaitap , 25,  Mumbai , 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5,  Hardik , 27,  Bhopal , 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6,  Komal , 22,  MP , 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7,  Muffy , 24,  Indore , 10000.00 );

该表将编成:

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

让我们制定另一份表格命令,载列所发布命令的细节和日期。


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 (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (102,  2009-10-08 00:00:00 , 3, 3000.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (100,  2009-10-08 00:00:00 , 3, 1500.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (101,  2009-11-20 00:00:00 , 2, 1560.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (103,  2008-05-20 00:00:00 , 4, 2060.00);

表格如下:


+-----+---------------------+-------------+---------+
| OID | DATE                | CUSTOMER_ID | AMOUNT  |
+-----+---------------------+-------------+---------+
| 102 | 2009-10-08 00:00:00 |           3 | 3000.00 |
| 100 | 2009-10-08 00:00:00 |           3 | 1500.00 |
| 101 | 2009-11-20 00:00:00 |           2 | 1560.00 |
| 103 | 2008-05-20 00:00:00 |           4 | 2060.00 |
+-----+---------------------+-------------+---------+

现在,如果我们在上述两张表格上执行以下Cross Join query,那么,这些表格中的每一行都与订单表中的每一行合并。

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

Output

下表为:

+----+----------+--------+---------------------+ | 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 | Chaitap | 3000 | 2009-10-08 00:00:00 | | 4 | Chaitap | 1500 | 2009-10-08 00:00:00 | | 4 | Chaitap | 1560 | 2009-11-20 00:00:00 | | 4 | Chaitap | 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 | +----+----------+--------+---------------------+

Joining Multiple Tables with Cross Join

我们还可以加入两个以上表格,同时加入。在这种情况下,显示双向变动,结果表将包含比个别表格更多的记录。

Syntax

下面是参加多个表格的辛迪加,使用横跨卡卡加入——

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

Example

让我们努力把三个表格的客户、订单和订单——Range合并起来,以显示多个表格的相互融合。

我们将努力利用下面的问询,创建“Range”教席。

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

现在,我们可以利用INSERT声明,将价值观纳入这一空表:

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

保护令表格如下:

+-----+-------------+ | SNO | ORDER_RANGE | +-----+-------------+ | 1 | 1-100 | | 2 | 100-200 | | 3 | 200-300 | +-----+-------------+

现在,我们利用以下交叉点,就表格提出看法,

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

Output

下表列出:

+----+----------+---------+---------------------+-------------+ | ID | NAME | AMOUNT | DATE | ORDER_RANGE | +----+----------+---------+---------------------+-------------+ | 1 | Ramesh | 2060.00 | 2008-05-20 00:00:00 | 1-100 | | 1 | Ramesh | 2060.00 | 2008-05-20 00:00:00 | 100-200 | | 1 | Ramesh | 2060.00 | 2008-05-20 00:00:00 | 200-300 | | 1 | Ramesh | 1560.00 | 2009-11-20 00:00:00 | 1-100 | | 1 | Ramesh | 1560.00 | 2009-11-20 00:00:00 | 100-200 | | 1 | Ramesh | 1560.00 | 2009-11-20 00:00:00 | 200-300 | | 1 | Ramesh | 1500.00 | 2009-10-08 00:00:00 | 1-100 | | 1 | Ramesh | 1500.00 | 2009-10-08 00:00:00 | 100-200 | | 1 | Ramesh | 1500.00 | 2009-10-08 00:00:00 | 200-300 | | 1 | Ramesh | 3000.00 | 2009-10-08 00:00:00 | 1-100 | | 1 | Ramesh | 3000.00 | 2009-10-08 00:00:00 | 100-200 | | 1 | Ramesh | 3000.00 | 2009-10-08 00:00:00 | 200-300 | | 2 | Khilan | 2060.00 | 2008-05-20 00:00:00 | 1-100 | | 2 | Khilan | 2060.00 | 2008-05-20 00:00:00 | 100-200 | | 2 | Khilan | 2060.00 | 2008-05-20 00:00:00 | 200-300 | | 2 | Khilan | 1560.00 | 2009-11-20 00:00:00 | 1-100 | | 2 | Khilan | 1560.00 | 2009-11-20 00:00:00 | 100-200 | | 2 | Khilan | 1560.00 | 2009-11-20 00:00:00 | 200-300 | | 2 | Khilan | 1500.00 | 2009-10-08 00:00:00 | 1-100 | | 2 | Khilan | 1500.00 | 2009-10-08 00:00:00 | 100-200 | | 2 | Khilan | 1500.00 | 2009-10-08 00:00:00 | 200-300 | | 2 | Khilan | 3000.00 | 2009-10-08 00:00:00 | 1-100 | | 2 | Khilan | 3000.00 | 2009-10-08 00:00:00 | 100-200 | | 2 | Khilan | 3000.00 | 2009-10-08 00:00:00 | 200-300 | | 3 | Kaushik | 2060.00 | 2008-05-20 00:00:00 | 1-100 | | 3 | Kaushik | 2060.00 | 2008-05-20 00:00:00 | 100-200 | | 3 | Kaushik | 2060.00 | 2008-05-20 00:00:00 | 200-300 | | 3 | Kaushik | 1560.00 | 2009-11-20 00:00:00 | 1-100 | | 3 | Kaushik | 1560.00 | 2009-11-20 00:00:00 | 100-200 | | 3 | Kaushik | 1560.00 | 2009-11-20 00:00:00 | 200-300 | | 3 | Kaushik | 1500.00 | 2009-10-08 00:00:00 | 1-100 | | 3 | Kaushik | 1500.00 | 2009-10-08 00:00:00 | 100-200 | | 3 | Kaushik | 1500.00 | 2009-10-08 00:00:00 | 200-300 | | 3 | Kaushik | 3000.00 | 2009-10-08 00:00:00 | 1-100 | | 3 | Kaushik | 3000.00 | 2009-10-08 00:00:00 | 100-200 | | 3 | Kaushik | 3000.00 | 2009-10-08 00:00:00 | 200-300 | | 4 | Chaitap | 2060.00 | 2008-05-20 00:00:00 | 1-100 | | 4 | Chaitap | 2060.00 | 2008-05-20 00:00:00 | 100-200 | | 4 | Chaitap | 2060.00 | 2008-05-20 00:00:00 | 200-300 | | 4 | Chaitap | 1560.00 | 2009-11-20 00:00:00 | 1-100 | | 4 | Chaitap | 1560.00 | 2009-11-20 00:00:00 | 100-200 | | 4 | Chaitap | 1560.00 | 2009-11-20 00:00:00 | 200-300 | | 4 | Chaitap | 1500.00 | 2009-10-08 00:00:00 | 1-100 | | 4 | Chaitap | 1500.00 | 2009-10-08 00:00:00 | 100-200 | | 4 | Chaitap | 1500.00 | 2009-10-08 00:00:00 | 200-300 | | 4 | Chaitap | 3000.00 | 2009-10-08 00:00:00 | 1-100 | | 4 | Chaitap | 3000.00 | 2009-10-08 00:00:00 | 100-200 | | 4 | Chaitap | 3000.00 | 2009-10-08 00:00:00 | 200-300 | | 5 | Hardik | 2060.00 | 2008-05-20 00:00:00 | 1-100 | | 5 | Hardik | 2060.00 | 2008-05-20 00:00:00 | 100-200 | | 5 | Hardik | 2060.00 | 2008-05-20 00:00:00 | 200-300 | | 5 | Hardik | 1560.00 | 2009-11-20 00:00:00 | 1-100 | | 5 | Hardik | 1560.00 | 2009-11-20 00:00:00 | 100-200 | | 5 | Hardik | 1560.00 | 2009-11-20 00:00:00 | 200-300 | | 5 | Hardik | 1500.00 | 2009-10-08 00:00:00 | 1-100 | | 5 | Hardik | 1500.00 | 2009-10-08 00:00:00 | 100-200 | | 5 | Hardik | 1500.00 | 2009-10-08 00:00:00 | 200-300 | | 5 | Hardik | 3000.00 | 2009-10-08 00:00:00 | 1-100 | | 5 | Hardik | 3000.00 | 2009-10-08 00:00:00 | 100-200 | | 5 | Hardik | 3000.00 | 2009-10-08 00:00:00 | 200-300 | | 6 | Komal | 2060.00 | 2008-05-20 00:00:00 | 1-100 | | 6 | Komal | 2060.00 | 2008-05-20 00:00:00 | 100-200 | | 6 | Komal | 2060.00 | 2008-05-20 00:00:00 | 200-300 | | 6 | Komal | 1560.00 | 2009-11-20 00:00:00 | 1-100 | | 6 | Komal | 1560.00 | 2009-11-20 00:00:00 | 100-200 | | 6 | Komal | 1560.00 | 2009-11-20 00:00:00 | 200-300 | | 6 | Komal | 1500.00 | 2009-10-08 00:00:00 | 1-100 | | 6 | Komal | 1500.00 | 2009-10-08 00:00:00 | 100-200 | | 6 | Komal | 1500.00 | 2009-10-08 00:00:00 | 200-300 | | 6 | Komal | 3000.00 | 2009-10-08 00:00:00 | 1-100 | | 6 | Komal | 3000.00 | 2009-10-08 00:00:00 | 100-200 | | 6 | Komal | 3000.00 | 2009-10-08 00:00:00 | 200-300 | | 7 | Muffy | 2060.00 | 2008-05-20 00:00:00 | 1-100 | | 7 | Muffy | 2060.00 | 2008-05-20 00:00:00 | 100-200 | | 7 | Muffy | 2060.00 | 2008-05-20 00:00:00 | 200-300 | | 7 | Muffy | 1560.00 | 2009-11-20 00:00:00 | 1-100 | | 7 | Muffy | 1560.00 | 2009-11-20 00:00:00 | 100-200 | | 7 | Muffy | 1560.00 | 2009-11-20 00:00:00 | 200-300 | | 7 | Muffy | 1500.00 | 2009-10-08 00:00:00 | 1-100 | | 7 | Muffy | 1500.00 | 2009-10-08 00:00:00 | 100-200 | | 7 | Muffy | 1500.00 | 2009-10-08 00:00:00 | 200-300 | | 7 | Muffy | 3000.00 | 2009-10-08 00:00:00 | 1-100 | | 7 | Muffy | 3000.00 | 2009-10-08 00:00:00 | 100-200 | | 7 | Muffy | 3000.00 | 2009-10-08 00:00:00 | 200-300 | +----+----------+---------+---------------------+-------------+
Previous Page Print Page Next Page Advertisements

SQL - Cross Join

Syntax

Example

Output

Joining Multiple Tables with Cross Join

Syntax

Example

Output

友情链接