MySQL Tutorial 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.

SELECT * FROM `movies` CROSS JOIN `members`

Executando o script acima em MySQL bancada 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

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.

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.

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`

Executando o script acima em MySQL workbench dá. 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

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

Executando o script acima em MySQL workbench 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 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 muitos outros MySQL cláusulas 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. Usar múltiplas consultas, o que gera 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.

É evidente que podemos conseguir melhores MySQL e desempenho de aplicativos pelo uso de 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.