Exemplos de aprendizagem de JOINs com o mundo real SQL

A declaração JOIN permite trabalhar com dados armazenados em várias tabelas. Neste artigo, vou guiá-lo através do tópico de cláusulas JOIN usando exemplos SQL do mundo real.

Imagine se você pudesse trabalhar apenas com uma tabela de banco de dados de cada vez. Felizmente, isto não é nada com que tenhamos que nos preocupar. Assim que você aprender a declaração JOIN, você pode começar a vincular dados. Neste artigo, vou usar exemplos SQL do mundo real que ilustram como usamos JOINs, como cada tipo de JOIN funciona, e quando usar cada tipo. Além disso, vou compartilhar algumas dicas que o ajudarão a evitar erros comuns ao usar JOINs.

O que é a Cláusula de SQL JOIN?

A cláusula JOIN nos permite combinar as colunas de duas ou mais tabelas com base em valores de colunas compartilhadas. Explicarei isso usando exemplos SQL em tempo real. Aqui está o primeiro: digamos que temos uma tabela students que contém nomes de estudantes, seus respectivos nomes de usuário e um número de identificação. Também temos uma tabela de "comentários" que armazena qualquer comments que estudantes tenham postado em um fórum. Aqui estão as duas tabelas.

Vamos adicionar alguns dados de teste:

INSERT INTO `students` VALUES
(1,'Jhon','jj2005'),(2,'Albert','salbert'),(3,'Mathew','powermath'),
(4,'Lisa','lisabbc'),(5,'Sandy','imsandyw'),(6,'Tamara','tamy21');

INSERT INTO 'comments' VALUES 
(1,'jj2005','Awesome!'),(2,'jj2005','This is great :)'),
(3,'powermath','Hmmm...'),(4,'imsandyw','Let\'s wait a moment'),
(5,'lisabbc','Sure thing'),(6,'lisabbc','wow!'),
(7,'lisabbc','lol :) :) :)');

Como você pode ver, ambas as tabelas têm a coluna forum_username em comum. Portanto, esta coluna pode ser usada em uma declaração JOIN para relacionar as duas tabelas em conjunto

Por exemplo, se quiséssemos saber o nome real do estudante para cada um dos comentários no fórum, escreveríamos esta consulta JOIN:

SELECT students.name, comments.forum_username, comments.comment
FROM students 
INNER JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

Os resultados ficariam assim:

Nota: Usei propositadamente forum_username para ilustrar o conceito JOIN, mas na prática você usaria a chave primária (neste caso, a coluna id da tabela students ) para relacionar as tabelas.

Conhecendo os tipos de junção

Há vários tipos de JOINs. Vamos analisá-los rapidamente:

INNER JOIN: Este JOIN retorna registros que têm uma correspondência em ambas as tabelas com base no predicado do join (que vem após a palavra-chave ON ). Este é o mesmo JOIN que usamos no exemplo anterior. A palavra-chave INNER é opcional.

LEFT [OUTER] JOIN: Isto retornará todos os registros da tabela da esquerda (ou seja, a tabela que você listar primeiro na JOIN) e somente os registros correspondentes da tabela da direita (ou seja, a segunda). A palavra-chave OUTER é opcional.

Voltando aos nossos exemplos

SELECT students.name, comments.forum_username, comments.comment
FROM students 
LEFT JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

Este LEFT JOIN devolveria um registro para todos os estudantes - incluindo Tamara e Albert, que não têm comentários e não seriam listados nos resultados de um INNER JOIN. Observe também que neste exemplo LEFT JOIN, os resultados da coluna comment de Tamara e Albert serão NULL

JOGO DIREITO [OUTER]: Este é o inverso do LEFT JOIN; ele retorna todos os registros da tabela da direita (segunda) e somente aqueles que têm uma correspondência da esquerda (primeira) tabela

Uma consulta semelhante ao exemplo

SELECT 
  students.name,
  comments.forum_username,
  comments.comment
FROM students 
RIGHT JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

FULL [OUTER] JOIN: Esta é essencialmente a combinação de um LEFT JOIN e um RIGHT JOIN. O conjunto de resultados incluirá todas as linhas de ambas as tabelas, preenchendo as colunas com valores de tabela quando possível ou com NULLs quando não houver correspondência na tabela da contraparte. Este não é um JOIN que você usará com muita freqüência na vida real. Nota: MySQL não tem esta declaração, mas um resultado semelhante pode ser alcançado usando o UNION de LEFT JOIN e RIGHT JOIN.

No caso de nossos exemplos SQL do mundo real, vale a pena notar que na seguinte cláusula FULL JOIN estamos fornecendo a palavra-chave ON como fazemos em LEFT ou RIGHT JOINs :

SELECT *
FROM students 
FULL OUTER JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

