MySQL CROSS JOIN

Resumo : neste tutorial, você aprenderá sobre a CROSS JOINclá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 JOINclá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 ne mlinhas respectivamente, o conjunto de resultados terá nxmlinhas.

Em outras palavras, a CROSS JOINcláusula retorna um produto cartesiano de linhas das tabelas unidas.

O seguinte ilustra a sintaxe da CROSS JOINcláusula que une duas tabelas t1e 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 JOINcláusula  LEFT JOINnão possui um predicado de junção. Em outras palavras, não possui a cláusula or .RIGHT JOINCROSS JOINONUSING

Se você adicionar uma WHEREcláusula, no caso tabela t1e t2tiver um relacionamento, CROSS JOINfunciona como a INNER JOINclá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 salesdbbanco 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 productscontém os dados mestre do produto que incluem ID do produto, nome do produto e preço de venda.
• A tabela storescontém as lojas onde os produtos são vendidos.
• A tabela salesconté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, iPade Macbook Proque são vendidos em duas lojas Northe 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 JOINcláusula.

Sexto, use a CROSS JOINclá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 IFNULLfunção para retornar 0 se a receita for NULL (caso a loja não tenha vendas).

Usando a CROSS JOINclá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 JOINcláusula para realizar uma junção cruzada.