MySQL应用之CROSS JOIN用法简介教程

小咪咪 2021-08-13 16:27 575阅读 0赞

### 文章目录 ###

*   *  1. cross join简介
     *  2. cross join用法

本博客例子自两篇博客的：

*  http://www.mysqltutorial.org/mysql-cross-join/
 *  https://www.w3resource.com/mysql/advance-query-in-mysql/mysql-cross-join.php

ps：不按照原文进行翻译，整理两篇博客，根据自己的实践进行验证，本博客可以作为mysql cross join的学习参考手册

## 1. cross join简介 ##

MySQL cross join是mysql中的一种连接方式，区别于内连接和外连接，对于cross join连接来说，其实使用的就是笛卡尔连接。在MySQL中，当CROSS JOIN不使用WHERE子句时，CROSS JOIN产生了一个结果集，该结果集是两个关联表的行的乘积。通常，如果每个表分别具有n和m行，则结果集将具有n\*m行

引用https://www.w3resource.com/mysql/advance-query-in-mysql/mysql-cross-join.php的图片，如图演示了cross join的过程，这个过程其实就是笛卡尔连接查询  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]

## 2. cross join用法 ##

cross join用法：

SELECT * FROM t1
    CROSS JOIN t2;

> 注意：cross join的时候是不需要on或者using关键字的，这个是区别于inner join和join的

如果WHERE在条件表中添加一个子句t1并t2具有关系，则CROSS JOIN该INNER JOIN子句的工作方式类似于以下查询中所示：

SELECT * FROM t1
    CROSS JOIN t2
    WHERE t1.id = t2.id;

ok，再列举一下cross join表作为衍生表的例子

SELECT * 
    FROM table111
    LEFT JOIN(table112 CROSS JOIN table113)
    ON table111.id=table113.id;

ok，介绍了cross join的简单用法，现在拿http://www.mysqltutorial.org/mysql-cross-join/的例子来介绍：

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

CREATE DATABASE IF NOT EXISTS salesdb;

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

USE testdb;

在salesdb数据库中创建新表：

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

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

将数据插入三个表中。假设我们有三个产品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');

ok，业务场景：现在要统计每个商店每种商品总共营业额是多少钱？

很显然，用SUM(quantity \* price)，再group by一下就可以，这个sql很好写

SELECT 
      sto.`store_name`,
      pro.`product_name`,
      SUM(quantity * price) AS revenue
    FROM
      sales sal 
      INNER JOIN stores sto 
        ON sto.`id` = sal.`store_id`
      INNER JOIN products pro
        ON sal.`product_id` = pro.`id`
      GROUP BY sto.`store_name`,pro.`product_name`;

![在这里插入图片描述][20191031195105578.png]  
ok，看了一下，发现没卖出的商品是没统计出来的，所以不太符合业务需求，业务是要统计所有的商店商品，所以可以用cross join笛卡尔连接，得出所有的商店商品组合数据

笛卡尔查询组合数据sql：

SELECT 
      a.`store_name`,
      b.product_name
    from stores cross join products

![在这里插入图片描述][20191031195122636.png]

前面统计sql已经有了，所以将组合数据SQL和统计数据的SQL进行关联：

SELECT 
      a.`store_name`,
      b.product_name,
      IFNULL(c.revenue, 0) AS revenue 
    FROM
      stores a 
      CROSS JOIN products b 
      LEFT JOIN 
        (SELECT 
          sto.`id` AS store_id,
          pro.`id` AS product_id,
          sto.`store_name`,
          pro.`product_name`,
          SUM(quantity * price) AS revenue 
        FROM
          sales sal 
          INNER JOIN stores sto 
            ON sto.`id` = sal.`store_id` 
          INNER JOIN products pro 
            ON sal.`product_id` = pro.`id` 
        GROUP BY sto.`store_name`,
          pro.`product_name`) c 
        ON a.id = c.store_id 
        AND b.id = c.product_id 
    ORDER BY a.store_name ;

![在这里插入图片描述][20191031195130688.png]

请注意，IFNULL如果收入为NULL （在商店没有销售的情况下），查询使用该函数返回0。

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

ok，本博客是翻译两篇英文博客的：

*  http://www.mysqltutorial.org/mysql-cross-join/
 *  https://www.w3resource.com/mysql/advance-query-in-mysql/mysql-cross-join.php

ok，本博客内容翻译自两篇英文博客，不过本博客进行一定修整，将两篇博客内容进行理解整合成这篇中文博客，原因是这两篇博客的例子还是不错的，举出了cross join的常用使用场景，当然除了两篇博客提出的用法，cross join因为其笛卡尔连接的特性，还可以用于批量写数据，对应批量的写法，可以参考我之前的MySQL博客，本博客性质属于翻译的，所以转载请注明出处

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]: /images/20210813/5001d895cdf0427a8d85c8e49e666915.png
[20191031195105578.png]: /images/20210813/88513f7c039e4a289b34e61edb3c1a79.png
[20191031195122636.png]: /images/20210813/c2fec71043374f1ab6ff90925af61864.png
[20191031195130688.png]: /images/20210813/1dd3d06eedd4453fb428eaf873ac870b.png