Tutorial de Joins em SQL: Cross Join, Full Outer Join, Inner Join, Left Join e Right Join

Artigo original: SQL Joins Tutorial: Cross Join, Full Outer Join, Inner Join, Left Join, and Right Join.

As joins (ou junções, no português) do SQL permitem que nossos sistemas de gerenciamento de banco de dados sejam, bem, relacionais.

Joins nos permitem reconstruir nossas tabelas separadas dentro do banco de dados em relacionamentos poderosos para nossas aplicações.

Neste artigo, vamos olhar para cada tipo diferente de join no SQL e aprender como usá-los.

Sobre o que vamos tratar neste artigo:

• O que é uma join?
• Como configurar nosso banco de dados
• CROSS JOIN
• Como configurar nossos dados de exemplo (diretores e filmes)
• FULL OUTER JOIN
• INNER JOIN
• LEFT JOIN / RIGHT JOIN
• Como filtrar dados com LEFT JOIN
• Múltiplas joins
• Joins com condições extras
• A realidade sobre escrever queries (ou buscas, no português)  com joins

(Alerta de spoiler: abordaremos cinco tipos diferentes– mas você precisará saber apenas dois deles!)

O que é uma join?

Uma join é uma operação que combina duas ou mais linhas em uma só linha.

Essas linhas normalmente vêm de duas tabelas diferentes, embora não precisem ser.

Antes de olharmos como escrever uma join propriamente dita, vamos olhar para o que seria o resultado de uma join.

Vamos pegar o exemplo de um sistema que armazena informações sobre usuários e seus endereços.

As linhas da tabela que armazena as informações dos usuários se parece com isso:

 id |     name     |        email        | age
----+--------------+---------------------+-----
  1 | John Smith   | johnsmith@gmail.com |  25
  2 | Jane Doe     | janedoe@Gmail.com   |  28
  3 | Xavier Wills | xavier@wills.io     |  3
...
(7 rows)

E as linhas da tabela que armazena informações sobre os endereços seria desse jeito:

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

Poderíamos escrever queries para buscar separadamente tanto as informações do usuário como seu endereço – mas, idealmente, poderíamos escrever apenas uma query para retornar todos os usuários e seus endereços no mesmo resultado.

E é exatamente isso que a join nos permite!

Veremos como escrever essas junções em breve, mas, se juntarmos nossas informações de usuário com as de endereço, teremos este resultado:

 id |     name     |        email        | age | id |      street       |     city      | state | user_id
----+--------------+---------------------+-----+----+-------------------+---------------+-------+---------
  1 | John Smith   | johnsmith@gmail.com |  25 |  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | Jane Doe     | janedoe@Gmail.com   |  28 |  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | Xavier Wills | xavier@wills.io     |  35 |  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

Aqui, podemos ver todos os nossos usuários e seus respectivos endereços em um único resultado.

Além de produzir resultados combinados, outro uso importante das joins é retirar informações adicionais da nossa busca, que também podem ser filtrados.

Por exemplo, se quisermos enviar uma correspondência física para nossos usuários que moram na cidade de Oklahoma, podemos usar essa join e definir um filtro baseado na coluna city.

Agora que sabemos o propósito das joins– vamos escrever algumas!

Como configurar nosso banco de dados

Antes de começarmos a escrever nossas queries, precisamos configurar nosso banco de dados.

Para os nossos exemplos, usaremos o PostgreSQL, mas a escrita das queries e os conceitos mostrados aqui podem ser facilmente traduzidos para qualquer outro sistema de banco de dados moderno (como o MySQL, o SQL Server etc.).

Para trabalhar com um banco PostgreSQL, usamos o psql – a linha de comando interativa do PostgreSQL. Se você tiver algum outro client (ou serviço, em português) com o qual esteja acostumado a trabalhar, não tem problema.

Vamos começar criando nosso banco de dados. Com o PostegreSQL já instalado, podemos executar o comando createdb <nome-do-banco-da-dados> em nosso terminal para criar um banco de dados. Vou chamar o meu de fcc:

$ createdb fcc

Vamos iniciar o console interativo usando o comando  psql e conectar o banco de dados que criamos usando \c <nome-do-banco-de-dados>:

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

Observação: eu limpei o resultado do psql nesses exemplos para tornar mais fácil a leitura. Então, não se preocupe se o seu retorno não for exatamente igual ao que está vendo no seu terminal.

