A2-02-16.DML-A Practical Use of MySQL CROSS JOIN Clause
转载自：http://www.mysqltutorial.org/mysql-cross-join/
A Practical Use of MySQL CROSS JOIN Clause
Summary: in this tutorial, you will learn about the MySQL CROSS JOIN clause and how to apply it to answer some interesting data questions.
Introduction to MySQL CROSS JOIN clause
The CROSS JOIN clause returns the Cartesian product of rows from the joined tables.
Suppose you join two tables using CROSS JOIN. The result set will include all rows from both tables, where each row in the result set is the combination of the row in the first table with the row in the second table. This situation happens when you have no relationship between the joined tables.
The danger thing is that if each table has 1,000 rows, you will get 1,000 x 1,000 = 1,000,000 rows in the result set, which is huge.
The following illustrates the syntax of the CROSS JOIN clause that joins two tables T1 and T2:
|
1
2
3
4
5
6
|
SELECT
*
FROM
T1
CROSS JOIN
T2;
|
Note that different from the INNER JOIN or LEFT JOIN clause, the CROSS JOIN clause does not have the join conditions.
If you add a WHERE clause, in case T1 and T2 has a relationship, the CROSS JOIN works like the INNER JOIN clause as shown in the following query:
|
1
2
3
4
5
6
7
8
|
SELECT
*
FROM
T1
CROSS JOIN
T2
WHERE
T1.id = T2.id;
|
MySQL CROSS JOIN clause example
We will use the following tables to demonstrate how the CROSS JOIN works.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
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
);
|
There are three tables involved:
- The
productstable contains the products master data that includes product id, product name, and sales price. - The
storestable contains the stores where the products are sold. - The
salestable contains the products that sold in a particular store by quantity and date.
Suppose we have three products iPhone, iPad and Macbook Pro which are sold in two stores Northand South.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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');
|
To get the total sales for each store and for each product, you calculate the sales and group them by store and product as follows:
|
1
2
3
4
5
6
7
8
9
10
11
|
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;
|
Now, what if you want to know also which store had no sales of a specific product. The query above could not answer this question.
To solve the problem, you need to use the CROSS JOIN clause.
First, you use the CROSS JOIN clause to get the combination of all stores and products:
|
1
2
3
4
5
6
|
SELECT
store_name, product_name
FROM
stores AS a
CROSS JOIN
products AS b;
|
Next, you join the result of the query above with the query that returns the total of sales by store and by product. The following query illustrates the idea（自己没有测试成功，可能是SQL模式的原因）:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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;
|
Note that the query used the IFNULL function to return 0 if the revenue is NULL (in case the store had no sales).
By using the CROSS JOIN clause this way, you can answer a wide range of questions e.g., find the sales revenue by salesman by month even if a salesman had no sales in a particular month.