CROSS JOIN: Este é outro tipo de join que você não usará com tanta freqüência - neste caso, porque ele recupera o produto cartesiano de ambas as mesas. Basicamente, isto lhe dá a combinação de todos os registros de ambas as tabelas. CROSS JOIN não aplica um predicado (não há nenhuma palavra-chave ON ), mas ainda é possível filtrar as linhas usando WHERE. Fazendo isso, o conjunto de resultados poderia ser equivalente a um INNER JOIN. No MySQL, JOIN, CROSS JOIN e INNER JOIN são equivalentes sintáticos, ou seja, eles podem se substituir um ao outro

Abaixo, não há nenhuma cláusula ON para filtrar os resultados. Todas as combinações possíveis de ambas as tabelas serão mostradas no conjunto de resultados:

SELECT *
FROM students 
CROSS JOIN comments 
ORDER BY students.name ASC;

Você pode saber mais sobre os tipos de Cláusulas JOIN em SQL em nosso canal no YouTube - We Learn SQL. Lembre-se de clicar em subscrever.

Exemplos de JOINs em SQL em tempo real

O número de cenários que requerem um JOIN é infinito, mas alguns cenários aparecem com mais freqüência. Em vez de passar pelo exemplo típico da tabela1/tabela2, eu prefiro apresentar exemplos SQL do mundo real. Podemos usá-los para obter algumas dicas práticas.

O relacionamento entre avô, pai e filho

Um cenário comum em tempo real trata de dados que seguem este tipo de relacionamento. Por exemplo, um user está localizado em um city que pertence a um state. As tabelas (com mais ou menos colunas) parecem algo parecido com isto

Vamos adicionar alguns dados:

INSERT INTO `states` VALUES (3,'California'),(2,'Florida'),(1,'Nevada');

INSERT INTO `cities` VALUES (1,1,'Miami'),(2,1,'Orlando'),
(3,2,'Las Vegas'),(4,2,'Coyote Springs');

INSERT INTO `users` VALUES (1,1,'Jhon','Doe'),(2,1,'Albert','Thomson'),
(3,2,'Robert','Ford'),(4,3,'Samantha','Simpson');

Para obter a lista completa de usuários em uma determinada cidade e estado, precisaremos juntar a tabela de filhos (User) com seu pai (City) e seu avô (State)

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM users User
INNER JOIN cities City
  ON User.city_id = City.id
INNER JOIN states State
  ON City.state_id = State.id;

Já temos algumas dicas úteis:

• As colunas usadas para vincular tabelas devem ser indexadas para maior desempenho.
• Quando as colunas que ligam as tabelas (como o exemplo anterior) estão apontando para a chave primária da tabela relacionada, então estamos falando de chaves estrangeiras. Neste caso, é melhor incluir esta relação como parte da definição de sua tabela; isso aumentará o desempenho. No MySQL, você pode criar uma chave estrangeira de usuário/cidade como esta:

  ALTER TABLE `users` 
  ADD INDEX `fk_city_idx` (`city_id` ASC);
  ALTER TABLE `users` 
  ADD CONSTRAINT `fk_city`
    FOREIGN KEY (`city_id`)
    REFERENCES `cities` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE;
  

  Isto lhe dá o benefício adicional da verificação de integridade que será realizada pelo motor quando os dados nestas tabelas forem atualizados ou excluídos.

Suponhamos que queremos encontrar todos os usuários em um estado. Você adiciona uma condição de filtragem à consulta, como mostrado abaixo:

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM users User
INNER JOIN cities City
  ON User.city_id = City.id
INNER JOIN states State
  ON City.state_id = State.id
  AND State.statename = 'Nevada';

Ou você poderia até mesmo usar uma junção implícita (mostrada em negrito), como esta:

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM
   users User,
   cities City,
   states State
WHERE User.city_id = City.id
  AND City.state_id = State.id
  AND State.statename = 'Nevada';

Mas sugiro que você escreva explicitamente o JOIN e mantenha os critérios de adesão e as condições de filtragem separados

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM users User
INNER JOIN cities City
  ON User.city_id = City.id
INNER JOIN states State
  ON City.state_id = State.id
WHERE State.statename = 'Nevada';

Um par de conceitos importantes:

• Podemos ver como o filho (user) a tabela é filtrada com base nas condições utilizadas no avô (state) tabela. Em outras palavras, os usuários foram baseados em um determinado estado. Da mesma forma, poderíamos ter filtrado os resultados com base no pai (city) tabela e obteve uma lista de usuários com base em uma determinada cidade.
• Uma regra geral é que o JOIN prevê (as condições após a palavra-chave ON ) deve ser usado somente para a relação de adesão. Deixe o restante das condições de filtragem dentro da seção WHERE. Isto simplificará a legibilidade da consulta e a manutenção futura do código.

