CTE vs. Tabla temporal: ¿Cuál es la diferencia?

SQL te ofrece dos opciones para manejar consultas complejas de manera eficiente: las CTE y las tablas temporales. Sigue leyendo para sopesar los pros y los contras de las CTE frente a las tablas temporales.

En SQL (Lenguaje de Consulta Estructurado), una expresión común de tabla (CTE) permite definir una subconsulta y asignarle un nombre para que pueda utilizarse dentro de una consulta principal. Una tabla temporal es una tabla de base de datos que se crea explícitamente, como cualquier otra tabla - pero con la particularidad de que desaparece cuando se cierra la conexión de base de datos en la que se creó. Así que sí, las CTE y las tablas temporales tienen algunas cosas en común. Pero hay más en el debate CTE vs. tabla temporal.

Para dominar a fondo este tema, puedes tomar nuestro curso Consultas recursivas y expresiones de tabla comunes . Aprenderás a utilizar CTEs para procesar estructuras de datos complejas como árboles y gráficos. Con sus 114 ejercicios, este curso le guiará hacia el dominio de las consultas SQL más complejas. También incluye un cuestionario final para poner a prueba los conocimientos adquiridos.

Las CTEs y las tablas temporales tienen propósitos en común. Ambas generan resultados intermedios para una consulta sin dejar objetos permanentes en la base de datos; esto ahorra espacio de almacenamiento. Pero existen diferencias importantes entre ellas, por lo que es útil saber cuándo utilizar una u otra. Empecemos por examinar la sintaxis de la CTE.

Sintaxis de la CTE

A continuación veremos un caso de uso en el que una CTE SQL es preferible a una consulta recursiva.

Una CTE SQL comienza con una cláusula WITH, seguida de un nombre y luego la definición de su subconsulta. Después, hay una sentencia SQL ordinaria (normalmente SELECT) que consulta los resultados de la CTE como si fuera una tabla normal. En su forma más simple, una CTE tiene este aspecto:

WITH cte_name AS (
  cte_query_definition
)
SELECT * 
FROM cte_name;

Para más detalles sobre la sintaxis CTE, lea Qué es una Expresión de Tabla Común en SQL.

Sintaxis de Tablas Temporales

Para las tablas temporales, la sintaxis varía ligeramente dependiendo de su sistema de gestión de bases de datos relacionales (RDBMS). La siguiente tabla muestra cómo crear tablas temporales en los RDBMS más populares:

CREATE TEMPORARY TABLE MyTempTbl (
  Id INT, 
  Name VARCHAR(50)
);

-- Local temp table:
CREATE TABLE #MyTempTbl (
  Id INT, 
  Name NVARCHAR(50)
);

-- Global temp table:
CREATE TABLE ##MyTempTbl (
  Id INT, 
  Name NVARCHAR(50)
);

CREATE TEMPORARY TABLE MyTempTbl (
  Id INT, 
  Name VARCHAR(50)
);

CREATE GLOBAL TEMPORARY TABLE MyTempTbl (
  Id NUMBER, 
  Name VARCHAR2(50)
)
-- data is kept until the end 
-- of the current transaction
ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE MyTempTbl (
  Id NUMBER, 
  Name VARCHAR2(50))
-- data is kept until the end 
-- of the current session
ON COMMIT PRESERVE ROWS;

Para obtener más información sobre la creación de todo tipo de tablas en SQL, sigue nuestro curso Los fundamentos de la creación de tablas en SQL. Sus 80 ejercicios te ayudarán a dominar el comando CREATE TABLE y todas sus opciones. Empezarás creando una tabla en su forma más básica y luego progresarás hasta definir claves primarias y foráneas, columnas NULL/NOT NULL y modificar la estructura de tablas existentes.

Ahora que hemos visto la sintaxis tanto de las CTE como de las tablas temporales, examinemos cómo funciona cada una de ellas

Resolviendo el Mismo Problema con CTEs y Tablas Temp

Intentemos un problema de ejemplo que puede resolverse utilizando una CTE y una tabla temporal.

Tiene dos tablas: una tabla detallada Sales y una tabla Countries tabla con información específica de cada país. Desea listar los totales de ventas por país junto con la información detallada del país. Puede conseguirlo utilizando una expresión común de tabla o una tabla temporal. Exploraremos ambos métodos para ver cómo se pueden utilizar eficazmente para esta tarea.

