Subconsultas vs. CTE: un manual de SQL

¿Alguna vez se ha preguntado por las diferencias entre una subconsulta y una expresión de tabla común (CTE) en SQL? Los conceptos parecen muy similares, pero conocer la diferencia -y cuándo usar cada uno- te ayudará a escribir consultas eficientes y legibles.

En primer lugar, explicaremos los conceptos subyacentes a las subconsultas y a las CTE. Luego veremos algunos ejemplos y finalmente analizaremos los pros y los contras de cada técnica.

¿Qué es una subconsulta?

Una subconsulta es una consulta dentro de otra consulta. Podemos utilizarla de múltiples maneras: en la cláusula FROM, para filtrar, o incluso como una columna. Para utilizar una subconsulta, basta con añadir paréntesis y poner la consulta dentro de ellos.

En nuestros primeros ejemplos, trabajaremos con datos sobre los resultados de una competición de salto de longitud. Tenemos dos tablas:

participant - Almacena información sobre los participantes en el concurso:

• id - Un ID único para cada participante.
• first_name - El nombre del participante.
• last_name - El apellido del participante.

jump - Almacena información sobre los saltos realizados por los participantes:

• id - El ID de cada salto.
• participant_id - El ID del participante que realizó el salto.
• contest_id - El ID del concurso en el que se realizó el salto.
• length - La longitud del salto, en centímetros.

participant

jump

Ya que conoces los datos que estamos utilizando, echa un vistazo a los siguientes ejemplos 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 muestra los participantes con sus saltos que fueron más largos que la longitud media de los saltos. En la condición WHERE, hemos utilizado una subconsulta para obtener la longitud media de los saltos. Como sólo hay un valor devuelto por esta subconsulta, podemos comparar fácilmente los datos de las columnas con él.

A continuación, otro ejemplo:

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;

En esta consulta, mostramos la mayor longitud media de salto de cualquier participante. Para obtener el resultado, primero calculamos la longitud media de los saltos de cada participante. Para ello, utilizamos una subconsulta dentro de la cláusula FROM. A continuación, simplemente utilizamos MAX() para obtener la mayor longitud media.

Estos son sólo dos ejemplos de subconsultas. Es un tema muy amplio -aunque los casos de uso son bastante sencillos- y hay demasiados conceptos para describirlos en este artículo. Puedes encontrar una breve descripción de las subconsultas en el artículo SQL Subqueries en el blog LearnSQL.es . Si estás interesado en trabajar con subconsultas, consulta la parte 6 del SQL para principiantes (incluso se llama Subqueries).

También puedes ver los episodios de nuestra serie We Learn SQL en Youtube. Varios de ellos han sido dedicados a las subconsultas SQL. Recuerda suscribirte a nuestro canal.

¿Qué es la CTE?

Una expresión de tabla común (llamada CTE para abreviar) es una consulta que creamos antes de escribir la consulta principal. Luego, podemos utilizarla simplemente como una tabla común dentro de nuestro código.

Mire el siguiente ejemplo. Una vez más, estamos utilizando los datos del concurso de salto de longitud:

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;

Esto devuelve exactamente el mismo resultado que el ejemplo anterior con la subconsulta: la mayor longitud media de salto de cualquier participante. Sin embargo, en lugar de escribir nuestra consulta dentro de la cláusula FROM, la hemos puesto en la cláusula WITH (que viene antes de la consulta principal). Hemos llamado a nuestro CTE average_length y lo hemos utilizado en la cláusula FROM de la consulta principal.

Por supuesto, los CTEs pueden ser mucho más complicados que este ejemplo. Pero no vamos a hablar de ello aquí. Si quieres aprender sobre CTEs, consulta el curso Consultas recursivas y expresiones de tabla comunes curso en LearnSQL.es.

¿No hay ninguna diferencia...?

Al principio, podrías pensar que casi no hay diferencia entre las subconsultas y los CTEs. Hemos utilizado tanto una subconsulta como un CTE en la cláusula FROM y la sintaxis era sólo un poco diferente. Sin embargo, no olvides el primer ejemplo: allí utilizamos una subconsulta en la cláusula WHERE. Allí no se podía usar un CTE, ¡y esa no es la única diferencia!

Subconsulta vs CTE: ¿Cuál es la diferencia?

Por supuesto, esto no significa que las CTE sean inferiores a las subconsultas. Examinemos las diferencias entre ambas, empezando por las CTE.