Por outro lado, poderíamos retornar Estados com base em certos critérios. Por exemplo, poderíamos recuperar estados que tenham pelo menos um usuário pertencente a esse estado:

SELECT 
  DISTINCT State.statename
FROM states State
INNER JOIN cities City
  ON City.state_id = State.id
INNER JOIN users User
  ON User.city_id = City.id

O que aprendemos aqui?

• O INNER JOIN remove quaisquer registros "inutilizados" ou inigualáveis (registros sem uma correspondência em ambos os lados do JOIN).
• DISTINCT filtra os registros duplicados. Como poderia haver vários usuários para um estado, se não utilizássemos DISTINCT teríamos tantos estados repetidos quanto usuários pertencentes a ele. (O mesmo efeito de filtragem também poderia ser obtido usando GROUP BY.)
• Uma filtragem adicional poderia ser conseguida adicionando condições WHERE.

A relação entre muitos e muitos

Deseja mais exemplos SQL em tempo real? Outro cenário típico para JOINs é quando os registros se relacionam uns com os outros de uma forma "muitos para muitos" ou N para N. Digamos que você tenha um sistema onde você cria crachás que são concedidos aos usuários. Neste caso, um usuário tem crachás e, ao mesmo tempo, um crachá tem usuários. Estes relacionamentos precisarão de uma terceira tabela que conectará as chaves primárias de users e badges. Seria algo parecido com isto:

Vamos adicionar alguns dados de amostra:

INSERT INTO `badges` VALUES 
(1,'gold heart',100),(2,'silver heart',50),(3,'metal heart',10),(4,'star',5),(5,'wood',2),(6,'dust',1);
INSERT INTO `users` VALUES (1,'Robert','Williams'),(2,'Anthony','McPeters'),(3,'Tania','Krugger'),(4,'JJ','Richards'),(5,'Katy','Thomas');
INSERT INTO `badges_users` VALUES
(1,1,1),(2,4,1),(3,4,2),(4,4,2),(5,4,2),(6,3,3),(7,3,3),(8,2,4);

Como um JOIN pode nos trazer a todos os usuários com seus respectivos crachás?

SELECT 
  User.first_name,
  User.last_name,
  BU.user_id,
  BU.badge_id,
  Badge.badge_name,
  Badge.badge_points
FROM users User
LEFT JOIN badges_users B
  ON User.id = BU.user_id
LEFT JOIN badges Badge
  ON BU.badge_id = Badge.id
ORDER BY Badge.badge_points DESC

Aqui estão algumas coisas a serem lembradas sobre este tipo de consulta:

• Utilizamos LEFT JOIN aqui de propósito porque ele mostrará aos usuários que não possuem nenhum crachá. Se tivéssemos usado um INNER JOIN ou uma junção interna implícita (estabelecendo as equivalências da identificação em um WHERE), então os usuários que não possuem crachás não seriam incluídos nos resultados. Se você quiser excluir esses usuários, deve usar um INNER JOIN.
• Além disso, utilizar um LEFT JOIN significa que os crachás não utilizados não serão listados; estamos nos concentrando nos usuários e não nos crachás.
• Finalmente, lembre-se de indexar corretamente a tabela intermediária (badges_users). Todas as suas chaves estrangeiras devem ser definidas.

Agora vamos agarrar todos os crachás que têm pelo menos um usuário. Expressados de outra forma, são crachás que foram usados pelo menos uma vez

SELECT DISTINCT Badge.badge_name
FROM badges Badge
LEFT JOIN badges_users BadgeUser
  ON Badge.id = BadgeUser.badge_id

E se quiséssemos obter todos os crachás não utilizados, a consulta se torna:

SELECT Badge.badge_name, Badge.badge_points
FROM badges Badge
LEFT JOIN badges_users BadgeUser
  ON Badge.id = BadgeUser.badge_id
WHERE BadgeUser.badge_id IS NULL

Note que:

• Nem sempre precisamos juntar todas as três tabelas. Unindo somente com a tabela intermediária (badges_users), que contém as referências ao user e badge ainda podemos realizar nossas consultas com sucesso.
• A tabela intermediária também poderia ser usada para salvar informações adicionais. Por exemplo, ela poderia armazenar o valor do carimbo da hora quando um usuário recebeu um determinado crachá.

Experimente você mesmo

É provável que você enfrente diariamente este tipo de situações ao lidar com tabelas que contêm dados relacionados. Eu recomendo fortemente que você examine os exemplos SQL do mundo real acima e os teste com os dados reais. Isto lhe dará uma compreensão mais clara dos conceitos envolvidos.

Se você tiver algum comentário sobre nossos exemplos SQL do mundo real ou suas próprias idéias de outras cláusulas JOIN da vida real, sinta-se à vontade para compartilhá-los para que todos nós possamos continuar aprendendo!