La solución basada en CTE utiliza una CTE denominada CountryTotals con una subconsulta que calcula los totales agrupados por CountryCode. También tiene una consulta principal que combina esos totales con los datos de cada país:

WITH CountryTotals (CountryCode, TotalSales) AS (
  SELECT 
    CountryCode, 
    SUM(Sales) AS TotalSales
  FROM Sales
  GROUP BY CountryCode
)
SELECT 
  c.*, 
  ct.TotalSales
FROM Countries c
INNER JOIN CountryTotals ct 
  ON ct.CountryCode = C.CountryCode;

Puede encontrar muchos otros ejemplos de expresiones comunes de tabla en estas CTE explicadas con ejemplos.

Para obtener el mismo resultado utilizando una tabla temporal, primero debe crear la tabla temporal con las columnas CountryCode y TotalSales. El siguiente código de ejemplo utiliza la sintaxis MySQL para hacerlo:

CREATE TEMPORARY TABLE CountryTotals (
  CountryCode char(3), 
  TotalSales decimal(15, 2)
);

Las columnas que definen la estructura de la tabla temporal son las mismas que las incluidas en la definición CTE de más arriba. Para crear la tabla temporal, el tipo de datos de cada columna debe coincidir con el devuelto por la columna correspondiente de la consulta que utilizará para llenarla de datos. Esta consulta se muestra a continuación:

Observe que el SELECT colocado después del INSERT es similar al que define la subconsulta en el CTE original.

Ahora que tiene los totales de ventas por país en la tabla temporal, puede unirla con la tabla Countries para obtener el resultado final:

SELECT
  c.*,
  ct.TotalSales
FROM Countries c
INNER JOIN CountryTotals ct 
  ON ct.CountryCode = C.CountryCode;

Esta consulta también es idéntica a la consulta principal del CTE (la que está fuera de la cláusula WITH ), ya que la tabla temporal tiene el mismo nombre que el CTE.

Es importante señalar que no siempre es posible sustituir las tablas temporales por CTE. Puede hacerse cuando las filas de la tabla temporal proceden de un único SELECT. Pero si los datos son modificados por UPDATE, DELETE u otros comandos INSERT después de haber sido cargados en la tabla temporal, no podrá reemplazar la tabla temporal con una CTE.

Cuándo utilizar una CTE frente a una tabla temporal

Incluso en aquellas situaciones en las que se puede sustituir una CTE por una tabla temporal (o viceversa), no siempre es una buena práctica hacerlo. Los casos de uso de las CTE y los de las tablas temporales son diferentes; hay situaciones en las que es preferible una CTE y situaciones en las que es preferible una tabla temporal.

Examinemos los casos de uso de las CTEs y las tablas temporales, empezando por las CTEs.

Casos de uso de CTE

Las CTEs no generan objetos persistentes en la base de datos. En otras palabras, el CTE sólo vive mientras se ejecuta la consulta que lo contiene. Una vez finalizada la ejecución de la consulta, el CTE desaparece sin dejar rastro. Además, la CTE y la consulta que la consume están contenidas en la misma sentencia SQL. Estas cualidades hacen que la CTE sea preferible para mejorar la legibilidad y la organización de las secuencias SQL, es decir, para facilitar la comprensión de las consultas por parte de los humanos.

Conjuntos de datos pequeños

Cuando el resultado de una subconsulta arroja un número relativamente pequeño de filas (es decir, 10 ó 20 mil filas), una CTE es la forma ideal de implementarla. Al no utilizar almacenamiento persistente, no genera operaciones de E/S en disco. Por lo tanto, las consultas se resuelven mucho más rápido.

Dentro de las Vistas

No se puede crear, rellenar con datos o consultar una tabla temporal dentro de una vista. Pero puedes incluir una CTE dentro de una vista y utilizarla en cualquier lugar como si fuera una tabla de sólo lectura.

Refactorización de

A la hora de refactorizar consultas de bases de datos, las CTE son grandes aliadas. En consultas largas y complejas, las CTE permiten definir resultados intermedios (es decir, subconsultas con nombre) que pueden reutilizarse repetidamente dentro de la misma consulta.

Además, la complejidad de una consulta larga puede reducirse gradualmente con CTEs anidadas hasta simplificarla a un SELECT de sólo unas pocas líneas.

Mejorar el rendimiento de las bases de datos

