Que atire a primeira pedra, quem nunca se aborreceu com tentativas frustradas de juntar as linhas de duas tabelas usando SQL e só depois de várias tentativas conseguir o resultado final (ou desistir de fazê-lo). Neste texto eu vou mostrar oito tipos de junção de tabelas usando o SELECT…JOIN de forma bem simples.

Para isso nós usaremos o SQlite como engine de banco de dados. Embora este seja um sistema de banco de dados pequeno e bem simples, nunca despreze a capacidade de um sistema que se diz capaz de gerenciar até 280TB de dados, sobretudo com registros de até 1GB, cada.

Uma das grandes vantagens do SQlite é que você não precisa instalar nada além do módulo, plugin ou addon de sua linguagem de programação favorita, e dependendo da linguagem nem isso.

Você inclusive pode rodá-lo diretamente aqui de dentro do seu navegador, para não ter desculpa que testará depois e nunca mais fazer isso porque é chato e demorado instalar um SGBD em sua máquina só para fazer uns testes.

Há um bom tempo eu já escrevi aqui sobre Sqlite e embora o texto esteja um pouco desatualizado, como introdução ele vale bem, mas não deixe de consultar a página oficial do projeto que já possui mais de 20 anos e possui muitas novidades e uma ótima documentação.

Por que juntar tabelas?

Este é um ponto elementar e crucial dos SGBDR (Sistema de Gerenciamento de Bancos de dados Relacionais).

Ao contrário do que ocorre com os bancos não relacionais, como MongoDB, Redis, AWS DocumentDB entre outros, os bancos relacionais tais como MySQL, PostgreSQL, Oracle, Microsoft SQL, SQlite entre inúmeros outros primam pela ideia de que você terá seus dados “organizados em temas”, ou se preferir, em várias tabelas e que de alguma forma você os juntará depois para obter a informação desejada.

Pode parecer sem sentido você guardar dados separados para depois juntá-los, mas na verdade dentro de um contexto lógico de eficiência do armazenamento e até de segurança isso é importante e muito útil.

Por exemplo, se você tiver um sistema para cadastro de produtos, não será necessário cadastrar os dados do fornecedor em todos os produtos que entrarem.

Este é um “hábito louco” que virou moda com os bancos No-SQL, que e que surtou os antigos DBAs acostumados com bancos relacionais.

Em um banco não-relacional, se você tiver 20 produtos fornecidos pela mesma empresa, você precisaria repetir os dados do fornecedor nos 20 produtos. Nota antes que reclamem: Crianças, estou falando do conceito apenas, obviamente um sistema bem modelado poderia “burlar” esta regra e implementar alguns artifícios para evitar isso.

Em um banco relacional, não. Ao invés de um único “tabelão” com tudo, você teria duas tabelas. Uma apenas com os dados dos fornecedores, e outra tabela dos vários produtos e que, de “alguma forma”, farão referência aos fornecedores na outra tabela.

Além desta organização permitir limitar o acesso as informações que não convenham, a nível de banco de dados isso vai reduzir a quantidade de dados trafegado pela rede, processado pelo sistema e até mesmo armazenado em disco.

Para o usuário da sua aplicação isso talvez não faça a menor diferença, mas para fins técnicos isso pode significar muitos terabytes de espaço ocupado, ou de memória alocada, o que se converte em muitos milhares ou quiça, milhões de dólares desperdiçados a cada vez que sua aplicação é usada.

Como juntar tabelas?

Infelizmente, ou felizmente, há várias formas de se juntar tabelas. Eu selecionei oito formas diferentes e seria muito leviano de minha parte se afirmasse que vou falar sobre tudo.

Até porque, como eu já disse, os testes foram feitos no Sqlite e somente nele, porém eventualmente uma engine ou outra pode suportar ou não determinados recursos.

Não é a intenção deste texto comparar estes recursos em cada engine de banco de dados, mas apenas mostrar como se faz, da forma mais simples e básica possível.

Sendo assim, tome este texto como referência e na dúvida realize os mesmos testes no seu sistema banco de dados desejado para confirmar se o resultado é o esperado.

Mas voltando ao nosso tema, o que vou deixar é apenas um breve resumo usando duas tabelas pequenas e simples de serem manipuladas e testadas.

Mãos na massa

Para testarmos sem precisar instalar nada, você pode acessar o endereço o Sqliteonline, uma ferramenta web que permite acessar diversos sistemas de base de dados a partir do seu navegador.

