Subconsultas

Subconsultas

Una subconsulta es una consulta anidada en un SELECT, INSERT,  UPDATE o DELETE e inclusive en otra subconsulta.  Las subconsultas se pueden utilizar en cualquier parte en la que se permita una expresión. Las subconsultas deben seguir ciertas reglas que se mencionan al final del post. Es necesario conocer la estructura de la base de datos para poder relacionar las tablas correctamente y lograr los resultados esperados. Los resultados obtenidos con subconsultas se pueden también obtener con el uso de Joins.

Las subconsultas pueden utilizarse de dos formas:
1. Dentro de la Lista de campos de la instrucción Select,  esta subconsulta es la que reporta un valor.  Se debe relacionar la tabla después del From
con la tabla de la Subconsulta.
     select ListaCampos, (Select …. subconsulta) from Tabla

2. En las clásulas Where
     Select ListadeCampos, OtroCampo, UltimoCampo from Tabla
WHERE Campo [NOT] IN (subconsulta)
WHERE Campo operador [ANY | ALL] (subconsulta)
WHERE [Not] Exists (subconsulta)

Ejercicios

— Usando Northwind
use Northwind
go

1. — Listado de las categorías y la cantidad de productos de cada una.

select C.CategoryID As ‘Código Categoría’,
C.CategoryName As ‘Descripción’,
(select COUNT(P.ProductID) from Products As P
where P.CategoryID = C.CategoryID) As ‘Cantidad Productos’
from Categories As C
go
Nota: la consulta para saber la cantidad de productos en la instrucción
anterior es la siguiente: (ej. Cat. 5)
select COUNT(P.ProductID) from Products As P where P.CategoryID = 5
go
Para poder especificar el código de la categoría se utiliza el campo CategoryID
de la tabla Categories.

La imagen muestra el resultado, note que la cantidad de productos en almacén de una determinada categoría se muestra con la consulta entre paréntesis que es la subconsulta.



2. — Empleados y la cantidad de órdenes del año 1997, orden descendente por  cantidad de órdenes.

select E.EmployeeID As ‘Código’, Empleado = E.FirstName + SPACE(1)+ E.LastName,  E.Address As ‘Dirección’,
(select Count(O.OrderID) from Orders As O where O.EmployeeID = E.EmployeeID
and year(O.Orderdate) = 1997) As ‘Cantidad Órdenes’
from Employees As E
go

3. — Un listado de los empleados y el monto total generado en las órdenes y la cantidad de órdenes de cada uno.

Para la cantidad de órdenes (el listado es para el Empleado con código 1)

select Count(O.OrderID) from Orders As O where O.EmployeeID = 1 and year(O.Orderdate) = 1997
go

Para el monto total de las órdenes del empleado con el código 1

select sum(O.Freight) from Orders As O where O.EmployeeID = 1
and year(O.Orderdate) = 1997
go

— Incluídas como Subconsultas en la siguiente instrucción

select E.EmployeeID As ‘Código’, Empleado = E.FirstName + SPACE(1)+ E.LastName,
E.Address As ‘Dirección’,
(select Count(O.OrderID) from Orders As O where O.EmployeeID = E.EmployeeID
and year(O.Orderdate) = 1997) As ‘Cantidad Órdenes’,
(select sum(O.Freight) from Orders As O where O.EmployeeID = E.EmployeeID
and year(O.Orderdate) = 1997) As ‘Monto total’
from Employees As E
go

4. — Órdenes que generaron los clientes de México

Primero determinar ¿Cuáles son los clientes de México?
Se necesitan los códigos de los clientes de México

select C.CustomerID from Customers As C where Country = ‘Mexico’
go

Para las órdenes

select O.OrderID As ‘Nº Orden’, Format(O.OrderDate,’dd/MM/yyy’) As ‘Fecha’,
O.CustomerID As ‘Cód. Cliente’, C.CompanyName As ‘Cliente’ , C.Country As ‘País’
from Orders As O
join Customers As C on O.CustomerID = C.CustomerID
where O.CustomerID in
(select C.CustomerID from Customers As C where Country = ‘Mexico’)
go

/* Códigos de los clientes de México que reporta la Subconsulta
ANATR ANTON CENTC PERIC TORTU */

5. — Las órdenes donde se vendieron los productos del Proveedor (Suppliers)  llamado Tokyo Traders
— Primero el código del proveedor Tokyo Traders

select S.SupplierID from Suppliers As S where S.CompanyName = ‘Tokyo Traders’
go

Luego los productos de ese proveedor.

select P.ProductID from Products As P
where P.SupplierID = (select S.SupplierID from Suppliers As S where S.CompanyName = ‘Tokyo Traders’)
go

Los códigos de los productos del proveedor Tokyo Traders son: 9, 10 y 74
Las ordenes en los que se vendieron los productos del proveedor Tokyo Traders

select O.OrderID As ‘Nº Orden’, OD.ProductID As ‘Cód. Producto’,
P.ProductName As ‘Descripción’, P.UnitPrice As ‘Precio Lista’,
OD.UnitPrice As ‘Precio Venta’, OD.Quantity As ‘Cantidad’,
Format(O.OrderDate,’dd/MM/yyyy’) As ‘Fecha de Orden’
from [Order Details] As OD
join Orders As O on OD.OrderID = O.OrderID
join Products As P on OD.ProductID = P.ProductID
where P.ProductID in (select P.ProductID from Products As P
where P.SupplierID = (select S.SupplierID from Suppliers As S where S.CompanyName = ‘Tokyo Traders’))
go

6. — Los clientes y la cantidad comprada por cada uno

select C.CustomerID As ‘Código Cliente’, C.CompanyName As ‘Cliente’,
C.Address As ‘Dirección’, C.Country As ‘País’,
(select SUM(O.Freight) from Orders As O
where O.CustomerID = C.CustomerID) As ‘Total Comprado’
from Customers As C
Where (select SUM(O.Freight) from Orders As O
where O.CustomerID = C.CustomerID) >0
go



Restricciones en Subconsultas

Las subconsultas tienen las siguientes restricciones

  • Los campos de una subconsulta que se especifica con un operador de comparación, sólo puede incluir un nombre de expresión o columna.
  • Los campos que incluyen EXISTS e IN pueden reportar un conjunto de resultados.
  • Si la cláusula WHERE de una consulta externa incluye un nombre de columna, debe ser compatible con una combinación con la columna indicada en la lista de selección de la subconsulta.
  • La palabra clave DISTINCT no se puede usar con subconsultas que incluyan GROUP BY.
  • No se pueden especificar las cláusulas COMPUTE e INTO.
  • Los tipos de datos ntext, text y image no están permitidos en subconsultas.
  • Las subconsultas que se especifican con un operador de comparación sin modificar (no seguido de la palabra clave ANY o ALL) no pueden incluir las cláusulas GROUP BY y HAVING.
  • Sólo se puede especificar ORDER BY si se especifica también TOP.
  • Una vista creada con una subconsulta no se puede actualizar.
  • La lista de selección de una subconsulta especificada con EXISTS,  por convención, tiene un asterisco (*) en lugar de un solo nombre de columna.  Las reglas de una subconsulta especificada con EXISTS son idénticas a las de una lista de selección estándar, porque este tipo de subconsulta  crea una prueba de existencia y devuelve TRUE o FALSE en lugar de datos.