5 Razões pelas quais você deve usar CTEs ao invés de subconsultas

Expressões de tabelas comuns, ou CTEs, foram introduzidas em SQL:1999 para lidar com casos em que a saída de uma consulta é usada dentro de outra consulta. Mas nós não tínhamos já subconsultas para isso? Neste artigo, vou demonstrar com vários exemplos porque os CTEs são melhores que as subconsultas para a estrutura e legibilidade de suas consultas SQL.

Vamos começar lembrando o que são CTEs e subconsultas e como elas diferem.

Expressões de tabelas comuns vs. subconsultas

Uma subconsulta é uma consulta aninhada dentro de outra consulta. Pode ser colocada em qualquer lugar em uma consulta, mesmo dentro de outra subconsulta. A sintaxe é muito simples - basta colocar sua subconsulta entre parênteses e inseri-la na consulta principal, onde quer que ela seja necessária. Note que as subconsultas são executadas antes da consulta principal (também chamada consulta pai ou consulta externa) para que seus resultados possam ser utilizados pela consulta principal. Muitas vezes é bastante complicado ler uma consulta SQL com múltiplas subconsultas porque, apesar de serem executadas primeiro, elas são definidas em algum lugar na consulta principal. Você pode ler mais sobre as subconsultas SQL neste guia introdutório.

Uma Expressão de Tabela Comum (CTE), também referida como uma cláusula WITH, é um conjunto temporário de resultados nomeados que você pode consultar em qualquer lugar em sua consulta. Ao contrário das subconsultas, que são inseridas exatamente onde você precisa delas, todas as CTEs são definidas antes da consulta principal e são então referenciadas na consulta usando o nome atribuído. Nos exemplos abaixo, veremos como isto melhora significativamente a estrutura e a legibilidade de uma consulta SQL. Primeiro você define todos os conjuntos de resultados temporários necessários e depois os utiliza na consulta principal, quando necessário.

Uma introdução detalhada às expressões comuns da tabela pode ser encontrada aqui. Saiba mais sobre a diferença entre CTEs SQL e subconsultas neste artigo de visão geral.

5 Razões para escolher os CTEs

Os aprendizes de SQL muitas vezes se perguntam se devem usar uma expressão de tabela comum ou uma subconsulta ao escrever uma consulta SQL complexa. Vamos analisar vários exemplos para explicar porque você deve preferir um CTE em vez de uma subconsulta. Para estes exemplos, vou usar a seguinte tabela que resume o desempenho dos funcionários de vários departamentos.

Para cada funcionário, temos sua identificação, nome completo, departamento, anos de experiência e uma pontuação de conclusão de KPI em uma escala de 0 a 100.

Vamos ver como os CTEs vs. subconsultas podem nos ajudar na análise desses dados.

5 Razões para usar os CTEs em vez de subconsultas

#1. CTEs usam Nomes Significativos

Você pode dar nomes significativos aos CTEs que especificam sua intenção e tornam a consulta mais legível. Por exemplo, digamos que queremos comparar o desempenho de cada funcionário com a média de conclusão dos KPIs em seus respectivos departamentos. Para adicionar a coluna correspondente à nossa tabela, precisamos primeiro calcular o KPI médio para cada departamento. Toda nossa consulta com uma subconsulta é a seguinte:

SELECT p.name, p.department, p.kpi, avg.average_dep_kpi
FROM performance p
JOIN 
  (SELECT department, AVG(kpi) AS average_dep_kpi
   FROM performance
   GROUP BY department) AS avg
ON p.department = avg.department;

Você pode obter a mesma saída usando uma expressão de tabela comum:

WITH avg_department_kpi AS
  (SELECT department, AVG(kpi) AS average_dep_kpi
   FROM performance
   GROUP BY department)
SELECT p.name, p.department, p.kpi, avg.average_dep_kpi
FROM performance p
JOIN avg_department_kpi avg
ON p.department = avg.department;

CTEs e subconsultas dão a mesma saída, e as consultas são mais ou menos do mesmo tamanho. Mas note que a legibilidade da consulta do CTE é melhor. O que significa uma consulta complexa pode ser óbvio para você quando estiver olhando para seu próprio código, mas pode não ser de todo claro para seus colegas. Ao ler a versão do CTE:

• Você vê a consulta na ordem em que ela será executada: primeiro a subconsulta e depois a consulta principal.
• Você pode determinar o objetivo da subconsulta com base em seu nome. Em nosso caso, avg_department_kpi refere-se ao CTE que produz o KPI médio para cada departamento.

Ao mesmo tempo, você deve ter notado que a consulta do CTE é um pouco mais longa do que a versão da subconsulta. Nem sempre é assim, especialmente quando precisamos da mesma subconsulta várias vezes em nossa consulta principal.

Se você é novo no CTE com sintaxe, você pode praticar expressões de tabela comuns em nosso curso interativo. Por enquanto, vamos passar para o segundo motivo para preferir CTEs em vez de subconsultas.

#2. Os CTEs São Reutilizáveis Dentro de uma Consulta

