5 razones por las que deberías usar CTEs en lugar de subconsultas

Las expresiones comunes de tabla, o CTEs, se introdujeron en SQL:1999 para manejar los casos en que la salida de una consulta se utiliza dentro de otra consulta. ¿Pero no teníamos ya subconsultas para esto? En este artículo, demostraré con múltiples ejemplos por qué las CTEs son mejores que las subconsultas para la estructura y legibilidad de tus consultas SQL.

Empecemos por recordar qué son las CTEs y las subconsultas y en qué se diferencian.

Expresiones de Tabla Comunes vs. Subconsultas

Una subconsulta es una consulta anidada dentro de otra consulta. Puede colocarse en cualquier parte de una consulta, incluso dentro de otra subconsulta. La sintaxis es muy simple - sólo tiene que poner su subconsulta dentro de paréntesis e insertarla en la consulta principal, donde sea necesario. Tenga en cuenta que las subconsultas se ejecutan antes de la consulta principal (también llamada consulta principal o externa) para que sus resultados puedan ser utilizados por la consulta principal. A menudo es bastante complicado leer una consulta SQL con múltiples subconsultas porque, aunque se ejecuten primero, están definidas en alguna parte de la consulta principal. Puedes leer más sobre las subconsultas SQL en esta guía introductoria.

Una Expresión de Tabla Común (CTE), también conocida como cláusula WITH, es un conjunto de resultados temporal con nombre al que se puede hacer referencia en cualquier parte de la consulta. A diferencia de las subconsultas, que se insertan exactamente donde se necesitan, todas las CTE se definen antes de la consulta principal y luego se hace referencia a ellas en la consulta utilizando el nombre asignado. En los ejemplos siguientes, veremos cómo esto mejora significativamente la estructura y la legibilidad de una consulta SQL. Primero se definen todos los conjuntos de resultados temporales que se necesitan y luego se utilizan en la consulta principal cuando es necesario.

Puede encontrar una introducción detallada a las expresiones de tablas comunes aquí. Aprenda más sobre la diferencia entre las CTEs y las subconsultas SQL en este artículo general.

5 razones para elegir las CTEs

Los estudiantes de SQL a menudo se preguntan si deben utilizar una expresión de tabla común o una subconsulta al escribir una consulta SQL compleja. Vamos a ver varios ejemplos para explicar por qué deberías preferir una CTE sobre una subconsulta. Para estos ejemplos, utilizaré la siguiente tabla que resume el rendimiento de los empleados de varios departamentos.

Para cada empleado, tenemos su ID, nombre completo, departamento, años de experiencia y una puntuación de finalización de KPI en una escala de 0 a 100.

Veamos cómo las CTEs frente a las subconsultas pueden ayudarnos a analizar estos datos.

5 razones para utilizar CTEs en lugar de subconsultas

#1. Las CTEs usan nombres significativos

Puede dar nombres significativos a las CTEs que especifiquen su intención y hagan la consulta más legible. Por ejemplo, digamos que queremos comparar el rendimiento de cada empleado con el promedio de realización de KPI en sus respectivos departamentos. Para añadir la columna correspondiente a nuestra tabla, primero tenemos que calcular el promedio de KPI para cada departamento. Nuestra consulta completa con una subconsulta es la siguiente:

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;

Puede obtener el mismo resultado utilizando una expresión de tabla común:

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;

Los CTEs y las subconsultas dan la misma salida, y las consultas son más o menos de la misma longitud. Pero tenga en cuenta que la legibilidad de la consulta CTE es mejor. Lo que significa una consulta compleja puede ser obvio para ti cuando miras tu propio código, pero puede no estar nada claro para tus colegas. Al leer la versión CTE:

• Verá la consulta en el orden en que se ejecutará: primero la subconsulta y luego la consulta principal.
• Puede determinar el propósito de la subconsulta basándose en su nombre. En nuestro caso, avg_department_kpi se refiere a la CTE que produce el promedio de KPI para cada departamento.

Al mismo tiempo, puede haber notado que la consulta CTE es un poco más larga que la versión de la subconsulta. Este no es siempre el caso, especialmente cuando necesitamos la misma subconsulta varias veces en nuestra consulta principal.

Si no conoce la sintaxis de WITH, puede practicar las expresiones comunes de la tabla en nuestro curso interactivo. Por ahora, pasemos a la segunda razón para preferir las CTEs a las subconsultas.

#2. Las CTEs son reutilizables dentro de una consulta

A diferencia de las subconsultas, no tiene que repetir la definición de una CTE cada vez que la necesite en la consulta. Se define una sola vez, al principio de la consulta, y luego se hace referencia a ella cuando sea necesario.

Digamos que queremos comparar el rendimiento de diferentes departamentos. En concreto, queremos ver el KPI medio de cada departamento junto con el KPI medio mínimo y máximo de todos los departamentos. Podríamos abordar este problema utilizando una consulta SQL con tres subconsultas:

1. Encontrar el KPI medio de cada departamento.
2. Encontrar el KPI medio mínimo de todos los departamentos.
3. Encontrar el KPI medio máximo de los departamentos.

Tenga en cuenta que las dos últimas subconsultas necesitarán el resultado de la primera. Por lo tanto, si se utilizan subconsultas, nuestra solución incluirá subconsultas definidas dentro de otras subconsultas. Todo esto 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;

