Junção MySQL

Resumo : neste tutorial, você aprenderá várias cláusulas de junção do MySQL na SELECTinstrução para consultar dados de duas tabelas.

Introdução às cláusulas de junção do MySQL

Um banco de dados relacional consiste em várias tabelas relacionadas interligadas por meio de colunas comuns, conhecidas como colunas de chave estrangeira . Por causa disso, os dados em cada tabela estão incompletos do ponto de vista comercial.

Por exemplo, no banco de dados de exemplo , temos as tabelas orderse  orderdetailsque estão vinculadas usando a orderNumbercoluna:

Para obter informações completas do pedido, você precisa consultar os dados das tabelas orderse   orderdetails.

É por isso que as junções entram em jogo.

Uma junção é um método de vincular dados entre uma ( auto-junção ) ou mais tabelas com base nos valores da coluna comum entre as tabelas.

MySQL suporta os seguintes tipos de junções:

1. Junção interna
2. Associação à esquerda
3. Juntar-se à direita
4. Junção cruzada

Para unir tabelas, você usa a cláusula cross join, inner join, left join ou right join. A cláusula join é usada na SELECTinstrução que aparece após a FROMcláusula.

Observe que o MySQL ainda não oferece suporte FULL OUTER JOIN.

Configurando tabelas de amostra

Primeiro, crie duas tabelas chamadas memberse committees:

CREATE TABLE members (
    member_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (member_id)
);

