Tutorial do MySQL JOINS: INNER, OUTER, LEFT, RIGHT, CROSS

Anonim

O que são JOINS?

As junções ajudam a recuperar dados de duas ou mais tabelas do banco de dados. As tabelas são mutuamente relacionadas usando chaves primárias e estrangeiras.

Nota: JOIN é o tópico mais mal compreendido entre os analistas de SQL. Para fins de simplicidade e facilidade de compreensão, usaremos um novo banco de dados para praticar a amostra. Como mostrado abaixo

eu ia primeiro nome sobrenome movie_id
1 Adão Smith 1
2 Ravi Kumar 2
3 Susan Davidson 5
4 Jenny Adrianna 8
6 Lee Pong 10
eu ia título categoria
1 CREDO DO ASSASSINO: EMBERS Animações
2 Real Steel (2012) Animações
3 Alvin e os Esquilos Animações
4 As Aventuras de Tin Tin Animações
5 Seguro (2012) Açao
6 Casa Segura (2012) Açao
7 GIA 18+
8 Prazo de 2009 18+
9 The Dirty Picture 18+
10 Marley e eu Romance

Tipos de junções

Cross JOIN

Cross JOIN é a forma mais simples de JOINs que combina cada linha de uma tabela de banco de dados com todas as linhas de outra.

Em outras palavras, ele nos dá combinações de cada linha da primeira tabela com todos os registros da segunda tabela.

Suponha que desejemos obter todos os registros de membros de todos os registros do filme, podemos usar o script mostrado abaixo para obter os resultados desejados.

SELECT * FROM `movies` CROSS JOIN `members`

Executar o script acima no ambiente de trabalho MySQL nos dá os seguintes resultados.

 

 

 

 id title   id first_name last_name movie_id 


 

 1 ASSASSIN'S CREED: EMBERS Animations 1 Adam Smith 1 


 

 1 ASSASSIN'S CREED: EMBERS Animations 2 Ravi Kumar 2 


 

 1 ASSASSIN'S CREED: EMBERS Animations 3 Susan Davidson 5 


 

 1 ASSASSIN'S CREED: EMBERS Animations 4 Jenny Adrianna 8 


 

 1 ASSASSIN'S CREED: EMBERS Animations 6 Lee Pong 10 


 

 2 Real Steel(2012) Animations 1 Adam Smith 1 


 

 2 Real Steel(2012) Animations 2 Ravi Kumar 2 


 

 2 Real Steel(2012) Animations 3 Susan Davidson 5 


 

 2 Real Steel(2012) Animations 4 Jenny Adrianna 8 


 

 2 Real Steel(2012) Animations 6 Lee Pong 10 


 

 3 Alvin and the Chipmunks Animations 1 Adam Smith 1 


 

 3 Alvin and the Chipmunks Animations 2 Ravi Kumar 2 


 

 3 Alvin and the Chipmunks Animations 3 Susan Davidson 5 


 

 3 Alvin and the Chipmunks Animations 4 Jenny Adrianna 8 


 

 3 Alvin and the Chipmunks Animations 6 Lee Pong 10 


 

 4 The Adventures of Tin Tin Animations 1 Adam Smith 1 


 

 4 The Adventures of Tin Tin Animations 2 Ravi Kumar 2 


 

 4 The Adventures of Tin Tin Animations 3 Susan Davidson 5 


 

 4 The Adventures of Tin Tin Animations 4 Jenny Adrianna 8 


 

 4 The Adventures of Tin Tin Animations 6 Lee Pong 10 


 

 5 Safe (2012) Action 1 Adam Smith 1 


 

 5 Safe (2012) Action 2 Ravi Kumar 2 


 

 5 Safe (2012) Action 3 Susan Davidson 5 


 

 5 Safe (2012) Action 4 Jenny Adrianna 8 


 

 5 Safe (2012) Action 6 Lee Pong 10 


 

 6 Safe House(2012) Action 1 Adam Smith 1 


 

 6 Safe House(2012) Action 2 Ravi Kumar 2 


 

 6 Safe House(2012) Action 3 Susan Davidson 5 


 

 6 Safe House(2012) Action 4 Jenny Adrianna 8 


 

 6 Safe House(2012) Action 6 Lee Pong 10 


 

 7 GIA 18+ 1 Adam Smith 1 


 

 7 GIA 18+ 2 Ravi Kumar 2 


 

 7 GIA 18+ 3 Susan Davidson 5 


 

 7 GIA 18+ 4 Jenny Adrianna 8 


 

 7 GIA 18+ 6 Lee Pong 10 


 

 8 Deadline(2009) 18+ 1 Adam Smith 1 


 

 8 Deadline(2009) 18+ 2 Ravi Kumar 2 


 

 8 Deadline(2009) 18+ 3 Susan Davidson 5 


 

 8 Deadline(2009) 18+ 4 Jenny Adrianna 8 


 

 8 Deadline(2009) 18+ 6 Lee Pong 10 


 

 9 The Dirty Picture 18+ 1 Adam Smith 1 


 

 9 The Dirty Picture 18+ 2 Ravi Kumar 2 


 

 9 The Dirty Picture 18+ 3 Susan Davidson 5 


 

 9 The Dirty Picture 18+ 4 Jenny Adrianna 8 


 

 9 The Dirty Picture 18+ 6 Lee Pong 10 


 

 10 Marley and me Romance 1 Adam Smith 1 


 

 10 Marley and me Romance 2 Ravi Kumar 2 


 

 10 Marley and me Romance 3 Susan Davidson 5 


 

 10 Marley and me Romance 4 Jenny Adrianna 8 


 

 10 Marley and me Romance 6 Lee Pong 10