Por el contrario, si utilizamos expresiones de tabla comunes, definimos nuestros tres CTEs al principio de la consulta, los referenciamos en la consulta principal cuando sea necesario, y evitamos múltiples subconsultas anidadas:

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;

En este caso, puedes observar que las consultas WITH hacen que tu código no sólo sea más legible sino también mucho más corto.

#3. Las CTEs dividen los cálculos complejos en partes

Las CTEs aportan claridad al proceso de cálculo. Cuando se utilizan subconsultas para los cálculos, la consulta se convierte a menudo en una maraña de subconsultas. Las CTEs, en cambio, muestran el proceso de cálculo con mayor claridad.

Supongamos que queremos saber cuántos empleados junior y experimentados hay en cada departamento. Consideraremos a los empleados con dos o menos años de experiencia como junior y a los que tienen más de dos años de experiencia como experimentados.

Aquí necesitamos básicamente dos subconsultas:

1. Para calcular el número de empleados junior en cada departamento.
2. Para calcular el número de empleados con experiencia en cada departamento.

Así es como podemos enfocar esto con subconsultas:

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 ejemplo no es muy complicado, así que probablemente puedas seguir la consulta anterior. Sin embargo, observe que:

• Tiene que adivinar el propósito de cada subconsulta.
• No está inmediatamente claro qué tablas se unen.
• La cláusula SELECT enumera los campos que se mostrarán en la salida, pero estos campos sólo se definen posteriormente dentro de las subconsultas.

Cuando los cálculos se complican, es realmente un reto seguir el proceso de cálculo a través de todas las subconsultas. En cambio, el proceso de cálculo utilizando CTEs es mucho más legible:

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;

Aquí, empezamos por definir todos los conjuntos de resultados temporales necesarios para los cálculos. A continuación, escribimos una simple consulta para obtener el resultado necesario. Todo está claro y ordenado.

Créame, apreciará realmente la estructura que proporcionan las expresiones de tabla comunes cuando trabaje con cálculos más complejos. Para probar mi punto, quiero sugerir otro ejemplo con cálculos anidados.

#4. Los CTEs anidados son realmente geniales

Considerando la reutilización de las CTEs y su capacidad para clarificar el proceso de cálculo, no es sorprendente que las cláusulas WITH sean perfectas para los cálculos anidados.

Digamos que queremos calcular cuántos empleados de cada departamento tienen una puntuación de KPI (1) por encima de la media o (2) por debajo de la media en su respectivo departamento. En concreto, para cada departamento, queremos mostrar la puntuación media del KPI, el número de empleados con un KPI superior a la media y el número de empleados con un KPI inferior a la media.

Aquí necesitamos básicamente tres subconsultas:

1. Obtener el KPI medio de cada departamento.
2. Obtener el número de empleados que tienen una puntuación de KPI superior a la media.
3. Para obtener el número de empleados que tienen una puntuación de KPI inferior a la media.

Sin embargo, al utilizar subconsultas, tendrá que añadir la primera subconsulta tres veces, incluyendo dos veces en las que estará anidada dentro de las otras dos subconsultas. La consulta resultante parece bastante desordenada:

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;

La consulta funcionó y obtuvimos la salida que queríamos, pero fíjate en lo difícil que es seguir las múltiples subconsultas anidadas. En el mundo real, a menudo se complica aún más.

Por el contrario, vea lo bien que se ve la misma consulta cuando se usan CTEs en lugar 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;

Puede seguir claramente cómo se definen los tres conjuntos de resultados temporales. Puede entender su propósito a partir de los nombres asignados. No necesita repetir la misma subconsulta varias veces dentro de la misma consulta principal. Cuando llega a la consulta principal, todo el trabajo preparatorio está hecho y puede simplemente unir los CTEs que ya han sido definidos.

#5. Las CTEs permiten la recursión

Por último, pero no menos importante, las CTEs son excelentes para procesar gráficos, árboles y otras estructuras jerárquicas. Esto se debe a que la sintaxis WITH puede procesar la recursión. Una consulta recursiva es una consulta que se refiere a sí misma.

Por ejemplo, si tenemos una estructura organizativa típica, en la que cada empleado tiene un superior y los superiores tienen múltiples subordinados, los CTE recursivos pueden ayudarnos a analizar estos datos de forma eficiente. Con una sola consulta SQL y datos a nivel individual, podemos calcular el salario total de cada uno de los departamentos y subdepartamentos o el número total de días de vacaciones no utilizados en cada una de las unidades organizativas.

La sintaxis de los CTEs recursivos es bastante complicada. Para una explicación detallada, recomiendo leer este artículo que explica cómo las consultas recursivas procesan las estructuras jerárquicas. También puedes consultar estos ejemplos de aplicación de consultas recursivas a estructuras gráficas.

¡Practiquemos los CTEs!

Ahora que te has dado cuenta de las ventajas que aportan las CTEs a las consultas SQL, ¡es hora de practicar! Te recomiendo que empieces con nuestro Consultas recursivas y expresiones de tabla comunes curso interactivo (114 desafíos de codificación), que cubre todo tipo de expresiones de tabla comunes, incluyendo CTEs simples, CTEs anidados y CTEs recursivos. Obtenga más detalles en este artículo general.

Gracias por leer y ¡feliz aprendizaje!