Subconsultas correlacionadas

Una subconsulta correlacionada es una subconsulta que hace referencia a una columna de la consulta exterior. A diferencia de una subconsulta normal (que se ejecuta una sola vez), la subconsulta correlacionada se ejecuta una vez por cada fila que procesa la consulta exterior, porque su resultado depende de los valores de esa fila.

Esto las hace más potentes pero potencialmente más lentas: si la consulta exterior procesa 1.000 filas, la subconsulta se ejecutará 1.000 veces.

Diferencia con subconsultas normales

En una subconsulta normal, el resultado es independiente de la consulta exterior:

-- Subconsulta normal: se ejecuta UNA vez
SELECT nombre, precio
FROM productos
WHERE precio > (SELECT AVG(precio) FROM productos);

La media se calcula una sola vez y se compara con cada producto. En cambio, en una subconsulta correlacionada, el resultado cambia para cada fila:

-- Subconsulta correlacionada: se ejecuta una vez POR CADA fila
SELECT nombre, precio, categoria_id
FROM productos p
WHERE precio > (
    SELECT AVG(p2.precio)
    FROM productos p2
    WHERE p2.categoria_id = p.categoria_id
);

Aquí, la media se calcula por categoría. Para cada producto, MySQL calcula la media de su categoría y compara. La referencia p.categoria_id en la subconsulta es lo que la convierte en correlacionada: depende de la fila actual de la consulta exterior.

Productos por encima de la media de su categoría

El ejemplo anterior completo, con el nombre de la categoría:

SELECT
    p.nombre AS producto,
    p.precio,
    c.nombre AS categoria
FROM productos p
JOIN categorias c ON p.categoria_id = c.id
WHERE p.precio > (
    SELECT AVG(p2.precio)
    FROM productos p2
    WHERE p2.categoria_id = p.categoria_id
)
ORDER BY c.nombre, p.precio DESC;

Cada categoría tiene su propia media, y solo los productos que la superan aparecen. Por ejemplo, en Smartphones (media 874.74), solo iPhone y Samsung la superan. En Novelas (media 14.99), solo "El nombre del viento" (16.99) está por encima.

Empleados que ganan más que la media de su nivel

Encontrar empleados cuyo salario supera la media de los empleados con el mismo supervisor:

SELECT
    e.nombre,
    e.puesto,
    e.salario
FROM empleados e
WHERE e.salario > (
    SELECT AVG(e2.salario)
    FROM empleados e2
    WHERE e2.supervisor_id = e.supervisor_id
)
ORDER BY e.salario DESC;

Sofía gana más que la media de los empleados que reportan a Ricardo (ella y Alberto). Natalia gana más que la media de los que reportan a Sofía (ella, Daniel y Patricia). Cristina aparece porque su supervisor Alberto solo tiene una subordinada directa, así que supera su propia "media".

Subconsulta correlacionada en el SELECT

Puedes usar subconsultas correlacionadas como columnas calculadas. El número de pedidos de cada cliente:

SELECT
    cl.nombre,
    cl.apellidos,
    (SELECT COUNT(*)
     FROM pedidos p
     WHERE p.cliente_id = cl.id) AS total_pedidos,
    (SELECT MAX(p.total)
     FROM pedidos p
     WHERE p.cliente_id = cl.id) AS pedido_mayor
FROM clientes cl
ORDER BY total_pedidos DESC
LIMIT 5;

Para cada cliente, se ejecutan dos subconsultas correlacionadas: una cuenta sus pedidos y otra encuentra su pedido de mayor importe. María tiene 3 pedidos, siendo el mayor de 1349.98 euros.

Aunque funcional, este enfoque puede ser lento con muchos clientes. Un LEFT JOIN con GROUP BY suele ser más eficiente para el mismo resultado.

Subconsulta correlacionada en HAVING

Categorías donde el número de productos supera la media de productos por categoría:

SELECT
    c.nombre AS categoria,
    COUNT(*) AS productos
FROM categorias c
JOIN productos p ON c.id = p.categoria_id
GROUP BY c.id, c.nombre
HAVING COUNT(*) > (
    SELECT AVG(cnt)
    FROM (
        SELECT COUNT(*) AS cnt
        FROM productos
        GROUP BY categoria_id
    ) AS medias
)
ORDER BY productos DESC;

La media de productos por categoría es aproximadamente 2.73 (30 productos / 11 categorías). Las categorías con 3 o más productos superan esa media.

Rendimiento

Las subconsultas correlacionadas se ejecutan una vez por cada fila de la consulta exterior. Esto significa que con una tabla de 10.000 filas, la subconsulta se ejecuta 10.000 veces. MySQL puede optimizar algunas subconsultas correlacionadas convirtiéndolas internamente en JOINs, pero no siempre.

Cuando el rendimiento sea un problema, considera reescribir la subconsulta correlacionada como un JOIN con una tabla derivada o como un CTE. El resultado será el mismo pero el rendimiento puede mejorar significativamente.

Practica con subconsultas correlacionadas

Usa el editor para practicar subconsultas que referencian la consulta externa:

En el siguiente artículo veremos EXISTS, un operador diseñado específicamente para trabajar con subconsultas correlacionadas de forma eficiente.