Introducción a las Subconsultas en SQL Server

Problema

¿Cómo y cuándo debo utilizar subconsultas mientras se trabaja con Microsoft SQL Server? ¿En qué circunstancias se debe utilizar subconsultas en SQL Server? En este artículo nos fijamos en la mejor manera de utilizarlas.

 

Solución

¿Qué son las subconsultas?

Las subconsultas son consultas que están anidadas dentro de otra consulta. Se les permite siempre que sea tomadas por el SQL Server como una expresión, y se indican encerrando la subconsulta entre paréntesis. Por ejemplo, utilizando la base de datos de ejemplo AdventureWorks, podríamos crear una consulta para encontrar empleados que tienen más vacaciones disponibles a la media:

 

SELECT
  BusinessEntityID,
  LoginID,
  JobTitle,
  VacationHours
FROM 
  HumanResources.Employee E1
WHERE 
  VacationHours > (SELECT
    AVG(VacationHours)
    FROM HumanResources.Employee E2)

 

 

Inicia 21 de Noviembre

 

 

 

En este ejemplo, se utilizó la subconsulta en la cláusula WHERE y nos regresa un solo valor.

La parte de abajo es la subconsulta en el ejemplo anterior. Es la parte de la consulta dentro de los paréntesis.

 

    (SELECT
    AVG(VacationHours)
    FROM HumanResources.Employee E2)

 

Podemos poner subconsultas en la cláusula FROM y acceder a las filas devueltas de ellos como cualquier otra tabla. Por ejemplo, si queremos saber cuál de los empleados tienen más horas de vacaciones que la media para su puesto de trabajo, podríamos escribir:

 

SELECT
  E1.BusinessEntityID,
  E1.LoginID,
  E1.JobTitle,
  E1.VacationHours,
  Sub.AverageVacation --Drawn from the subquery
FROM HumanResources.Employee E1
JOIN (SELECT
      JobTitle,
      AVG(VacationHours) AverageVacation
      FROM HumanResources.Employee E2
      GROUP BY JobTitle) sub
ON E1.JobTitle = Sub.JobTitle
WHERE E1.VacationHours > Sub.AverageVacation
ORDER BY E1.JobTitle

 

Las subconsultas correlacionadas

Las subconsultas correlacionadas son subconsultas que hacen referencia a la consulta principal. Esto les permite hacer una amplia gama de comparaciones o manipulaciones. Por ejemplo, si queremos conocer qué empleados tenían sus PAY RATES modificadas en 2003, podríamos usar una subconsulta correlacionada como la siguiente:

 

SELECT
  *
FROM HumanResources.Employee E
WHERE EXISTS (SELECT
                *
    FROM HumanResources.EmployeePayHistory EPH
    WHERE E.BusinessEntityID = EPH.BusinessEntityID --Comparing a value from the outer query
    AND YEAR(EPH.RateChangeDate) = 2002)

 

 

Inicia 21 de Noviembre

 

 

 

Esa subconsulta correlacionada compara los valores de la tabla de base de la consulta externa con valores de la tabla en la consulta interna. Sería relativamente fácil volver a escribir la consulta concreta uniendo las tablas con JOIN en lugar de utilizar una subconsulta. Esto es bastante común y muchos usos para subconsultas podrían reformularse para evitar el uso de la subconsulta y en su lugar utilizar técnicas como el JOIN.

Un uso ligeramente más sofisticado para subconsultas correlacionadas permite concatenaciones a través de las filas, al menos en SQL Server 2005 y versiones posteriores. Por ejemplo, si queríamos una lista concatenada de todos los títulos que estaban asociados con cada nivel de organización de la parte HumanResources de AdventureWorks, podríamos utilizar:


SELECT
  E.OrganizationLevel,
  STUFF((SELECT DISTINCT
    ', ' + E2.JobTitle
  FROM HumanResources.Employee E2
  WHERE E.OrganizationLevel = E2.OrganizationLevel --Correlation between inner and outer queries
  FOR xml PATH ('')), 1, 1, '')
FROM HumanResources.Employee E
GROUP BY E.OrganizationLevel

 

