MySQL CROSS JOIN

摘要：在本教程中，您将了解 MySQL CROSS JOIN子句以及如何更有效地使用它。

MySQL CROSS JOIN子句简介

假设您使用CROSS JOIN子句连接两个表。结果集将包括两个表中的所有行，其中每一行都是第一个表中的行与第二个表中的行的组合。一般来说，如果每个表分别有n和m行，则结果集将有nxm行。

换句话说， CROSS JOIN子句返回连接表中行的笛卡尔积。

下面说明了连接两个表t1和t2的CROSS JOIN子句的语法：

SELECT * FROM t1
CROSS JOIN t2;Code language: SQL (Structured Query Language) (sql)

请注意，与INNER JOIN 、 LEFT JOIN和RIGHT JOIN子句不同， CROSS JOIN子句没有连接谓词。换句话说，它没有ON或USING子句。

如果添加WHERE子句，如果表t1和t2存在关系，则CROSS JOIN工作方式类似于INNER JOIN子句，如以下查询所示：

SELECT * FROM t1
CROSS JOIN t2
WHERE t1.id = t2.id;
Code language: SQL (Structured Query Language) (sql)

MySQL CROSS JOIN 子句示例

让我们设置一些表来演示CROSS JOIN子句。

设置样本表

首先，创建一个新的数据库salesdb ：

CREATE DATABASE IF NOT EXISTS salesdb;Code language: SQL (Structured Query Language) (sql)

其次，将当前数据切换到新数据库salesdb ：

USE salesdb;Code language: SQL (Structured Query Language) (sql)

第三，在salesdb数据库中创建新表：

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    price DECIMAL(13,2 )
);

CREATE TABLE stores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    store_name VARCHAR(100)
);

CREATE TABLE sales (
    product_id INT,
    store_id INT,
    quantity DECIMAL(13 , 2 ) NOT NULL,
    sales_date DATE NOT NULL,
    PRIMARY KEY (product_id , store_id),
    FOREIGN KEY (product_id)
        REFERENCES products (id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (store_id)
        REFERENCES stores (id)
        ON DELETE CASCADE ON UPDATE CASCADE
);Code language: SQL (Structured Query Language) (sql)

以下是这三个表的说明：

• products表包含产品主数据，其中包括产品 ID、产品名称和销售价格。
• 表stores包含销售产品的商店。
• 表sales包含按数量和日期在特定商店中销售的产品。

最后，将数据插入到三个表中。假设我们有iPhone 、 iPad和Macbook Pro三种产品，分别在North和South两个商店销售。

INSERT INTO products(product_name, price)
VALUES('iPhone', 699),
      ('iPad',599),
      ('Macbook Pro',1299);

INSERT INTO stores(store_name)
VALUES('North'),
      ('South');

INSERT INTO sales(store_id,product_id,quantity,sales_date)
VALUES(1,1,20,'2017-01-02'),
      (1,2,15,'2017-01-05'),
      (1,3,25,'2017-01-05'),
      (2,1,30,'2017-01-02'),
      (2,2,35,'2017-01-05');
Code language: SQL (Structured Query Language) (sql)

MySQL 交叉连接示例

此语句返回每个商店和产品的总销售额，您可以计算销售额并按商店和产品对它们进行分组，如下所示：

SELECT 
    store_name,
    product_name,
    SUM(quantity * price) AS revenue
FROM
    sales
        INNER JOIN
    products ON products.id = sales.product_id
        INNER JOIN
    stores ON stores.id = sales.store_id
GROUP BY store_name , product_name; 
Code language: SQL (Structured Query Language) (sql)

现在，如果您还想知道哪家商店没有销售特定产品，该怎么办？上面的查询无法回答这个问题。

为了解决这个问题，需要使用CROSS JOIN子句。

首先，使用CROSS JOIN子句获取所有商店和产品的组合：

SELECT 
    store_name, product_name
FROM
    stores AS a
        CROSS JOIN
    products AS b;Code language: SQL (Structured Query Language) (sql)

接下来，将上述查询的结果与返回按商店和产品划分的总销售额的查询连接起来。以下查询说明了这个想法：

SELECT 
    b.store_name,
    a.product_name,
    IFNULL(c.revenue, 0) AS revenue
FROM
    products AS a
        CROSS JOIN
    stores AS b
        LEFT JOIN
    (SELECT 
        stores.id AS store_id,
        products.id AS product_id,
        store_name,
            product_name,
            ROUND(SUM(quantity * price), 0) AS revenue
    FROM
        sales
    INNER JOIN products ON products.id = sales.product_id
    INNER JOIN stores ON stores.id = sales.store_id
    GROUP BY stores.id, products.id, store_name , product_name) AS c ON c.store_id = b.id
        AND c.product_id= a.id
ORDER BY b.store_name;
Code language: SQL (Structured Query Language) (sql)

请注意，如果收入为NULL （如果商店没有销售额），则查询使用IFNULL函数返回 0。

通过以这种方式使用CROSS JOIN子句，您可以回答各种问题，例如，按销售员、月份查找销售收入，即使销售员在特定月份没有销售额。