Una Common Table Expression (CTE), también conocida como 'WITH clause', es una característica estándar de SQL que permite definir un conjunto de resultados temporal y nombrado dentro del ámbito de una única sentencia. Actúa como una vista temporal que solo existe durante la ejecución de la consulta. Las CTEs mejoran la legibilidad de consultas complejas al descomponerlas en bloques lógicos más pequeños y reutilizables. Pueden ser no recursivas o recursivas, siendo estas últimas particularmente útiles para recorrer jerarquías o grafos.
Las CTEs son una característica ampliamente soportada en la mayoría de los sistemas de gestión de bases de datos relacionales modernos. Ejemplos concretos incluyen: PostgreSQL, SQL Server (desde SQL Server 2005), MySQL (desde MySQL 8.0), Oracle Database (desde Oracle 9i Release 2), y SQLite. También son utilizadas en entornos de Big Data y data warehousing con motores de consulta como Apache Hive, Presto/Trino y Google BigQuery, donde facilitan la estructuración de transformaciones de datos complejas dentro de las consultas SQL.
Para un Arquitecto de Sistemas, las CTEs son una herramienta valiosa para la gestión de la complejidad y la optimización de la lógica de negocio en la capa de datos. Permiten encapsular lógica compleja, mejorando la mantenibilidad y la depuración de las consultas. Estratégicamente, pueden ser utilizadas para simular vistas sin la sobrecarga de persistir metadatos, o para refactorizar consultas monolíticas en componentes más manejables. Sin embargo, es crucial entender que las CTEs no siempre garantizan una mejora de rendimiento; en algunos casos, el optimizador de consultas puede materializarlas, lo que podría consumir más recursos. La decisión de usarlas debe sopesar la legibilidad y la modularidad frente a las consideraciones de rendimiento específicas del motor de base de datos y el volumen de datos, a menudo requiriendo pruebas de rendimiento y análisis del plan de ejecución.