Tablas temporales en SQL

Lección 9.4 · Tiempo de lectura: ~7 min

Una tabla temporal en SQL es una tabla que existe solo durante la sesión (conexión) actual y se elimina automáticamente cuando termina. En esta lección aprenderás a crear tablas temporales, a llenarlas a partir de una consulta, en qué se diferencian de las tablas normales y de las CTE, y qué limitaciones tienen en MySQL.

Un análisis complejo suele hacerse por pasos: seleccionar los clientes que interesan y luego calcular varios informes sobre ellos. El resultado intermedio se puede repetir como subconsulta o CTE en cada informe, o guardarse una vez en una tabla temporal y reutilizarse en todas las consultas siguientes.

Las bases de datos de ejemplo de SQLtest son de solo lectura, así que allí CREATE TEMPORARY TABLE devuelve un error de permisos. Los ejemplos usan el esquema Sakila.

Qué es una tabla temporal

Una tabla temporal se ve y funciona como una tabla normal: puedes insertar, seleccionar, actualizar y eliminar filas. Las diferencias están en su duración y su visibilidad:

• es visible solo en la sesión que la creó; las demás conexiones no la ven, aunque creen una tabla temporal con el mismo nombre;
• se elimina automáticamente cuando termina la sesión;
• está pensada para datos intermedios, no para almacenamiento permanente.

Sintaxis

CREATE TEMPORARY TABLE table_name (
    column1 data_type,
    column2 data_type
);

Lo más habitual es crear una tabla temporal directamente a partir de una consulta:

CREATE TEMPORARY TABLE table_name AS
SELECT ...;

Ejemplo: reutilizar un resultado intermedio

Guardemos los clientes que hicieron más de 30 pagos:

CREATE TEMPORARY TABLE tmp_active_customers AS
SELECT customer_id, COUNT(*) AS payment_count
FROM payment
GROUP BY customer_id
HAVING COUNT(*) > 30;

Ahora varias consultas independientes pueden usarla sin repetir la agregación:

SELECT ac.customer_id, c.first_name, c.last_name, ac.payment_count
FROM tmp_active_customers AS ac
INNER JOIN customer AS c ON c.customer_id = ac.customer_id
ORDER BY ac.payment_count DESC;

SELECT COUNT(*) AS customers,
       AVG(payment_count) AS avg_payments,
       MAX(payment_count) AS max_payments
FROM tmp_active_customers;

Resultado: la lista de los clientes más activos y cifras de resumen del mismo grupo, calculado una sola vez.

Cuando ya no necesites la tabla en una sesión larga, elimínala explícitamente:

DROP TEMPORARY TABLE IF EXISTS tmp_active_customers;

En MySQL, DROP TEMPORARY TABLE elimina solo una tabla temporal, así que no puede borrar por error una tabla normal con el mismo nombre.

Tabla temporal vs tabla normal

La última fila es una trampa: si una tabla temporal se llama igual que una tabla existente, oculta esa tabla durante el resto de la sesión. Usa un prefijo como tmp_.

Tabla temporal vs CTE

Usa una CTE (lección 6.4) cuando el resultado intermedio se necesita en una sola consulta. Usa una tabla temporal cuando se necesita en varias consultas, cuando es costoso de calcular o cuando es grande y se beneficia de un índice:

CREATE TEMPORARY TABLE tmp_action_customers AS
SELECT DISTINCT r.customer_id
FROM rental AS r
INNER JOIN inventory AS i ON i.inventory_id = r.inventory_id
INNER JOIN film_category AS fc ON fc.film_id = i.film_id
INNER JOIN category AS c ON c.category_id = fc.category_id
WHERE c.name = 'Action';

ALTER TABLE tmp_action_customers ADD PRIMARY KEY (customer_id);

Limitaciones en MySQL

• Una referencia por consulta. MySQL no permite que una tabla temporal aparezca dos veces en la misma consulta, por ejemplo en un self-join o a la vez en la consulta externa y en una subconsulta: el error es "Can't reopen table". En esos casos usa una CTE o una tabla normal.
• Replicación y pools de conexiones. En aplicaciones que reutilizan conexiones, una tabla temporal puede sobrevivir hasta la siguiente petición en la misma conexión. Elimínala explícitamente cuando termines.
• Motor de almacenamiento. Las tablas temporales se guardan en memoria o en disco según su tamaño. ENGINE = MEMORY mantiene la tabla en RAM, pero no admite columnas TEXT/BLOB y está limitado por max_heap_table_size.

Conclusiones clave:

• Una tabla temporal vive hasta el final de la sesión y solo es visible en esa sesión.
• CREATE TEMPORARY TABLE ... AS SELECT guarda el resultado de una consulta.
• Una tabla temporal se puede reutilizar en muchas consultas e indexar; una CTE existe solo dentro de una sentencia.
• Usa un prefijo tmp_: una tabla temporal oculta una tabla normal con el mismo nombre.
• En MySQL, una tabla temporal solo se puede referenciar una vez por consulta.

Preguntas frecuentes

¿Cuándo se elimina una tabla temporal?

Cuando termina la sesión que la creó. Puedes eliminarla antes con DROP TEMPORARY TABLE table_name en MySQL o DROP TABLE table_name en otras bases de datos.

¿Cuál es la diferencia entre una tabla temporal y una CTE?

Una CTE existe solo dentro de una sentencia SQL. Una tabla temporal es una tabla real que existe hasta el final de la sesión, así que varias consultas separadas pueden usarla, y puede tener índices.

¿Otros usuarios pueden ver mi tabla temporal?

No. Una tabla temporal solo es visible en la sesión que la creó. Dos sesiones pueden incluso tener tablas temporales con el mismo nombre sin ningún conflicto.