Subconsultas correlacionadas en SQL Server

Subconsultas correlacionadas y no correlacionadas en SQL Server

En este artículo se va a explicar el uso de las subconsultas correlacionadas
y las subconsultas no correlacionadas. Dependiendo del diseño de la base de datos siempre es conveniente probar diferentes formas de como hacer consultas complejas que impliquen varias tablas o análisis recursivo sobre la misma tabla. Para información básica Ver subconsultas.
Para ejercicios ver Subconsultas Caso 1, Caso 2 y Caso 3

Subconsultas correlacionadas

Las subconsultas correlacionadas son aquellas que ejecutan fila a fila en una consulta principal con el resultado de una consulta interna. Para cada fila de la consulta principal se evalua la consulta interna, si el resultado es verdadero la fila de la consulta principal es incluida en el conjunto de resultados.

Select para consultas correlacionadas

Sintaxis
Select Campo1, Campo2, Campo3, … from Tabla1 Alias
Where Expresión Operador
(select ExpresiónN1 from Tabla2 where Expresión = Alias.Expresión)

Note que hay una consulta principal y al filtrar usando la cláusula Where se utiliza otra consulta (subconsulta interna).

Subconsultas no correlacionadas

Las subconsultas no correlacionadas se ejecutan una sola vez y reportan un conjunto
de resultados en base a una consulta principal.

Ejemplos

Usando la base de datos Northwind
use Northwind
go

Ejercicio 1

Listar los productos cuyo precio de venta se encuentro por encima del precio promedio de los productos de su categoría
Primero calcular el precio promedio de los productos de categoría 1
select avg(P.UnitPrice) from Products As P where P.CategoryID = 1
go
El resultado es: 45.1854, este valor puede variar si los precios son diferentes, la base de datos Northwind ha sido utilizada en ejercicios que pueden variar los valores, compruebe los resultados.
Los productos de la categoría 1 ordenados por precio son los siguientes
select P.ProductID, P.ProductName, P.UnitPrice
from Products As P
where P.CategoryID = 1
order by P.UnitPrice desc
go

Ahora listar los productos de esa categoría cuyo precio se encuentra por encima del promedio se incluirá la consulta para el promedio de precios en la cláusula where de la consulta principal, note que se está tomando como ejemplo los productos de la categoría 1.

select P.ProductID, P.ProductName, P.UnitPrice
from Products As P
where P.UnitPrice >
(select avg(I.UnitPrice) from Products As I where I.CategoryID = 1)
and P.CategoryID = 1
order by P.UnitPrice desc
go

Productos con precios mayores al promedio cuyo valor es 45.1854 para este ejercicio.

Ahora el resultado para todas las categorias, se incluirá el código de la categoría.

select P.ProductID, P.ProductName, P.UnitPrice, P.CategoryID
from Products As P where P.UnitPrice >
(select avg(I.UnitPrice) from Products As I where I.CategoryID = P.CategoryID)
order by P.CategoryID, P.UnitPrice desc
go

Note los productos de la categoría 1.

Productos de todas las categorías con los precios mayyores al promedio por categoría.

Para comprobar el resultado de la consulta anterior, utilizaremos como ejemplo los productos de la categoría 2.
Calcular el precio promedio de los productos de categoría 2
select avg(P.UnitPrice) from Products As P where P.CategoryID = 2
go
Resultado: 24.9147
Para ver los resultados listaremos los registros de la categoría 2
select P.ProductID, P.ProductName, P.UnitPrice
from Products As P
where P.UnitPrice >
(select avg(I.UnitPrice) from Products As I where I.CategoryID = 2)
and P.CategoryID = 2
order by P.UnitPrice desc
go
Note que los cinco registros que aparecen tienen el precio mayor al promedio de los precios de la categoría 2 (Valor de 24.9147) y coinciden con los productos de categoría 2 que aparecen en el listado de precios de todas las categorías.

Listado de productos de todas las categorías y de la categoría 2.

Convertir la subconsulta correlacionada a una consulta con Join

Para mas información ver Consultas de varias tablas con Joins
select P.ProductID, P.ProductName, P.UnitPrice, P.CategoryId
from Products As P
join (select I.CategoryId, avg(I.UnitPrice) As Promedio from Products As I
group by I.CategoryId) As Prom
on P.CategoryId = Prom.CategoryId
where P.UnitPrice > Prom.Promedio
order by P.CategoryID, P.UnitPrice desc
go

Ejercicio 2

Listar los productos que tengan stock inferior al promedio de stocks por categoría. Incluir el nombre del proveedor (Suppliers) y la descripción de la categoría, la descripción de la categoría se ha incluído con una subconsulta regular.

select P.ProductID As ‘Código’,
P.ProductName As ‘Descripción’ ,
P.UnitPrice As ‘Precio’,
P.UnitsInStock As ‘Stock’,
(select S.CompanyName from Suppliers As S
where S.SupplierID = P.SupplierID) As ‘Proveedor’,
P.CategoryID As ‘Cód. Categoría’,
(select C.CategoryName from Categories As C
where C.CategoryID = P.CategoryID) As ‘Categoría’
from Products As P
where P.UnitsInStock <
(select avg(I.UnitsInStock) from Products As I
where I.CategoryID = P.CategoryID)
order by P.CategoryID, P.UnitsInStock desc
go

Mostrando el plan de ejecución estimado el costo es: 0.0453711

Convirtiendo la misma consulta con Joins

select P.ProductID As ‘Código’,
P.ProductName As ‘Descripción’ ,
P.UnitPrice As ‘Precio’,
P.UnitsInStock As ‘Stock’,
S.CompanyName As ‘Proveedor’,
P.CategoryID As ‘Cód. Categoría’,
C.CategoryName As ‘Categoría’
from Products As P
join Categories As C on P.CategoryID = C.CategoryID
join Suppliers As S on P.SupplierID = S.SupplierID
where P.UnitsInStock <
(select avg(I.UnitsInStock) from Products As I
where I.CategoryID = P.CategoryID)
order by P.CategoryID, P.UnitsInStock desc
go

Mostrando el plan de ejecución estimado el costo es: 0.0482638

La consulta usando subconsulta para mostrar los nombres del proveedor y de la categoría es mas rápida.

Ejercicio 3

Listar los clientes que tienen registrada por lo menos tres ordenes

Select C.CustomerId As ‘Cód. Cliente’, C.CompanyName As ‘Cliente’,
(Select count(Ord.CustomerID) from Orders As Ord
where Ord.CustomerID = C.CustomerID) As ‘Cantidad de Órdenes’
from Customers As C
where exists
(Select distinct O.CustomerId from Orders As O
where O.CustomerID = C.CustomerID)
and
(Select count(Ord.CustomerID) from Orders As Ord
where Ord.CustomerID = C.CustomerID) >= 3
order by [Cantidad de Órdenes]
go

En este ejercicio se ha incluido una subconsulta regular para la cantidad de órdenes y la misma se ha utilizado para el filtro que indica que sean como mínimo 3 órdenes.
La subconsulta correlacionada es la que se usa para listar los Id de los clientes que tienen órdenes registradas en el filtro usando el operador exists. (Select distinct O.CustomerId from Orders As O
where O.CustomerID = C.CustomerID)