Qual é a diferença entre ON e WHERE em SQL JOINs?

Quando você une tabelas em SQL, você pode ter condições em uma cláusula ON e em uma cláusula WHERE. Muitos ficam confusos com a diferença entre elas. Neste artigo, discutiremos este tópico primeiro lembrando-lhe o propósito das cláusulas ON e WHERE e depois demonstrando com exemplos que tipos de condições devem estar em cada uma dessas cláusulas.

Tanto a cláusula ON quanto a cláusula WHERE podem especificar as condições. Mas há alguma diferença entre elas? Em caso positivo, onde você deve especificar quais condições em sua consulta SQL? Vamos descobrir juntos!

ON vs. WHERE Condições

O objetivo da cláusula ON é especificar as condições de adesão, ou seja, definir como as tabelas devem ser unidas. Especificamente, você define como os registros devem ser emparelhados.

Em contraste, a finalidade da cláusulaWHERE é especificar as condições de filtragem, ou seja, definir quais linhas devem ser mantidas no conjunto de resultados.

Vejamos um exemplo para entender a diferença. Temos as seguintes duas tabelas que (1) listam os usuários (a tabela users) de nosso site de aluguel e (2) listar as casas (a tabela houses) disponível para aluguel.

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
JOIN houses h
ON u.id = h.owner_id
WHERE u.registration_date < '2020-01-01';

Observe que temos condições tanto na cláusula ON quanto na cláusula WHERE:

• Com a condição ON, especificamos que as tabelas devem ser unidas pela correspondência entre a coluna id na tabela de usuários e a coluna owner_id nas casas.
• Com a condição WHERE, filtramos o resultado definido, mantendo apenas os usuários que se registraram antes de 1 de janeiro de 2020.

Assim, utilizamos as condições ON e WHERE de acordo com sua finalidade, resultando em uma consulta SQL clara e legível.

Aqui está o resultado definido:

Não tem certeza de como o JOIN funciona em nossa consulta SQL? Pratique unindo tabelas com este curso interativo de SQL JOINs.

ON e WHERE Condições em INNER JOINs

No exemplo acima, podemos ver como as condições ON e WHERE são utilizadas de acordo com sua respectiva finalidade e prática comum.

Entretanto, é útil saber que, para (INNER) JOINs, pode-se especificar tanto a condição JOIN quanto a condição de filtragem com uma cláusula ON. Por exemplo, podemos obter o mesmo resultado que o acima com a seguinte consulta SQL:

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
JOIN houses h
ON u.id = h.owner_id AND u.registration_date < '2020-01-01';

Esta consulta é executada da mesma forma. Dito isto, não recomendo misturar a condição de join e a condição de filtragem na mesma cláusula. Se você comparar as duas consultas, você verá que a primeira é mais legível:

• É mais fácil seguir a primeira consulta: primeiro, você une as tabelas por uma determinada condição, depois filtra o resultado por uma condição diferente.
• A intenção de toda a consulta é mais clara para o leitor externo quando as condições são separadas, seguindo as regras.

ON e ON ON ON ON e WHERE Condições em OUTER JOINs

Quando se trata de OUTER JOINs (ou seja, LEFT JOIN, RIGHT JOIN, e FULL JOIN), é crucial utilizar as condições de ON e WHERE da maneira como elas são destinadas. Caso contrário, você obterá resultados errados. Vamos ver com um exemplo.

Mais uma vez, queremos obter a lista de usuários que se registraram antes de 1º de janeiro de 2020, juntamente com suas respectivas casas. Desta vez, porém, queremos manter todos os usuários, inclusive aqueles que não têm casas registradas em nosso site de locação. Assim, vamos fazer um LEFT JOIN ao invés de um JOIN (ou seja, um INNER JOIN).

Veremos se existem diferenças entre especificar a condição de filtragem na cláusula ON e especificá-la na cláusula WHERE. Se seguirmos as regras e usarmos as condições como pretendido, teremos a seguinte consulta:

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
LEFT JOIN houses h
ON u.id = h.owner_id
WHERE u.registration_date < '2020-01-01';

