Subquery vs. CTE: Uma Cartilha SQL

Você já se perguntou sobre as diferenças entre uma subconsulta e uma expressão de mesa comum (CTE) em SQL? Os conceitos parecem muito semelhantes, mas saber a diferença - e quando usar cada um deles - ajudará você a escrever consultas eficientes e legíveis.

Primeiro, vamos explicar os conceitos subjacentes às subconsultas e CTEs. Em seguida, veremos alguns exemplos e, finalmente, analisaremos os prós e os contras de cada técnica.

O que é uma subconsulta?

Uma subconsulta é uma consulta dentro de uma consulta. Podemos utilizá-la de várias maneiras: na cláusula FROM, para filtragem, ou mesmo como uma coluna. Para usar uma subconsulta, basta adicionar parênteses e colocar a consulta dentro deles.

Em nossos primeiros exemplos, vamos trabalhar com dados sobre os resultados de uma competição de salto em distância. São-nos dadas duas tabelas:

participant - Armazena informações sobre os participantes do concurso:

• id - Uma identificação única para cada participante.
• first_name - O primeiro nome do participante.
• last_name - O sobrenome do participante.

jump - Armazena informações sobre os saltos feitos pelos participantes:

• id - O ID de cada salto.
• participant_id - O ID do participante que fez o salto.
• contest_id - O ID do concurso no qual o salto foi feito.
• length - O comprimento do salto, em centímetros.

participant

jump

Como você conhece os dados que estamos usando, dê uma olhada nos seguintes exemplos de subconsultas:

SELECT
  first_name,
  last_name,
  length
FROM participant
JOIN jump
  ON jump.participant_id = participant.id
WHERE length > (
  SELECT
    AVG(length)
  FROM jump
);

Esta consulta mostra os participantes com seus saltos que foram maiores do que o comprimento médio do salto. Na condição WHERE, usamos uma subconsulta para obter o comprimento médio de salto. Como há apenas um valor retornado por esta subconsulta, podemos facilmente comparar os dados da coluna com os da subconsulta.

A seguir, outro exemplo:

SELECT
  MAX(avg_length) AS max_length
FROM (
  SELECT
    first_name,
    last_name,
    AVG(length) AS avg_length
  FROM participant
  JOIN jump
    ON jump.participant_id = participant.id
  GROUP BY first_name,
    last_name
) AS average_lengths;

Nesta consulta, estamos mostrando o maior comprimento médio de salto por qualquer participante. Para obter o resultado, primeiro calculamos o comprimento médio de salto de cada participante. Fizemos isso usando uma subconsulta dentro da cláusula FROM. Em seguida, simplesmente usamos MAX() para retornar o maior comprimento médio.

Estes são apenas dois exemplos de subconsultas. É um tópico amplo - embora os casos de uso sejam bastante simples - e há demasiados conceitos para serem descritos neste artigo. Uma breve visão geral das subconsultas pode ser encontrada no artigo SQL Subqueries no blog LearnSQL.com.br . Se você estiver interessado em trabalhar com subconsultas, confira a parte 6 de SQL para Iniciantes curso (é até chamado de Subconsultas).

Você também pode assistir a episódios de nossa série We Learn SQL no Youtube. Vários deles foram dedicados às subconsultas SQL. Lembre-se de assinar o nosso canal.

O que é CTE?

Uma expressão de tabela comum (chamada CTE para abreviar) é uma consulta que criamos antes de escrever a consulta principal. Então, podemos simplesmente usá-la como uma tabela regular dentro de nosso código.

Veja o exemplo a seguir. Mais uma vez, estamos usando os dados do concurso de salto em distância:

WITH average_lengths AS (
  SELECT
    first_name,
    last_name,
    AVG(length) AS avg_length
  FROM participant
  JOIN jump
    ON jump.participant_id = participant.id
  GROUP BY first_name,
    last_name
)

SELECT
  MAX(avg_length) AS max_length
FROM average_lengths;