A imagem acima mostra como se parece a nossa ferramenta de interação com o Sqlite. Por padrão ele já vem com uma tabela de exemplo chamada “demo” e contendo alguns dados e que não nos serão úteis e por isso nós os removeremos em breve.

Para quem já usou outras ferramentas do tipo não verá muita diferença entre ambas. Uma lista (ou árvore) de objetos no painel lateral, ao centro, um editor de consultas, com um visualizador de resultados abaixo e eventualmente pode-se exibir ou não um painel à direita com informações adicionais.

As tabelas de teste

Eu já li vários artigos sobre o assunto desde que comecei a lidar com bases de dados e em todos eles o autor decidiu por montar uma estrutura complexa com várias tabelas para explicar este assunto.

Honestamente, o que pode ficar bonito e massagear o ego do autor, mostrando que ele sabe muito, acaba limitando a disseminação do conhecimento e ao menos para mim, os primeiros texto que li quando estava começando a me aventurar neste mundo de consultas SQL, aqueles exemplos escabrosos só serviram para me deixar mais desesperado achando que aquilo era complexo demais.

Por conta disso, eu decidi por usar apenas duas tabelas. Isso é suficiente para nossos testes e deve salvar muita gente boa que está perdida nos estudos de bases de dados.

Todos os comandos SQL usados nos exemplos abaixo estão em um repositório no github para quem quiser consultá-los e usá-los como referência no futuro, mas eu os repetirei aqui para que você não precise alternar entre o texto e o Github.

O arquivo 01-create-tables.sql contém as instruções como seguem:

-- Drop demo table
DROP TABLE IF EXISTS demo;
-- Tabela admin
DROP TABLE IF EXISTS admin;
CREATE TABLE admin (doc int , name string, pass);
INSERT INTO admin VALUES (1, "joao", "feb");
INSERT INTO admin VALUES (2, "maria", "cb3");
INSERT INTO admin VALUES (3, "samuel", "a2r");
INSERT INTO admin VALUES (7, "regina", "abc");
-- Tabela user
DROP TABLE IF EXISTS user;
CREATE TABLE user (doc int , name string, mail);
INSERT INTO user VALUES (1, "joao", "joao@emp.com");
INSERT INTO user VALUES (2, "maria", "maria@emp.com");
INSERT INTO user VALUES (3, "samuel", "samuca@emp.com");
INSERT INTO user VALUES (4, "pedro", "pedro@emp.com");
INSERT INTO user VALUES (5, "leia", "leia@emp.com");
INSERT INTO user VALUES (6, "sara", "sara@emp.com");

O que temos aí são a remoção da tabela “demo”, a criação de duas novas tabelas “admin” e “user” e alguns registros em cada uma.

Você pode abrir o seu editor do SQlite, colar estes comandos como estão e clicar no botão “RUN” para que as duas tabelas sejam criadas e alimentadas.

O código será executado rapidamente e você poderá prosseguir com os exemplo seguintes.

Fica então uma dica aqui. Para executar os próximos exemplos você deverá limpar o editor, deixando-o vazio para entrar com os novos comandos, ou então clique com o botão direito sobre o nome da aba (onde tem o ícone da casinha 🏠) e então escolha “New Tab”.

Encare estas tabelas como parte de um sistema maior onde na tabela “admin” temos o login e senha dos responsáveis pela atualização dos dados no sistema, enquanto que a tabela “user” possui outros usuários que apenas consultam o sistema, ou recebem informações geradas ali por e-mail.

Para vê-los, você pode executar um “SELECT * FROM admin” e clicar em “RUN”. Veja também o que tem na tabela “user” com “SELECT * FROM user”, ou se preferir, clique sobre o nome da tabela no painel lateral e escolha “SELECT”.

Dito isso, vamos para as nossas junções de tabelas.

LEFT JOIN

Neste tipo junção nós obtemos todos os registros da primeira tabela e os complementamos com os dados da segunda, caso eles existam.

Cole o conteúdo do arquivo 02-left-join.sql no seu editor e clique em RUN” para ver o resultado.

-- LEFT JOIN = Todos os admins, com os dados da tabela user, caso existam
SELECT * 
FROM admin
LEFT JOIN user
ON admin.doc = user.doc;

O resultado será o da imagem abaixo. Observe que o usuário administrativo “regina” não possui dados na tabela “user”, então AQUI NO SQLITE os campos correspondente a esta tabela são retornados como “NULL”.

