Curso SQL — PARTE 4

Agora que já sabemos resumir dados de uma única tabela, é necessário aprendermos a cruzar dados, pois em um banco de dados relacional muitas vezes nem todos os dados que precisamos se encontra na mesma tabela.

O que aprendemos até aqui trabalhando com apenas uma tabela ainda se aplica, porém agora iremos trabalhar com mais de uma origem de dados na mesma consulta. Em outras palavras, o nosso FROM não vai ser mais de uma única tabela.

Vamos iniciar mudando um pouquinho aquele código que utilizamos na PARTE 2 para gerar o nosso ambiente de estudo com as tabelas. Copie e cole o código abaixo na aba esquerda do sql fiddle e clique em build schema

##Para criar a tabela e definir seus campos:
CREATE TABLE IF NOT EXISTS `alunos_turma_a` (
 `id` int(6) unsigned NOT NULL,
 `sobrenome` varchar(200) NOT NULL,
 `nome` varchar(200) NOT NULL,
 `telefone` int(9) unsigned NOT NULL,
 `cidade_res` varchar(200) NOT NULL,
 PRIMARY KEY (`id`)
 ) DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `faturas_em_aberto` (
`id` int(6) unsigned NOT NULL,
`mes_aberto` int(2) NOT NULL,
`valor` int(4) NOT NULL) DEFAULT CHARSET=utf8;
##Para inserir os primeiros registros:
INSERT INTO `alunos_turma_a`(`id`, `sobrenome`, `nome`, `telefone`, `cidade_res`) VALUES
(‘1’ ,’Einstein’ ,’Albert’ ,’11223456' ,’Sao Paulo’ ),
(‘2’ ,’Planck’ ,’Max’ ,’19541980' ,’Campinas’ ),
(‘3’ ,’Vinci’ ,’Leonardo’ ,’19442930' ,’Jundiai’ );
INSERT INTO `faturas_em_aberto`(`id`, `mes_aberto`, `valor`) VALUES
(‘1’ , 10 , 500 ),
(‘1’ , 11 , 500 ),
(‘3’ , 11 , 500 ),
(‘4’ , 10 , 550 );

O comando UNION

A primeira maneira de trabalhar com duas ou mais tabelas é unindo-as como uma única tabela. Para isso precisamos que ambas as tabelas tenham o mesmo tipo de dado em suas colunas e os mesmos nomes de coluna para que o SQL consiga unir as colunas corretamente. O resultado será uma única tabela contendo todos os registros das tabelas que foram unidas.

SELECT * 
FROM alunos_1 
UNION ALL
SELECT * 
FROM alunos_2

Fig. 1 — Ilustração do comando UNION

O comando JOIN

Existem casos onde queremos unir informações de duas tabelas, porém de uma maneira diferente. Queremos complementar a informação da tabela A com as informações da tabela B de maneira similar ao que fazemos com a função PROCV no Excel. Apesar de parecer com um PROCV do Excel o join permite mais de uma forma de relacionar os dados de uma tabela.

INNER JOIN

Fig. 2 — Diagrama representando INNER JOIN

Esse comando retorna apenas os dados que tenham parte nas duas tabelas. Pensando em uma aplicação prática podemos imaginar duas tabelas, uma com as informações pessoais do aluno de uma turma e outra com as informações de boletos em atraso de toda a escola. Se quisermos uma tabela com o nome apenas dos alunos inadimplentes daquela turma iremos precisar pegar os dados que estão apenas na intersecção das duas tabelas (Alunos e Faturas em Aberto).

Precisamos também de uma chave que relacione essas duas tabelas. Aqui nossa condição de join foi que a coluna id da Tabela Alunos fosse igual à coluna id da Tabela Faturas em Aberto.

Existem dados que aparecem na tabela Alunos, mas não aparecem na tabela Faturas em Aberto e por isso não irão aparecer como resultado do join. Isso acontece, por exemplo, com o aluno Max Planck. Por outro lado se a chave se repete em uma das tabelas como é o caso do aluno id = 1 na tabela Faturas em Aberto, o dado correspondente na tabela Alunos irá se repetir. Observe que na tabela Alunos Inadimplentes o nome de Albert se repete já que ele possui duas faturas em atraso.

O Aluno cujo id = 4 da tabela Faturas em Aberto não está na turma de Alunos selecionados e por isso não deve constar na Tabela Alunos inadimplentes.

Fig 3 — Ilustração do comando INNER JOIN

SELECT al.* 
 ,ft.mes_aberto
 ,ft.valor
 FROM alunos_turma_a AS al
 INNER JOIN faturas_em_aberto AS ft 
 ON al.id = ft.id
 ORDER BY al.id

FULL JOIN

Fig. 4— Diagrama representando FULL JOIN

Esse comando retorna todos os dados de ambas as tabelas. Quando parte do dado é encontrada apenas na primeira tabela as colunas correspondentes da segunda tabela permanecem com valor nulo. De maneira análoga quando o dado é encontrado apenas na segunda tabela as colunas da primeira permanecem com dados vazios. Se aplicarmos o FULL JOIN nas tabelas do exemplo anterior o resultado será:

Fig 5— Ilustração do comando FULL JOIN

OBS1: Quando não há dados em alguma das tabelas o resultado é NULL.

OBS2: Não há uma função preparada que já nos retorna o FULL JOIN no MySql 5.6 que é a versão que estamos usando aqui e por isso precisamos fazer uma adaptação usando LEFT JOIN, RIGHT JOIN e WHERE para chegar no resultado desejado. Para outras codificações SQL existe essa função já implementada.

SELECT al.id as id
 ,al.sobrenome
 ,al.nome
 ,al.telefone 
 ,al.cidade_res
 ,ft.mes_aberto 
 ,ft.valor
 FROM alunos_turma_a AS al
 LEFT OUTER JOIN faturas_em_aberto AS ft 
 ON al.id = ft.id
 UNION ALL
SELECT ft.id as id
 ,al.sobrenome 
 ,al.nome 
 ,al.telefone
 ,al.cidade_res
 ,ft.mes_aberto
 ,ft.valor
 FROM alunos_turma_a AS al
 RIGHT OUTER JOIN faturas_em_aberto AS ft 
 ON al.id = ft.id
 WHERE al.id is null

LEFT JOIN

Fig. 6 — Diagrama representando LEFT JOIN

O left join preserva os dados da tabela à esquerda e complementa-os com dados da tabela à direita quando estes existem. Suponha que no exemplo das tabelas Alunos e Faturas quiséssemos complementar a tabela Alunos com as informações de inadimplência, porém mantendo todos os alunos mesmo quando o aluno não estivesse inadimplente poderíamos usar o left jon.

Get Yuri Veiga’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

OBS: Para esse e os próximos eventos considere a tabela Alunos Turma A como a tabela da esquerda (LEFT table) e a tabela Faturas em Aberto como a tabela da direita (RIGHT table).

Fig. 7— Ilustração do comando LEFT JOIN

RIGHT JOIN

Fig. 8— Diagrama representando RIGHT JOIN

O RIGHT JOIN funciona da mesma maneira que o LEFT JOIN. A diferença é a tabela que será complementada. Imagine que no nosso exemplo agora a tabela da esquerda é a de alunos e a da direita é a tabela de faturas em aberto. Se fizermos um RIGHT JOIN iremos obter todas as linhas da tabela faturas em aberto e também as colunas da tabela alunos quando a combinação de id’s existir. Veja o exemplo de aplicação:

Fig. 9— Ilustração do comando RIGHT JOIN

SELECT al.*
 ,ft.mes_aberto
 ,ft.valor
 FROM alunos_turma_a AS al
 RIGHT JOIN faturas_em_aberto AS ft 
 ON al.id = ft.id
 ORDER BY al.id

LEFT ANTI JOIN

Fig. 10— Diagrama representando LEFT ANTI JOIN

Se quiséssemos encontrar apenas aqueles alunos que não apresentam faturas em aberto e estão na turma A, poderíamos usar o LEFT ANTI já que ele irá conservar apenas os alunos que estão na turma A e não estão na tabela de inadimplentes.

Fig. 11 — Ilustração do comando LEFT ANTI JOIN

OBS3: Assim como fizemos no caso do FULL JOIN, teremos que construir o left anti join a partir do LEFT + WHERE usando as relações lógicas.

SELECT ft.id as id
 ,al.sobrenome
 ,al.nome
 ,al.telefone
 ,al.cidade_res
 ,ft.mes_aberto
 ,ft.valor
FROM alunos_turma_a AS al
 LEFT OUTER JOIN faturas_em_aberto AS ft 
 ON al.id = ft.id
 WHERE ft.id is null

RIGHT ANTI JOIN

Fig. 12 — Diagrama representando RIGHT ANTI JOIN

Imagine agora que nosso objetivo é chegar em uma tabela que contenha os alunos inadimplentes que não estão na turma A. De maneira análoga ao que fizemos no exemplo do LEFT ANTI join iremos aplicar o RIGHT ANTI JOIN. Observe o resultado dessa operação:

Fig. 13— Ilustração do comando RIGHT ANTI JOIN

SELECT ft.id as id
 ,al.sobrenome 
 ,al.nome
 ,al.telefone
 ,al.cidade_res
 ,ft.mes_aberto
 ,ft.valor
 FROM alunos_turma_a AS al
 RIGHT OUTER JOIN faturas_em_aberto AS ft 
 ON al.id = ft.id
 WHERE al.id is null

DESAFIO: Procure encontrar um exemplo de EXCLUSIVE FULL JOIN, desenhe seu diagrama (como ficariam coloridos os dois círculos) e construa a query.

Qual seria o resultado esperado? (pode fazer em um rascunho no papel mesmo ou no Excel).

O resultado obtido por você nessa query foi o que você tinha planejado?

Renomeando Tabelas

Da mesma maneira como renomeamos as colunas com apelidos, podemos fazer para as tabelas quando essas possuem nomes muito grandes. Um exemplo dessa aplicação:

SELECT al.id
 ,al.nome
 ,ft.valor
 ,ft.mes_aberto
 FROM alunos_turma_a as al
 LEFT JOIN faturas_em_aberto as ft

Note que devemos especificar a origem da coluna. Não é necessário quando o nome da coluna é exclusivo de uma das colunas, mas é uma boa prática especificar a origem daquela coluna, pois isso evita erros de processamento. Para colunas como o id, que existe em ambas as tabelas, é fundamental especificar a tabela de origem de onde se pretende pegar a informação, pois caso contrário o SQL ficará “confuso” sobre qual a origem do id e retornará erro.

Se você chegou aqui, parabéns! Você aprendeu conceitos básicos para escrever uma query SQL.
Quer retribuir esse conteúdo? Então é só compartilhar o link desse curso no Linkedin comentando sobre sua experiência e como esse curso te ajudou até aqui, combinado?