Melhorando desempenho de consultas SQL com CTE (Common Table Expressions)

Neste tutorial, você aprenderá como usar o CTE (common table expression) ou expressão de tabela comum, também são chamadas de “WITH querys”, e sua finalidade é simplificar consultas complexas e geralmente melhora o desempenho.

Uma CTE é um conjunto de resultados temporário que você pode fazer referência em outra instrução SQL, incluindo SELECT, INSERT, UPDATE e DELETE.

As Expressões de Tabela Comuns são temporárias no sentido de que só existem durante a execução da consulta.

CTE existe nos principais banco de dados, por exemplo: Oracle, SqlServer, MySql, MariaDB, SQLite e outros, em nossos exemplos vamos utilizar o PostgreSQL como base.

Vantagens de usar CTE
A seguir estão algumas vantagens de usar expressões de tabela comuns ou CTEs:

• Melhore a legibilidade de consultas complexas. Você usa CTEs para organizar consultas complexas de maneira mais organizada e legível.
• Capacidade de criar consultas recursivas. Consultas recursivas são consultas que fazem referência a si mesmas. As consultas recursivas são úteis quando você deseja consultar dados hierárquicos.
• Use em conjunto com funções. Você pode usar CTEs em conjunto com funções para criar um conjunto de resultados inicial e usar outra instrução select para processar ainda mais esse conjunto de resultados.

Veja a seguir a sintaxe de criação de um CTE:

Primeiro defina o nome e as colunas do CTE.

WITH cte_identificador (coluna1, coluna2)  
AS 

Em seguida o corpo da consulta.

WITH cte_identificador (coluna1, coluna2)
AS (
  SELECT 1, 2
)

Por último, defina a consulta externa, que fará referência ao nome CTE.

WITH cte_identificador (coluna1, coluna2)  
AS (  
    SELECT 1, 2 
   ) 
SELECT * FROM cte_identificador

Você também pode fazer CTEs recursivo, adicionando a palavra chave: “recursive”! Depois de adicionar “recursive”, o Postgres agora permite que você faça referência ao seu CTE de dentro de si mesmo, permitindo que você “gere” linhas com base na recursão.

Agora um exemplo simples.

Vamos supor que queremos saber qual foi a média da turma e qual média dos alunos foi maior que a média da turma? Qual foi a média de cada um desses alunos?

Uma solução utilizando CTE:

WITH cte_media_aluno  AS (
	SELECT id_aluno, ROUND(AVG(nota),2) as media_aluno
        FROM tb_notas 
        GROUP BY tb_notas.id_aluno
), 
cte_media_turma AS (
	SELECT ROUND(AVG(nota),2) as media_sala
        FROM tb_notas 
)

SELECT id_aluno, media_aluno, cte_media_turma.media_sala
FROM cte_media_aluno
JOIN cte_media_turma  ON TRUE
WHERE media_aluno > media_sala;

Temos o seguinte resultado:

Agora vamos observar a solução sem utilizar CTE:

SELECT id_aluno, media_aluno, nt.media_sala
FROM (
 SELECT id_aluno, ROUND(AVG(nota),2) as media_aluno
        FROM tb_notas 
        GROUP BY tb_notas.id_aluno
) 
JOIN (
 SELECT ROUND(AVG(nota),2) as media_sala
        FROM tb_notas 
)  AS nt
    ON TRUE
WHERE media_aluno > media_sala;

Fica bem visível que a consulta com CTE melhora a legibilidade da consulta e consequentemente manutenção futura já que separamos bem cada informação que precisamos.

Também é fácil observar que quanto mais complexo é sua consultar melhor se encaixa os CTE.

Só para esclarecer, os CTEs têm seu lugar. Eles tornam o código mais fácil de ler, mas não é apropriado para qualquer consulta SQL.

No próximo post vamos mostrar mais exemplos e os reais resultados ao usar CTE.

Esperamos ter ajudado com esta dica, siga nos para mais dicas.