Diferencia #1: Las CTEs pueden ser recursivas

Veamos la primera ventaja de las CTEs. Las CTEs permiten utilizar un poderoso concepto: la recursividad. Gracias a la recursividad, SQL es ahora Turing completo - cualquier programa que pueda ser escrito en cualquier lenguaje de programación puede también ser escrito en SQL. (Si tiene dudas de que SQL es un lenguaje de programación, consulte ¿Es SQL un lenguaje de programación? en el blog LearnSQL.es ).

¿Cómo funciona la recursión en SQL? Permite que su CTE se llame a sí mismo hasta que se cumpla una condición final especificada. En cada paso, la consulta se expande a sí misma y cambia los datos que tiene. Veamos un ejemplo.

Trabajaremos con los siguientes datos de unos empleados de una empresa ficticia. Están almacenados en la tabla employee que contiene las siguientes columnas:

• id - Un ID único para cada empleado.
• first_name - El nombre del empleado.
• last_name - El apellido del empleado.
• manager_id - El ID del gerente de ese empleado.

employee

Ahora, nos gustaría mostrar la cadena de gestión desde el director general (una persona sin valor en la columna manager_id ) hasta cada empleado. La siguiente consulta resolverá este problema. Echa un vistazo:

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;

El resultado tendrá este aspecto:

Hemos escrito una consulta que puede crear fácilmente toda una cadena de relaciones. Podrías pensar que esto se podría lograr con subconsultas, pero a medida que la cadena de gestión se hace más y más profunda, tendrías que escribir más y más código. La cantidad de código que tendrías que escribir dependería de la profundidad de la cadena - y eso sólo se puede comprobar con una CTE recursiva.

¿Cómo funciona esta consulta? Comienza ejecutando la primera parte (antes de la UNION ALL) y selecciona un empleado sin gerente (es decir, Maisy Bloom). A continuación, la parte que se encuentra debajo de UNION ALL selecciona a los empleados gestionados directamente por Maisy (Caine Farrow). Como la consulta se llama a sí misma, vuelve a ejecutar la misma parte y selecciona a todos los empleados gestionados por Caine (Waqar Jarvis y Lacey-Mai Rahman). Repite esta operación mientras tenga filas que unir. Tras recorrer toda la cadena de gestión, la consulta se detiene.

Si este es tu primer encuentro con la recursividad en SQL, puede ser un poco difícil de entender. Y eso es totalmente normal. Consulta Hazlo en SQL: Recursive SQL Tree Traversal para una explicación más detallada.

Diferencia #2: Las CTEs son reutilizables

Una gran ventaja de los CTEs es que pueden ser usados múltiples veces en una consulta. No es necesario copiar todo el código de la CTE - simplemente se pone el nombre de la CTE.

Usando los datos de la sección anterior, nos gustaría 1) filtrar los empleados que no tienen un gerente y luego 2) mostrar cada empleado con su gerente - pero sólo si tienen un gerente. El resultado será el siguiente:

Ahora, veamos cómo un CTE resolvería esta tarea:

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;

Ahora veamos cómo una subconsulta lograría el mismo 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 puede ver, la consulta CTE tiene menos código. También es más legible: simplemente se repite el nombre de la CTE (not_null_manager) en lugar de todo un trozo de código.

En realidad, no hay mucha diferencia en la eficiencia del rendimiento entre estas dos consultas. Aunque sólo declare la CTE una vez, el tiempo de ejecución es casi el mismo.

Diferencia #3: Las CTEs pueden ser más legibles

Ya sabes que puedes escribir menos código utilizando CTEs. ¿Qué pasa con la organización del código? Aquí hay otro ejemplo que se centra en la cláusula FROM.

¿Aún recuerdas los primeros ejemplos? ¿Los que devolvían la mayor longitud media de salto? Si no es así, aquí tienes un rápido repaso.

Este utiliza una 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;

Y éste utiliza una 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;

¿Cuál crees que es más legible? Yo diría que la segunda (CTE) es un poco mejor. El código está menos anidado y hay una pequeña capa de abstracción. A primera vista se sabe que esta consulta selecciona la longitud media máxima.

La legibilidad de los CTEs se muestra aún más en los ejemplos de la sección anterior. Y cuando tus consultas sean cada vez más grandes, el uso de CTEs aumentará significativamente la legibilidad de tu código. Y la legibilidad es clave para el desarrollo eficiente del código.

