Resumo : neste tutorial, você aprenderá sobre a CROSS JOIN
cláusula MySQL e como usá-la de forma mais eficaz.
Introdução à cláusula MySQL CROSS JOIN
Suponha que você junte duas tabelas usando a CROSS JOIN
cláusula. O conjunto de resultados incluirá todas as linhas de ambas as tabelas, onde cada linha é a combinação da linha da primeira tabela com a linha da segunda tabela. Em geral, se cada tabela tiver n
e m
linhas respectivamente, o conjunto de resultados terá nxm
linhas.
Em outras palavras, a CROSS JOIN
cláusula retorna um produto cartesiano de linhas das tabelas unidas.
O seguinte ilustra a sintaxe da CROSS JOIN
cláusula que une duas tabelas t1
e t2
:
SELECT select_list
FROM t1
CROSS JOIN t2;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Observe que diferente das cláusulas , e, a INNER JOIN
cláusula LEFT JOIN
não possui um predicado de junção. Em outras palavras, não possui a cláusula or .RIGHT JOIN
CROSS JOIN
ON
USING
Se você adicionar uma WHERE
cláusula, no caso tabela t1
e t2
tiver um relacionamento, CROSS JOIN
funciona como a INNER JOIN
cláusula mostrada na consulta a seguir:
SELECT select_list
FROM t1
CROSS JOIN t2
WHERE t1.id = t2.id;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Exemplos de cláusulas MySQL CROSS JOIN
Vejamos alguns exemplos para entender como funciona o cross join.
1) Exemplo de junção cruzada simples
Usaremos uma junção cruzada para criar um baralho de 52 cartas.
Primeiro, crie uma tabela que armazene os ternos:
CREATE TABLE suits (
suit_id INT,
suit_name VARCHAR(10)
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Segundo, crie uma tabela para armazenar classificações:
CREATE TABLE ranks (
rank_id INT,
rank_name VARCHAR(5)
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Terceiro, insira os dados na tabela de naipes e classificações:
INSERT INTO suits (suit_id, suit_name) VALUES
(1, 'Hearts'),
(2, 'Diamonds'),
(3, 'Clubs'),
(4, 'Spades');
INSERT INTO ranks (rank_id, rank_name) VALUES
(1, 'Ace'),
(2, '2'),
(3, '3'),
(4, '4'),
(5, '5'),
(6, '6'),
(7, '7'),
(8, '8'),
(9, '9'),
(10, '10'),
(11, 'Jack'),
(12, 'Queen'),
(13, 'King');
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Finalmente, use uma cruz para combinar os naipes e as classificações para criar um baralho de 52 cartas:
SELECT
suit_name,
rank_name
FROM
suits CROSS
JOIN ranks
ORDER BY
suit_name,
rank_name;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+-----------+-----------+
| suit_name | rank_name |
+-----------+-----------+
| Clubs | 10 |
| Clubs | 2 |
| Clubs | 3 |
| Clubs | 4 |
| Clubs | 5 |
| Clubs | 6 |
| Clubs | 7 |
| Clubs | 8 |
| Clubs | 9 |
| Clubs | Ace |
| Clubs | Jack |
| Clubs | King |
| Clubs | Queen |
| Diamonds | 10 |
| Diamonds | 2 |
| Diamonds | 3 |
| Diamonds | 4 |
| Diamonds | 5 |
| Diamonds | 6 |
| Diamonds | 7 |
| Diamonds | 8 |
| Diamonds | 9 |
| Diamonds | Ace |
| Diamonds | Jack |
| Diamonds | King |
| Diamonds | Queen |
| Hearts | 10 |
| Hearts | 2 |
| Hearts | 3 |
| Hearts | 4 |
| Hearts | 5 |
| Hearts | 6 |
| Hearts | 7 |
| Hearts | 8 |
| Hearts | 9 |
| Hearts | Ace |
| Hearts | Jack |
| Hearts | King |
| Hearts | Queen |
| Spades | 10 |
| Spades | 2 |
| Spades | 3 |
| Spades | 4 |
| Spades | 5 |
| Spades | 6 |
| Spades | 7 |
| Spades | 8 |
| Spades | 9 |
| Spades | Ace |
| Spades | Jack |
| Spades | King |
| Spades | Queen |
+-----------+-----------+
52 rows in set (0.00 sec)
Linguagem de código: texto simples ( texto simples )
Nesta consulta, usamos uma junção cruzada para combinar cada naipe da tabela de naipes com cada classificação da tabela de classificações, resultando em um produto cartesiano que emparelha cada naipe com cada classificação.
2) Um exemplo complexo de junção cruzada
Primeiro, crie um novo banco de dados salesdb
:
CREATE DATABASE IF NOT EXISTS salesdb;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Segundo, mude os dados atuais para o novo banco de dados salesdb
:
USE salesdb;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Terceiro, crie novas tabelas no salesdb
banco de dados:
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
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Aqui estão as descrições das tabelas:
- A tabela
products
contém os dados mestre do produto que incluem ID do produto, nome do produto e preço de venda. - A tabela
stores
contém as lojas onde os produtos são vendidos. - A tabela
sales
contém os produtos vendidos em uma determinada loja por quantidade e data.
Quarto, insira dados nas três tabelas.
Suponha que temos três produtos iPhone
, iPad
e Macbook Pro
que são vendidos em duas lojas North
e 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');
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Quinto, a seguinte declaração retorna o total de vendas de cada produto em cada loja:
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;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Agora, e se você quiser determinar qual loja não teve vendas de um determinado produto? A declaração mencionada anteriormente não é capaz de responder a esta questão.
Para resolver o problema, você pode usar a CROSS JOIN
cláusula.
Sexto, use a CROSS JOIN
cláusula para obter a combinação de todas as lojas e produtos:
SELECT
store_name, product_name
FROM
stores AS a
CROSS JOIN
products AS b;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A seguir, junte o resultado da consulta acima com uma consulta que retorne o total de vendas por loja e produto:
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;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Observe que a consulta utilizou a IFNULL
função para retornar 0 se a receita for NULL
(caso a loja não tenha vendas).
Usando a CROSS JOIN
cláusula desta forma, você pode responder a uma ampla gama de perguntas, por exemplo, encontrar a receita de vendas por vendedor, mês, mesmo que o vendedor não tenha vendas em um determinado mês.
Resumo
- Uma junção cruzada combina cada linha de uma tabela com cada linha de outra tabela, resultando em um produto cartesiano.
- Use a
CROSS JOIN
cláusula para realizar uma junção cruzada.