Resumo : neste tutorial, você aprenderá várias cláusulas de junção do MySQL na SELECT
instruçã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 orders
e orderdetails
que estão vinculadas usando a orderNumber
coluna:
Para obter informações completas do pedido, você precisa consultar os dados das tabelas orders
e 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:
Para unir tabelas, você usa a cláusula cross join, inner join, left join ou right join. A cláusula join é usada na SELECT
instrução que aparece após a FROM
cláusula.
Observe que o MySQL ainda não oferece suporte FULL OUTER JOIN
.
Configurando tabelas de amostra
Primeiro, crie duas tabelas chamadas members
e 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 members
e 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 members
e 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 members
mesa, 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_1
e 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 USING
clá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 name
colunas de ambas as tabelas members
e committees
para 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 USING
clá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 NULL
para 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 USING
clá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 members
com a committees
tabela:
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 USING
sintaxe:
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 WHERE
cláusula e IS NULL
um 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á NULL
no 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 USING
sintaxe:
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 WHERE
cláusula com o IS NULL
operador:
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 members
e :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 USING
sintaxe:
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 members
mesa, 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 n
linhas e a segunda tabela tenha m
linhas. A junção cruzada que une as tabelas retornará nxm
linhas.
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 members
tabelas 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.