O resultado parece bom. Temos todos os usuários que temos em nosso exemplo inicial. Além disso, temos mais dois usuários que não têm casas correspondentes em nosso site, mas foram incluídos no conjunto de resultados devido ao LEFT JOIN. Note que ambos se registraram antes de 1º de janeiro de 2020, conforme especificado em nossa condição de filtragem.

Obteremos o mesmo resultado se misturarmos a condição de junção e a condição de filtragem na cláusula ON? Vamos descobrir:

SELECT u.id, u.name, u.registration_date, h.address, h.city
FROM users u
LEFT JOIN houses h
ON u.id = h.owner_id AND u.registration_date < '2020-01-01';

Como você pode ver, os resultados são diferentes. Temos todos os usuários incluídos, mesmo aqueles que se registraram em 2020 ou 2021. Isto porque o LEFT JOIN mantém todos os registros da tabela da esquerda mesmo quando a lógica do ON falha. Portanto, neste exemplo, especificar a condição de filtragem na cláusula ON não funciona para nós. Para obter o resultado correto, precisamos especificar as condições conforme pretendido.

Curiosamente, há situações em que a condição WHERE pode "cancelar" a intenção de um OUTER JOIN. Como exemplo, digamos que queremos listar todos os usuários com suas casas correspondentes, mas somente se as casas tiverem 3 ou mais quartos.

Como queremos manter todos os usuários, usaremos um OUTER JOIN, especificamente um LEFT JOIN. Nossa exigência quanto ao número de quartos é claramente uma condição de filtragem. Portanto, vamos incluí-la na cláusula WHERE. Aqui está nossa consulta SQL com as condições especificadas como pretendido:

SELECT u.id, u.name, h.address, h.city, h.bedrooms
FROM users u
LEFT JOIN houses h
ON u.id = h.owner_id
WHERE h.bedrooms > 2;

Não parece correto, não é mesmo? O resultado parece que usamos um INNER JOIN em vez de um LEFT JOIN. Os usuários sem casas não são incluídos na tabela resultante, pois eles têm NULL na coluna de quartos quando as mesas são unidas. Como os valores de NULL são considerados inferiores a 0, as linhas correspondentes são removidas quando aplicamos a condição de filtragem - o número de dormitórios superior a 2.

Há duas soluções possíveis para este problema:

• Adicionar outra condição de filtragem à cláusula WHERE, como os dormitórios é NULL:
  

  SELECT u.id, u.name, h.address, h.city, h.bedrooms
  FROM users u
  LEFT JOIN houses h
  ON u.id = h.owner_id
  WHERE h.bedrooms > 2 OR h.bedrooms is NULL;
  

• Movendo a condição de filtragem para a cláusula ON:
  

  SELECT u.id, u.name, h.address, h.city, h.bedrooms
  FROM users u
  LEFT JOIN houses h
  ON u.id = h.owner_id AND h.bedrooms > 2;
  

Qualquer uma destas consultas nos dá o seguinte resultado:

Agora você já sabe! Em OUTER JOINs, faz uma diferença como especificamos as condições.

Vamos praticar o JOINs em SQL!

SQL JOINs não são muito difíceis de entender. Entretanto, como você pode ver nos exemplos deste artigo, há nuances que devem ser consideradas ao unir tabelas e escrever condições de união em SQL.

Se você realmente quer dominar Cláusulas JOIN em SQL, praticar com conjuntos de dados do mundo real é um fator chave de sucesso. Recomendo começar com o curso interativo SQL JOINs - ele inclui 93 desafios de codificação cobrindo os tipos mais comuns de junções como JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, e até mesmo auto junções e junções não-equi. Após este curso, você saberá como unir várias tabelas, como unir tabelas sem uma coluna comum, e como filtrar corretamente os dados com diferentes tipos de JOINs.

Para aqueles que querem experiência com ainda mais casos de uso SQL JOIN, recomendo levar o Trilha de Práticas em SQL pista. Inclui cinco cursos interativos com mais de 600 desafios de codificação, cobrindo não apenas o básico de Cláusulas JOIN em SQL mas também como filtrar o conjunto de resultados com uma cláusula WHERE, como agregar dados com GROUP BY e HAVING, e como utilizar subconsultas incluindo subconsultas correlatas. Você vai se divertir muito!

BÔNUS. Aqui estão as 10 principais perguntas da entrevista SQL JOIN com respostas.