JUNÇÃO INTERNA

O JOIN interno é usado para retornar linhas de ambas as tabelas que satisfaçam a condição fornecida.

Suponha que você deseja obter a lista de membros que alugaram filmes junto com os títulos dos filmes alugados por eles. Você pode simplesmente usar um INNER JOIN para isso, que retorna linhas de ambas as tabelas que satisfazem as condições fornecidas.

SELECT members.`first_name` , members.`last_name` , movies.`title`FROM members ,moviesWHERE movies.`id` = members.`movie_id`

Executar o script acima dá

 

 

 

 first_name last_name title 


 

 Adam Smith ASSASSIN'S CREED: EMBERS 


 

 Ravi Kumar Real Steel(2012) 


 

 Susan Davidson Safe (2012) 


 

 Jenny Adrianna Deadline(2009) 


 

 Lee Pong Marley and me

Observe que o script de resultados acima também pode ser escrito da seguinte maneira para obter os mesmos resultados.

SELECT A.`first_name` , A.`last_name` , B.`title`FROM `members`AS AINNER JOIN `movies` AS BON B.`id` = A.`movie_id`

JOINs externos

Os JOINs externos do MySQL retornam todos os registros correspondentes de ambas as tabelas.

Ele pode detectar registros sem correspondência na tabela associada. Ele retorna valores NULL para registros da tabela associada se nenhuma correspondência for encontrada.

Parece confuso? Vejamos um exemplo -

ASSOCIAÇÃO À ESQUERDA

Suponha que agora você deseja obter os títulos de todos os filmes junto com os nomes dos membros que os alugaram. É claro que alguns filmes não estão sendo alugados por ninguém. Podemos simplesmente usar LEFT JOIN para esse propósito.

O LEFT JOIN retorna todas as linhas da tabela à esquerda, mesmo que nenhuma linha correspondente tenha sido encontrada na tabela à direita. Onde nenhuma correspondência foi encontrada na tabela à direita, NULL é retornado.

SELECT A.`title` , B.`first_name` , B.`last_name`FROM `movies` AS ALEFT JOIN `members` AS BON B.`movie_id` = A.`id`

A execução do script acima no ambiente de trabalho MySQL dá. Você pode ver que no resultado retornado que está listado abaixo para filmes que não são alugados, os campos de nome de membro têm valores NULL. Isso significa que nenhum membro correspondente encontrado na tabela de membros para aquele filme em particular.

 

 

 

 title first_name last_name 


 

 ASSASSIN'S CREED: EMBERS Adam Smith 


 

 Real Steel(2012) Ravi Kumar 


 

 Safe (2012) Susan Davidson 


 

 Deadline(2009) Jenny Adrianna 


 

 Marley and me Lee Pong 


 

 Alvin and the Chipmunks NULL NULL 


 

 The Adventures of Tin Tin NULL NULL 


 

 Safe House(2012) NULL NULL 


 

 GIA NULL NULL 


 

 The Dirty Picture NULL NULL 


 

 