Si quieres aprender más sobre el uso de CTEs para mejorar la legibilidad de tu código, definitivamente debes revisar el artículo Cómo organizar las consultas SQL con CTEs.

Pero las subconsultas son a veces insustituibles

Hasta ahora, has aprendido bastantes diferencias entre subconsultas y CTEs. Para ser honesto, estaba tratando de convencerte de que las CTEs son mucho mejores que las subconsultas. Pero en esta sección, aprenderás por qué las subconsultas pueden ser invaluables.

Filtrar con una subconsulta

El primer ejemplo de este artículo utilizó una subconsulta en la cláusula WHERE. No mostré un ejemplo similar en la sección CTE. Esto se debe a que sólo se pueden utilizar subconsultas en la cláusula WHERE.

Además, hay un buen número de palabras clave que se pueden utilizar en la condición WHERE - por ejemplo, ALL, ANY, EXISTS, ¡y algunas más! Lamentablemente, no puedo explicarlas aquí; me llevaría demasiado tiempo. En su lugar, recomiendo consultar la sección de subconsultas de nuestro SQL para principiantes curso. No sólo aprenderás sobre estas palabras clave, sino que resolverás algunos problemas usándolas. O consulta el artículo Subconsultas SQL en nuestro blog si quieres una breve explicación.

Las subconsultas pueden actuar como columnas

También puede utilizar las subconsultas como lo haría con una columna. La única restricción es que la subconsulta debe devolver sólo un valor. Echa un vistazo:

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 devuelve el número de saltos superiores a 600 cm (calculado en la primera subconsulta) y el número de saltos inferiores o iguales a 600 cm (calculado en la segunda subconsulta). El resultado será el siguiente:

Subconsultas correlacionadas

Vuelva a mirar el ejemplo anterior. ¿Se ha dado cuenta de que he utilizado una referencia a una tabla en la consulta externa dentro de la subconsulta? Incluso he hecho referencia al valor de la fila actual de esa tabla. Esto se llama "subconsulta correlacionada". Le permite utilizar valores de la consulta externa dentro de la subconsulta.

Es una técnica muy útil, pero también bastante complicada; no la explicaremos en este artículo. Sin embargo, siéntase libre de revisar Subconsulta correlacionada en SQL: A Beginner's Guide en nuestro blog para una explicación.

Una Diferencia Más: Las CTEs deben ser nombradas

La última diferencia entre las CTEs y las subconsultas está en la denominación. Las CTEs siempre deben tener un nombre. Por otro lado, en la mayoría de los motores de bases de datos, las subconsultas no requieren ningún nombre (la única excepción es la cláusula FROM en mi motor de bases de datos favorito, PostgreSQL).

Es una buena práctica nombrar las subconsultas colocadas en las cláusulas FROM o SELECT, pero no es un requisito. Y, para ser precisos, no puedes nombrar las subconsultas que utilizas en la cláusula WHERE.

Puede que pienses que nombrar no es una gran diferencia y que no te afectará mucho. Sin embargo, es posible que tengas que comprobar rápidamente algo en la base de datos. En este caso, la sintaxis más fácil podría ser su elección. Incluso si la consulta es menos legible, tenlo en cuenta: este tipo de consultas rara vez se leen después de ser utilizadas.

Subconsultas vs CTEs - ¿Cuál es mejor?

Has aprendido mucho sobre las diferencias entre CTEs y subconsultas. Entonces, ¿cuál es mejor? La respuesta es ninguna de las dos cosas, o depende - tanto las subconsultas como los CTEs tienen pros y contras. Cada consulta debe ser analizada y la elección entre estas dos debe decidirse caso por caso. Pero para ello, tendrás que aprender a fondo ambos conceptos.

Para aprender más sobre las subconsultas, puedo sugerir la parte de Subconsultas del curso de LearnSQL SQL para principiantes de LearnSQL. Si quieres aprender más sobre CTEs, el curso Consultas recursivas y expresiones de tabla comunes es tu mejor opción. Estos cursos te ayudarán a aprender rápidamente estos conceptos. Así, podrás decidir qué consultas se benefician de las CTEs y cuáles requieren subconsultas.

Sin embargo, si ya estás algo familiarizado con las subconsultas correlacionadas y no quieres aprender lo mismo una vez más, también podrías afinar tus habilidades en nuestro Ejercicios prácticos de SQL curso.