Concatenation Example Results

 

 

Ese tipo de consultas sería difícil de enmarcar como un JOIN, aunque se puede volver a escribir utilizando funciones definidas por el usuario. Se han analizado las implicancias de rendimiento de las dos técnicas y se encontró que este estilo, utilizando la consulta correlacionada, es sustancialmente más rápido que el uso de las funciones definidas por el usuario.

 

Mientras las subconsultas correlacionadas como esta funcionan bien, a menudo pueden causar problemas de rendimiento, ya que pueden forzar al servidor para ejecutar la subconsulta para cada fila considerada por la instrucción externa, por lo tanto , las subconsultas correlacionadas deben ser utilizados con precaución.

 

Expresiones de tabla común (CTE)

Las expresiones de tabla común (CTE) se introdujeron con SQL Server 2005 y son esencialmente una sintaxis diferente para crear una subconsulta. A veces puede que sea más fácil de leer y mantener el código si se volverá a utilizar. Esto es especialmente cierto cuando se tienen varias subconsultas.

 

Un CTE tiene el formato: WITH [Nombre CTE] AS ([consulta]). El CTE, se puede referenciar por su nombre, pero sólo dentro de la instrucción donde fue creado. Cualquier instrucción que viene antes de un CTE se debe terminar con un punto y coma. (;). Se pueden tener más de un CTE dentro de una instrucción si los mismos están separados por comas.

Por ejemplo, si quisiéramos obtener más información sobre los empleados que han trabajado en más de un departamento o trabajado más de un turno designado, podríamos utilizar CTE para manejar la concatenación.


--Any previous query in the same batch must be terminated by a semicolon (;)
WITH ConcatDepartments
AS (SELECT
  EDH.BusinessEntityID,
  STUFF((SELECT
    ', ' + D.name
  FROM HumanResources.EmployeeDepartmentHistory EDH2
  JOIN HumanResources.Department D
    ON EDH2.DepartmentID = D.DepartmentID
  WHERE EDH2.BusinessEntityID = EDH.BusinessEntityID --correllate inner and outer
  FOR xml PATH ('')), 1, 1, '') AS Departments,
  COUNT(*) AS NumDepartments
FROM HumanResources.EmployeeDepartmentHistory EDH
GROUP BY EDH.BusinessEntityID), --The comma allows another CTE in the same statement

ConcatShifts
AS (SELECT
  EDH.BusinessEntityID,
  STUFF((SELECT
    ', ' + S.Name
  FROM HumanResources.EmployeeDepartmentHistory EDH2
  JOIN HumanResources.[Shift] S
    ON EDH2.DepartmentID = S.ShiftID
  WHERE EDH2.BusinessEntityID = EDH.BusinessEntityID --correllate inner and outer
  FOR xml PATH ('')), 1, 1, '') AS Shifts,
  COUNT(*) AS NumShifts
FROM HumanResources.EmployeeDepartmentHistory EDH
GROUP BY EDH.BusinessEntityID)

SELECT
  P.BusinessEntityID,
  P.FirstName,
  P.LastName,
  CD.Departments,
  CS.Shifts
FROM Person.Person P
INNER JOIN ConcatDepartments CD
  ON P.BusinessEntityID = CD.BusinessEntityID
INNER JOIN ConcatShifts CS
  ON p.BusinessEntityID = cs.BusinessEntityID
WHERE CD.NumDepartments > 1
OR CS.NumShifts > 1;

CTE Example Results

 

CTE recursivas

Un CTE también puede hacer referencia a sí mismo, por lo que es recursivo. Entre otras cosas, esto puede ser utilizado para el recuento, aunque puede que no sea eficiente. Aquí un ejemplo sencillo:

 

WITH CountingCTE
AS (SELECT
  1 AS num
UNION ALL
SELECT
  num + 1
FROM CountingCTE
WHERE num < 7)
SELECT
  *
FROM CountingCTE

 

 

 

En el uso más sofisticado, CTE recursivas son útiles para trabajar con una jerarquía de elementos primarios y secundarios.

 

Inicia 21 de Noviembre

(Fuente: https://www.mssqltips.com)