0Pricing
SQL Interview Prep · Lección

CTE frente a subconsulta frente a tabla temporal

Compare las ventajas y desventajas de la materialización, la reutilización y el comportamiento del optimizador

CTE frente a subconsulta frente a tabla temporal es una lección gratuita de SQL Interview Prep en CoddyKit. Esta es la lección 3 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de SQL Interview Prep, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de SQL Interview Prep incluye 4 lecciones en total.

Tres formas de organizar la lógica

Cuando una consulta necesita un resultado intermedio, dispone de tres herramientas habituales: una subconsulta, un CTE y una tabla temporal. Los entrevistadores le piden compararlas porque la elección indica si comprende la materialización y el comportamiento del optimizador.

Esta lección le proporciona un marco de decisión que podrá exponer incluso bajo presión.

La subconsulta

Una subconsulta es una consulta insertada dentro de otra, normalmente en FROM, WHERE o SELECT. Forma parte de la misma instrucción y el optimizador la considera una única unidad.

  • No necesita un nombre; las tablas derivadas sí necesitan un alias.
  • El optimizador puede combinarla libremente con la consulta externa.
  • Se vuelve extensa y difícil de leer cuando se anida a demasiada profundidad.
SELECT *
FROM (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
) t
WHERE t.total > 1000;

El CTE

Un CTE es una subconsulta con nombre dentro de un bloque WITH, cuyo ámbito se limita a una sola instrucción. Se lee mejor que una subconsulta profundamente anidada y se puede referenciar varias veces.

  • Tiene un nombre, por lo que documenta la intención.
  • Se puede referenciar más de una vez en la misma instrucción.
  • Sigue teniendo el ámbito de una sola instrucción y después desaparece.
WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM spend
WHERE total > 1000;

La tabla temporal

Una tabla temporal es una tabla física real que existe durante la sesión o la transacción. Se rellena con una instrucción y se consulta en instrucciones posteriores e independientes.

  • Persiste durante varias instrucciones de la sesión.
  • Puede tener índices y estadísticas.
  • Implica operaciones de E/S de disco y una limpieza explícita.
CREATE TEMP TABLE spend AS
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id;

SELECT * FROM spend WHERE total > 1000;

La materialización: la distinción fundamental

El concepto clave que exploran los entrevistadores es la materialización: si el resultado intermedio se escribe físicamente en algún lugar.

  • Las subconsultas y los CTE normalmente no se materializan; el optimizador suele insertarlos directamente en la consulta.
  • Una tabla temporal siempre se materializa en el almacenamiento.
  • Algunas bases de datos permiten forzar o impedir la materialización de CTE mediante indicaciones.

Las barreras del optimizador y la antigua trampa de Postgres

Históricamente, PostgreSQL trataba cada CTE como una barrera de optimización, lo materializaba e impedía el desplazamiento de predicados. Desde Postgres 12, los CTE sencillos no recursivos a los que se hace referencia una sola vez se insertan directamente de forma predeterminada; las indicaciones MATERIALIZED y NOT MATERIALIZED permiten anular este comportamiento.

Mencionar este matiz demuestra un sólido perfil sénior.

WITH spend AS NOT MATERIALIZED (
    SELECT customer_id, SUM(amount) AS total
    FROM orders GROUP BY customer_id
)
SELECT * FROM spend WHERE total > 1000;

Reutilización dentro de una sola instrucción

Si hace referencia varias veces al mismo resultado intermedio en una instrucción, un CTE puede ser más claro que repetir una subconsulta. Sin embargo, tenga cuidado: un CTE insertado directamente puede recalcularse en cada referencia.

Cuando el recálculo resulta costoso, forzar la materialización o utilizar una tabla temporal evita realizar el trabajo dos veces.

Reutilización entre instrucciones

Los CTE y las subconsultas solo existen durante una instrucción. Si necesita el mismo resultado en varias consultas independientes, la tabla temporal es la herramienta adecuada.

Un caso habitual es un proceso ETL de varios pasos o un informe en el que se crea una tabla de preparación una sola vez y después se ejecutan varios análisis sobre ella. Añadir índices a la tabla temporal puede acelerar cada consulta posterior.

Índices y estadísticas

Solo una tabla temporal puede tener índices y estadísticas actualizadas. Para un conjunto intermedio enorme que se une muchas veces, esto puede ser decisivo.

  • CTE/subconsulta: el optimizador calcula estimaciones a partir de las tablas subyacentes.
  • Tabla temporal: puede ejecutar ANALYZE sobre ella y añadir índices optimizados para las uniones posteriores.

Por tanto, para resultados grandes y muy reutilizados, una tabla temporal puede ofrecer un mejor rendimiento pese a los pasos adicionales.

El marco de decisión

Una respuesta concisa para la entrevista:

  • Subconsulta: uso puntual, poca profundidad y legibilidad suficiente.
  • CTE: mejora la legibilidad o se referencia varias veces en una misma instrucción.
  • Tabla temporal: se reutiliza entre instrucciones, el resultado es muy grande o necesita índices y estadísticas.

Como opción predeterminada, elija un CTE por claridad; recurra a una tabla temporal cuando la materialización o la reutilización entre instrucciones aporte una ventaja real.

Cómo plantear la disyuntiva

Evite afirmaciones absolutas como «los CTE siempre son más lentos». Diga, en su lugar: los CTE y las subconsultas normalmente se insertan directamente, por lo que aportan legibilidad; una tabla temporal se materializa y merece la pena cuando reutilizo un resultado grande entre instrucciones o necesito un índice.

Reconocer que este comportamiento depende del motor y, en Postgres, también de la versión demuestra un conocimiento profundo.

Comprobación rápida

Elija el escenario en el que una tabla temporal sea claramente la mejor opción.

Repaso: CTE frente a subconsulta y tabla temporal

La elección depende de la materialización y el ámbito.

  • Subconsultas y CTE: normalmente se insertan directamente, tienen el ámbito de una sola instrucción y se eligen por su legibilidad.
  • Los CTE aportan nombres y reutilización dentro de una misma instrucción.
  • Tablas temporales: siempre se materializan, persisten entre instrucciones y pueden tener índices.
  • Postgres 12 y versiones posteriores inserta directamente los CTE sencillos; utilice las indicaciones MATERIALIZED para controlarlo.

A continuación: refactorizar una consulta anidada y enrevesada para convertirla en CTE claros.

Preguntas frecuentes

¿La lección «CTE frente a subconsulta frente a tabla temporal» es gratis?

Sí — el texto completo de «CTE frente a subconsulta frente a tabla temporal» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de SQL Interview Prep, actualiza a CoddyKit PRO. El curso de SQL Interview Prep incluye 4 lecciones en total.

¿Qué aprenderé en «CTE frente a subconsulta frente a tabla temporal»?

Compare las ventajas y desventajas de la materialización, la reutilización y el comportamiento del optimizador Practicas SQL Interview Prep con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.

¿Necesito experiencia previa para empezar SQL Interview Prep?

No se requiere experiencia previa. SQL Interview Prep en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 3 de 4.

¿Cuánto tiempo toma la lección «CTE frente a subconsulta frente a tabla temporal»?

La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.

¿Puedo escribir y ejecutar código en esta lección de SQL Interview Prep?

Sí. Cada lección de SQL Interview Prep incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.

Todas las lecciones de este curso

  1. Escribir su primer CTE
  2. Encadenar varios CTE
  3. CTE frente a subconsulta frente a tabla temporal
  4. Refactorizar consultas anidadas en CTE
← Volver a SQL Interview Prep