Las expresiones de tabla comunes o CTE para abreviar se utilizan dentro de SQL Server para simplificar combinaciones complejas y subconsultas, y para proporcionar un medio para consultar datos jerárquicos como un organigrama. En este artículo, le presentaremos las expresiones de tabla comunes, los dos tipos de CTEs y sus usos. Además, presentaremos el general de CTE., Una vez que esté familiarizado, le recomiendo que lea también estos artículos:

• CTE no recursivo
• CTE recursivo

Introducción a las expresiones de tabla comunes

un Cte (expresión de tabla común) es un conjunto de resultados temporales al que puede hacer referencia dentro de otra instrucción SELECT, INSERT, UPDATE o DELETE. Fueron introducidos en la versión 2005 de SQL Server. Son compatibles con SQL y forman parte de la especificación ANSI SQL 99.

un CTE siempre devuelve un conjunto de resultados., Se utilizan para simplificar las consultas, por ejemplo, podría usar una para eliminar una tabla derivada del cuerpo de la consulta principal.

Nota: Todos los ejemplos de esta lección se basan en Microsoft SQL Server Management Studio y la base de datos AdventureWorks2012. Puede comenzar a usar estas herramientas gratuitas utilizando mi guía Introducción a SQL Server.

¿qué es una expresión CTE o de tabla común en SQL Server?

una CTE (expresión de tabla común) define un conjunto de resultados temporales que puede usar en una instrucción SELECT. Se convierte en una forma conveniente de gestionar consultas complicadas.,

Las expresiones de tabla comunes se definen dentro de la instrucción utilizando el operador WITH. Puede definir una o más expresiones de tabla comunes de esta manera.

Aquí hay un ejemplo muy simple de un CTE:

WITH Employee_CTE (EmployeeNumber, Title)AS(SELECT NationalIDNumber, JobTitle FROM HumanResources.Employee)SELECT EmployeeNumber, TitleFROM Employee_CTE

vamos a desglosar esto un poco.

La parte azul es la CTE. Observe que contiene una consulta que se puede ejecutar por sí sola en SQL., Esto se denomina definición de consulta CTE:

SELECT NationalIDNumber, JobTitleFROM HumanResources.Employee

Cuando la ejecuta, ve resultados como:

observe que cuando definimos el CTE le damos al resultado un nombre así como sus columnas. De esta manera un CTE actúa como una vista. El resultado y las columnas se nombran de manera diferente. Esto le permite encapsular lógica de consulta complicada con la expresión de tabla común.

ahora volviendo al CTE, observe que la instrucción WITH., Allí verás que el nombre y las columnas están definidos. Estas columnas corresponden a las columnas devueltas desde la consulta interna.

finalmente observe que nuestra consulta final hace referencia al CTE y a las columnas definidas.

desde la perspectiva de nuestra consulta externa todo lo que » ve » es esta definición. No se trata de cómo se construye el CTE, solo de su nombre y columnas.,

como tal, los resultados del CTE son:

observe los nombres de las columnas, que se basan en los definidos en el CTE.

Quiero señalar que puede definir más de un CTE dentro de una instrucción WITH. Esto puede ayudarlo a simplificar algunas consultas muy complicadas que finalmente se unen. Cada pieza complicada puede incluir en su propio CTE que luego se refiere y se une fuera de la cláusula WITH.,

Aquí hay un ejemplo usando dos CTE, es un ejemplo simple, pero muestra cómo se definen dos CTE, y luego se usan en una combinación interna

WITH PersonCTE (BusinessEntityID, FirstName, LastName)AS (SELECT Person.BusinessEntityID, FirstName, LastName FROM Person.Person WHERE LastName LIKE 'C%'),PhoneCTE (BusinessEntityID, PhoneNumber)AS (SELECT BusinessEntityID, PhoneNumber FROM Person.PersonPhone)SELECT FirstName, LastName, PhoneNumberFROM PersonCTEINNER JOINPhoneCTEON PersonCTE.BusinessEntityID = PhoneCTE.BusinessEntityID;

la primera expresión común de la tabla es de color verde, la segunda azul. Como puede ver en la instrucción SELECT, los CTE se unen como si fueran tablas. Esperemos que pueda ver que a medida que sus consultas se vuelven más complicadas, los CTE pueden convertirse en una forma realmente útil de separar las operaciones; por lo tanto, simplifique su consulta final.

¿por qué necesita CTE?,

hay varias razones por las que es posible que desee utilizar un CTE sobre otros métodos. Algunos de ellos incluyen:

• legibilidad – los CTE promueven la legibilidad. En lugar de agrupar toda la lógica de consulta en una consulta grande, cree varios CTE, que son los combinados más adelante en la instrucción. Esto le permite obtener los fragmentos de datos que necesita y combinarlos en una selección final.
• sustituir una vista – puede sustituir una CTE por una vista. Esto es útil si no tiene permisos para crear un objeto view o si no desea crear uno, ya que solo se usa en esta consulta.,
• Recursion-Use CTE para crear consultas recursivas, es decir, consultas que pueden llamarse a sí mismas. Esto es útil cuando necesita trabajar en datos jerárquicos como organigramas.limitaciones de
• : supere las limitaciones de la instrucción SELECT, como hacer referencia a sí misma (recursión) o realizar un grupo mediante el uso de funciones no deterministas.
• Ranking-siempre que desee utilizar la función de clasificación, como ROW_NUMBER(), RANK (), NTILE (), etc.

los tipos de CTE

Las expresiones comunes de la tabla se pueden clasificar en dos grandes categorías: Cte recursivo y CTE no recursivo.,

Los CTE recursivos son expresiones de tabla comunes que se refieren a sí mismos. La recursión puede ser un tema bastante difícil de entender, realmente no lo entendí hasta que tomé una clase de LISP en 1986, pero espero poder explicártelo.

profundizaremos en la recursión en un post separado, pero por ahora permítanme presentarles la recursión usando este diagrama:

Aquí ven la imagen de espejos opuestos. Debido a la reflexión, se convierte en una imagen en una imagen.

Las consultas recursivas son así.,

cuando se ejecuta una consulta recursiva, se ejecuta repetidamente en un subconjunto de los datos. Una consulta recursiva es básicamente una consulta que se llama a sí misma. En algún momento hay una condición final, por lo que no se llama indefinidamente.

de alguna manera, cuando miras la imagen, puedes imaginar que cada imagen en una imagen es la imagen que se llama a sí misma. Sin embargo, a diferencia de la «reflexión infinita» en los espejos, llega un punto donde una consulta recursiva encuentra la condición final y deja de llamarse a sí misma.,

en ese punto, la recursión comienza a desenrollarse, recopilar y calcular datos a medida que revisa cada resultado sucesivo.

Los CTE No recursivos, como su nombre lo indica, son no usar recursión. No se refieren a sí mismos. Son más fáciles de entender, así que los veremos primero en detalle en el siguiente artículo de esta serie.

conclusión

esperemos que ahora tenga una apreciación de lo que son los CTE y por qué podemos querer usarlos. En los próximos dos artículos vamos a entrar en mucho más detalle en los CTE, y cuándo usarlos.,

hasta entonces, recomendaría revisar mis artículos sobre uniones y subconsultas, ya que usaremos este tipo de consultas para nuestros ejemplos.