MySQL系列之什么是CROSS JOIN！

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的过程，这个过程其实就是笛卡尔连接查询

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

ok，看了一下，发现没卖出的商品是没统计出来的，所以不太符合业务需求，业务是要统计所有的商店商品，所以可以用cross join笛卡尔连接，得出所有的商店商品组合数据

笛卡尔查询组合数据sql：

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

前面统计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 ;