Uso de una subconsulta correlacionada en una sentencia T-SQL

En el artículo del mes pasado, discutí qué y cómo usar una subconsulta en una sentencia T-SQL. Este mes ampliaré este tema hablando de las subconsultas correlacionadas. Explicaré qué es una subconsulta correlacionada y mostraré varios ejemplos diferentes sobre cómo utilizar una subconsulta en una sentencia T-SQL.

¿Qué es una subconsulta correlacionada?

Una subconsulta correlacionada es una sentencia SELECT anidada dentro de otra sentencia T-SQL, que contiene una referencia a una o más columnas en la consulta externa. Por lo tanto, se puede decir que la subconsulta correlacionada depende de la consulta externa. Esta es la principal diferencia entre una subconsulta correlacionada y una subconsulta simple. Una subconsulta simple no depende de la consulta externa, puede ejecutarse independientemente de la consulta externa y devolverá un conjunto de resultados. Una subconsulta correlacionada, al depender de la consulta externa, devolverá un error de sintaxis si se ejecuta por sí misma.

Una subconsulta correlacionada se ejecutará muchas veces mientras se procesa la sentencia T-SQL que contiene la subconsulta correlacionada. La subconsulta correlacionada se ejecutará una vez por cada fila candidata seleccionada por la consulta externa. Las columnas de la consulta externa, a las que se hace referencia en la subconsulta correlacionada, se sustituyen por los valores de la fila candidata antes de cada ejecución. Dependiendo de los resultados de la ejecución de la subconsulta correlacionada, se determinará si la fila de la consulta externa se devuelve en el conjunto de resultados final.

Utilización de una subconsulta correlacionada en una cláusula WHERE

Suponga que desea un informe de todos los «OrderID» en los que el cliente no compró más del 10% de la cantidad media vendida para un producto determinado. De esta manera podría revisar estos pedidos, y posiblemente contactar con los clientes, para ayudar a determinar si hubo una razón para el pedido de baja cantidad. Una subconsulta correlacionada en una cláusula WHERE puede ayudarle a producir este informe. Aquí hay una sentencia SELECT que produce la lista deseada de «OrderID’s»:

select distinct OrderId from Northwind.dbo. OD where Quantity <l; (select avg(Quantity) * .1 from Northwind.dbo. where OD.ProductID = ProductID)

La subconsulta correlacionada en el comando anterior está contenida dentro del paréntesis que sigue al signo mayor que en la cláusula WHERE anterior. Aquí puede ver que esta subconsulta correlacionada contiene una referencia a «OD.ProductID». Esta referencia compara el «ProductID» de la consulta externa con el «ProductID» de la consulta interna. Cuando se ejecuta esta consulta, el motor SQL ejecutará la consulta interna, la subconsulta correlacionada, para cada registro «». Esta consulta interna calculará la «Cantidad» media para el «ProductID» concreto de la fila candidata que se está procesando en la consulta externa. Esta subconsulta correlativa determina si la consulta interna devuelve un valor que cumple la condición de la cláusula WHERE. Si lo hace, la fila identificada por la consulta externa se coloca en el conjunto de registros que se devolverá de la sentencia T-SQL SELECT completa.

El código siguiente es otro ejemplo que utiliza una subconsulta correlacionada en la cláusula WHERE para mostrar los dos principales clientes, según el importe en dólares asociado a sus pedidos, por región. Es posible que desee realizar una consulta como esta para poder premiar a estos clientes, ya que son los que más compran por región.

select CompanyName, ContactName, Address, City, Country, PostalCode from Northwind.dbo.Customers OuterCwhere CustomerID in ( select top 2 InnerC.CustomerId from Northwind.dbo. OD join Northwind.dbo.Orders O on OD.OrderId = O.OrderID join Northwind.dbo.Customers InnerC on O.CustomerID = InnerC.CustomerId Where Region = OuterC.Region group by Region, InnerC.CustomerId order by sum(UnitPrice * Quantity * (1-Discount)) desc ) order by Region

Aquí puede ver que la consulta interna es una subconsulta correlacionada porque hace referencia a «OuterC», que es el alias de la tabla «Northwind.DBO.Customer» en la consulta externa. Esta consulta interna utiliza el valor «Región» para calcular los dos principales clientes de la región asociada a la fila que se está procesando desde la consulta externa. Si el «CustomerID» de la consulta externa es uno de los dos principales clientes, entonces el registro se coloca en el conjunto de registros a devolver.

