Imagina que tienes una consulta más o menos compleja que te permite obtener información cruzada de varias tablas con condiciones complicadas, pero que realmente lo que quieres no son esos datos exactamente, sino poder usarlos después para hacer más filtros con ellos en la misma consulta. Por ejemplo, sacas un montón de datos de varias tablas relacionadas, con campos combinados o calculados, algunos otros extraídos de campos XML, etc... y luego quieres averiguar solamente qué registros tienen un determinado campo diferente, o quieres hacer condiciones adicionales basadas en los campos calculados, etc... Cosas así...
Es decir, imagínate que lo que quieres hacer es algo similar a esto:
SELECT Campo1, Campo2 FROM
(SELECT complejo)
WHERE Condiciones<
ORDER BY Campos
Parece que es lo más natural del mundo poder hacerlo.
El problema es que SQL Server no te lo permite hacer en la mayor parte de las ocasiones. Lo más seguro es que al intentarlo obtengas el siguiente mensaje de error:
Msg 102, Level 15, State 1, Line x Incorrect syntax near ')'.
o lo que es lo mismo:
Msg 102, Level 15, State 1, Line x Sintaxis incorrecta cerca de ')'.
Lo cual no es, desde luego, una gran pista sobre lo que está pasando.
Además si lo estamos usando desde el SQL Server Management Studio nos subraya el nombre de los campos de la consulta exterior como "nombre de campo no válido"...
¿Cómo podemos solucionar esto y poder realizar la consulta que necesitamos?
Expresiones de Tabla Comunes
SQL Server nos ofrece una funcionalidad bastante interesante llamada Common Table Expressions o CTEs. Se trata de una manera de definir un conjunto de datos temporal (como una tabla temporal) que sólo pervive mientras se ejecute nuestra consulta y no se almacena en ningún sitio. Sin embargo, nos permite consultarla y trabajar con ella como si fuese una tabla real de la base de datos. Estas CTE pueden hacer referencia a sí mismas (algo súper-potente) y se puede usar varias veces en la misma consulta.
Esto nos abre la posibilidad de crear consultas recursivas, un concepto muy interesante que nos proporciona mucha potencia. Este es un tema avanzado que no trataremos en este post.
Además de emplear en consultas recursivas se pueden utilizar, entre otras cosas, para:
- Filtrar de manera sencilla por campos que antes no existían
- Sustituir el uso de vistas, que muchas veces no son necesarias
- Referenciar a la misma tabla varias veces en la misma expresión
- Agrupar o filtrar por campos que se derivan de subconsultas
- Agrupar o filtrar por campos que resultan de funciones que no son deterministas (es decir, que devuelven un valor diferente cada vez que se llaman, aún pasándoles los mismos argumentos).
La sintaxis básica de una CTE es la siguiente:
WITH
NombreCTE [(campos devueltos)]
AS
(Subconsulta)
SELECT * FROM NombreCTE...
Es decir, se especifica un nombre para la "tabla temporal" (CTE), un "AS" y luego entre paréntesis la consulta compleja que queremos utilizar como base para la CTE. Justo a continuación ya podemos meter la consulta a realizar usando para ello el nombre que le hayamos dado a la CTE como si fuera una tabla más.
En caso de querer cambiar los nombres que traen por defecto, se puede indicar entre paréntesis el nombre de los campos que va a devolver la subconsulta por orden. Aunque esto no se utiliza a menudo, ya que solemos dejar los nombres por defecto que tengan o se les hayan asignado.
Así, por ejemplo, supongamos que en la base de datos de ejemplo Northwind queremos averiguar las ventas agregadas por empleado usando un CTE y evitando tener que agrupar por demasiados campos. Empezamos por crear una consulta que nos devuelva ese dato agrupado por id de empleado:
SELECT Employees.EmployeeID,
SUM([Order Details].UnitPrice * [Order Details].Quantity * [Order Details].Discount)
FROM Employees INNER JOIN Orders ON
Employees.EmployeeID = Orders.EmployeeID INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID
GROUP BY Employees.EmployeeID
Esta consulta cruza algunas tablas y devuelve el dato de ventas por empleado agrupado, algo así:
Lo bueno de esta consulta es que, a pesar de tener que cruzar 3 tablas, es muy fácil de obtener, pues solo debemos agrupar por un campo: el ID de empleado.
Bien, ahora nos gustaría usar ese resultado parcial para ligarlo con el resto de datos de empleados o cualquier otra información de otras tablas que podamos necesitar. En este caso vamos a enlazarlo simplemente con el nombre y los apellidos de cada empleado para ver cómo usar esto en una CTE. Evidentemente podría usarse para cualquier consulta más compleja, involucrando más tablas.
La sintaxis de la CTE sería la siguiente:
WITH cteVentasxEmpleado AS (
SELECT Employees.EmployeeID,
SUM([Order Details].UnitPrice * [Order Details].Quantity * [Order Details].Discount) AS Total
FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Employees.EmployeeID)
SELECT FirstName, LastName, cteVentasxEmpleado.Total From Employees
INNER JOIN cteVentasxEmpleado ON
Employees.EmployeeID = cteVentasxEmpleado.EmployeeID
ORDER BY cteVentasxEmpleado.Total DESC
Y el resultado:
He marcado en negrita la parte correspondiente a la CTE y a su uso.
Fíjate en que el segundo campo (el que devuelve las ventas agrupadas) no tenía nombre y he tenido que darle uno ("Total" en este caso). Es obligatorio que todos los campos que se devuelvan de la subconsulta para la CTE tengan nombre. Si no se lo especificamos con AS en el caso de campos calculados, tendríamos que usar la sintaxis opcional que indica exactamente el nombre de los campos entre paréntesis, vista más arriba. Pero así es más cómodo.
Entonces, indicamos entre WITH y AS el nombre de nuestra CTE (podemos pensar en ella como una tabla temporal) y tras los paréntesis escribimos la consulta que necesitemos, usando para ello el nombre anterior como si fuese una tabla más.
CONSEJO: Si utilizas este tipo de construcción desde código, no desde la interfaz de administración, conviene poner siempre delante del WITH un símbolo de punto y coma, así ;WITH. El motivo es que como WITH se utiliza también en otras construcciones, el intérprete de consultas puede generar un error confundiendo la sintaxis. De hecho suele ocurrir. Así que, si utilizas un CTE y te funciona bien desde el Management Studio pero te da un error de sintaxis si lo metes en tu código, es debido a esto. Basta con poner un ";" delante y problema solucionado.
Intentaremos escribir un posterior artículo para explicar el uso de CTEs para crear consultas recursivas.
Fecha de publicación: