CTE vs. Subconsulta en SQL: ¿Cuál es la Diferencia?

¿Qué son las Expresiones Comunes de Tabla (CTEs)? ¿Son lo mismo que las subconsultas? ¿Cuándo es necesario utilizar las CTE? Este artículo analiza las similitudes y diferencias entre las CTE y las subconsultas.

Cuando presento a un estudiante las Expresiones Comunes de Tabla, su primera reacción es "¡Eso es sólo una subconsulta! ¿Por qué tengo que aprender eso?". Vamos a responder a esta pregunta viendo lo que se puede hacer con una subconsulta SQL y qué ventajas adicionales tiene utilizar una CTE.

Si después de leer este artículo decides que te gustaría aprender a utilizar las CTE de SQL, LearnSQL.es te ofrece un completo curso de Expresiones Comunes de Tabla que cubre el tema en profundidad. Comenzando con ejemplos sencillos, trabajarás con conceptos difíciles como el procesamiento de árboles y gráficos. Cada tema cuenta con explicaciones y ejemplos paso a paso. Con 114 ejercicios interactivos, obtendrá mucha práctica. Probablemente necesitará unas 18 horas para completar el curso.

¿Qué es una subconsulta?

Una subconsulta es una consulta anidada dentro de la consulta principal; esto se explica mejor viendo un ejemplo. Todos los ejemplos en este artículo utilizan SQL Server; la sintaxis puede ser ligeramente diferente en otros dialectos de SQL.

Supongamos que la dirección quiere ofrecer un descuento a todos los clientes cuyas compras anuales hayan sido superiores a la media de todos los clientes. La consulta para extraer una lista de estos clientes podría tener este aspecto:

SELECT 
  account_no, 
  name 
FROM customers 
WHERE annual_purchases >
  (SELECT AVG(annual_purchases) FROM customers);

La consulta principal enumera los clientes y la subconsulta calcula las compras anuales medias de todos los clientes. La subconsulta no necesita un nombre (excepto cuando se trabaja en PostgreSQL).

Si no estás familiarizado con las subconsultas, puedes echar un vistazo al curso SQL para principiantes de LearnSQL.es, que tiene una sección dedicada a las subconsultas. Puedes leer más sobre subconsultas en nuestra Guía para principiantes sobre la subconsulta SQL. Cómo Practicar las Subconsultas SQL te da algunas ideas sobre cómo poner en práctica estos conceptos.

¿Qué son las CTEs?

Las Expresiones Comunes de Tabla son conjuntos de resultados con nombre que se definen al principio de una consulta y a los que ésta puede acceder como si fueran tablas. Supongamos que queremos comparar los salarios de los empleados con el salario medio de su función. La consulta podría tener este aspecto:

WITH avg_salary AS (
  SELECT 
    role, 
    avg(salary) AS average 
  FROM employee 
  GROUP BY role
)
SELECT 
  employee.role, 
  name, 
  salary, 
  avg_salary
FROM employee 
JOIN avg_salary ON avg_salary.role = employee.role
ORDER BY role, name

Los CTE se introducen mediante la palabra clave WITH, y los resultados se almacenan en una tabla temporal con nombre. En este ejemplo, los resultados de la CTE se almacenan en la tabla avg_salary, que es utilizada por la consulta principal para obtener el salario medio de cada función.

De hecho, las CTE sencillas como ésta también podrían escribirse como subconsultas. El artículo "¿Qué es una CTE?" explica las CTE con más detalle.

¿Cuáles son las Diferencias entre CTEs y Subconsultas?

Comenzaré por enumerar brevemente las principales diferencias, y luego examinaré algunas de ellas con más detalle.

• Las CTEs se definen al principio de la consulta, mientras que las subconsultas se definen en línea.
• Las CTEs siempre deben tener nombre. Sólo PostgreSQL insiste en que las subconsultas deben tener un nombre.
• Las CTEs pueden usarse recursivamente. Explicaré esto más adelante en el artículo.
• Las CTEs son mucho más legibles que las subconsultas cuando se está escribiendo un informe complejo.
• Una CTE puede utilizarse muchas veces dentro de una consulta, mientras que una subconsulta sólo puede utilizarse una vez. Esto puede hacer que la definición de la consulta sea mucho más corta, pero no se traducirá necesariamente en una mejora del rendimiento.
• Las subconsultas se pueden utilizar en una cláusula WHERE junto con las palabras clave IN o EXISTS, pero no se puede hacer esto con las CTE.
• Las subconsultas pueden utilizarse para recoger un único dato de una tabla con el fin de actualizar un valor de otra tabla.

