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

O que são JUNÇÕES?

As junções ajudam a recuperar dados de duas ou mais tabelas de banco de dados.

As tabelas são mutuamente relacionadas usando chaves primárias e estrangeiras.

Nota: JOIN é o tópico mais incompreendido entre os estudiosos de SQL. Por uma questão de simplicidade e facilidade de compreensão, usaremos um novo banco de dados para praticar a amostra. Como mostrado abaixo

id primeiro nome último nome id_do_filme
1 Adam ferreiro 1
2 Ravi Kumar 2
3 Susan Davidson 5
4 Jenny Adrianna 8
5 Lee Pong 10
id título categoria
1 ASSASSIN'S CREED: EMBERS animações
2 Aço Real (2012) animações
3 Alvin e os Esquilos animações
4 As Aventuras de Tin Tin animações
5 Seguro (2012) Açao Social
6 Casa Segura (2012) Açao Social
7 GIA 18+
8 Prazo 2009 18+
9 A imagem suja 18+
10 Marley e eu Romance

Tipos de junções

Cruzar JOIN

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

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

Suponha que queiramos obter todos os registros de membros em relação a todos os registros de filmes. Podemos usar o script mostrado abaixo para obter os resultados desejados.

Tipos de junções

SELECT * FROM `movies` CROSS JOIN `members`

Executando o script acima em Ambiente de trabalho MySQL nos dá o seguintewing 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

INNER JOIN

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

Suponha que você queira obter uma 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 atendem a determinadas condições.

INNER JOIN

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

Executando 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 A
INNER JOIN `movies` AS B
ON B.`id` = A.`movie_id`

JOINs externos

MySQL Outer JOINs retornam todos os registros correspondentes de ambas as tabelas.

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

Parece confuso? Vejamos um exemplo -

LEFT JOIN

Suponha agora que 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 foram alugados por ninguém. Podemos simplesmente usar LEFT JOIN para o propósito.

JOINs externos

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 for encontrada na tabela à direita, NULL será retornado.

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

A execução do script acima no ambiente de trabalho MySQL fornece. Você pode ver que no resultado retornado 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 encontrou a tabela de membros para aquele filme específico.

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 será retornado.

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

JUNTAR À DIREITA

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

A execução do script acima no ambiente de trabalho MySQL fornece o seguintewing 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 USANDO é precisa ter nomes idênticos para colunas correspondentes em ambas as tabelas.

Na tabela “movies” até agora usamos sua chave primária com o nome “id”. Referimo-nos ao mesmo na tabela “membros” com o nome “movie_id”.

Vamos renomear o campo “id” da tabela “movies” para ter o nome “movie_id”. Fazemos isso para ter nomes de campos correspondentes 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 A
LEFT JOIN `members` AS B
USING ( `movie_id` )

Além de usar ON e USANDO com JOINs você pode usar muitas outras cláusulas do MySQL como GRUPO POR, ONDE e até funções como SOMA, 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 por uma, usar a saída de cada uma em consultas sucessivas. Claro, isso é possível. Mas usando JOINs, você pode realizar o trabalho usando apenas uma consulta com quaisquer parâmetros de pesquisa. Por outro lado MySQL pode alcançar melhor desempenho com JOINs, pois pode usar indexação. Basta usar uma única consulta JOIN em vez de executar várias consultas para reduzir a sobrecarga do servidor. Em vez disso, usar múltiplas consultas leva a mais transferências de dados entre MySQL e aplicativos (software). Além disso, também requer mais manipulações de dados no final do aplicativo.

É claro que podemos obter melhores desempenhos do MySQL e dos aplicativos usando JOINs.

Resumo

  • JOINS nos permite combinar dados de mais de uma tabela em um único conjunto de resultados.
  • JOINS têm 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 frequentemente usada em operações JOIN é “ON”. A cláusula “USING” exige 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.