En algunos RDBMS (por ejemplo, MS SQL Server), las CTE permiten crear planes de ejecución más eficientes que las tablas temporales. Esto puede mejorar sustancialmente el rendimiento de la base de datos al reducir los tiempos de ejecución de las consultas.

El beneficio de rendimiento obtenido depende del optimizador del RDBMS, lo que significa que el uso de CTEs no siempre se traduce en un mejor rendimiento. Pero es un factor a tener en cuenta a la hora de elegir entre una CTE y una tabla temporal para la gestión de subconsultas.

Recursión

Un área en la que el uso de CTEs es obligatorio es en las consultas recursivas. En SQL, una CTE recursiva es capaz de invocarse a sí misma para implementar procesos recursivos, como atravesar estructuras de datos jerárquicas. En otras palabras, la recursividad requiere CTEs.

Permitir la recursividad en SQL evita la necesidad de utilizar otros lenguajes de programación; esto promueve una mayor eficiencia en las consultas que deben invocarse a sí mismas. Puedes leer más en nuestro artículo ¿Qué es una CTE Recursiva en SQL?

Ahora que ya sabes en qué casos es conveniente utilizar una CTE, puedes reforzar tus conocimientos repasando las preguntas más comunes de las entrevistas sobre CTE en SQL y entrenándote con estos ejercicios de CTE.

Casos de Uso de Tablas Temporales

La principal ventaja de las CTE - no almacenar datos de forma persistente - puede ser una desventaja. En algunos casos de uso, es preferible tener los datos almacenados en tablas (incluso temporales). Veamos las situaciones más típicas.

Reutilización de conjuntos de datos

La reutilización del código CTE se limita a una única consulta. En cambio, los datos almacenados en una tabla temporal pueden utilizarse repetidamente en diferentes consultas. El requisito clave es que estas consultas se ejecuten dentro de la misma conexión de base de datos.

Procesos ETL

Cuando se implementan procesos ETL (Extraer, Transformar, Cargar) mediante secuencias de comandos SQL, a menudo es necesario generar resultados intermedios y aplicar transformaciones sucesivas a un único conjunto de datos. Sólo una vez hecho esto podrá volcarlos finalmente en tablas permanentes. En estas situaciones, resulta muy útil mantener disponibles tablas temporales durante todo el proceso de transformación.

Grandes conjuntos de datos

Cuando se trabaja con conjuntos de datos temporales de gran volumen (por ejemplo, millones de registros), las tablas temporales son siempre preferibles a las CTE. Si intenta ejecutar una CTE que incluya una subconsulta de gran volumen, es muy probable que el motor de la base de datos intente almacenar los resultados temporales en la memoria del servidor. Esto agotaría la capacidad disponible o requeriría algunos recursos del sistema operativo para volcar la memoria al disco. En resumen, provocaría un bajo rendimiento de la base de datos. Por tanto, si se sabe de antemano que los resultados de una subconsulta van a ser muy grandes, es mejor utilizar tablas temporales en lugar de CTEs.

Generación de índices

Como cualquier otra tabla de una base de datos, las tablas temporales admiten la creación de índices para acelerar la ejecución de las consultas. Cuando los resultados intermedios requieren un gran número de filas (y deben utilizarse repetidamente), almacene esos resultados en una tabla temporal con uno o más índices. La creación de los índices puede llevar algún tiempo, pero este tiempo se recupera con creces si luego deben ejecutarse varias consultas sobre el mismo conjunto de datos.

En general, los mejores candidatos para la indexación son las columnas utilizadas en JOINs, WHERE, o GROUP BY.

Resumiendo el debate CTE vs. Tabla temporal

Elegir entre CTEs y tablas temporales no siempre es fácil. Hemos discutido la sintaxis y los casos de uso típicos de ambas soluciones.

Recuerde explorar nuestro Consultas recursivas y expresiones de tabla comunes que le enseñará a utilizar las expresiones comunes de tabla. El curso está dirigido a analistas de bases de datos principiantes, estudiantes y desarrolladores que deseen profundizar sus conocimientos de SQL. Para realizar el curso, sólo necesita un ordenador con conexión a Internet y un navegador web.

¡Ahora es el momento de desplegar su sabiduría! Si sabe cuándo utilizar CTEs frente a tablas temporales, siempre conseguirá la máxima eficacia cuando trabaje con datos temporales.