Eu aconselho você a seguir os exemplos e executar as queries por conta própria. Desse modo, você aprenderá e se lembrará com mais facilidade, trabalhando nos exemplos em vez de apenas ler sobre eles.

Agora, vamos às joins!

CROSS JOIN

O tipo mais simples de join que podemos fazer é a CROSS JOIN, ou "produto cartesiano".

Essa join pega cada linha de uma tabela e a combina com cada linha da outra tabela.

Se tivéssemos duas listas – uma contendo 1, 2, 3 e a outra contendo A, B, C — o produto cartesiano das duas seria:

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

Cada valor da primeira lista é emparelhado com cada valor da segunda lista.

Vamos escrever esse exemplo simples em uma query do SQL.

Primeiro, vamos criar duas tabelas simples e inserir alguns dados:

CREATE TABLE letters(
  letter TEXT
);

INSERT INTO letters(letter) VALUES ('A'), ('B'), ('C');

CREATE TABLE numbers(
  number TEXT
);

INSERT INTO numbers(number) VALUES (1), (2), (3);

Nossas duas tabelas, letters e numbers, tem apenas uma coluna: um simples campo de texto.

Agora, vamos juntá-las com um CROSS JOIN:

SELECT *
FROM letters
CROSS JOIN numbers;

 letter | number
--------+--------
 A      | 1
 A      | 2
 A      | 3
 B      | 1
 B      | 2
 B      | 3
 C      | 1
 C      | 2
 C      | 3
(9 rows)

Esse é o tipo mais simples de join que podemos fazer – mesmo nesse exemplo simples, no entanto, podemos ver a join em funcionamento: as duas linhas separadas (uma de letters e outra de numbers) foram combinadas para formar uma única linha.

Embora esse tipo de join seja frequentemente discutido como sendo apenas um exemplo acadêmico, ele tem um bom caso de uso: cobrir intervalos de datas.

CROSS JOIN com intervalos de datas

Um bom caso de uso do CROSS JOIN é pegar cada linha de uma tabela e aplicar para cada dia de um intervalo de data específico.

Digamos, por exemplo, que estávamos criando uma aplicação que monitora atividades diárias – coisas como escovar os dentes, tomar o café da manhã ou tomar banho.

Se você quiser gerar um registro para cada tarefa e para cada dia da semana passada, você poderia usar a CROSS JOIN em um intervalo de datas.

Para criar esse intervalo, podemos usar a função generate_series:

SELECT generate_series(
  (CURRENT_DATE - INTERVAL '5 day'),
  CURRENT_DATE,
  INTERVAL '1 day'
)::DATE AS day;

A função generate_series recebe três parâmetros:

O primeiro parâmetro é o valor de início. Nesse exemplo, usamos CURRENT_DATE - INTERVAL '5 day'. Isso retorna o dia atual menos 5 dias – ou "cinco dias atrás".

O segundo parâmetro é a data atual (CURRENT_DATE).

O terceiro parâmetro é o "intervalo" – ou o quanto nós queremos incrementar em cada valor por vez. Já que essas são tarefas diárias, vamos usar o intervalo de um dia (INTERVAL '1 day').

Juntando tudo, isso gera uma série de datas começando de cinco dias atrás, terminando hoje e sendo incrementando um dia por vez.

Finalmente, removemos a parte do tempo, lançando a saída desses valores para uma data usando ::DATE, e atribuímos um nome à coluna usando AS day para fazer com que o retorno seja um pouco melhor.

O resultado dessa query são os últimos cinco dias mais o dia de hoje:

    day
------------
 2020-08-19
 2020-08-20
 2020-08-21
 2020-08-22
 2020-08-23
 2020-08-24
(6 rows)

Voltando ao nosso exemplo de tarefas diárias, vamos criar uma tabela simples para conter as tarefas que queremos completar e inserir algumas tarefas:

CREATE TABLE tasks(
  name TEXT
);

INSERT INTO tasks(name) VALUES
('Escovar os dentes'),
('Tomar o café da manhã'),
('Tomar banho'),
('Vestir-se');

Nossa tabela tasks tem apenas uma coluna, name, Nela, nós inserimos quatro tarefas.

Agora, vamos usar a CROSS JOIN nas nossas tarefas e na query que cria o intervalo de datas:

SELECT
  tasks.name,
  dates.day