Isto retorna exatamente o mesmo resultado do exemplo anterior com a subconsulta: o maior comprimento médio de salto por qualquer participante. Entretanto, em vez de escrever nossa consulta dentro da cláusula FROM, nós a colocamos na cláusula WITH (que vem antes da consulta principal). Chamamos nosso CTE average_length e a utilizou na cláusula FROM na consulta principal.

É claro que os CTEs podem ser muito mais complicados do que este exemplo. Mas não vamos discutir isso aqui. Se você gostaria de aprender sobre os CTEs, confira a Consultas Recursivas curso em LearnSQL.com.br.

Não há diferença alguma...?

No início, você pode pensar que quase não há diferença entre subconsultas e CTEs. Usamos tanto uma subconsulta quanto um CTE na cláusula FROM e a sintaxe foi apenas um pouco diferente. Entretanto, não se esqueça do primeiro exemplo - utilizamos uma subconsulta na cláusula WHERE. Você não poderia usar um CTE lá, e essa não é a única diferença!

Subquery vs CTE: Qual é a diferença?

Claro, isto não significa que os CTEs são inferiores às subconsultas. Vamos examinar as diferenças entre os dois, começando com os CTEs.

Diferença nº 1: Os CTEs podem ser recursivos.

Vamos dar uma olhada na primeira vantagem dos CTEs. Os CTEs permitem a utilização de um conceito poderoso: a recorrência. Graças à recorrência, o SQL está agora Turing completo - todo programa que pode ser escrito em qualquer linguagem de programação também pode ser escrito em SQL. (Se você tem dúvidas de que SQL é uma linguagem de programação, confira Se SQL é uma linguagem de programação? no blogLearnSQL.com.br ).

Como funciona a recursividade em SQL? Ela permite que seu CTE se chame a si mesmo até que uma condição final especificada seja cumprida. Em cada etapa, a consulta se expande e muda os dados que possui. Vamos ver um exemplo.

Vamos trabalhar com os seguintes dados para alguns funcionários fictícios da empresa. São armazenados no employee tabela, que contém as seguintes colunas:

• id - Uma identificação única para cada funcionário.
• first_name - O primeiro nome do funcionário.
• last_name - O sobrenome do funcionário.
• manager_id - O ID do gerente desse funcionário.

employee

Agora, gostaríamos de mostrar a cadeia de administração do CEO (uma pessoa sem valor na coluna manager_id ) para cada funcionário. A consulta abaixo irá resolver este problema. Dê uma olhada:

WITH RECURSIVE employee_chain AS (
  SELECT
    id,
    first_name,
    last_name,
    first_name || ' ' || last_name AS chain
  FROM employee
  WHERE manager_id IS NULL
  UNION ALL
  SELECT
    employee.id,
    employee.first_name,
    employee.last_name,
    chain || '->' || employee.first_name || ' ' || employee.last_name
  FROM employee_chain
  JOIN employee
    ON employee.manager_id = employee_chain.id
)

SELECT
  first_name,
  last_name,
  chain
FROM employee_chain;

O resultado será o seguinte:

Escrevemos uma consulta que pode facilmente criar toda uma cadeia de relações. Você pode pensar que isto poderia ser alcançado com subconsultas, mas à medida que a cadeia de gestão se aprofunda cada vez mais, você teria que escrever mais e mais código. A quantidade de código que você teria que escrever dependeria da profundidade da cadeia - e isso só pode ser verificado com um CTE recursivo.

Como funciona esta consulta? Ela começa executando a primeira parte (antes do UNION ALL) e seleciona um funcionário sem um gerente (ou seja, Maisy Bloom). Em seguida, a parte abaixo da UNION ALL seleciona o(s) funcionário(s) diretamente gerenciado(s) pela Maisy (Caine Farrow). Como a consulta é chamada a si mesma, ela então executa novamente a mesma parte e seleciona todos os funcionários administrados pela Caine (Waqar Jarvis e Lacey-Mai Rahman). Ela repete esta operação desde que tenha filas para se juntar. Após atravessar toda a cadeia de gestão, a consulta é interrompida.

Se este é seu primeiro encontro com a recursividade em SQL, pode ser um pouco difícil de entender. E isso é totalmente normal. Confira Faça em SQL: Traversal de árvores SQL recursivas para uma explicação mais detalhada.