Algunas funcionalidades están limitadas únicamente a las subconsultas. Daré dos ejemplos en los que las subconsultas no pueden sustituirse por CTEs. En el primer ejemplo, la subconsulta forma parte de la cláusula WHERE. En el segundo, se utiliza para recoger un único valor de una tabla con el fin de actualizar otra. Como he mencionado anteriormente, SQL no permite utilizar CTEs para estas tareas.

Sólo Subconsultas: Uso de Subconsultas en WHERE

En el primer ejemplo, un banco guarda los detalles de todas las transacciones del día en una tabla llamada daily_trans. Los datos de esta tabla incluyen un número de cuenta, un código de transacción y un importe.

La base de datos también tiene una tabla llamada transaction_typesy sus columnas incluyen el código de transacción y un indicador llamado debit_credit, que se establece en 1 para los tipos de transacción que abonan en la cuenta del cliente, y en 2 para los que cargan en la cuenta.

Si el banco desea obtener una lista de todas las transacciones de crédito del día, la consulta podría tener este aspecto:

SELECT 
  account_no, 
  tran_code, 
  amount
FROM daily_trans
WHERE tran_code IN
  (SELECT tran_code 
   FROM transaction_types
   WHERE debit_credit = 1);

Este tipo de consulta sólo puede escribirse utilizando una subconsulta. Aquí no se podría sustituir la subconsulta por una CTE.

Sólo Subconsultas: Uso de Subconsultas en UPDATE

En el segundo ejemplo, el mismo banco tiene una tabla llamada customercuyas columnas incluyen un número de cuenta, un nombre de cliente y el número de empleado de la persona designada para la atención al cliente.

El banco ha realizado una reorganización de responsabilidades y desea reasignar a la persona de soporte para algunos clientes. Para ello, ha creado una tabla llamada reassignments que contiene el número de identificación del antiguo empleado de soporte y el número de empleado de la persona de soporte que asumirá sus responsabilidades.

Para llevar a cabo las reasignaciones, podríamos escribir una consulta como ésta:

UPDATE customer 
SET support_person =
  (SELECT new_employee 
   FROM reassignments
   WHERE old_employee = customer.support_person);

Las CTEs hacen más legibles las consultas complejas

Para ilustrar cómo las CTEs pueden hacer las cosas más fáciles de entender, tomemos una consulta que utiliza múltiples subconsultas y recodifiquémosla utilizando CTEs.

Supongamos que tenemos una tienda que vende tres tipos de productos: libros, música y vídeos. El gerente quiere saber cuánto ha comprado cada cliente en cada categoría.

El informe puede tener este aspecto:

Esta es una consulta que utiliza subconsultas para producir el informe:

SELECT 
	customer, 
	sum(purchases) AS Total, 
	total_books AS Books, 
	total_music AS Music, 
	total_videos AS Videos
FROM sales
JOIN 
	(SELECT account_no, sum(purchases) AS total_books FROM sales 
	WHERE product_type = 'Books'
	GROUP BY account_no) books
ON books.account_no = sales.account_no
JOIN 
	(SELECT account_no, sum(purchases) AS total_music FROM sales 
	WHERE product_type = 'Music'
	GROUP BY account_no) music
ON music.account_no = sales.account_no
JOIN 
	(SELECT account_no, sum(purchases) AS total_videos FROM sales 
	WHERE product_type = 'Videos'
	GROUP BY account_no) videos
ON videos.account_no = sales.account_no
GROUP BY customer
ORDER BY customer

Como puede ver, es bastante complicado. Y es difícil de seguir si alguien más necesita hacerle cambios más tarde.

Veamos ahora qué aspecto tendría esta consulta si la reescribiéramos utilizando Expresiones Comunes de Tabla:

WITH books AS (
  SELECT 
    customer, 
    sum(purchases) AS total_books 
  FROM sales 
  WHERE product_type = 'Books'
  GROUP BY customer
),

music AS (
  SELECT 
    customer, 
    sum(purchases) AS total_music 
  FROM sales 
  WHERE product_type = 'Music'
  GROUP BY customer
),

videos as (
  SELECT 
    customer, 
    sum(purchases) AS total_videos 
  FROM sales 
  WHERE product_type = 'Videos'
  GROUP BY customer
)

SELECT 
  customer, 
  sum(purchases) AS Total, 
  total_books AS Books, 
  total_music AS Music, 
  total_videos AS Videos
