Subquery vs. JOIN

Um dos desafios na escrita de consultas SQL é escolher entre usar uma subconsulta ou um JOIN. Há muitas situações em que um JOIN é a melhor solução, e há outras em que uma subconsulta é melhor. Vamos considerar este tópico em detalhes.

As subconsultas são usadas em consultas SQL complexas. Normalmente, há uma consulta principal externa e uma ou mais subconsultas aninhadas dentro da consulta externa.

As subconsultas podem ser simples ou correlacionadas. As subconsultas simples não dependem das colunas na consulta externa, enquanto as subconsultas correlacionadas se referem aos dados da consulta externa.

Você pode aprender sobre subconsultas na seção Subconsultas no curso interativo "SQL para Iniciantes" ou praticar a escrita de subconsultas na seção Subconsultas do curso "Curso de Práticas em SQL ". Ou simplesmente leia o artigo "SQL Subqueries " de Maria Alcaraz.

A cláusula JOIN não contém consultas adicionais. Ela conecta duas ou mais tabelas e seleciona dados delas em um único conjunto de resultados. É mais freqüentemente usada para unir tabelas com chaves primárias e estrangeiras. Você pode praticar Cláusulas JOIN em SQL em nosso curso interativo Cláusulas JOIN em SQL curso. Contém mais de 90 exercícios para revisar e praticar diferentes tipos de JOINs. Você também pode ler mais sobre JOINs no artigo "Como Praticar Cláusulas JOIN em SQL" de Emil Drkušić.

As subconsultas e JOINs podem ser usadas em uma consulta complexa para selecionar dados de várias tabelas, mas o fazem de maneiras diferentes. s vezes você tem a escolha de uma das duas, mas há casos em que uma subconsulta é a única opção real. Descreveremos os vários cenários abaixo.

Considere duas tabelas simples, product e saleque usaremos em nossos exemplos.

Aqui está o product mesa.

Esta tabela contém as seguintes colunas:

• id: o identificador do produto.
• name: : o nome do produto.
• cost: : o custo do produto.
• year: o ano em que o produto foi fabricado.
• city: a cidade em que o produto foi fabricado.

E a outra mesa, sale:

que tem as seguintes colunas:

• id: o identificador da venda.
• product_id: : o identificador do produto vendido.
• price: : o preço de venda.
• year: : o ano em que o produto foi vendido.
• city: : a cidade em que o produto foi vendido.

Utilizaremos estas duas tabelas para escrever consultas complexas com subconsultas e JOINs.

Quando reescrever as subconsultas com os JOINs

Os iniciantes em SQL frequentemente usam subconsultas quando os mesmos resultados podem ser alcançados com JOINs. Enquanto as subconsultas podem ser mais fáceis de entender e usar para muitos usuários de SQL, JOINs são frequentemente mais eficientes. JOINs também são mais fáceis de ler à medida que as consultas se tornam mais complexas. Portanto, vamos nos concentrar primeiro em quando você pode substituir uma subconsulta por um JOIN para uma melhor eficiência e legibilidade.

Subconsulta Scalar

O primeiro caso é o da subquisição escalar. Uma subconsulta escalar retorna um único valor (uma coluna e uma linha) para ser usado pela consulta externa. Aqui está um exemplo.

Suponhamos que precisamos dos nomes e dos custos dos produtos que foram vendidos por $2.000.

Vejamos o código com uma subconsulta:

SELECT name, cost 
FROM product
WHERE id=(SELECT product_id 
  FROM sale 
    WHERE price=2000 
    AND product_id=product.id
  );

e o resultado:

A consulta externa seleciona os nomes (name) e o custo (cost) dos produtos. Como não queremos todos os produtos, usamos uma cláusula WHERE para filtrar as linhas para os IDs dos produtos devolvidos pela subconsulta.

Agora vamos olhar para a subquisição. O sale A tabela contém os registros de vendas dos produtos. A subconsulta primeiro filtra os registros somente para aqueles com preço de venda igual a $2.000 (price=2000). Em seguida, utiliza as identificações dos produtos (product_id) nas vendas selecionadas para identificar os registros do product tabela (product_id=product.id). Esta é uma subconsulta correlacionada, uma vez que a segunda condição na subconsulta faz referência a uma coluna na consulta externa. Apenas dois produtos foram vendidos a $2.000: a poltrona e a mesa de TV.

