Comparar filas con subconsultas correlacionadas
Las consultas correlacionadas son subconsultas que hacen referencia a columnas de la consulta externa, creando una dependencia que hace que la subconsulta se ejecute una vez para cada fila procesada por la consulta externa. Aunque esto podría ser ineficaz, las consultas correlacionadas son útiles para comparaciones de fila por fila y cálculos que son difíciles o imposibles de expresar en caso contrario.
Descripción de la ejecución de subconsulta correlacionada
Una subconsulta correlacionada hace referencia a una o varias columnas de la consulta externa, creando una dependencia lógica entre las dos. A diferencia de una subconsulta normal que se ejecuta una vez y devuelve un resultado fijo, una subconsulta correlacionada se ejecuta repetidamente, una vez para cada fila que procesa la consulta externa.
Piense en él como un bucle anidado: para cada fila de la consulta externa, la base de datos evalúa la subconsulta mediante los valores de esa fila. Este comportamiento permite comparaciones eficaces de fila por fila, pero también significa que necesita comprender el modelo de ejecución para escribir consultas eficaces.
Tenga en cuenta cómo difieren estas dos consultas:
-- Non-correlated subquery (executes once)
SELECT ProductID, Name, ListPrice
FROM SalesLT.Product
WHERE ListPrice > (SELECT AVG(ListPrice) FROM SalesLT.Product);
-- Correlated subquery (executes per outer row)
SELECT p1.ProductID, p1.Name, p1.ListPrice
FROM SalesLT.Product AS p1
WHERE p1.ListPrice > (
SELECT AVG(p2.ListPrice)
FROM SalesLT.Product AS p2
WHERE p2.ProductCategoryID = p1.ProductCategoryID -- References outer query
);
En el ejemplo no relacionado, la subconsulta calcula un único precio medio en todos los productos. Este valor se calcula una vez y, a continuación, el precio de cada producto se compara con ese número fijo.
En el ejemplo correlacionado, la subconsulta hace referencia a p1.ProductCategoryID de la consulta externa. Esto crea una dependencia: para cada fila de producto, la subconsulta calcula el precio medio de esa categoría específica. Un producto de la categoría "Bikes" se compara con el promedio de bicicletas, mientras que un producto en "Accesorios" se compara con el promedio de accesorios.
Nota:
El optimizador de consultas suele transformar las subconsultas correlacionadas en combinaciones equivalentes internamente. Sin embargo, comprender el comportamiento correlacionado lógico le ayuda a escribir consultas correctas, incluso cuando la ejecución física difiere.
Filtrar con subconsultas correlacionadas
Las subconsultas correlacionadas de la WHERE cláusula habilitan condiciones de filtrado específicas de fila que serían imposibles con comparaciones estáticas. En lugar de comparar con un único valor fijo, cada fila se evalúa con un valor calculado dinámicamente en función de los atributos de esa fila.
Este patrón es útil cuando necesita identificar valores atípicos dentro de grupos, buscar registros que superen el umbral de su propia categoría o aplicar reglas de negocio que varían según el contexto. En el ejemplo siguiente se encuentran los productos con un precio superior a su promedio de categorías, lo que significa que un accesorio de bajo precio podría marcarse como caro, mientras que una bicicleta a un precio más alto podría no ser:
SELECT
p.ProductID,
p.Name,
p.ListPrice,
pc.Name AS Category
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > (
SELECT AVG(p2.ListPrice)
FROM SalesLT.Product AS p2
WHERE p2.ProductCategoryID = p.ProductCategoryID
)
ORDER BY pc.Name, p.ListPrice DESC;
Puede aplicar el mismo patrón para identificar a los clientes cuyo comportamiento difiere de su línea base personal.
La consulta siguiente busca a los clientes que hayan realizado al menos un pedido que supere su propio valor medio de pedido, lo que ayuda a identificar patrones de compra inusuales o transacciones de alto valor:
SELECT DISTINCT
c.CustomerID,
c.FirstName,
c.LastName
FROM SalesLT.Customer AS c
INNER JOIN SalesLT.SalesOrderHeader AS soh
ON c.CustomerID = soh.CustomerID
WHERE soh.TotalDue > (
SELECT AVG(soh2.TotalDue)
FROM SalesLT.SalesOrderHeader AS soh2
WHERE soh2.CustomerID = c.CustomerID
);
Uso de EXISTS con subconsultas correlacionadas
El EXISTS operador combinado con una subconsulta correlacionada comprueba si existen filas coincidentes en una tabla relacionada, devolviendo un resultado verdadero o falso simple. Este patrón es muy eficaz porque el motor de base de datos puede dejar de buscar en cuanto encuentre la primera fila coincidente. A diferencia de las subconsultas que devuelven datos reales, EXISTS solo necesita confirmar la presencia o ausencia.
Use EXISTS cuando necesite responder a preguntas como "¿qué clientes han realizado pedidos?" o "¿Qué productos nunca se han vendido?" Normalmente, la subconsulta usa SELECT 1 porque los valores reales no importan:
-- Find customers who have placed at least one order
SELECT CustomerID, FirstName, LastName
FROM SalesLT.Customer AS c
WHERE EXISTS (
SELECT 1
FROM SalesLT.SalesOrderHeader AS soh
WHERE soh.CustomerID = c.CustomerID
);
-- Find customers who have never placed an order
SELECT CustomerID, FirstName, LastName
FROM SalesLT.Customer AS c
WHERE NOT EXISTS (
SELECT 1
FROM SalesLT.SalesOrderHeader AS soh
WHERE soh.CustomerID = c.CustomerID
);
EXISTS es aún más valioso cuando necesita comprobar condiciones complejas que combinan varios criterios. Puede agregar cualquier lógica de filtrado dentro de la subconsulta y la consulta externa solo incluirá filas en las que exista al menos una fila relacionada coincidente.
Los ejemplos siguientes muestran la búsqueda de productos con pedidos y categorías de gran cantidad en los que cada producto cumple un umbral de precio:
-- Find products that have been ordered in quantities greater than 10
SELECT p.ProductID, p.Name
FROM SalesLT.Product AS p
WHERE EXISTS (
SELECT 1
FROM SalesLT.SalesOrderDetail AS sod
WHERE sod.ProductID = p.ProductID
AND sod.OrderQty > 10
);
-- Find categories where all products are priced above $100
SELECT pc.ProductCategoryID, pc.Name
FROM SalesLT.ProductCategory AS pc
WHERE NOT EXISTS (
SELECT 1
FROM SalesLT.Product AS p
WHERE p.ProductCategoryID = pc.ProductCategoryID
AND p.ListPrice <= 100
);
Sugerencia
EXISTS supera normalmente a IN en las subconsultas, especialmente al comprobar la existencia en tablas grandes. El optimizador puede detenerse después de encontrar la primera coincidencia con EXISTS, mientras que IN puede que necesite recuperar todos los valores coincidentes.
Calcular valores con subconsultas correlacionadas en SELECT
Las subconsultas correlacionadas de la SELECT cláusula calculan un valor independiente para cada fila del conjunto de resultados. Este patrón permite incluir valores agregados o derivados de tablas relacionadas junto con los detalles de la fila principal, sin contraer el resultado en grupos.
Este enfoque es útil cuando necesita mostrar información contextual, como mostrar cada producto junto con el precio medio de su categoría, o cada empleado junto con el número total de empleados de su departamento. La subconsulta se ejecuta una vez por fila, utilizando los valores de esa fila para filtrar el cálculo:
-- Show each product with its category's average price
SELECT
p.ProductID,
p.Name,
p.ListPrice,
(
SELECT AVG(p2.ListPrice)
FROM SalesLT.Product AS p2
WHERE p2.ProductCategoryID = p.ProductCategoryID
) AS CategoryAvgPrice,
p.ListPrice - (
SELECT AVG(p2.ListPrice)
FROM SalesLT.Product AS p2
WHERE p2.ProductCategoryID = p.ProductCategoryID
) AS DifferenceFromAvg
FROM SalesLT.Product AS p;
También puede usar este patrón para contar registros relacionados o recuperar valores específicos de tablas relacionadas. La consulta siguiente crea un resumen del cliente que incluye el recuento de pedidos de cada cliente y la fecha de pedido más reciente, calculado individualmente para cada fila del cliente:
-- Show each customer with their order count
SELECT
c.CustomerID,
c.FirstName,
c.LastName,
(
SELECT COUNT(*)
FROM SalesLT.SalesOrderHeader AS soh
WHERE soh.CustomerID = c.CustomerID
) AS OrderCount,
(
SELECT MAX(soh.OrderDate)
FROM SalesLT.SalesOrderHeader AS soh
WHERE soh.CustomerID = c.CustomerID
) AS LastOrderDate
FROM SalesLT.Customer AS c;
Nota:
Las subconsultas correlacionadas de la SELECT cláusula deben devolver exactamente un valor. Si la subconsulta podría devolver varias filas, encapsularla en una función de agregado como MAX(), MIN()o SUM().
Buscar top-N por grupo con subconsultas correlacionadas
Una de las aplicaciones más prácticas de las subconsultas correlacionadas es encontrar los N elementos principales dentro de cada grupo. Este patrón responde a preguntas como "¿Cuáles son los tres productos más caros de cada categoría?" o "quiénes son los cinco principales vendedores de cada región?"
La subconsulta correlacionada examina cada fila y determina si pertenece a las N mejores de su grupo comprobando cuántas otras filas del mismo grupo tienen un rango más alto. Este enfoque funciona bien cuando las funciones de ventana no están disponibles o cuando se necesita lógica de clasificación compleja que las funciones de ventana no pueden expresar.
La consulta siguiente busca los tres productos más caros por categoría seleccionando los productos cuyas IDs aparecen entre los 3 primeros para su categoría.
SELECT
pc.Name AS Category,
p.Name AS Product,
p.ListPrice
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ProductID IN (
SELECT TOP 3 p2.ProductID
FROM SalesLT.Product AS p2
WHERE p2.ProductCategoryID = p.ProductCategoryID
ORDER BY p2.ListPrice DESC
)
ORDER BY pc.Name, p.ListPrice DESC;
Un enfoque alternativo cuenta cuántos elementos tienen un rango superior a la fila actual. Si menos de N elementos tienen un valor mayor, la fila actual se encuentra entre los N primeros. Esta técnica maneja los empates de forma diferente y puede ser útil cuando se necesitan todos los elementos que están empatados en la posición N.
-- Find products that are in the top 3 by price within their category
SELECT
pc.Name AS Category,
p.Name AS Product,
p.ListPrice
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
ON p.ProductCategoryID = pc.ProductCategoryID
WHERE (
SELECT COUNT(*)
FROM SalesLT.Product AS p2
WHERE p2.ProductCategoryID = p.ProductCategoryID
AND p2.ListPrice > p.ListPrice
) < 3
ORDER BY pc.Name, p.ListPrice DESC;
Comparar filas consecutivas
Las subconsultas correlacionadas pueden acceder a valores de filas anteriores o posteriores en función de los criterios de ordenación, lo que permite comparaciones de período a lo largo del período y análisis de tendencias. Este patrón es útil para calcular los cambios entre registros consecutivos, como comparar cada orden con el orden anterior o realizar un seguimiento de cómo evolucionan los valores a lo largo del tiempo.
La subconsulta busca una fila relacionada filtrando las filas que vienen antes (o después) de la fila actual en la secuencia lógica y, a continuación, ordena los resultados para obtener la fila adyacente inmediatamente:
-- Show each order with the previous order's total
SELECT
soh.SalesOrderID,
soh.OrderDate,
soh.TotalDue,
(
SELECT TOP 1 soh2.TotalDue
FROM SalesLT.SalesOrderHeader AS soh2
WHERE soh2.CustomerID = soh.CustomerID
AND soh2.OrderDate < soh.OrderDate
ORDER BY soh2.OrderDate DESC
) AS PreviousOrderTotal
FROM SalesLT.SalesOrderHeader AS soh
ORDER BY soh.CustomerID, soh.OrderDate;
Sugerencia
En las comparaciones de filas consecutivas, las funciones de ventana como LAG() y LEAD() suelen ser más eficaces y legibles que las subconsultas correlacionadas. Use subconsultas correlacionadas cuando necesite condiciones más complejas que la compatibilidad con las funciones de ventana.
Elegir entre subconsultas correlacionadas y alternativas
Las subconsultas correlacionadas no siempre son la mejor opción. La tabla siguiente le ayuda a elegir la técnica correcta:
| Uso de este enfoque | Cuando lo necesite... |
|---|---|
| Subconsultas correlacionadas | Compare cada fila con un valor calculado dinámicamente en función de los atributos de esa fila, pruebe la existencia con EXISTS/NOT EXISTSo recupere exactamente un valor relacionado por fila con lógica de selección compleja. |
| Combinaciones | Recupere columnas de varias tablas o cuando las relaciones sean sencillas sin cálculos por fila. |
| Funciones de ventana | Calcule los totales en ejecución, las clasificaciones o acceda a las filas anteriores o siguientes con LAG()/LEAD(). Más eficaz que las subconsultas correlacionadas para estos patrones. |
| CTEs | Haga referencia al mismo resultado calculado varias veces o interrumpa la lógica compleja en pasos con nombre legibles. |
Consideraciones sobre el rendimiento
Las subconsultas correlacionadas pueden afectar al rendimiento cuando no se optimizan correctamente. Dado que la subconsulta se ejecuta una vez para cada fila de la consulta externa, las consultas correlacionadas mal diseñadas pueden dar lugar a miles o millones de ejecuciones de subconsultas en tablas grandes.
Siga estas instrucciones para optimizar el rendimiento de la subconsulta correlacionada:
Crear índices en columnas de correlación: asegúrese de que se indexan las columnas a las que se hace referencia en la cláusula de
WHEREla subconsulta que vincula a la consulta externa. Por ejemplo, si la subconsulta filtra enProductCategoryID, un índice de esa columna permite que la base de datos busque rápidamente filas coincidentes en lugar de examinar toda la tabla para cada fila externa.Incluir columnas adicionales en índices: si la subconsulta también filtra o agrega en otras columnas, considere la posibilidad de un índice compuesto. Un índice en
(ProductCategoryID, ListPrice)admite la búsqueda de correlación y el filtrado basado en precios o la agregación en una única búsqueda de índice.Evaluar enfoques alternativos: muchas subconsultas correlacionadas se pueden reescribir como combinaciones o funciones de ventana con un mejor rendimiento. Si estás buscando el valor máximo por grupo, una función de ventana con
ROW_NUMBER()a menudo supera a una subconsulta correlacionada que seleccionaMAX()para cada fila.Revisar planes de ejecución: use
SET STATISTICS IO ONy examine el plan de ejecución real para comprender cómo procesa el optimizador la subconsulta correlacionada. El optimizador puede transformarlo en una unión internamente, o puede ejecutarlo fila por fila como se escribe.Prueba con volúmenes de datos realistas: las subconsultas correlacionadas que funcionan bien en conjuntos de datos de prueba pequeños pueden ralentizarse con tablas de tamaño de producción. Realice siempre pruebas comparativas con datos representativos antes de realizar la implementación en producción.
Importante
Revise siempre los planes de ejecución al trabajar con subconsultas correlacionadas en tablas grandes. El optimizador puede transformarlos de forma eficaz, pero las correlaciones complejas pueden beneficiarse de la reescritura de consultas.
Para obtener más información sobre las subconsultas, vea Subconsultas (Transact-SQL) y EXISTS (Transact-SQL).