Ao contrário das subconsultas, você não precisa repetir uma definição de CTE cada vez que precisar dela na consulta. Você a define apenas uma vez, no início de sua consulta, e depois a referencia quando necessário.

Digamos que queremos comparar o desempenho de diferentes departamentos. Especificamente, queremos ver o KPI médio em cada departamento junto com o KPI médio mínimo e máximo entre departamentos. Poderíamos resolver este problema usando uma consulta SQL com três subconsultas:

1. Para encontrar o KPI médio de cada departamento.
2. Para encontrar o KPI médio mínimo em todos os departamentos.
3. Para encontrar a média máxima de KPI entre departamentos.

Observe que as duas últimas subconsultas precisarão ambas do resultado da primeira. Assim, se utilizarmos subconsultas, nossa solução incluirá subconsultas definidas dentro de outras subconsultas. Tudo isso parece bastante confuso:

SELECT 
  ad.department, 
  ad.average_kpi, 
  min.min_avg_kpi_department,
  max.max_avg_kpi_department
FROM
    (SELECT department, AVG(kpi) AS average_kpi
    FROM performance
    GROUP BY department) AS ad
CROSS JOIN
    (SELECT MIN (average_kpi) AS min_avg_kpi_department
     FROM
       (SELECT department, AVG(kpi) AS average_kpi
        FROM performance
        GROUP BY department) AS ad) AS min
CROSS JOIN
    (SELECT MAX (average_kpi) AS max_avg_kpi_department
     FROM
       (SELECT department, AVG(kpi) AS average_kpi
        FROM performance
        GROUP BY department) AS ad) AS max;

Em contraste, quando usamos expressões comuns de tabela, definimos nossos três CTEs no início da consulta, referenciamo-los na consulta principal quando necessário, e evitamos múltiplas subconsultas aninhadas:

WITH avg_per_department AS (
    SELECT department, AVG(kpi) AS average_kpi
    FROM performance
    GROUP BY department),

    min_kpi_department AS (
    SELECT MIN (average_kpi) AS min_avg_kpi_department
    FROM avg_per_department),

    max_kpi_department AS (
    SELECT MAX (average_kpi) AS max_avg_kpi_department
    FROM avg_per_department)
SELECT 
  ad.department, 
  ad.average_kpi, 
  min.min_avg_kpi_department,
  max.max_avg_kpi_department
FROM avg_per_department ad
CROSS JOIN min_kpi_department min
CROSS JOIN max_kpi_department max;

Neste caso, você pode observar que COM as consultas tornam seu código não apenas mais legível, mas também muito mais curto.

#3. Os CTEs dividem as computações complexas em partes

Os CTEs trazem clareza ao processo de computação. Quando são usadas subconsultas para cálculos, a consulta muitas vezes se transforma em uma confusão de subconsultas. Os CTEs, por outro lado, mostram o processo de computação com mais clareza.

Digamos que queremos saber quantos funcionários juniores e experientes estão em cada departamento. Vamos considerar os funcionários com dois ou menos anos de experiência como júnior e aqueles com mais de dois anos de experiência como experientes.

Basicamente, precisamos de duas subconsultas aqui:

1. Para calcular o número de funcionários júnior em cada departamento.
2. Para calcular o número de funcionários experientes em cada departamento.

Aqui está como podemos abordar isto com subquéritos:

SELECT 
  e.department, 
  e.experienced_employees, 
  j.junior_employees
FROM (SELECT department, COUNT(*) AS experienced_employees
   FROM performance
   WHERE years_experience > 2
   GROUP BY department) AS e
JOIN (SELECT department, COUNT(*) AS junior_employees
   FROM performance
   WHERE years_experience <= 2
   GROUP BY department) AS j
ON e.department = j.department;

Este exemplo não é muito complicado, então você provavelmente poderá seguir a consulta acima. No entanto, observe isso:

• Você precisa adivinhar o propósito de cada subconsulta.
• Não está imediatamente claro quais tabelas estão unidas.
• A cláusula SELECT lista os campos a serem exibidos na saída, mas estes campos são definidos somente mais tarde dentro das subconsultas.

Quando os cálculos ficam mais complicados, é realmente um desafio seguir o processo de computação através de todas as subconsultas. Em contraste, o processo de cálculo usando CTEs é muito mais legível:

WITH experienced AS
  (SELECT department, COUNT(*) AS experienced_employees
   FROM performance
   WHERE years_experience > 2
   GROUP BY department),

  junior AS
  (SELECT department, COUNT(*) AS junior_employees
   FROM performance
   WHERE years_experience <= 2
   GROUP BY department)

SELECT 
  e.department, 
  e.experienced_employees, 
  j.junior_employees
FROM experienced e
JOIN junior j
ON e.department = j.department;

Aqui, começamos por definir todos os conjuntos de resultados temporários necessários para os cálculos. Em seguida, apenas escrevemos uma simples consulta para obter o resultado necessário. Tudo é limpo e claro.

Acredite, você vai realmente apreciar a estrutura que as expressões comuns das tabelas fornecem quando se trabalha com cálculos mais complexos. Para provar meu ponto de vista, quero sugerir mais um exemplo com os cálculos aninhados.