O grifo sobre este NULL no SQlite é porque em outros sistemas o retorno poderia ser diferente, mas o fato é que este usuário não terá nada ali.

Não cabe aqui julgar se este resultado é certo ou errado. Talvez para um sistema que esperasse encontrar o e-mail do administrador isso estivesse errado e provavelmente houve uma falha ao cadastrar os dados do admin, mas pode ser que simplesmente o sistema não precise disso.

Ou talvez, a motivação da consulta seja realmente obter os dados de todos os usuários e ainda identificar quem é administrador e quem não é. Este tipo de detalhe deve ser levantado lá na fase de análise de requisitos e depois modelado de acordo.

ANTI LEFT JOIN

Este tipo de junção é similar ao anterior porém seu propósito é justamente encontrar os administradores que não possuem dados na tabela “user”. Se lembra da “regina”?

Carregue o código SQL do arquivo 03-anti-left-join.sql e execute o em uma nova aba.

-- ANTI LEFT JOIN = Todos os admins, que não possuem dados na tabela user
SELECT * 
FROM admin
LEFT JOIN user
ON admin.doc = user.doc
WHERE user.doc IS NULL;

O resultado será o que segue:

Perceba que a diferença está apenas na clausula WHERE onde foi definido que o atributo “doc”, na tabela “user” deve ser NULL.

Novamente, é preciso saber o propósito da consulta. Neste caso nós conseguimos identificar os administradores que possuem cadastro como usuários comuns também.

RIGHT JOIN

O RIGHT JOIN é o inverso do LEFT JOIN e em regras gerais a maioria, ou senão todos, podem ser transformados um no outro. Inclusive o próprio manual do MySQL incentiva e recomenda isso (você pode conferir por conta própria no manual oficial do MySQL 8.0 na seção sobre JOIN).

A frase que está lá, no momento em que eu escrevo este post é o seguinte:

RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN.

Ou em tradução livre:

RIGHT JOIN funciona de forma análoga ao LEFT JOIN. Para manter o código portável entre bases de dados, é recomendados que você use LEFT JOIN ao invés de RIGHT JOIN.

Por conta disso, muitas vezes o desenvolvedor ou analista de dados vai evitar a todo custo usá-lo, se possível. Além de – em uma eventual migração de base de dados – o tal comando não estar disponível, depois de ver uma dezena de LEFT JOIN no seu código, fazer o cérebro funcionar ao contrário para entender um RIGHT JOIN pode levar algum tempo desnecessário.

Execute o código abaixo (arquivo 04-right-join-1.sql) e veja o resultado.

-- RIGHT JOIN = Todos os usuarios, com os dados da tabela admin, caso possuam
SELECT * 
FROM admin
RIGHT JOIN user
ON admin.doc = user.doc;

Se você comparar este código com o arquivo 02-left-join.sql, verá que só foi mudada a instrução “LEFT” ou “RIGHT”.

A saída desta instrução foi a listagem de todos os usuários, com os seus dados como administrador, caso seja.

Isso poderia ser útil, por exemplo, em uma listagem de usuários onde usaríamos o campo “doc” da tabela “admin” para acionar um flag mostrando ou não um ícone destacando os usuários com privilégios administrativos. Claro que para isso poderiamos refinar a consulta exibindo apenas os campos necessários, ao invés de exibir tudo, mas isso é tema para outro post.

Antes de passarmos para o próximo. E se eu quisesse converter este RIGHT JOIN em um LEFT JOIN!? Simples, inverta a ordem das tabelas.

Este é o código de 04-right-join-2.sql. Perceba que eu apenas troquei a ordem das tabelas user e admin, no mesmo código do LEFT JOIN.

-- LEFT JOIN substituindo o RIGHT JOIN
SELECT * 
FROM user
LEFT JOIN admin
ON admin.doc = user.doc;

Uma curiosidade aqui, caso você experimente rodar este código, é que como agora a tabela “user” é a tabela principal, seus dados serão exibidos primeiro, mas os resultados serão os mesmos.

Em geral você fará referência aos campos da sua consulta pelos seus nomes e não pela posição, então esta mudança não fará diferença, mas se isso lhe for um problema, basta você indicar a ordem de exibição desejada.

Veja o arquivo 04-right-join-3.sql abaixo como isso foi resolvido. Este é o mesmo LEFT JOIN anterior, porém ao invés do SELECT *, eu defini que quero os campos de admin primeiro.

