Aprende SQL · lección gratuita

Lección 25 · Tablas derivadas (subconsultas en FROM)

Resumen

Una tabla derivada es una subconsulta que aparece en la cláusula FROM y a la que se le da un alias: la consulta externa la trata como si fuera una tabla más. Sirven para construir resultados por pasos: primero agregas o transformas en la subconsulta, y luego filtras, ordenas o vuelves a agregar sobre ese resultado intermedio. Son la solución natural a problemas que un solo SELECT no resuelve, como agregar sobre un agregado (el promedio de los totales por pedido) o filtrar por una métrica calculada.

• Tabla derivada: FROM (SELECT ...) AS alias. El alias es obligatorio en la práctica.
• Composición por capas: la subconsulta produce filas/columnas que la externa consume como si fueran una tabla real.
• Agregar sobre un agregado: calcula el total por pedido en la derivada y luego AVG()/MAX() por encima.
• Filtrar por métrica calculada: un WHERE o HAVING externo opera sobre columnas que solo existen tras agregar (lo que un WHERE plano no puede hacer).
• JOIN con derivadas: puedes unir una tabla normal con una derivada para enriquecer filas con totales precalculados.

Sintaxis / Conceptos

Por qué FROM y no WHERE

Una subconsulta escalar en WHERE devuelve un valor; una subconsulta en IN devuelve una columna; una tabla derivada en FROM devuelve una tabla completa (varias filas y columnas) que la consulta externa puede seleccionar, filtrar, ordenar y volver a agregar. El alias permite referirte a sus columnas (t.revenue, r.total). Internamente es muy parecido a un CTE (WITH), pero inline.

El patrón "agregado de agregado"

No puedes hacer AVG(SUM(...)) directamente: las funciones de agregación no se anidan. La forma correcta es agregar una vez en la tabla derivada (p. ej. revenue por pedido con GROUP BY pedido_id) y aplicar la segunda agregación en la consulta externa (AVG(revenue)). Este patrón es muy frecuente en analítica.

Orden de evaluación

La tabla derivada se resuelve primero (produce su conjunto de filas), y solo entonces la consulta externa aplica su JOIN, WHERE, GROUP BY y ORDER BY. Así puedes ordenar o filtrar por columnas que solo nacen tras agregar.

SELECT nombre, salario, (SELECT nombre FROM departamentos WHERE id = empleados.departamento_id) AS depto FROM empleados LIMIT 5

Ejemplos

-- Tabla derivada: revenue por pedido. La externa ordena y toma el de mayor facturación.
-- revenue de una línea = cantidad * precio_unitario * (1 - descuento).
SELECT t.pedido_id, t.revenue
FROM (
  SELECT pedido_id,
         SUM(cantidad * precio_unitario * (1 - descuento)) AS revenue
  FROM detalle_pedidos
  GROUP BY pedido_id
) AS t
ORDER BY t.revenue DESC
LIMIT 1;

-- Agregado de agregado: revenue MEDIO por pedido (AVG sobre la suma por pedido).
SELECT ROUND(AVG(revenue), 2) AS revenue_medio_por_pedido
FROM (
  SELECT pedido_id, SUM(cantidad * precio_unitario * (1 - descuento)) AS revenue
  FROM detalle_pedidos
  GROUP BY pedido_id
) AS t;

-- JOIN de una tabla normal con una derivada: facturación total por cliente.
-- Primero agregamos por cliente en la derivada; luego unimos para mostrar el nombre.
SELECT c.empresa, r.total
FROM clientes c
JOIN (
  SELECT p.cliente_id,
         SUM(d.cantidad * d.precio_unitario * (1 - d.descuento)) AS total
  FROM pedidos p
  JOIN detalle_pedidos d ON d.pedido_id = p.id
  GROUP BY p.cliente_id
) AS r ON r.cliente_id = c.id
ORDER BY r.total DESC
LIMIT 5;

-- Filtrar por una métrica calculada: departamentos con 3 o más empleados.
SELECT d.nombre, sub.n_emp
FROM departamentos d
JOIN (
  SELECT departamento_id, COUNT(*) AS n_emp
  FROM empleados
  GROUP BY departamento_id
) AS sub ON sub.departamento_id = d.id
WHERE sub.n_emp >= 3
ORDER BY sub.n_emp DESC;

💡 Dale siempre un alias a la tabla derivada y cualifica sus columnas con él (t.revenue). Si vas a reutilizar la misma subconsulta varias veces o anidas muchos niveles, considera un CTE con WITH (Módulo 7): es el mismo concepto con mejor legibilidad.

Cheatsheet

Construcción	Qué hace
FROM (SELECT ...) AS t	Tabla derivada: subconsulta usada como tabla
t.columna	Referencia a una columna de la derivada (alias)
AVG(x) FROM (… SUM(x) … GROUP BY)	Agregado sobre agregado (por capas)
JOIN (SELECT ... GROUP BY) r ON ...	Une tabla normal con totales precalculados
WHERE externo sobre métrica	Filtra por columnas que solo existen tras agregar

---

← Subconsultas correlacionadas y EXISTSExpresiones de tabla común: WITH →

Ver todas las lecciones de Aprende SQL →