Lección 6.3: Subconsultas Correlacionadas

En las lecciones anteriores, utilizamos subconsultas "independientes" que podían ejecutarse por sí solas. En esta lección, introducimos Subconsultas Correlacionadas - un tipo más avanzado de subconsulta que depende de celdas o valores de la consulta externa.

¿Qué es una Subconsulta Correlacionada?

Una subconsulta es correlacionada cuando se refiere a una columna de una tabla en la consulta externa. A diferencia de una subconsulta regular, una subconsulta correlacionada no puede ejecutarse de forma independiente de la consulta externa.

Cómo funciona:

1. La base de datos toma una fila de la consulta externa.
2. Ejecuta la subconsulta interna utilizando valores de esa fila específica.
3. Utiliza el resultado de la subconsulta interna para satisfacer la cláusula WHERE (o SELECT).
4. Pasa a la siguiente fila y repite el proceso.

Nota de Rendimiento: Debido a que una subconsulta correlacionada se ejecuta potencialmente una vez por cada fila en la consulta externa, puede ser más lenta que un JOIN o una subconsulta regular en conjuntos de datos muy grandes.

1. Subconsultas Correlacionadas en WHERE

El uso más común es comparar el valor de una fila con un conjunto de datos relacionado específicamente con esa fila.

Escenario: Encontrar todas las películas que tienen un costo de reemplazo mayor que el costo de reemplazo promedio de las películas en la misma categoría de clasificación (por ejemplo, G, PG, R).

SELECT
    title,
    rating,
    replacement_cost
FROM
    film AS f1
WHERE
    replacement_cost > (
        SELECT AVG(replacement_cost)
        FROM film AS f2
        WHERE f1.rating = f2.rating
    );

• Correlación: f1.rating = f2.rating vincula la subconsulta interna a la fila actual de la consulta externa.
• Lógica: Para cada película, la base de datos calcula el costo promedio para su clasificación específica y verifica si esa película cuesta más.

2. Subconsultas Correlacionadas en SELECT

Puedes usar subconsultas correlacionadas para recuperar datos descriptivos o agregados para cada fila sin usar una cláusula GROUP BY.

Escenario: Mostrar la lista de categorías y el título de la película más larga en cada categoría.

SELECT
    c.name AS category_name,
    (
        SELECT f.title
        FROM film f
        JOIN film_category fc ON f.film_id = fc.film_id
        WHERE fc.category_id = c.category_id
        ORDER BY f.length DESC
        LIMIT 1) AS longest_film_title
FROM
    category AS c;

3. Subconsultas Correlacionadas con EXISTS

Vimos el operador EXISTS en la lección anterior. EXISTS se utiliza casi siempre con una subconsulta correlacionada.

Escenario: Encontrar clientes que han alquilado al menos una película en una tienda específica (Tienda 1).

SELECT
    first_name,
    last_name
FROM
    customer AS c
WHERE
    EXISTS (
        SELECT 1
        FROM rental AS r
        INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
        WHERE r.customer_id = c.customer_id
        AND i.store_id = 1
    );

Conclusiones Clave de Esta Lección

• Una Subconsulta Correlacionada depende de la consulta externa para sus valores.
• Se ejecuta fila por fila (una vez por cada fila candidata).
• Alias son esenciales para distinguir entre las instancias de la tabla externa e interna.
• Son poderosas para comparaciones relativas a grupos (comparar una fila con su propio grupo).
• Ten en cuenta el rendimiento al usarlas en millones de registros.