Note: Null is returned for non-matching rows on right

JUNTAR À DIREITA

RIGHT JOIN é obviamente o oposto de LEFT JOIN. O RIGHT JOIN retorna todas as colunas da tabela à direita, mesmo que nenhuma linha correspondente tenha sido encontrada na tabela à esquerda. Onde nenhuma correspondência for encontrada na tabela à esquerda, NULL é retornado.

Em nosso exemplo, vamos supor que você precise obter os nomes dos membros e os filmes alugados por eles. Agora temos um novo membro que ainda não alugou nenhum filme

SELECT A.`first_name` , A.`last_name`, B.`title`FROM `members` AS ARIGHT JOIN `movies` AS BON B.`id` = A.`movie_id`

A execução do script acima no ambiente de trabalho MySQL fornece os seguintes resultados.

 

 

 

 first_name last_name title 


 

 Adam Smith ASSASSIN'S CREED: EMBERS 


 

 Ravi Kumar Real Steel(2012) 


 

 Susan Davidson Safe (2012) 


 

 Jenny Adrianna Deadline(2009) 


 

 Lee Pong Marley and me 


 

 NULL NULL Alvin and the Chipmunks 


 

 NULL NULL The Adventures of Tin Tin 


 

 NULL NULL Safe House(2012) 


 

 NULL NULL GIA 


 

 NULL NULL The Dirty Picture 


 

 


Note: Null is returned for non-matching rows on left

Cláusulas "ON" e "USING"

Nos exemplos de consulta JOIN acima, usamos a cláusula ON para combinar os registros entre as tabelas.

A cláusula USING também pode ser usada para o mesmo propósito. A diferença com USING é que ele precisa ter nomes idênticos para as colunas correspondentes em ambas as tabelas.

Na tabela "movies" até agora usamos sua chave primária com o nome "id". Nós nos referimos ao mesmo na tabela "membros" com o nome "movie_id".

Vamos renomear o campo "movies" das tabelas "id" para ter o nome "movie_id". Fazemos isso para ter nomes de campo idênticos.

ALTER TABLE `movies` CHANGE `id` `movie_id` INT( 11 ) NOT NULL AUTO_INCREMENT;

A seguir, vamos usar USING com o exemplo LEFT JOIN acima.

SELECT A.`title` , B.`first_name` , B.`last_name`FROM `movies` AS ALEFT JOIN `members` AS BUSING ( `movie_id` )

Além de usar ON e USING com JOINs, você pode usar muitas outras cláusulas do MySQL como GROUP BY, WHERE e até funções como SUM , AVG , etc.

Por que devemos usar junções?

Agora você pode pensar, por que usamos JOINs quando podemos fazer a mesma tarefa executando consultas. Especialmente se você tem alguma experiência em programação de banco de dados, sabe que podemos executar consultas uma a uma, usar a saída de cada uma em consultas sucessivas. Claro, isso é possível. Mas usando JOINs, você pode fazer o trabalho usando apenas uma consulta com quaisquer parâmetros de pesquisa. Por outro lado, o MySQL pode obter melhor desempenho com JOINs, pois pode usar a indexação. Basta usar uma única consulta JOIN em vez de executar várias consultas para reduzir a sobrecarga do servidor. O uso de várias consultas, em vez disso, leva a mais transferências de dados entre o MySQL e os aplicativos (software). Além disso, requer mais manipulações de dados no final do aplicativo também.

É claro que podemos obter melhores desempenhos do MySQL e do aplicativo com o uso de JOINs.

Resumo

  • JOINS nos permite combinar dados de mais de uma tabela em um único conjunto de resultados.
  • JOINS tem melhor desempenho em comparação com subconsultas
  • INNER JOINS retorna apenas linhas que atendem aos critérios fornecidos.
  • OUTER JOINS também pode retornar linhas onde nenhuma correspondência foi encontrada. As linhas sem correspondência são retornadas com a palavra-chave NULL.
  • Os principais tipos de JOIN incluem Inner, Left Outer, Right Outer, Cross JOINS etc.
  • A cláusula freqüentemente usada em operações JOIN é "ON". A cláusula "USING" requer que as colunas correspondentes tenham o mesmo nome.
  • JOINS também pode ser usado em outras cláusulas, como GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS etc.