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 19 de Diciembre
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 19 de Diciembre
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
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 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.