Subconsulta relacionada en la cláusula HAVING

Supongamos que sus organizaciones quieren llevar a cabo un programa de incentivos durante todo el año para aumentar los ingresos. Por lo tanto, anuncian a sus clientes que si cada pedido que realicen, durante el año, es superior a 750 dólares, usted les proporcionará un reembolso al final del año a razón de 75 dólares por cada pedido que realicen. A continuación se muestra un ejemplo de cómo calcular el importe de la rebaja. Este ejemplo utiliza una subconsulta correlacionada en la cláusula HAVING para identificar a los clientes que califican para recibir el reembolso. Aquí está mi código para esta consulta:

select C.CustomerID, Count(*)*75 Rebate from Northwind.DBO.Customers C join Northwind.DBO.Orders O on c.CustomerID = O.CustomerID where Datepart(yy,OrderDate) = '1998' group by C.CustomerId having 750 < ALL(select sum(UnitPrice * Quantity * (1-Discount)) from Northwind.DBO.Orders O join Northwind.DBO. OD on O.OrderID = OD.OrderID where CustomerID = C.CustomerId and Datepart(yy,OrderDate) = '1998' group by O.OrderId ) 

Al revisar esta consulta, puede ver que estoy usando una consulta correlacionada en la cláusula HAVING para calcular el importe total del pedido para cada pedido del cliente. Utilizo el «CustomerID» de la consulta externa y el año del pedido «Datepart(yy,OrderDate)», para ayudar a identificar los registros de pedidos asociados a cada cliente, que fueron realizados el año ‘1998’. Para estos registros asociados estoy calculando el importe total del pedido, para cada pedido, sumando todos los registros «», utilizando la siguiente fórmula: sum(UnitPrice * Quantity * (1-Discount)). Si todos y cada uno de los pedidos de un cliente, para el año 1998 tienen un importe total en dólares superior a 750, entonces calculo el importe de la rebaja en la consulta externa utilizando esta fórmula «Count(*)*75 «.

El motor de consulta de SQL Server sólo ejecutará la subconsulta correlacionada interna en la cláusula HAVING para aquellos registros de clientes identificados en la consulta externa, o básicamente sólo aquellos clientes que realizaron pedidos en «1998».

Realización de una sentencia de actualización utilizando una subconsulta correlacionada

Una subconsulta correlacionada puede incluso utilizarse en una sentencia de actualización. Aquí hay un ejemplo:

create table A(A int, S int)create table B(A int, B int)set nocount on insert into A(A) values(1)insert into A(A) values(2)insert into A(A) values(3)insert into B values(1,1)insert into B values(2,1)insert into B values(2,1)insert into B values(3,1)insert into B values(3,1)insert into B values(3,1)update A set S = (select sum(B) from B where A.A = A group by A) select * from Adrop table A,B

Aquí está el conjunto de resultados que obtengo cuando ejecuto esta consulta en mi máquina:

A S ----------- ----------- 1 12 23 3

En mi consulta anterior, he utilizado la subconsulta correlacionada para actualizar la columna A de la tabla A con la suma de la columna B de la tabla B para las filas que tienen el mismo valor en la columna A que la fila que se está actualizando.

Conclusión

Déjame resumir. Una subconsulta y una subconsulta correlacionada son consultas SELECT codificadas dentro de otra consulta, conocida como consulta externa. La subconsulta correlacionada y la subconsulta ayudan a determinar el resultado del conjunto de resultados devuelto por la consulta completa. Una subconsulta, cuando se ejecuta independientemente de la consulta externa, devolverá un conjunto de resultados y, por tanto, no depende de la consulta externa. En cambio, una subconsulta correlacionada no puede ejecutarse independientemente de la consulta externa porque utiliza una o más referencias a columnas de la consulta externa para determinar el conjunto de resultados devuelto por la subconsulta correlacionada. Espero que ahora entienda las diferencias entre las subconsultas y las subconsultas correlacionadas, y cómo se pueden utilizar en su código T-SQL.

» Ver todos los artículos del columnista Gregory A. Larsen