-- LEFT JOIN substituindo o RIGHT JOIN com os campos na mesma ordem
SELECT admin.*, user.*
FROM user
LEFT JOIN admin
ON admin.doc = user.doc;

Eu vou economizar uma captura de tela aqui, pois o resultado é exibido exatamente igual ao RIGHT JOIN. Você pode testar ai que vai funcionar! 😉

E como você escolherá entre um e outro? Use a forma que melhor te convir. Ambos estão certos e como mostrado fazem a mesma coisa.

ANTI RIGHT JOIN

Tal como ocorre com o ANTI LEFT JOIN há uma pequena porém importante mudança no comportamento do RIGHT JOIN com esta técnica.

Volte ao primeiro exemplo do RIGHT JOIN (o do arquivo 04-right-join-1.sql). Lá nós exibimos todos os usuários, com informações que permitem identificar quem é ou não administrador do sistema.

Aqui, nós veremos apenas os usuários que não são administradores. Carregue e rode o arquivo 05-anti-right-join.sql abaixo para ver o resultado:

-- ANTI RIGHT JOIN = Todos os usuarios, que não sejam admin
SELECT * 
FROM admin
RIGHT JOIN user
ON admin.doc = user.doc
WHERE admin.doc IS NULL;

E tal como ocorre com o RIGHT JOIN simples, seria possível usar um LEFT JOIN no lugar desse aqui também. Mas eu vou deixar você pensar em como fazer isso.

INNER JOIN

No exemplo no LEFT JOIN nós exibimos todos os administradores, independentemente de terem ou não dados na tabela “user”.

Se considerarmos que no ANTI LEFT JOIN, exibimos apenas aqueles administradores que não possuem dados na tabela “user” (digamos, por algum bug).

Agora com o INNER JOIN nós vamos exibir somente aqueles administradores que possuem dados na tabela “user”. Ou seja, se consideramos que o anterior exibiu cadastros com problema este aqui exibirá somente os que estão OK.

A consulta, que está no arquivo 06-inner-join.sql pode ser vista abaixo e em seguida o resultado da sua execução:

-- INNER JOIN = Somente admin que também sejam user (tem dados em ambas as tabelas)
SELECT * 
FROM admin
INNER JOIN user
ON admin.doc = user.doc;

FULL JOIN

Aqui nós começamos a pisar em um terreno de terra fofa e bastante confusa, mas não é culpa do FULL JOIN, mas do fato de que as junções acima são mais ou menos padronizadas e a maioria ou senão todas as engines de bases de dados as implementam da mesma forma.

Mas a partir daqui as implementações dos padrões são bem vergonhosas em alguns sistemas. Mas vamos deixar isso para depois e vamos ver como funciona primeiro.

Carregue e execute o arquivo 07-full-outer-join.sql com o conteúdo igual ao mostrado abaixo:

-- FULL JOIN = Todos os usuarios e admins independente de terem ou não alguma relação
SELECT * 
FROM admin
FULL OUTER JOIN user
ON admin.doc = user.doc;

O resultado é o que segue. O FULL JOIN permite juntar o conteúdo de ambas as tabelas, independente das relações entre elas.

Isso permitirá que você veja todos os usuários e todos os administradores em uma só consulta, o que pode ser bastante útil em alguns casos.

Entretanto, se você se frustou, ou se decepcionou com a mensagem do manual do MySQL sobre RIGHT JOIN você ficará mais ainda com o fato de que o MYSQL, naquele documento imenso não faz qualquer menção ao FULL JOIN e isso porque simplesmente não existe este recurso em uma das mais usadas engines de base de dados usada no mundo! Você pode ler todo o manual se quiser e provavelmente não achará nenhuma referência.

Agora se você procurar em sites e fóruns de discussão, encontrará muitos desenvolvedores chateados que implementam alguma forma de suprir esta necessidade fazendo duas consultas (LEFT JOIN e RIGHT JOIN) juntando-as com o UNION ALL.

Funciona! Mas que é um tapa-buraco, é. Seria melhor para todo mundo se o FULL JOIN estivesse lá bonitão e esplendoroso para ser usado quando quiséssemos. Segue um exemplo aqui no Stack Over Flow.

Um outro problema aqui é que embora LEFT, RIGHT E FULL sejam consideradas OUTER JOIN, em oposto ao INNER JOIN que já vimos, alguns autores usam o termo OUTER JOIN para se referir ao FULL JOIN causando alguma confusão.