Diferença nº 2: Os CTEs são reutilizáveis

Uma grande vantagem dos CTEs é que eles podem ser usados várias vezes em uma consulta. Você não precisa copiar todo o código CTE - você simplesmente coloca o nome CTE.

Usando os dados da seção anterior, gostaríamos de 1) filtrar os funcionários que não têm um gerente e depois 2) mostrar cada funcionário com seu gerente - mas somente se eles tiverem um gerente. O resultado será o seguinte:

Agora, vamos ver como um CTE resolveria esta tarefa:

WITH not_null_manager AS (
  SELECT
    *
  FROM employee
  WHERE manager_id IS NOT NULL
)

SELECT
  nnm1.first_name,
  nnm1.last_name,
  nnm2.first_name,
  nnm2.last_name
FROM not_null_manager AS nnm1
JOIN not_null_manager AS nnm2
  ON nnm1.manager_id = nnm2.id;

Agora, vamos ver como uma subquisição conseguiria o mesmo resultado:

SELECT
  nnm1.first_name,
  nnm1.last_name,
  nnm2.first_name,
  nnm2.last_name
FROM (
  SELECT
    *
  FROM employee
  WHERE manager_id IS NOT NULL
) AS nnm1
JOIN (
  SELECT
    *
  FROM employee
  WHERE manager_id IS NOT NULL
) AS nnm2
  ON nnm1.manager_id = nnm2.id;

Como você pode ver, a consulta do CTE tem menos código. Também é mais legível: basta repetir o nome do CTE (not_null_manager) em vez de um pedaço inteiro de código.

Não há realmente muita diferença na eficiência do desempenho entre estas duas consultas. Mesmo que você declare o CTE apenas uma vez, o tempo de execução é quase o mesmo.

Diferença #3: Os CTEs podem ser mais legíveis

Portanto, você sabe que pode escrever menos código usando CTEs. E quanto à organização do código? Aqui está outro exemplo que se concentra na cláusula FROM.

Você ainda se lembra dos primeiros exemplos? Os que devolvem o maior comprimento médio de salto? Se não, aqui vai uma rápida revisão.

Este aqui usa uma subconsulta:

SELECT
  MAX(avg_length) AS max_length
FROM (
  SELECT
    first_name,
    last_name,
    AVG(length) AS avg_length
  FROM participant
  JOIN jump
    ON jump.participant_id = participant.id
  GROUP BY first_name,
    last_name
) AS average_lengths;

E este aqui usa um CTE:

WITH average_lengths AS (
  SELECT
    first_name,
    last_name,
    AVG(length) AS avg_length
  FROM participant
  JOIN jump
    ON jump.participant_id = participant.id
  GROUP BY first_name,
    last_name
)

SELECT
  MAX(avg_length) AS max_length
FROM average_lengths;

Qual deles você acha que é mais legível? Eu diria que o segundo (CTE) é um pouco melhor. O código é menos aninhado e há uma pequena camada de abstração. À primeira vista, você sabe que esta consulta seleciona o comprimento médio máximo.

A capacidade de leitura dos CTEs é mostrada ainda mais nos exemplos da seção anterior. E quando suas consultas crescerem cada vez mais, o uso de CTEs aumentará significativamente a capacidade de leitura de seu código. E a legibilidade é a chave para o desenvolvimento eficiente do código.

Se você quiser aprender mais sobre o uso de CTEs para melhorar a legibilidade de seu código, você deve definitivamente verificar o artigo Como Organizar Consultas SQL com CTEs.

Mas as subconsultas às vezes são insubstituíveis.

Até agora, você aprendeu algumas diferenças entre as subconsultas e os CTEs. Para ser honesto, eu estava tentando convencê-lo de que os CTEs são muito melhores do que as subconsultas. Mas nesta seção, você aprenderá por que as subconsultas podem ser inestimáveis.

Filtragem com uma subconsulta

O primeiro exemplo deste artigo utilizou uma subconsulta na cláusula WHERE. Eu não mostrei um exemplo semelhante na seção CTE. Isso porque somente subconsultas podem ser usadas na cláusula ONDE!

