Subconsultas correlativas
Se pueden evaluar muchas consultas mediante la ejecución de la subconsulta una vez y la sustitución del valor o valores resultantes en la cláusula WHERE de la consulta externa. En las consultas que incluyen una subconsulta correlativa (conocida también como una consulta repetitiva), la subconsulta depende de la consulta externa para sus valores. Esto significa que la subconsulta se ejecuta varias veces, una vez por cada fila que pueda ser seleccionada por la consulta externa.
Esta consulta recupera una instancia del nombre y apellido de cada empleado cuya bonificación en la tabla SalesPerson es 5000 y cuyos números de identificación de empleado coinciden en las tablas Employee y SalesPerson.
USE AdventureWorks;
GO
SELECT DISTINCT c.LastName, c.FirstName, e.EmployeeID
FROM Person.Contact AS c JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE 5000.00 IN
(SELECT Bonus
FROM Sales.SalesPerson sp
WHERE e.EmployeeID = sp.SalesPersonID) ;
GO
Éste es el conjunto de resultados.
LastName FirstName EmployeeID
------------------------- ----------------- -----------
Ansman-Wolfe Pamela 280
Saraiva José 282
(2 filas afectadas)
La subconsulta anterior de esta instrucción no se puede evaluar independientemente de la consulta externa. Necesita un valor para Employee.EmployeeID, pero este valor cambia a medida que SQL Server examina distintas filas de Employee.
Así es, exactamente, como se evalúa esta consulta: SQL Server tiene en cuenta cada fila de la tabla Employee para incluirla en el resultado sustituyendo el valor de cada fila en la consulta interna. Por ejemplo, si SQL Server examina primero la fila de Syed Abbas, la variable Employee.EmployeeID toma el valor 288, que SQL Server sustituye en la consulta interna.
USE AdventureWorks;
GO
SELECT Bonus
FROM Sales.SalesPerson
WHERE SalesPersonID = 288;
El resultado es 0 (Syed Abbas no recibió una bonificación porque no es un vendedor), con lo que la consulta externa se evalúa como:
USE AdventureWorks;
GO
SELECT LastName, FirstName
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
WHERE 5000 IN (0.00);
Puesto que esto es falso, la fila de Syed Abbas no se incluye en los resultados. Realice el mismo procedimiento con la fila de Pamela Ansman-Wolfe. Observe que esta fila se incluye en el resultado.
Las subconsultas correlativas también pueden incluir funciones con valores de tabla en la cláusula FROM mediante la referencia a las columnas de una tabla de la consulta externa como un argumento de la función con valores de tabla. En este caso, para cada fila de la consulta externa, la función con valores de tabla se evalúa según la subconsulta.