#4. Os CTEs aninhados são realmente legais

Considerando a reusabilidade dos CTEs e sua capacidade de esclarecer o processo de cálculo, não é surpreendente que as cláusulas sejam perfeitas para os cálculos aninhados.

Digamos que queremos calcular quantos funcionários em cada departamento têm uma pontuação de KPI que está (1) acima da média ou (2) abaixo da média em seu respectivo departamento. Especificamente, para cada departamento, queremos mostrar a pontuação média do KPI, o número de funcionários com pontuação acima da média e o número de funcionários com pontuação abaixo da média.

Basicamente, precisamos de três subconsultas aqui:

1. Para obter a média de KPI para cada departamento.
2. Para obter o número de funcionários que têm uma pontuação de KPI acima da média.
3. Para obter o número de funcionários que têm uma pontuação abaixo da média de KPI.

Entretanto, ao utilizar as subconsultas, será necessário adicionar a primeira subconsulta três vezes, incluindo duas vezes quando ela será aninhada dentro das outras duas subconsultas. A consulta resultante parece bastante confusa:

SELECT 
  avg.department, 
  avg.average_kpi, 
  aa.employees_above_average, 
  ba.employees_below_average
FROM
    (SELECT department, AVG(kpi) AS average_kpi
    FROM performance
    GROUP BY department) AS avg
JOIN
    (SELECT p.department, count(*) AS employees_above_average
     FROM performance p
     JOIN
       (SELECT department, AVG(kpi) AS average_kpi
        FROM performance
        GROUP BY department) AS avg
     ON p.department = avg.department
     WHERE kpi > average_kpi
     GROUP BY p.department) AS aa
ON avg.department = aa.department
JOIN
    (SELECT p.department, count(*) AS employees_below_average
     FROM performance p
     JOIN
       (SELECT department, AVG(kpi) AS average_kpi
        FROM performance
        GROUP BY department) AS avg
     ON p.department = avg.department
     WHERE kpi < average_kpi
     GROUP BY p.department) AS ba
ON avg.department = ba.department;

A consulta funcionou e conseguimos o resultado desejado, mas observe como é difícil acompanhar as múltiplas subconsultas aninhadas. No mundo real, muitas vezes fica ainda mais complicado.

Em contraste, veja o quanto a mesma consulta parece limpa ao usar CTEs em vez de subconsultas:

WITH avg_kpi_department AS
    (SELECT department, AVG(kpi) AS average_kpi
    FROM performance
    GROUP BY department),

    above_average AS
    (SELECT p.department, count(*) AS employees_above_average
     FROM performance p
     JOIN avg_kpi_department avg
     ON p.department = avg.department
     WHERE kpi > average_kpi
     GROUP BY p.department),

     below_average AS
     (SELECT p.department, count(*) AS employees_below_average
     FROM performance p
     JOIN avg_kpi_department avg
     ON p.department = avg.department
     WHERE kpi < average_kpi
     GROUP BY p.department)

SELECT 
  avg.department, 
  avg.average_kpi, 
  aa.employees_above_average, 
  ba.employees_below_average
FROM avg_kpi_department avg
JOIN above_average aa
ON avg.department = aa.department
JOIN below_average ba
ON avg.department = ba.department;

Você pode seguir claramente como os três conjuntos de resultados temporários são definidos. Você pode entender o propósito deles a partir dos nomes atribuídos. Você não precisa repetir a mesma subconsulta várias vezes dentro da mesma consulta principal. Ao chegar à consulta principal, todo o trabalho preparatório é feito e você pode simplesmente juntar-se aos CTEs que já foram definidos.

#5. Os CTEs Permitem a Recurssão

Por último, mas não menos importante, os CTEs são ótimos no processamento de gráficos, árvores e outras estruturas hierárquicas. Isto porque a sintaxe pode processar a recursividade. Uma consulta recursiva é uma consulta que se refere a si mesma.

Por exemplo, se tivermos uma estrutura organizacional típica, onde cada funcionário tem um superior e os superiores têm múltiplos subordinados, os CTEs recursivos podem nos ajudar a analisar esses dados de forma eficiente. Com apenas uma consulta SQL e dados em nível individual, podemos calcular o salário total para cada um dos departamentos e subdepartamentos ou o número total de dias de férias não utilizados em cada uma das unidades organizacionais.

A sintaxe das CTEs recursivas é bastante complicada. Para uma explicação detalhada, recomendo a leitura deste artigo que explica como as consultas recursivas processam as estruturas hierárquicas. Você também pode querer verificar estes exemplos de aplicação de consultas recursivas a estruturas gráficas.

Vamos praticar os CTEs!

Agora que você percebe os benefícios que os CTEs trazem para as consultas SQL, é hora de praticar! Eu recomendo começar com nossos Consultas Recursivas curso interativo (114 desafios de codificação), que abrange todos os tipos de expressões comuns de tabela, incluindo CTEs simples, CTEs aninhados, e CTEs recursivos. Obtenha mais detalhes neste artigo de visão geral.

Obrigado pela leitura, e feliz aprendizado!