FROM sales
JOIN books ON books.customer = sales.customer
JOIN music ON music.customer = sales.customer
JOIN videos ON videos.customer = sales.customer
GROUP BY customer
ORDER BY customer

La mayoría de la gente encontrará esta segunda versión mucho más fácil de entender, aunque ambas consultas producen exactamente los mismos resultados.

¿Qué son las ETC recursivas?

Antes he dicho que sólo las CTEs pueden ser recursivas. Entonces, ¿qué es una consulta recursiva? Las consultas recursivas le permiten navegar por datos jerárquicos y producir informes adecuados para datos de tipo árbol y gráfico. Algunos ejemplos de datos jerárquicos son

• En una organización, un empleado puede depender de un subgerente; el subgerente depende de un gerente, y el gerente depende del gerente.
• En la fabricación, un producto puede estar formado por varios componentes. Cada componente también puede estar formado por muchos subcomponentes, y los subcomponentes pueden estar hechos de varias materias primas.

Veamos un ejemplo. Una heladería tiene varios productos en su carta. Por ejemplo, un banana split se compone de plátanos, salsa de chocolate y helado. Pero la salsa de chocolate también tiene varios ingredientes. Puede llevar cacao en polvo, azúcar y otros ingredientes.

El propietario quiere una lista completa de cada plato del menú seguida de todos sus ingredientes. Parte de la lista puede tener este aspecto:

En la base de datos tenemos dos tablas:

• La tabla Item contiene una lista de cada plato del menú y de cada ingrediente.
• La tabla Bill_of_materials contiene los enlaces entre cada artículo y sus ingredientes.

La tabla Items contiene esta información:

He aquí algunos ejemplos de entradas en la Bill_of_materials tabla. La columna item_id contiene un enlace al artículo principal de la tabla. Items mientras que component_id contiene un enlace a uno de sus ingredientes. Por lo tanto, la primera entrada muestra que el artículo 10: Cacao es un ingrediente del artículo 9: Salsa de chocolate.

A continuación se muestra la consulta recursiva utilizada para recorrer esta información. La consulta se ha escrito en SQL Server; en otros dialectos sería ligeramente diferente.

WITH menu_ingredients (id, path, description, item_id)
AS (
  SELECT 
CAST (id AS bigint), 
CAST (description as varchar (255)), 
CAST ('' AS varchar(40)),
CAST (id AS bigint)
   FROM items 
   WHERE description = 'Menu'
   
   UNION ALL
   
   SELECT
CAST (bom.component_id AS bigint), 
CAST (m.path + ' > ' + i.description AS varchar(255)),
i.description,
CAST (bom.item_id AS bigint)
   FROM menu_ingredients m, bill_of_materials bom
   JOIN items i
   ON i.id = bom.component_id
   WHERE bom.item_id = m.id
)

SELECT 
  path, 
  description 
FROM menu_ingredients
ORDER BY path

Una explicación completa de lo que está pasando aquí está fuera del alcance de este artículo, pero voy a explicar rápidamente los conceptos básicos. En pocas palabras, cuando se añade una fila al conjunto de resultados en una consulta recursiva, ésta puede "ver" la fila anterior y utilizarla para recoger información que puede utilizarse para encontrar la siguiente fila. Esta consulta comienza por la entrada de nivel superior de la lista de materiales: el propio menú. A partir de ahí, puede recorrer todas sus filas "hijas", es decir, los ingredientes que lo componen. Y cada ingrediente puede recoger sus propias filas hijas, si las tiene. Para una explicación más detallada de la recursividad, consulte ¿Qué es una CTE recursiva en SQL? Y como mencioné anteriormente, las consultas recursivas están completamente cubiertas en LearnSQL.es's Common Table Expressions course.

CTE vs. Subconsulta

En resumen, elija una CTE cuando:

• Desea que una consulta compleja sea más legible.
• Necesita utilizar una consulta recursiva.

Elija una subconsulta cuando:

• Está utilizando las palabras clave de la cláusula WHERE IN o EXISTS para recoger los criterios de selección de otra tabla.
• Desea seleccionar un único dato de otra tabla como nuevo valor de un campo en una sentencia UPDATE.

En cualquier otra circunstancia, puede elegir la que le resulte más cómoda.

Y si necesitas practicar con CTEs o subconsultas, puedes probar el sitio web Ejercicios prácticos de SQL que contiene cientos de ejercicios interactivos que te ayudarán a consolidar tus conocimientos.

¡Feliz aprendizaje!