• LEFT JOIN = LEFT OUTER JOIN
• RIGHT JOIN = RIGHT OUTER JOIN
• FULL JOIN = FULL OUTER JOIN
• INNER JOIN

ANTI FULL JOIN

O ANTI FULL JOIN é o oposto do FULL JOIN. Enquanto aquele retornava todos os admins e todos os usuários, este aqui retorna apenas que é exclusivamente administrador e quem é exclusivamente usuário.

O arquivo 08-anti-full-outer-join.sql que usaremos neste exemplo é mostrado abaixo. Carregue o arquivo no seu editor do Sqlite e o execute.

-- ANTI OUTER JOIN = Somente quem é exclusivamente admin ou exclusivamente usuario
SELECT * 
FROM admin
FULL OUTER JOIN user
ON admin.doc = user.doc
WHERE admin.doc IS NULL OR user.doc IS NULL;

Ao carregar e executá-lo o resultado será este abaixo. Veja que os administradores que possuem entrada na tabela usuários foram desprezadas.

Como em todos os outros casos de “ANTI *** JOIN” a diferença aqui fica por conta da cláusula WHERE forçando a exibir os registros cuja chave é nula.

CROSS JOIN

Por último mas não menos importante (embora esquecido em algumas engines) temos o CROSS JOIN que retorna o produto cartesiano de ambas as tabelas.

Ou em termos mais simples, para cada linha da primeira tabela serão retornadas todas as linhas da segunda. Há um perigo neste uso, pois dado que o resultado é o produto de ambas as tabelas, se em uma tabela tivermos 1 milhão de registros e na segunda outro 1 milhão, o resultado retornado será nada menos do que 1 trilhão de registros e haja memória para comportar este conjunto todo de dados.

Para usar este recurso, tenha certeza de retornar o menor conjunto possível de dados, limitando o número de campos, ou aumentando as restrições etc. Se o driver de acesso a sua base de dados não gerenciar bem a memória você poderá ter sérios problemas.

Eu limitei a captura de tela a mostrar apenas os primeiros registros retornados, no entanto esta listagem possui 24 linhas (para cada um dos 4 admins são relacionados todos os 6 registros de usuarios).

Uma curiosidade sobre o CROSS JOIN é que ele não é um padrão SQL e tanto o SQLite, quanto o MySQL e o PostgreSQL os implementam como sendo um alias para o INNER JOIN.

Do manual do MySQL:

In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

Do manual do PostgreSQL:

CROSS JOIN is equivalent to INNER JOIN ON (TRUE), that is, no rows are removed by qualification. They produce a simple Cartesian product, the same result as you get from listing the two tables at the top level of FROM, but restricted by the join condition (if any).

Do manual do SQLite:

There is no difference between the “INNER JOIN”, “JOIN” and “,” join operators. They are completely interchangeable in SQLite. The “CROSS JOIN” join operator produces the same result as the “INNER JOIN”, “JOIN” and “,” operators, but is handled differently by the query optimizer in that it prevents the query optimizer from reordering the tables in the join. An application programmer can use the CROSS JOIN operator to directly influence the algorithm that is chosen to implement the SELECT statement. Avoid using CROSS JOIN except in specific situations where manual control of the query optimizer is desired. Avoid using CROSS JOIN early in the development of an application as doing so is a premature optimization. The special handling of CROSS JOIN is an SQLite-specific feature and is not a part of standard SQL.

Parada para foto

Juntando todos os conceitos em uma só foto chegaríamos a este gráfico aqui.

Eu havia começado a desenhar o gráfico quando, depois do primeiro círculo, o meu nobreak não aguentou e me fez perder “tudo” (culpa minha que não salvei o trabalho), então desisti e resolvi procurar pelo trabalho de alguma nobre alma. E está aí. Os créditos e o agradecimento vão seus autores na Wikipédia em língua espanhola.

Finalizando

Se você usou o sqliteonline.com para realizar os testes, basta fechar janela que os seus dados serão excluídos.

As referências para este texto estão nas seções onde eles foram apresentados, mas de forma mais geral, os principais foram os que segue:

IDE SQLite
Site oficial SQLITE
Manual do MySQL 8.0
Manual do PostgreSQL 15
Tutorial de SQL do W3Schools
Repositório no Github com os códigos usado neste post

E caso tenha alguma sugestão, dica ou apenas queira deixar um comentário é só escrever.