Esta consulta não é muito eficiente. Como devemos modificá-la?

Podemos construir uma estrutura JOIN e obter o mesmo resultado. Veja a consulta com um JOIN:

SELECT p.name, p.cost 
FROM product p 
JOIN sale s ON p.id=s.product_id
WHERE s.price=2000;

Nesta consulta, nós conectamos as duas tabelas product e sale com um operador JOIN. Na condição JOIN, os registros do product tabela estão vinculados aos registros do sale tabela através das identificações dos produtos. No final, as linhas são filtradas por uma cláusula WHERE para selecionar o registro quando o preço de venda do produto for igual a $2.000.

Subconsulta Dentro da Cláusula IN

Outra subquisição que é facilmente substituída por um JOIN é a utilizada em um operador IN. Neste caso, a subconsulta retorna para a consulta externa uma lista de valores.

Digamos que queremos obter os nomes e os custos dos produtos vendidos em nosso exemplo.

SELECT name, cost 
FROM product 
WHERE id IN (SELECT product_id FROM sale);

A consulta externa seleciona os nomes e os custos dos produtos; em seguida, filtra os registros cujas identificações de produto pertencem à lista devolvida pela subconsulta. A subconsulta seleciona os IDs dos produtos da sale (SELECT product_id FROM sale), de modo que somente os produtos vendidos são devolvidos por esta consulta no conjunto de resultados finais, como este:

Há mais produtos na tabela de product mesa, mas apenas quatro deles foram vendidos.

A consulta abaixo retorna o mesmo resultado, utilizando um JOIN:

SELECT DISTINCT p.name, p.cost 
FROM product p 
JOIN sale s ON s.product_id=p.id;

Torna-se uma consulta muito simples. Ela conecta as duas tabelas por identificação do produto e seleciona os nomes e os custos desses produtos. É uma INNER JOIN, portanto, se um produto não tem sua identificação no sale mesa, não será devolvido.

Observe que também usamos a palavra-chave DISTINCT para remover registros duplicados. Isto é freqüentemente necessário se você transformar subconsultas com um IN ou um NOT IN em JOINs.

Deseja saber mais sobre as subconsultas SQL com o operador IN? Assista a um episódio de nossa série We Learn SQL no Youtube. Lembre-se de assinar o nosso canal.

Subconsulta na cláusula NOT IN

Esta é exatamente como a situação anterior, mas aqui a subconsulta é usada em um operador NOT IN. Queremos selecionar os nomes e os custos dos produtos que não foram vendidos.

Abaixo está um exemplo com uma subconsulta dentro do operador NOT IN:

SELECT name, cost 
FROM product 
WHERE id NOT IN (SELECT product_id FROM sale);

Os resultados:

A subconsulta devolve os IDs dos produtos da sale (os produtos vendidos) e os compara com os IDs do produto na consulta externa. Se um registro na consulta externa não encontrar seu ID de produto na lista devolvida pela subconsulta, o registro é devolvido.

Como se reescreve esta subconsulta com um JOIN? Você pode fazê-lo assim:

SELECT DISTINCT p.name, p.cost
FROM product p 
LEFT JOIN sale s ON s.product_id=p.id 
WHERE s.product_id IS NULL;

Esta consulta conecta as duas tabelas product e sale pelos IDs dos produtos. Você também deve usar a palavra-chave DISTINCT, como fizemos quando transformamos a subconsulta anterior com um IN em um JOIN.

Observe que ao reescrever a subconsulta no NOT IN, usamos um LEFT JOIN e um WHERE. Dessa forma, você começa com todos os produtos, inclusive aqueles não vendidos, e depois seleciona apenas os registros que estão NULL na coluna product_id. O NULL denota que o produto não foi vendido.

Subconsultas relacionadas em EXISTÊNCIAS e em NÃO EXISTÊNCIAS

As subconsultas em um EXISTS ou em um NOT EXISTS também são fáceis de reescrever com JOINs.

A consulta abaixo usa uma subconsulta para obter os detalhes sobre os produtos que não foram vendidos em 2020.

