Escribir consultas que usan tablas derivadas
Las tablas derivadas permiten escribir instrucciones Transact-SQL que son más modulares, lo que ayuda a dividir las consultas complejas en partes más fáciles de administrar. El uso de tablas derivadas en las consultas también puede proporcionar soluciones alternativas para algunas de las restricciones impuestas por el orden lógico de procesamiento de consultas, como el uso de alias de columna.
Al igual que las subconsultas, se crean tablas derivadas en la cláusula FROM de una instrucción SELECT externa. A diferencia de las subconsultas, las tablas derivadas se escriben mediante una expresión con nombre que es lógicamente equivalente a una tabla y a la que se puede hacer referencia como una tabla en otra parte de la consulta externa.
Las tablas derivadas no se almacenan en la base de datos. Por lo tanto, no se requieren privilegios de seguridad especiales para escribir consultas mediante tablas derivadas, aparte de los derechos para seleccionar entre los objetos de origen. Una tabla derivada se crea en el momento de la ejecución de la consulta externa y sale del ámbito cuando finaliza la consulta externa. Las tablas derivadas no tienen necesariamente un impacto en el rendimiento, en comparación con la misma consulta expresada de forma diferente. Cuando se procesa la consulta, la instrucción se desempaqueta y se evalúa con los objetos de bases de datos subyacentes.
Devolución de resultados mediante tablas derivadas
Para crear una tabla derivada, escriba una consulta interna entre paréntesis, seguida de una cláusula AS y un nombre para la tabla derivada, utilizando la sintaxis siguiente:
SELECT <outer query column list>
FROM (SELECT <inner query column list>
FROM <table source>) AS <derived table alias>
Por ejemplo, puede usar una tabla derivada para recuperar información sobre los pedidos realizados al año por clientes distintos:
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders) AS derived_year
GROUP BY orderyear;
La consulta interna crea un conjunto de pedidos y lo coloca en el año derivado de la tabla derivada. La consulta externa funciona en la tabla derivada y resume los resultados. El resultado debería tener este aspecto:
| orderyear | cust_count |
|---|---|
| 2019 | 67 |
| 2020 | 86 |
| 2021 | 81 |
Pasar argumentos a tablas derivadas
Las tablas derivadas pueden aceptar argumentos pasados desde una rutina de llamada, como un lote de Transact-SQL, una función o un procedimiento almacenado. Puede escribir tablas derivadas con variables locales que sirvan como marcadores de posición. En tiempo de ejecución, los marcadores de posición se pueden reemplazar por valores proporcionados en el lote o por valores pasados como parámetros al procedimiento almacenado que invocó la consulta. Esto permitirá que el código se reutilice de forma más flexible que volver a escribir la misma consulta con valores diferentes cada vez.
Por ejemplo, el siguiente lote declara una variable local (marcada con el símbolo @) para el identificador de empleado y, a continuación, usa la capacidad de SQL Server para asignar un valor a la variable en la misma instrucción. La consulta acepta la variable @emp_id y la usa en la expresión de tabla derivada:
DECLARE @emp_id INT = 9; --declare and assign the variable
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (
SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders
WHERE empid=@emp_id --use the variable to pass a value to the derived table query
) AS derived_year
GROUP BY orderyear;
GO
Al escribir consultas que usan tablas derivadas, tenga en cuenta las siguientes directrices:
- La instrucción SELECT anidada que define la tabla derivada debe tener asignado un alias. La consulta externa usará el alias en su instrucción SELECT de la misma manera que hace referencia a las tablas con alias unidas en una cláusula FROM.
- Todas las columnas a las que se hace referencia en la cláusula SELECT de la tabla derivada deben tener alias asignados, un procedimiento recomendado que no siempre es necesario en Transact-SQL. Cada alias debe ser único dentro de la expresión. Los alias de columna se pueden declarar en línea con las columnas o externamente a la cláusula.
- La instrucción SELECT que define la expresión de tabla derivada no puede usar una cláusula ORDER BY, a menos que también incluya un operador TOP, una cláusula OFFSET/FETCH o una cláusula FOR XML. Como resultado, la tabla derivada no proporciona ningún criterio de ordenación. Los resultados se ordenan en la consulta externa.
- La instrucción SELECT que define la tabla derivada se puede escribir para aceptar argumentos en forma de variables locales. Si la instrucción SELECT está incrustada en un procedimiento almacenado, los argumentos se pueden escribir como parámetros para el procedimiento.
- Las expresiones de tabla derivadas que están anidadas dentro de una consulta externa pueden contener otras expresiones de tabla derivadas. Se permite el anidamiento, pero no se recomienda debido a su mayor complejidad y a que se reduce la legibilidad.
- Es posible que no se pueda hacer referencia a una tabla derivada varias veces dentro de una consulta externa. Si necesita manipular los mismos resultados, deberá definir la expresión de tabla derivada cada vez, como en cada lado de un operador JOIN.