FROM tasks
CROSS JOIN
(
  SELECT generate_series(
    (CURRENT_DATE - INTERVAL '5 day'),
    CURRENT_DATE,
    INTERVAL '1 day'
  )::DATE	AS day
) AS dates

(Já que nossa query de produção de dados não é exatamente uma tabela, nós podemos escrevê-la como uma subquery.)

A partir dessa query, retornamos o nome das tarefas e o dia. O resultado terá essa aparência:

     name              |    day
---------------+-------------------
 Escovar os dentes     | 2020-08-19
 Escovar os dentes     | 2020-08-20
 Escovar os dentes     | 2020-08-21
 Escovar os dentes     | 2020-08-22
 Escovar os dentes     | 2020-08-23
 Escovar os dentes     | 2020-08-24
 Tomar o café da manhã | 2020-08-19
 Tomar o café da manhã | 2020-08-20
 Tomar o café da manhã | 2020-08-21
 Tomar o café da manhã | 2020-08-22
 ...
 (24 rows)

Como esperávamos, nós recebemos uma linha para cada tarefa para cada dia em nosso intervalo de datas.

A CROSS JOIN é a join mais simples que podemos fazer, mas, para ver os outros tipos, vamos precisar de um exemplo mais realista de configuração de tabela:

Como configurar nossos dados de exemplo (diretores e filmes)

Para ilustrar os próximos tipos de join, vamos usar o exemplo de movies e movie directors (filmes e diretores de filmes, em português).

Nessa situação, o filme tem apenas um diretor, mas um filme não necessariamente precisa ter um diretor associado – imagine que um novo filme foi anunciado mas a escolha do diretor ainda não foi feita ou confirmada.

Nossa tabela directors armazenará o nome de cada diretor, enquanto a tabela movies armazenará o nome de cada filme, assim como a referência do diretor associado (caso haja uma).

Vamos criar as duas tabelas e inserir alguns dados nelas:

CREATE TABLE directors(
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

INSERT INTO directors(name) VALUES
('John Smith'),
('Jane Doe'),
('Xavier Wills')
('Bev Scott'),
('Bree Jensen');

CREATE TABLE movies(
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  director_id INTEGER REFERENCES directors 
);

INSERT INTO movies(name, director_id) VALUES
('Movie 1', 1),
('Movie 2', 1),
('Movie 3', 2),
('Movie 4', NULL),
('Movie 5', NULL);

Temos cinco diretores, cinco filmes e três desses filmes tem diretores associados a eles. O diretor com o ID 1 tem dois filmes, enquanto o diretor ID 2 tem um.

FULL OUTER JOIN

Agora que temos alguns dados para trabalhar, vamos olhar a FULL OUTER JOIN.

A FULL OUTER JOIN tem algumas semelhanças com a CROSS JOIN, mas algumas diferenças importantes.

A primeira diferença é que uma FULL OUTER JOIN precisa de uma condição para fazer a join.

A condição da join especifica como duas linhas de duas tabelas diferentes estão relacionadas uma com a outra, bem como o critério a ser usado para juntá-las.

No nosso exemplo, nossa tabela movies tem uma referência aos diretores através da coluna director_id. Essa coluna corresponde à coluna id da tabela directors. Essas são as duas colunas que usaremos como condição para a join.

Escrevemos essa join entre as duas tabelas desta forma:

SELECT *
FROM movies
FULL OUTER JOIN directors
  ON directors.id = movies.director_id;

Observe a condição da join especificada, que corresponde o filme ao seu diretor : ON movies.director_id = directors.id.

Nossos resultados vão se parecer com um produto cartesiano estranho:

  id  |  name   | director_id |  id  |     name
------+---------+-------------+------+--------------
    1 | Movie 1 |           1 |    1 | John Smith
    2 | Movie 2 |           1 |    1 | John Smith
    3 | Movie 3 |           2 |    2 | Jane Doe
    4 | Movie 4 |        NULL | NULL | NULL
    5 | Movie 5 |        NULL | NULL | NULL
 NULL | NULL    |        NULL |    5 | Bree Jensen
 NULL | NULL    |        NULL |    4 | Bev Scott
 NULL | NULL    |        NULL |    3 | Xavier Wills
(8 rows)

As primeiras linhas que veremos são aquelas onde o filme tem um diretor e que nossa condição de join avaliou como verdadeira.

No entanto, após essas linhas, vemos algumas linhas restantes de cada tabela – mas com valores NULL, onde a outra tabela não teve uma correspondência.

Observação: se você não está familiarizado com valores do tipo NULL, pode veja esta explicação (em inglês) no tutorial de operadores SQL

Também vemos outra diferença entre a CROSS JOIN e a FULL OUTER JOIN aqui. A FULL OUTER JOIN retorna uma linha distinta em cada linha– diferentemente da CROSS JOIN, que retorna múltiplas.

INNER JOIN

O próximo tipo de join, a INNER JOIN, é mais comumente utilizado do que todos os outros.

Uma inner join retorna apenas as linhas onde a condição de join é verdadeira.

No nosso exemplo, uma inner join entre nossas tabelas de  movies e directors retornaria apenas registros onde o filme possui um diretor atribuído.

A sintaxe é basicamente a mesma que vimos antes:

SELECT *
FROM movies
INNER JOIN directors
  ON directors.id = movies.director_id;

Nosso resultado mostra apenas os três filmes que possuem um diretor.

 id |  name   | director_id | id |    name
----+---------+-------------+----+------------
  1 | Movie 1 |           1 |  1 | John Smith
  2 | Movie 2 |           1 |  1 | John Smith
  3 | Movie 3 |           2 |  2 | Jane Doe
(3 rows)

Já que uma inner join inclui apenas as linhas que tenham uma condição de join que a corresponda, a ordem de junção das duas tabelas não fará diferença.

Se invertermos a ordem das tabelas, a query terá o mesmo resultado:

SELECT *
FROM directors
INNER JOIN movies
  ON movies.director_id = directors.id;

 id |    name    | id |  name   | director_id
----+------------+----+---------+-------------
  1 | John Smith |  1 | Movie 1 |           1
  1 | John Smith |  2 | Movie 2 |           1
  2 | Jane Doe   |  3 | Movie 3 |           2
(3 rows)

Como listamos a tabela de directors primeiro nessa query e selecionamos todas as colunas (SELECT *), vemos a coluna de directors primeiro e depois as outras colunas de movies - o resultado dos dados, no entanto, é o mesmo.

Essa é uma propriedade muito útil das inner joins, mas não é uma realidade de todos os outros tipos de join– como veremos no nosso próximo exemplo.

LEFT JOIN/RIGHT JOIN

Os próximos dois tipos de join usam um modificador (LEFT ou RIGHT) que afeta qual dado de tabela será incluído no resultado final.

Observação: a LEFT JOIN e a RIGHT JOIN também podem ser referidas como LEFT OUTER JOIN e RIGHT OUTER JOIN.

Essas joins são usadas em queries onde queremos retornar todos os dados da tabela específica e, caso existam, os dados correspondentes da outra tabela.

Se os dados correspondentes não existirem, ainda teremos os dados da tabela primária.

Seria como uma busca por uma informação específica e um bônus caso a informação de bônus exista.

Isso será simples de entender com um exemplo. Vamos encontrar todos os filmes e seus diretores, mas não vamos nos importar com o fato de eles terem um diretor ou não – esse é o bônus:

SELECT *
FROM movies
LEFT JOIN directors
  ON directors.id = movies.director_id;

A query segue o mesmo padrão que vimos antes – apenas especificamos a join como LEFT JOIN.

Nesse exemplo, a tabela movies é a tabela left (ou da esquerda, em português).

Se escrevermos a query em uma linha, fica um pouco mais fácil de ver:

... FROM movies LEFT JOIN directors ...

Uma left join retorna todos os dados da tabela da esquerda.

Uma left join retorna também todas linhas da tabela da direita (a outra tabela, no caso) que correspondam à condição da join.

As colunas da tabela da direita que não correspondam à condição da join serão retornadas como NULL.

 id |  name   | director_id |  id  |    name
----+---------+-------------+------+------------
  1 | Movie 1 |           1 |    1 | John Smith
  2 | Movie 2 |           1 |    1 | John Smith
  3 | Movie 3 |           2 |    2 | Jane Doe
  4 | Movie 4 |        NULL | NULL | NULL
  5 | Movie 5 |        NULL | NULL | NULL
(5 rows)

Olhando para os resultados,  podemos ver o porquê de esse tipo de join ser útil para uma busca que "traga tudo disto e, caso exista, um pouco daquilo".

RIGHT JOIN

A RIGHT JOIN funciona exatamente como a LEFT JOIN – exceto que as regras agora são inversas para a ordem das tabelas.

Em uma right join, todas as linhas da tabela da direita são retornadas. A tabela da esquerda é retornada com base na condição da join.

Vamos usar a mesma query que vimos acima e substituir a LEFT JOIN por RIGHT JOIN:

SELECT *
FROM movies
RIGHT JOIN directors
  ON directors.id = movies.director_id;

  id  |  name   | director_id | id |     name
------+---------+-------------+----+--------------
    1 | Movie 1 |           1 |  1 | John Smith
    2 | Movie 2 |           1 |  1 | John Smith
    3 | Movie 3 |           2 |  2 | Jane Doe
 NULL | NULL    |        NULL |  5 | Bree Jensen
 NULL | NULL    |        NULL |  4 | Bev Scott
 NULL | NULL    |        NULL |  3 | Xavier Wills
(6 rows)

Nosso resultado agora são todas as linhas de directors e, caso existam, os dados de movies.

Tudo  o que fizemos foi trocar qual tabela é considerada primária – a tabela da qual queremos ver todos os dados sem considerar se temos dados associados.

LEFT JOIN/RIGHT JOIN na produção de aplicações

Na criação de uma aplicação, eu utilizo apenas LEFT JOIN e nunca uso RIGHT JOIN.

Eu faço isso porque, na minha opinião, um LEFT JOIN torna a query muito mais fácil de ser lida e entendida.

Quando estou escrevendo uma query, gosto de começar a pensar em um resultado "base", digamos todos os dados de filmes, e então trazer (ou retirar) os grupos de informações dessa base.

Como eu gosto de começar com uma base, a LEFT JOIN se encaixa bem nessa linha de raciocínio. Eu quero todas as linhas da minha tabela base (a tabela da esquerda) e, condicionalmente, as da tabela da direita.

Na prática, eu não acho que já tenha visto uma RIGHT JOIN em uma aplicação em produção. Entenda, não tem nada de errado com uma RIGHT JOIN - eu apenas acho que deixa a query mais difícil de entender.

Reescrevendo o RIGHT JOIN

Se quisermos virar nosso cenário acima, ao invés de retornar todos os diretores e condicionalmente seus filmes, podemos facilmente reescrever a RIGHT JOIN para uma LEFT JOIN.

Tudo que precisamos fazer é trocar a ordem das tabelas da query e mudar a palavra RIGHT para LEFT:

SELECT *
FROM directors
LEFT JOIN movies
  ON movies.director_id = directors.id;

Observação: eu gosto de colocar a tabela que será adicionada (a da direita– no exemplo acima, de movies) primeiro na condição da join (ON movies.director_id = ...) – mas essa é apenas minha preferencia pessoal.

Como filtrar dados com LEFT JOIN

Existem dois casos de uso para a LEFT JOIN (ou a RIGHT JOIN).

O primeiro, nós já vimos: retornar todas as linhas de uma tabela e, condicionalmente, as da outra.

O segundo caso de uso é para retornar as linhas da primeira tabela onde os dados da segunda tabela não estão presentes.

O cenário tem essa aparência: encontre todos os diretores que não pertencem a nenhum filme.

Para fazer isso, vamos começar com uma LEFT JOIN e nossa tabela directors será a primária ou a tabela da esquerda:

SELECT *
FROM directors
LEFT JOIN movies
  ON movies.director_id = directors.id;

Para um diretor que não pertence a nenhum filme, as colunas da tabela movies são NULL:

 id |     name     |  id  |  name   | director_id
----+--------------+------+---------+-------------
  1 | John Smith   |    1 | Movie 1 |           1
  1 | John Smith   |    2 | Movie 2 |           1
  2 | Jane Doe     |    3 | Movie 3 |           2
  5 | Bree Jensen  | NULL | NULL    |        NULL
  4 | Bev Scott    | NULL | NULL    |        NULL
  3 | Xavier Wills | NULL | NULL    |        NULL
(6 rows)

No nosso exemplo, os diretores com ID 3, 4 e 5 não pertencem a nenhum filme.

Para filtrar nosso resultado para apenas essas linhas, podemos adicionar uma cláusula WHERE que retornará apenas os filmes onde os dados são NULL:

SELECT *
FROM directors
LEFT JOIN movies
  ON movies.director_id = directors.id
WHERE movies.id IS NULL;

 id |     name     |  id  | name | director_id
----+--------------+------+------+-------------
  5 | Bree Jensen  | NULL | NULL |        NULL
  4 | Bev Scott    | NULL | NULL |        NULL
  3 | Xavier Wills | NULL | NULL |        NULL
(3 rows)

E aqui estão nossos três filmes sem diretor!

É comum usar a coluna id da tabela para filtrar os dados (WHERE movies.id IS NULL), mas todas as colunas da tabela movies são NULL - nesse caso, qualquer uma funcionaria.

Já que sabemos que todas as colunas de movies seriam NULL. Na query acima, poderíamos escrever apenas SELECT directors.* ao invés de SELECT * para retornar todas as informações dos diretores.

Usando a LEFT JOIN para encontrar dados correspondentes

Na nossa query anterior, encontramos diretores que não pertenciam a nenhum filme.

Usando a mesma estrutura, podemos encontrar os diretores que pertencem a algum filme mudando apenas a nossa condição WHERE para buscar as linhas onde os dados do filme não são NULL:

SELECT *
FROM directors
LEFT JOIN movies
  ON movies.director_id = directors.id
WHERE movies.id IS NOT NULL;

 id |    name    | id |  name   | director_id
----+------------+----+---------+-------------
  1 | John Smith |  1 | Movie 1 |           1
  1 | John Smith |  2 | Movie 2 |           1
  2 | Jane Doe   |  3 | Movie 3 |           2
(3 rows)

Pode até parecer prático, mas, na verdade, nós implementamos novamente a INNER JOIN!

Múltiplas joins

Nós já vimos como combinar duas tabelas, mas e se quisermos fazer múltiplas joins em uma linha?

Na verdade, é bastante simples, mas para ilustrar isso vamos precisar de uma terceira tabela: tickets.

Essa tabela representará os ingressos vendidos por filme:

CREATE TABLE tickets(
  id SERIAL PRIMARY KEY,
  movie_id INTEGER REFERENCES movies NOT NULL
);

INSERT INTO tickets(movie_id) VALUES (1), (1), (3);

A tabela tickets  tem apenas um id e a referência a um filme: movie_id.

Nós também inserimos dois ingressos vendidos para o filme ID 1, e um ingresso vendido para o filme ID 3.

Agora, vamos combinar directors com movies — e, então, movies com tickets!

SELECT *
FROM directors
INNER JOIN movies
  ON movies.director_id = directors.id
INNER JOIN tickets
  ON tickets.movie_id = movies.id;

Já que ambas são inner joins, a ordem em que nós escrevemos a join não importa. Poderíamos ter começado com tickets e, então, adicionado movies e finalizado com a adição de directors.

Novamente, tudo se resume a forma na qual você está tentando consultar e o que faz a busca mais compreensível.

No nosso resultado, vamos perceber que reduzimos ainda mais as linhas retornadas:

 id |    name    | id |  name   | director_id | id | movie_id
----+------------+----+---------+-------------+----+----------
  1 | John Smith |  1 | Movie 1 |           1 |  1 |        1
  1 | John Smith |  1 | Movie 1 |           1 |  2 |        1
  2 | Jane Doe   |  3 | Movie 3 |           2 |  3 |        3
(3 rows)

Isso faz sentido porque adicionamos outra INNER JOIN. Na prática, ela adiciona uma outra condicional do tipo "AND" na nossa busca.

Nossa query, essencialmente, diz: "retorne todos os diretores que pertencem a filmes que também tenham ingressos vendidos"

Se, ao invés disso, quiséssemos encontrar diretores que pertencem a filmes que talvez não tenham ingressos vendidos ainda, poderíamos substituir nossa INNER JOIN por uma LEFT JOIN:

SELECT *
FROM directors
JOIN movies
  ON movies.director_id = directors.id
LEFT JOIN tickets
  ON tickets.movie_id = movies.id;

Podemos ver que Movie 2 agora volta ao nosso conjunto de resultados:

 id |    name    | id |  name   | director_id |  id  | movie_id
----+------------+----+---------+-------------+------+----------
  1 | John Smith |  1 | Movie 1 |           1 |    1 |        1
  1 | John Smith |  1 | Movie 1 |           1 |    2 |        1
  2 | Jane Doe   |  3 | Movie 3 |           2 |    3 |        3
  1 | John Smith |  2 | Movie 2 |           1 | NULL |     NULL
(4 rows)

Esse filme ainda não teve nenhum ingresso vendido, mas foi excluído anteriormente dos resultados por conta da INNER JOIN.

Vou deixar isso como um Exercício para o Leitor™. Como você encontraria os diretores que pertencem a algum filme e não tem nenhum ingresso vendido?

A ordem de execução das joins

No fim, realmente não ligamos para a ordem como as joins são executadas.

Uma das diferenças principais entre o SQL e outras linguagens de programação modernas é o fato de o SQL ser uma linguagem declarativa.

Isso significa que especificamos o que queremos, mas não especificamos os detalhes da execução – esses detalhes são deixados para o planejador de buscas da base de dados. Especificamos as joins que queremos e as condições e o planejador do banco de dados cuida do resto.

Na realidade, porém, o banco de dados não está juntando três tabelas ao mesmo tempo. Ao invés disso, ele provavelmente unirá as duas primeiras tabelas em um resultado intermediário e, então, usará esse resultado para unir a terceira tabela.

(Observação: essa é uma explicação bastante simplificada.)

Então, como estamos trabalhando em buscas com várias joins, podemos simplesmente pensar que são joins entre duas tabelas – mesmo que uma delas seja bastante grande.

Joins com condições extras

O último tópico de que trataremos são as joins com condições extras.

Da mesma forma que ocorre com a cláusula WHERE, podemos adicionar quantas condicionais quisermos as condições da join.

Por exemplo, se quisermos encontrar os filmes cujos diretores não tenham o nome "John Smith", podemos adicionar uma condição extra à nossa join com um AND:

SELECT *
FROM movies
INNER JOIN directors
  ON directors.id = movies.director_id
  AND directors.name <> 'John Smith';

Podemos usar qualquer operador onde colocaríamos uma cláusula WHERE nessa condicional da join.

Podemos também ter o mesmo resultado dessa busca se colocarmos a condicional com a cláusula WHERE :

SELECT *
FROM movies
INNER JOIN directors
  ON directors.id = movies.director_id
WHERE directors.name <> 'John Smith';

Existem algumas diferenças sutis acontecendo por trás dos panos aqui, mas, para o propósito deste artigo, o resultado é o mesmo.

(Se você não é familiarizado com todas as outras formas de filtrar uma busca em SQL, dê uma olhada neste artigo comentado anteriormente - em inglês.)

A realidade sobre escrever queries e joins

Na realidade, eu apenas uso joins de três formas diferentes:

INNER JOIN

O primeiro caso de uso é o de registros onde o relacionamento entre duas tabelas existe. Isso é coberto totalmente pelo INNER JOIN.

Essas são situações como encontrar "filmes que tenham diretores" ou "usuários que tenham postagens".

LEFT JOIN

O segundo caso de uso é o de registros de uma tabela – e se o relacionamento existir– e registros de uma segunda tabela. Esse caso é coberto pelo LEFT JOIN.

Esse caso é o de situações como "filmes com diretores caso existam" ou "usuários com postagens caso haja alguma"

Exclusão do  LEFT JOIN

O terceiro caso de uso mais comum é o nosso segundo caso com uma LEFT JOIN: encontrar os registros de uma tabela que não tenham relação com a segunda tabela.

São situações como "filmes sem diretores" ou "usuários sem postagens".

Dois tipos de joins muito úteis.

Eu não acho que já tenha usado uma FULL OUTER JOIN ou uma  RIGHT JOIN em uma aplicação em produção. Esses tipos de caso de uso não costumam aparecer frequentemente ou a query pode ser escrita de forma mais simples (no caso do RIGHT JOIN).

Ocasionalmente, eu já usei o CROSS JOIN para algumas coisas como espalhar registros em um intervalo de datas (como vimos no começo do artigo), mas esse cenário também não costuma aparecer com frequência.

Então, boas notícias! Existem realmente dois tipos de joins que você precisa saber para 99,9% dos casos que aparecerão: INNER JOIN e LEFT JOIN!

Se você gostou deste artigo, pode seguir o autor no Twitter, onde ele fala sobre assuntos relacionados a bancos de dados e a desenvolvimento.

Obrigado pela leitura!

Um último conselho para o final: a maioria dos bancos de dados permitirá que você escreva apenas JOIN no lugar do INNER JOIN – isso poupará um pouco de digitação. :)