CREATE TABLE committees (
    committee_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (committee_id)
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Segundo, insira algumas linhas nas tabelas memberse committees:

INSERT INTO members(name)
VALUES('John'),('Jane'),('Mary'),('David'),('Amelia');

INSERT INTO committees(name)
VALUES('John'),('Mary'),('Amelia'),('Joe');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Terceiro, consulte os dados das tabelas memberse committees:

SELECT * FROM members;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

+-----------+--------+
| member_id | name   |
+-----------+--------+
|         1 | John   |
|         2 | Jane   |
|         3 | Mary   |
|         4 | David  |
|         5 | Amelia |
+-----------+--------+
5 rows in set (0.00 sec)Linguagem de código:  texto simples  ( texto simples )

SELECT * FROM committees;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

+--------------+--------+
| committee_id | name   |
+--------------+--------+
|            1 | John   |
|            2 | Mary   |
|            3 | Amelia |
|            4 | Joe    |
+--------------+--------+
4 rows in set (0.00 sec)Linguagem de código:  texto simples  ( texto simples )

Alguns membros são membros do comitê e outros não. Por outro lado, alguns membros da comissão estão na membersmesa, outros não.

Cláusula MySQL INNER JOIN

O seguinte mostra a sintaxe básica da cláusula inner join que une duas tabelas table_1e table_2:

SELECT column_list
FROM table_1
INNER JOIN table_2 ON join_condition;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A cláusula de junção interna une duas tabelas com base em uma condição conhecida como predicado de junção.

A cláusula inner join compara cada linha da primeira tabela com cada linha da segunda tabela.

Se os valores de ambas as linhas satisfizerem a condição de junção, a cláusula de junção interna cria uma nova linha cuja coluna contém todas as colunas das duas linhas de ambas as tabelas e inclui esta nova linha no conjunto de resultados. Em outras palavras, a cláusula inner join inclui apenas linhas correspondentes de ambas as tabelas.

Se a condição de junção usar o operador de igualdade ( =) e os nomes das colunas em ambas as tabelas usadas para correspondência forem iguais, você poderá usar a USINGcláusula:

SELECT column_list
FROM table_1
INNER JOIN table_2 USING (column_name);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A declaração a seguir usa uma cláusula de junção interna para encontrar membros que também são membros do comitê:

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
INNER JOIN committees c ON c.name = m.name;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

+-----------+--------+--------------+-----------+
| member_id | member | committee_id | committee |
+-----------+--------+--------------+-----------+
|         1 | John   |            1 | John      |
|         3 | Mary   |            2 | Mary      |
|         5 | Amelia |            3 | Amelia    |
+-----------+--------+--------------+-----------+
3 rows in set (0.00 sec)Linguagem de código:  texto simples  ( texto simples )

Neste exemplo, a cláusula de junção interna usa os valores nas namecolunas de ambas as tabelas memberse committeespara fazer a correspondência.

O diagrama de Venn a seguir ilustra a junção interna:

Como ambas as tabelas usam a mesma coluna para correspondência, você pode usar a USINGcláusula conforme mostrado na consulta a seguir:

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
INNER JOIN committees c USING(name);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Cláusula LEFT JOIN do MySQL

Semelhante a uma junção interna, uma junção à esquerda também requer um predicado de junção. Ao unir duas tabelas usando uma junção à esquerda, são introduzidos os conceitos de tabelas à esquerda e à direita.

A junção esquerda seleciona dados começando na tabela esquerda. Para cada linha da tabela esquerda, a junção esquerda é comparada com cada linha da tabela direita.

Se os valores nas duas linhas satisfizerem a condição de junção, a cláusula left join cria uma nova linha cujas colunas contêm todas as colunas das linhas em ambas as tabelas e inclui esta linha no conjunto de resultados.

Se os valores nas duas linhas não corresponderem, a cláusula left join ainda criará uma nova linha cujas colunas contêm colunas da linha na tabela esquerda e NULLpara colunas da linha na tabela direita.

Em outras palavras, a junção esquerda seleciona todos os dados da tabela esquerda, independentemente de existirem linhas correspondentes na tabela direita ou não.

Caso não haja linhas correspondentes da tabela direita encontradas, a junção esquerda usa NULLs para colunas da linha da tabela direita no conjunto de resultados.

Aqui está a sintaxe básica de uma cláusula left join que une duas tabelas:

SELECT column_list 
FROM table_1 
LEFT JOIN table_2 ON join_condition;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A junção esquerda também suporta a USINGcláusula se a coluna usada para correspondência em ambas as tabelas for a mesma:

SELECT column_list 
FROM table_1 
LEFT JOIN table_2 USING (column_name);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O exemplo a seguir usa uma cláusula left join para unir o memberscom a committeestabela:

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
LEFT JOIN committees c USING(name);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

+-----------+--------+--------------+-----------+
| member_id | member | committee_id | committee |
+-----------+--------+--------------+-----------+
|         1 | John   |            1 | John      |
|         2 | Jane   |         NULL | NULL      |
|         3 | Mary   |            2 | Mary      |
|         4 | David  |         NULL | NULL      |
|         5 | Amelia |            3 | Amelia    |
+-----------+--------+--------------+-----------+
5 rows in set (0.00 sec)Linguagem de código:  texto simples  ( texto simples )

O diagrama de Venn a seguir ilustra a junção à esquerda:

Esta instrução usa a cláusula left join com a USINGsintaxe:

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
LEFT JOIN committees c USING(name);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Para encontrar membros que não são membros do comitê, adicione uma WHEREcláusula e IS NULLum operador como segue:

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
LEFT JOIN committees c USING(name)
WHERE c.committee_id IS NULL;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

+-----------+--------+--------------+-----------+
| member_id | member | committee_id | committee |
+-----------+--------+--------------+-----------+
|         2 | Jane   |         NULL | NULL      |
|         4 | David  |         NULL | NULL      |
+-----------+--------+--------------+-----------+
2 rows in set (0.00 sec)Linguagem de código:  texto simples  ( texto simples )

Geralmente, esse padrão de consulta pode encontrar linhas na tabela esquerda que não possuem linhas correspondentes na tabela direita.

Este diagrama de Venn ilustra como usar a junção esquerda para selecionar linhas que existem apenas na tabela esquerda:

Cláusula MySQL RIGHT JOIN

A cláusula de junção à direita é semelhante à cláusula de junção à esquerda, exceto que o tratamento das tabelas esquerda e direita é invertido. A junção direita começa a selecionar dados da tabela direita em vez da tabela esquerda.

A cláusula de junção direita seleciona todas as linhas da tabela direita e corresponde às linhas da tabela esquerda. Se uma linha da tabela da direita não tiver linhas correspondentes da tabela da esquerda, a coluna da tabela da esquerda terá NULLno conjunto de resultados final.

Aqui está a sintaxe da junção correta:

SELECT column_list 
FROM table_1 
RIGHT JOIN table_2 ON join_condition;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Semelhante à cláusula left join, a cláusula right também suporta a USINGsintaxe:

SELECT column_list 
FROM table_1 
RIGHT JOIN table_2 USING (column_name);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Para encontrar linhas na tabela da direita que não possuem linhas correspondentes na tabela da esquerda, você também usa uma WHEREcláusula com o IS NULLoperador:

SELECT column_list 
FROM table_1 
RIGHT JOIN table_2 USING (column_name)
WHERE column_table_1 IS NULL;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Esta instrução usa a junção correta para unir as tabelas memberse :committees

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
RIGHT JOIN committees c on c.name = m.name;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

+-----------+--------+--------------+-----------+
| member_id | member | committee_id | committee |
+-----------+--------+--------------+-----------+
|         1 | John   |            1 | John      |
|         3 | Mary   |            2 | Mary      |
|         5 | Amelia |            3 | Amelia    |
|      NULL | NULL   |            4 | Joe       |
+-----------+--------+--------------+-----------+
4 rows in set (0.00 sec)Linguagem de código:  texto simples  ( texto simples )

Este diagrama de Venn ilustra a junção correta:

A instrução a seguir usa a cláusula de junção direita com a USINGsintaxe:

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
RIGHT JOIN committees c USING(name);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Para encontrar os membros do comitê que não estão na membersmesa, use esta consulta:

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
RIGHT JOIN committees c USING(name)
WHERE m.member_id IS NULL;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

+-----------+--------+--------------+-----------+
| member_id | member | committee_id | committee |
+-----------+--------+--------------+-----------+
|      NULL | NULL   |            4 | Joe       |
+-----------+--------+--------------+-----------+
1 row in set (0.00 sec)Linguagem de código:  texto simples  ( texto simples )

Este diagrama de Venn ilustra como usar a junção direita para selecionar dados que existem apenas na tabela certa:

Cláusula MySQL CROSS JOIN

Ao contrário da junção interna , junção esquerda e junção direita , a cláusula cross join não possui uma condição de junção.

A junção cruzada cria um produto cartesiano de linhas das tabelas unidas. A junção cruzada combina cada linha da primeira tabela com cada linha da tabela direita para formar o conjunto de resultados.

Suponha que a primeira tabela tenha nlinhas e a segunda tabela tenha mlinhas. A junção cruzada que une as tabelas retornará nxmlinhas.

O seguinte mostra a sintaxe da cláusula cross-join:

SELECT select_list
FROM table_1
CROSS JOIN table_2;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Este exemplo usa a cláusula cross join para unir as memberstabelas committees:

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
CROSS JOIN committees c;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

+-----------+--------+--------------+-----------+
| member_id | member | committee_id | committee |
+-----------+--------+--------------+-----------+
|         1 | John   |            4 | Joe       |
|         1 | John   |            3 | Amelia    |
|         1 | John   |            2 | Mary      |
|         1 | John   |            1 | John      |
|         2 | Jane   |            4 | Joe       |
|         2 | Jane   |            3 | Amelia    |
|         2 | Jane   |            2 | Mary      |
|         2 | Jane   |            1 | John      |
|         3 | Mary   |            4 | Joe       |
|         3 | Mary   |            3 | Amelia    |
|         3 | Mary   |            2 | Mary      |
|         3 | Mary   |            1 | John      |
|         4 | David  |            4 | Joe       |
|         4 | David  |            3 | Amelia    |
|         4 | David  |            2 | Mary      |
|         4 | David  |            1 | John      |
|         5 | Amelia |            4 | Joe       |
|         5 | Amelia |            3 | Amelia    |
|         5 | Amelia |            2 | Mary      |
|         5 | Amelia |            1 | John      |
+-----------+--------+--------------+-----------+
20 rows in set (0.00 sec)Linguagem de código:  texto simples  ( texto simples )

A junção cruzada é útil para gerar dados de planejamento. Por exemplo, você pode realizar o planejamento de vendas usando a junção cruzada de clientes, produtos e anos.

Neste tutorial, você aprendeu várias instruções de junção do MySQL, incluindo cross join, inner join, left join e right join, para consultar dados de duas tabelas.