Las subconsultas son una herramienta poderosa en SQL Server que te permiten anidar consultas dentro de otras consultas o declaraciones. Se pueden utilizar de varias formas para recuperar datos específicos o realizar cálculos complejos. En este artículo, exploraremos las mejores prácticas para utilizar subconsultas de manera efectiva.

¿Qué son las subconsultas?

Una subconsulta es una consulta que se encuentra entre paréntesis y se utiliza como una expresión dentro de otra consulta o declaración. Se puede colocar en la cláusula SELECT, FROM o WHERE de una consulta. Veamos un ejemplo:

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

En este ejemplo, estamos recuperando los detalles de los empleados que tienen más horas de vacaciones disponibles que el promedio. La subconsulta, encerrada entre paréntesis, calcula el promedio de horas de vacaciones de la tabla Employee y lo compara con la columna VacationHours en la consulta principal.

Las subconsultas también se pueden utilizar en la cláusula FROM para crear una estructura similar a una tabla temporal que se puede acceder como cualquier otra tabla. Esto es útil cuando se desea realizar cálculos o filtrar datos en función de los resultados de una subconsulta. Aquí tienes un ejemplo:

SELECT
  E1.BusinessEntityID,
  E1.LoginID,
  E1.JobTitle,
  E1.VacationHours,
  Sub.AverageVacation
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

En esta consulta, estamos uniendo la tabla Employee con una subconsulta que calcula las horas de vacaciones promedio para cada título de trabajo. El resultado es una lista de empleados que tienen más horas de vacaciones que el promedio para su título de trabajo.

Subconsultas correlacionadas

Una subconsulta correlacionada es una subconsulta que hace referencia a la consulta o declaración principal. Esto permite comparaciones o manipulaciones más complejas. Por ejemplo:

SELECT
  *
FROM HumanResources.Employee E
WHERE EXISTS (SELECT
                *
              FROM HumanResources.EmployeePayHistory EPH
              WHERE E.BusinessEntityID = EPH.BusinessEntityID
              AND YEAR(EPH.RateChangeDate) = 2002)

En esta consulta, estamos recuperando todos los empleados que tuvieron cambios en sus tasas de pago en 2002. La subconsulta compara valores de la consulta externa con valores de la consulta interna, lo que nos permite filtrar los resultados en función de condiciones específicas.

Aunque las subconsultas correlacionadas pueden ser poderosas, también pueden causar problemas de rendimiento si no se utilizan con cuidado. Pueden obligar al servidor a ejecutar la subconsulta por cada fila considerada por la declaración externa. Es importante utilizar subconsultas correlacionadas con precaución y considerar técnicas alternativas como las uniones cuando sea posible.

Expresiones de tabla comunes (CTEs)

Las expresiones de tabla comunes (CTEs) son otra forma de crear subconsultas en SQL Server. Se introdujeron en SQL Server 2005 y proporcionan una sintaxis diferente para crear conjuntos de resultados temporales. Las CTE pueden hacer que el código sea más legible y mantenible, especialmente cuando se trabaja con múltiples subconsultas.

Una CTE se define utilizando la cláusula WITH y se puede hacer referencia por su nombre dentro de la declaración en la que se creó. Aquí tienes un ejemplo:

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
    FOR xml PATH ('')), 1, 1, '') AS Departments,
    COUNT(*) AS NumDepartments
  FROM HumanResources.EmployeeDepartmentHistory EDH
  GROUP BY EDH.BusinessEntityID
),
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
    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;

En esta consulta, estamos utilizando CTE para concatenar nombres de departamentos y nombres de turnos para los empleados que han trabajado en varios departamentos o turnos. El resultado es una lista de empleados con sus respectivos departamentos y turnos.

CTEs recursivas

Una CTE también puede ser recursiva, lo que significa que puede hacer referencia a sí misma. Esto es útil cuando se trabaja con jerarquías padre-hijo o cuando se realizan operaciones de conteo. Sin embargo, las CTE recursivas pueden no ser eficientes o escalar bien en ciertos escenarios.

Aquí tienes un ejemplo simple de una CTE recursiva:

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

En esta consulta, estamos utilizando una CTE recursiva para generar una secuencia de números del 1 al 7. La CTE se referencia a sí misma en la segunda parte de la cláusula UNION ALL, incrementando el número en 1 hasta que se cumpla la condición.

Es importante tener en cuenta que las CTE recursivas no siempre son la solución más eficiente para contar o trabajar con jerarquías. Se recomienda analizar las implicaciones de rendimiento y considerar enfoques alternativos si es necesario.

Conclusión

Las subconsultas son una característica poderosa en SQL Server que permiten la recuperación y manipulación de datos complejos. Al comprender cuándo y cómo utilizar subconsultas de manera efectiva, puedes mejorar tus habilidades en SQL Server y escribir consultas más eficientes. Ya sea que necesites comparar valores, realizar cálculos o trabajar con datos jerárquicos, las subconsultas proporcionan una solución flexible y versátil.