MySQL CROSS JOIN 子句

MySQL CROSS JOIN子句简介

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

假设使用连接两个表的CROSS JOIN，结果集将包括两个表中的所有行，其中结果集中的每一行都是第一个表中的行与第二个表中的行的组合，无论两个表之间有没有关系。

注意：如果每个表有1,000行，结果集中将获得1,000 x 1,000 = 1,000,000行，这是巨大的。

语法：

SELECT 
    *
FROM
    T1
        CROSS JOIN
    T2; 

如果添加一个WHERE子句，并且T1和T2有关系，那么CROSS JOIN就像INNER JOIN查询一样：

SELECT 
    *
FROM
    T1
        CROSS JOIN
    T2
WHERE
    T1.id = T2.id; 

MySQL CROSS JOIN子句实例

我们将使用以下三个表来演示CROSS JOIN是如何工作的：

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

假设我们有三个产品iPhone，iPad并且Macbook Pro，其在两个商店North和South出售。

+------+----------------+-----------+
|  id  |  product_name  |   price   |
+------+----------------+-----------+
|   1  |     iPhone     |    699    |
|   2  |     iPad       |    599    |
|   3  |   Macbook Pro  |    1299   |
+------+----------------+-----------+
 
+------+----------------+
|  id  |   store_name   |
+------+----------------+
|   1  |     North      |
|   2  |     South      |
+------+----------------+

+------------+--------------+------------+--------------+
|  store_id  |  product_id  |  quantity  |  sales_date  |
+------------+--------------+------------+--------------+
|     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' | 
+------------+--------------+------------+--------------+

要获得每个商店和每个产品的总销售额，我们需要计算销售额并按商店和产品对其进行分组，如下所示：

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; 

运行结果：

+------------+--------------+------------+
| store_name | product_name | revenue    |
+------------+--------------+------------+
| North      | iPad         |  8985.0000 |
| North      | iPhone       | 13980.0000 |
| North      | Macbook Pro  | 32475.0000 |
| South      | iPad         | 20965.0000 |
| South      | iPhone       | 20970.0000 |
+------------+--------------+------------+

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

要解决此问题，需要使用CROSS JOIN子句。

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

SELECT 
    store_name, product_name
FROM
    stores AS a
        CROSS JOIN
    products AS b;

运行结果：

+------------+--------------+
| store_name | product_name |
+------------+--------------+
| North      | iPhone       |
| South      | iPhone       |
| North      | iPad         |
| South      | iPad         |
| North      | Macbook Pro  |
| South      | Macbook Pro  |
+------------+--------------+

接下来，将上面查询的结果与按商店和按产品返回销售总额的查询相结合：

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 store_name , product_name) AS c ON c.store_id = b.id
        AND c.product_id= a.id
ORDER BY b.store_name; 

运行结果：

+------------+--------------+---------+
| store_name | product_name | revenue |
+------------+--------------+---------+
| North      | iPhone       |   13980 |
| North      | Macbook Pro  |   32475 |
| North      | iPad         |    8985 |
| South      | Macbook Pro  |       0 |
| South      | iPad         |   20965 |
| South      | iPhone       |   20970 |
+------------+--------------+---------+

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

通过CROSS JOIN查询方式，可以回答各种各样的问题，例如，即使销售人员在特定月份没有销售，也可以按月查找销售员的销售收入。