Tablas derivadas

Una tabla derivada es una subconsulta que aparece en la cláusula FROM. MySQL ejecuta la subconsulta primero, almacena el resultado como una tabla temporal y después permite que la consulta exterior trabaje con ella como si fuera una tabla más. Es una forma potente de descomponer consultas complejas en pasos más simples.

Sintaxis

SELECT columnas
FROM (
    SELECT ...
) AS alias;

El alias es obligatorio. Sin él, MySQL devuelve un error. El alias se usa para referenciar las columnas de la tabla derivada en la consulta exterior.

Pre-agregar y después filtrar

El caso de uso más habitual: necesitas filtrar por el resultado de una función de agregación, pero HAVING no es suficiente porque quieres hacer operaciones adicionales sobre los datos agregados.

Clientes cuyo gasto total supera la media de gasto entre clientes:

SELECT nombre, gasto_total
FROM (
    SELECT
        cl.nombre,
        SUM(p.total) AS gasto_total
    FROM clientes cl
    JOIN pedidos p ON cl.id = p.cliente_id
    GROUP BY cl.id, cl.nombre
) AS gastos
WHERE gasto_total > (
    SELECT AVG(gasto)
    FROM (
        SELECT SUM(total) AS gasto
        FROM pedidos
        GROUP BY cliente_id
    ) AS medias
)
ORDER BY gasto_total DESC;

La primera tabla derivada calcula el gasto total por cliente. La segunda calcula la media de esos gastos. La consulta exterior filtra los clientes que superan la media. Este nivel de anidamiento sería mucho más difícil de expresar sin tablas derivadas.

Tabla derivada con JOIN

Las tablas derivadas pueden usarse en JOINs como cualquier otra tabla. Productos con su categoría y el total de ventas de esa categoría:

SELECT
    p.nombre AS producto,
    p.precio,
    c.nombre AS categoria,
    vc.total_ventas,
    vc.ingresos_categoria
FROM productos p
JOIN categorias c ON p.categoria_id = c.id
JOIN (
    SELECT
        pr.categoria_id,
        COUNT(*) AS total_ventas,
        SUM(dp.cantidad * dp.precio_unitario) AS ingresos_categoria
    FROM detalle_pedidos dp
    JOIN productos pr ON dp.producto_id = pr.id
    GROUP BY pr.categoria_id
) AS vc ON p.categoria_id = vc.categoria_id
WHERE p.categoria_id = 6
ORDER BY p.precio DESC;

La tabla derivada vc calcula los ingresos por categoría una sola vez. Después se une con la tabla de productos. Esto es mucho más eficiente que usar una subconsulta correlacionada en el SELECT, que recalcularía los ingresos para cada fila.

Rankings con tablas derivadas

Puedes usar tablas derivadas para crear rankings. Los 3 productos más vendidos por número de unidades:

SELECT
    ranking.posicion,
    ranking.producto,
    ranking.unidades_vendidas
FROM (
    SELECT
        pr.nombre AS producto,
        SUM(dp.cantidad) AS unidades_vendidas,
        ROW_NUMBER() OVER (ORDER BY SUM(dp.cantidad) DESC) AS posicion
    FROM detalle_pedidos dp
    JOIN productos pr ON dp.producto_id = pr.id
    GROUP BY pr.id, pr.nombre
) AS ranking
WHERE ranking.posicion <= 3;

La tabla derivada calcula las unidades vendidas y asigna un número de posición. La consulta exterior filtra las 3 primeras posiciones. Sin la tabla derivada, no podríamos filtrar por la función de ventana ROW_NUMBER() directamente.

Comparación por periodos

Las tablas derivadas son ideales para comparar datos de diferentes periodos lado a lado:

SELECT
    e.nombre AS empleado,
    nov.pedidos_nov,
    dic.pedidos_dic,
    dic.pedidos_dic - nov.pedidos_nov AS variacion
FROM (
    SELECT empleado_id, COUNT(*) AS pedidos_nov
    FROM pedidos
    WHERE MONTH(fecha_pedido) = 11 AND YEAR(fecha_pedido) = 2025
    GROUP BY empleado_id
) AS nov
JOIN (
    SELECT empleado_id, COUNT(*) AS pedidos_dic
    FROM pedidos
    WHERE MONTH(fecha_pedido) = 12 AND YEAR(fecha_pedido) = 2025
    GROUP BY empleado_id
) AS dic ON nov.empleado_id = dic.empleado_id
JOIN empleados e ON nov.empleado_id = e.id;

Cada tabla derivada agrega los datos de un mes diferente. El JOIN combina ambos resultados por empleado. Patricia mejoró en diciembre mientras que Natalia procesó un pedido menos. Esta es una técnica habitual en informes financieros y dashboards.

Limitaciones

Las tablas derivadas tienen algunas restricciones en MySQL:

No pueden hacer referencia a otras tablas del mismo FROM (no son "laterales" por defecto, aunque MySQL 8.0.14+ soporta LATERAL). Se materializan completamente en memoria o en disco antes de que la consulta exterior las use, lo que puede consumir recursos con conjuntos de datos grandes. No se pueden referenciar más de una vez en la misma consulta (para eso, las CTEs son mejores).

Cuándo usar tablas derivadas vs CTEs

Las tablas derivadas y las CTEs (que veremos en el siguiente artículo) resuelven problemas similares. Las tablas derivadas son más concisas para casos simples. Las CTEs son más legibles cuando hay múltiples niveles de anidamiento o cuando necesitas reutilizar el mismo resultado intermedio varias veces.

Practica con tablas derivadas

Usa el editor para crear subconsultas en el FROM:

En el siguiente artículo exploraremos las CTEs (Common Table Expressions), que ofrecen una sintaxis más limpia para las mismas operaciones y añaden la posibilidad de recursión.