Além disso, existem algumas palavras-chave que você pode usar na condição WHERE - por exemplo ALL, ANY, EXISTS, e mais algumas! Infelizmente, eu não posso explicá-las aqui; levaria muito tempo. Em vez disso, recomendo verificar a seção Subconsultas do nosso SQL para Iniciantes curso. Você não somente aprenderá sobre estas palavras-chave, mas também resolverá alguns problemas usando-as! Ou consulte o artigo SQL Subqueries em nosso blog se você quiser uma breve explicação.

As subconsultas podem agir como colunas

Você também pode usar subconsultas como se fosse uma coluna. A única restrição é que a subconsulta deve retornar apenas um valor. Dê uma olhada:

SELECT DISTINCT
  contest_id,
  (
    SELECT
      COUNT(length)
    FROM jump AS inside_jump
    WHERE inside_jump.contest_id = outside_jump.contest_id
      AND inside_jump.length > 600
  ) AS longer_jumps,
  (
    SELECT
      COUNT(length)
    FROM jump AS inside_jump
    WHERE inside_jump.contest_id = outside_jump.contest_id
      AND inside_jump.length <= 600
  ) AS shorter_jumps
FROM jump AS outside_jump;

Para cada concurso, esta consulta retorna o número de saltos maiores que 600cm (computados na primeira subconsulta) e o número de saltos menores ou iguais a 600cm (computados na segunda subconsulta). O resultado será o seguinte:

Subconsultas correlatas

Veja o exemplo acima novamente. Você notou que eu usei uma referência a uma tabela na consulta externa dentro da subconsulta? Eu até me referi ao valor da linha atual daquela tabela. Isto é chamado de "subconsulta correlata". Ela permite utilizar valores da consulta externa dentro da subconsulta.

Esta é uma técnica muito útil, mas também é bastante complicada; não vamos explicá-la neste artigo. No entanto, sinta-se à vontade para verificar a Subquery Correlated Subquery em SQL: Um Guia para Iniciantes em nosso blog para uma explicação.

Mais uma diferença: Os CTEs devem ser nomeados

A última diferença entre CTEs e subconsultas está na nomenclatura. Os CTEs devem sempre ter um nome. Por outro lado, na maioria dos motores de banco de dados, as subconsultas não exigem nenhum nome (a única exceção é a cláusula FROM no meu motor de banco de dados favorito, PostgreSQL).

É uma boa prática nomear subconsultas colocadas nas cláusulas FROM ou SELECT, mas não é uma exigência. E, para ser mais preciso, você não pode nomear as subconsultas que utiliza na cláusula WHERE.

Você pode pensar que nomear não é uma grande diferença e não o afetará muito. Entretanto, você pode ter que verificar rapidamente algo no banco de dados. Neste caso, a sintaxe mais fácil pode ser a sua escolha. Mesmo que a consulta seja menos legível, tenha isto em mente - tais consultas raramente são lidas após serem usadas.

Subconsultas vs CTEs - O que é melhor?

Você aprendeu muito sobre as diferenças entre os CTEs e as subconsultas. Então, qual é o melhor? A resposta não é nem uma nem outra, ou depende - tanto as subconsultas como os CTEs têm prós e contras. Cada consulta deve ser analisada e a escolha entre estas duas deve ser decidida caso a caso. Mas para fazer isso, você terá que aprender a fundo os dois conceitos.

Para saber mais sobre as subconsultas, posso sugerir a parte Subconsultas do LearnSQL's SQL para Iniciantes curso. Se você gostaria de aprender mais sobre CTEs, o Consultas Recursivas curso é sua melhor aposta. Estes cursos o ajudarão a aprender rapidamente estes conceitos. Assim, você será capaz de decidir quais consultas se beneficiam dos CTEs e quais requerem subconsultas.

Entretanto, se você já está um pouco familiarizado com subconsultas correlatas e não quer aprender a mesma coisa mais uma vez, você também pode querer aprimorar suas habilidades em nossos cursos de Curso de Práticas em SQL curso.