SELECT name, cost, city
FROM product  
WHERE NOT EXISTS ( SELECT id  
  FROM sale WHERE year=2020 AND product_id=product.id );

O resultado:

Para cada produto na consulta externa, a subconsulta seleciona os registros cujo ano de venda é 2020 (year=2020). Se não houver registros para um determinado produto na subconsulta, a cláusula NOT EXISTS retorna verdadeiro.

O conjunto de resultados contém os produtos com o ano de venda diferente de 2020, assim como os produtos sem nenhum registro no sale mesa. Você pode reescrever a mesma consulta usando um JOIN:

SELECT p.name, p.cost, p.city FROM product p 
LEFT JOIN  sale s ON s.product_id=p.id 
WHERE s.year!=2020 OR s.year IS NULL;

Aqui, nós conectamos a product mesa com o sale tabela através de um operador LEFT JOIN. Isto nos permite incluir os produtos que nunca foram vendidos no conjunto de resultados. A cláusula WHERE filtra os registros, selecionando os produtos sem registros no sale tabela (s.year IS NULL) assim como os produtos com o ano de venda que não seja 2020 (s.year!=2020).

Quando não é possível substituir uma subconsulta por um JOIN

JOINs podem ser eficientes, mas há situações que exigem uma subquisição e não uma JOIN. Abaixo estão algumas dessas situações.

Subquery in FROM With a GROUP BY

O primeiro deles é uma subconsulta em uma cláusula FROM usando um GROUP BY para calcular valores agregados.

Vejamos o exemplo a seguir:

SELECT city, sum_price  
 FROM  
(
  SELECT city, SUM(price) AS sum_price FROM sale 
  GROUP BY city 
) AS s
WHERE sum_price < 2100;

e o resultado:

Aqui, a subconsulta seleciona as cidades e calcula a soma dos preços de venda por cidade. A soma de todos os preços de venda em cada cidade a partir do sale A tabela é calculada pela função agregada SUM(). Usando os resultados da subconsulta, a consulta externa seleciona apenas as cidades cujo preço total de venda é inferior a $2.100 (WHERE sum_price < 2100). Você deve lembrar das lições anteriores como usar pseudônimos para subconsultas e como selecionar um valor agregado em uma consulta externa.

Subquery Retornando um Valor Agregado em uma Cláusula WHERE

Outra situação em que você não pode reescrever uma estrutura de subconsultas com um JOIN é um valor agregado sendo comparado em uma cláusula WHERE. Vejam este exemplo:

SELECT name FROM product
WHERE cost<(SELECT AVG(price) from sale);

O resultado:

Esta consulta recupera os nomes dos produtos cujos custos são mais baixos do que o preço médio de venda. O preço médio de venda é calculado com a ajuda da função agregada AVG() e é retornado pela subconsulta. O custo de cada produto é comparado a este valor na consulta externa.

Subconsulta em uma cláusula ALL

Ainda outra situação é uma subquisição com uma cláusula ALL.

SELECT name FROM product
WHERE cost > ALL(SELECT price from sale);

A subconsulta retorna todos os preços de venda no sale mesa. A consulta externa retorna o nome do produto com o preço de venda mais alto do que o custo.

O resultado:

Quando usar uma subconsulta vs. uma junção

Revisamos alguns usos comuns de subconsultas e as situações em que algumas subconsultas poderiam ser reescritas com JOINs. Um JOIN é mais eficiente na maioria dos casos, mas há casos em que não é possível construir outras construções além de uma subconsulta. Enquanto as subconsultas podem ser mais legíveis para iniciantes, JOINs são mais legíveis para os codificadores SQL experientes, à medida que as consultas se tornam mais complexas. É uma boa prática evitar múltiplos níveis de subconsultas aninhadas, já que elas não são facilmente legíveis e não têm bom desempenho. Em geral, é melhor escrever uma consulta com JOINs do que com subconsultas, se possível, especialmente se as subconsultas estiverem correlacionadas.

Se você estiver interessado em aprender mais ou se quiser praticar suas habilidades, verifique as seções de subconsultas no curso "SQL para Iniciantes" ou no curso "Curso de Práticas em SQL".