Conversão de subconsultas em adesões

Nem todas as consultas são iguais, especialmente em termos de desempenho. Neste artigo, veremos como você pode converter as subconsultas SQL em junções para maior eficiência.

Quando devo usar as subconsultas SQL?

Ótima pergunta! Infelizmente, não há uma resposta concreta. Os iniciantes em SQL tendem a usar em excesso as subconsultas. Normalmente, quando descobrem que a construção de SQL funciona em uma situação, eles tentam aplicar essa mesma abordagem a outras situações. É natural.

No entanto, em alguns casos, uma subconsulta pode ser substituída por um JOIN mais eficiente. Se você pode evitar uma subconsulta e substituí-la por uma cláusula JOIN, você deve fazê-lo sem hesitação. Mas, claro, em alguns casos, o uso de uma subconsulta é a única maneira de resolver uma questão de dados. Neste artigo, vou mostrar exemplos de ambos os casos: quando uma subquisição é obrigatória e quando uma subquisição deve ser evitada e substituída por uma JUNTA.

Os dados

Antes de chegarmos aos exemplos, vamos analisar brevemente o banco de dados de amostras que utilizaremos. O banco de dados tem duas tabelas que representam as estatísticas de produção de uma empresa fictícia de cultivo de maçãs chamada EverRed. A empresa tem três fazendas.

A primeira tabela é current_year_productionA seção "Maçãs", que contém informações sobre o número de maçãs produzidas no ano corrente por cada fazenda, assim como a área e o número de árvores em cada fazenda. A segunda tabela, production_historyA empresa armazena informações sobre a produção passada de cada fazenda. Abaixo estão alguns exemplos de dados destas duas tabelas.

Exemplo: substituição de uma subquadra por uma JOIN

Suponha que você seja um analista de dados SQL trabalhando na EverRed. O proprietário da empresa quer que você obtenha os nomes das fazendas onde a empresa está produzindo mais maçãs no ano corrente do que no ano anterior (2017).

Solução usando uma subconsulta:

SELECT farm_name, 
FROM current_year_production CYP
WHERE production_in_kg > (
  SELECT production_in_kg 
  FROM production_history PH  
  WHERE PH.farm_id = CYP.farm_id
  AND year = 2017
) 

Solução usando uma cláusula JOIN:

SELECT farm_name, 
FROM current_year_production CYP 
JOIN production_history PH
  ON PH.farm_id = CYP.farm_id 
WHERE PH.year = 2017
  AND CYP.production_in_kg > PH.production_in_kg 

A diferença entre estas duas abordagens está no desempenho. Enquanto a cláusula JOIN no segundo exemplo precisa ser executada apenas uma vez, a subquisição no primeiro exemplo será executada uma vez por fazenda. Neste caso, temos apenas três fazendas, portanto, a diferença é insignificante. Mas e se você trabalhasse para uma empresa maior que tem 10.000 fazendas globais? A subquisição precisaria ser executada 10.000 vezes. Claramente, uma subconsulta é ineficiente para nossos propósitos aqui.

Além disso, em um banco de dados de teste com apenas algumas fazendas, ambas as consultas são executadas com um tempo de resposta aceitável; entretanto, quando passamos para um banco de dados produtivo (onde o volume de dados geralmente é muito maior), o tempo de resposta da abordagem de subquisição aumentará significativamente, enquanto o tempo de resposta da abordagem de JUNTOS permanecerá estável.

O resultado de ambas as consultas equivalentes anteriores é:

Exemplo: quando as subconsultas são o único caminho a ser seguido

Vamos agora supor que o proprietário da empresa, após ler os resultados que você entregou na consulta anterior, lhe peça para obter os nomes das fazendas que estão produzindo mais maçãs por metro quadrado este ano do que a média histórica. Isto parece complexo, mas é mais fácil do que parece.

Solução usando uma subconsulta:

SELECT
  farm_name, 
  production_in_kg / area AS "production_per_meter"
FROM Current_year_production 
WHERE production_in_kg / area > (
  SELECT AVG(PH.production_in_kg / CYP.area) 
  FROM production_history PH 
  JOIN Current_year_production CYP  
    ON PH.farm_id = CYP.farm_id 
) 

Não podemos substituir esta subconsulta por um JOIN porque não temos uma tabela com a média previamente calculada. Em outras palavras, precisamos primeiro calcular a média histórica. E para fazer isso, precisamos de um GROUP BY, que pode quebrar a relação um-para-um necessária para uma JUNTA. Outro ponto a ser observado é que a métrica "maçãs por metro quadrado" é obtida com a seguinte expressão:

	production_in_kg / area

Utilizamos a métrica "maçãs por metro quadrado" porque precisamos de alguma forma de comparar a produtividade das diferentes fazendas e classificá-las. O total de "produção_em_kg" de uma fazenda não é um valor comparável - porque é provável, por exemplo, que a maior fazenda tenha uma melhor production_in_kg. Portanto, dividimos a "produção_em_kg" pela área de cada fazenda para padronizar os valores e criar uma métrica comparável.

Descobrimos que a produção média histórica por metro quadrado é de 0,42. Então, o resultado da consulta anterior é:

Exemplo: quando as JUNTAS e subconsultas são igualmente eficientes

Como última pergunta de dados, vamos tentar obter os anos em que a empresa produziu menos maçãs do que no ano corrente. Podemos escrever esta pergunta usando duas abordagens diferentes.

Solução usando uma subconsulta:

SELECT year, sum(production_in_kg) 
FROM production_history PH
GROUP BY year
HAVING sum(production_in_kg) < (
  SELECT sum(production_in_kg)
  FROM current_year_production
)

Solução usando uma cláusula JOIN:

SELECT year, sum(PH.production_in_kg) 
FROM production_history PH 
JOIN current_year_production CYP
  ON PH.farm_id = CYP.farm_id
GROUP BY year
HAVING sum(PH.production_in_kg) < sum(CYP.production_in_kg)

Você pode ver que ambas as consultas são realmente similares; a principal diferença está na cláusula JOIN e na subconsulta. Neste caso, ambas as consultas são igualmente eficientes - a subconsulta é executada uma vez na cláusula HAVING, portanto não há problema de desempenho.

Tente você mesmo!

Para concluir, é importante notar que as subconsultas e as JUNTAS são recursos realmente importantes para um desenvolvedor SQL. Vimos exemplos onde podemos substituir uma subconsulta por um JOIN e exemplos onde não podemos fazer tal substituição. E às vezes, as subconsultas e os JOINs são igualmente eficientes.

Mas como você sabe quando usar uma subconsulta e quando usar um JOIN? Com toda honestidade, a única maneira de desenvolver sua intuição é resolver regularmente exercícios SQL. Se você está procurando aprimorar suas habilidades SQL, nossos Curso de Práticas em SQL oferece 88 problemas práticos para uma revisão abrangente.

Fique atento a mais artigos!