Menú de navegaciónMenú
Categorías

La mejor forma de Aprender Programación online y en español www.campusmvp.es

?id=6bc8ad47-95f6-4b1b-a3d5-437ef0f140f0

SQL Server: Expresiones de tabla comunes

SQLCodeImagina 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í:

SQLServer-CTE-Res-Consulta-Simple

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:

SQLServer-CTE-Res

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:
campusMVP campusMVP es la mejor forma de aprender a programar online y en español. En nuestros cursos solamente encontrarás contenidos propios de alta calidad (teoría+vídeos+prácticas) creados y tutelados por los principales expertos del sector. Nosotros vamos mucho más allá de una simple colección de vídeos colgados en Internet porque nuestro principal objetivo es que tú aprendas. Ver todos los posts de campusMVP
Archivado en: Acceso a Datos

No te pierdas nada, recibe lo mejor en tu email

Si te ha gustado este art­ículo, únete a miles de desarrolladores que ya reciben cada mes nuestro boletí­n por email. No te pierdas los mejores trucos, noticias y frikadas.

Enviamos poco, pero bueno. Palabra de desarrollador.

Suscríbete aquí­

Sí­guenos también en:

Telegram LinkedIn YouTube
La mejor formación online para desarrolladores como tú

Comentarios (7) -

Hola!

Antes de nada felicidades por el artículo ya que me parece muy útil e instructivo.

Por otra parte solo comentar una cosilla. Podriamos llegar a tener código como el siguiente:

SELECT
  e.EmployeeId,
  s.Amount
FROM
  (
    SELECT    
      Employee.EmployeeId,
      [OrderDetails].UnitPrice * [Order].Quantity "Amount"
    FROM
      Employee
    INNER JOIN
      [Order] ON Employee.EmployeeId = [Order].EmployeeId
    INNER JOIN
      [OrderDetails] ON [Order].OrderId = [OrderDetails].OrderId
    GROUP BY
            Employee.EmployeeId,
            [OrderDetails].UnitPrice,
            [Order].Quantity
   ) as s

INNER JOIN Employee e
  ON s.EmployeeId = e.EmployeeId

Con el cual podríamos perfectamente utilizar una "Sub-query" dentro de una query mayor para poder hacer cosas con ella simplemente asignándole un nombre (en el ejemplo: "as s")

Un saludo

Responder

Hola Alfonso:

Así es: en muchas ocasiones puedes hacer esto mismo que indicas, aunque no siempre.

Lo que ocurre es que las CTEs te permiten crear este tipo de consultas en ocasiones en las que lo anterior no sirve. En el artículo se enumeran unos cuantos casos.

En cualquier caso, en general el uso de un CTE es mucho más sencillo y legible que lo que comentas, y además tiene otras ventajas como el hecho de poder referenciar los resultados en diversas ocasiones dentro de la misma consulta.

No estoy incluyendo aquí otros casos más interesantes de uso de las CTE cuando se usan para recursividad, algo que quizá veamos en un futuro post.

¡Gracias por comentar!

Responder

Rafa Serrano
Spain Rafa Serrano

Hola! Al igual que Alfonso pienso que es un gran artículo. Como TODOS los que haceis vamos...

Yo hace tiempo que las uso pero les veo un GRAN inconveniente y es que un CTE solo te permite hacer una query.  Es decir si montamos el CTE cteVentasxEmpleado no podríamos hacer algo tan sencillo como:
WITH cteVentasxEmpleado AS (...)
SELECT TOP 1* FROM cteVentasxEmpleado;
SELECT TOP 2 * FROM cteVentasxEmpleado;

Si este fuera el caso bastaría con crear una tabla temporal (que pervive mientras se ejecute nuestra consulta pero se almacena en la tempdb)
Una vez que se desconecta se elimina pero puede ser que cambiemos el criterio y queramos volver a generar la tabla. Para ello tendríamos que hacer lo siguiente:

IF OBJECT_ID(N'tempdb..#cteVentasxEmpleado', N'U') IS NOT NULL
DROP TABLE #cteVentasxEmpleado;

select *
into #cteVentasxEmpleado
from (...)

SELECT TOP 1* FROM cteVentasxEmpleado
SELECT TOP 2 * FROM cteVentasxEmpleado


Un saludo!

Responder

Gracias por la aportación Rafa!

Responder

José Luis
José Luis

Me quedo claro el concepto, solo una duda cual es la diferencia entre una vista y CTE

Responder

Hola José Luis:

Una CTE tiene varias ventajas frente a una vista. Para empezar, como se apunta en el artículo, con una CTE puedes crear consultas recursivas, algo que no podrías hacer con un vista. Las CTEs son temporales y no se almacenan físicamente, aunque por otro lado no se pueden indexar cosa que una vista sí (aunque si no la indexas no tienes ninguna ventaja sobre una CTE o una tabla temporal).

En cierto modo si no te metes en recursión una CTE se puede comparar con una vista desechable o una tabla temporal en muchas ocasiones. cada cual tiene sus ventajas e inconvenientes.

Saludos.

Responder

Muy clara la explicación.
Saludos.

Responder

Agregar comentario

Los datos anteriores se utilizarán exclusivamente para permitirte hacer el comentario y, si lo seleccionas, notificarte de nuevos comentarios en este artículo, pero no se procesarán ni se utilizarán para ningún otro propósito. Lee nuestra política de privacidad.