CET en SQL

Las expresiones de tabla común (CTE) se introdujeron en SQL estándar para simplificar varias clases de consultas SQL para las que una tabla derivada no era adecuada. CTE se introdujo en SQL Server 2005, la expresión de tabla común (CTE) es un conjunto de resultados con nombre temporal al que puede hacer referencia dentro de una declaración SELECT, INSERT, UPDATE o DELETE. También puede usar un CTE en CREAR una vista, como parte de la consulta SELECCIONAR de la vista. Además, a partir de SQL Server 2008, puede agregar un CTE a la nueva instrucción MERGE. 

Uso de CTE: 
podemos definir CTE agregando una cláusula WITH directamente antes de la instrucción SELECT, INSERT, UPDATE, DELETE o MERGE. La cláusula WITH puede incluir uno o más CTE separados por comas. Se puede seguir la siguiente sintaxis: 

[WITH  [, ...]]  
 
::=
cte_name [(column_name [, ...])]
AS (cte_query) 

Después de definir su cláusula WITH con los CTE, puede hacer referencia a los CTE como lo haría con cualquier otra tabla. Sin embargo, puede hacer referencia a una CTE solo dentro del ámbito de ejecución de la instrucción que sigue inmediatamente a la cláusula WITH. Después de ejecutar su declaración, el conjunto de resultados de CTE no está disponible para otras declaraciones. 

Creación de una expresión de tabla común recursiva: 
una CTE recursiva es aquella que hace referencia a sí misma dentro de esa CTE. La CTE recursiva es útil cuando se trabaja con datos jerárquicos, ya que la CTE continúa ejecutándose hasta que la consulta devuelve la jerarquía completa. 

Un ejemplo típico de datos jerárquicos es una tabla que incluye una lista de empleados. Para cada empleado, la tabla proporciona una referencia al gerente de esa persona. Esa referencia es en sí misma una identificación de empleado dentro de la misma tabla. Puede utilizar una CTE recursiva para mostrar la jerarquía de los datos de los empleados. 

Si un CTE se crea incorrectamente, puede entrar en un bucle infinito. Para evitar esto, se puede agregar la sugerencia MAXRECURSION en la cláusula OPTION de la declaración principal SELECT, INSERT, UPDATE, DELETE o MERGE. 

Se crea una tabla: 

CREATE TABLE Employees
(
  EmployeeID int NOT NULL PRIMARY KEY,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL,
  ManagerID int NULL
)

INSERT INTO Employees VALUES (1, 'Ken', 'Thompson', NULL)
INSERT INTO Employees VALUES (2, 'Terri', 'Ryan', 1)
INSERT INTO Employees VALUES (3, 'Robert', 'Durello', 1)
INSERT INTO Employees VALUES (4, 'Rob', 'Bailey', 2)
INSERT INTO Employees VALUES (5, 'Kent', 'Erickson', 2)
INSERT INTO Employees VALUES (6, 'Bill', 'Goldberg', 3)
INSERT INTO Employees VALUES (7, 'Ryan', 'Miller', 3)
INSERT INTO Employees VALUES (8, 'Dane', 'Mark', 5)
INSERT INTO Employees VALUES (9, 'Charles', 'Matthew', 6)
INSERT INTO Employees VALUES (10, 'Michael', 'Jhonson', 6) 

Después de crear la tabla Empleados, se crea la siguiente instrucción SELECT, que está precedida por una cláusula WITH que incluye un CTE denominado cteReports: 

WITH
  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
  AS
  (
    SELECT EmployeeID, FirstName, LastName, ManagerID, 1
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, 
      r.EmpLevel + 1
    FROM Employees e
      INNER JOIN cteReports r
        ON e.ManagerID = r.EmpID
  )
SELECT
  FirstName + ' ' + LastName AS FullName, 
  EmpLevel,
  (SELECT FirstName + ' ' + LastName FROM Employees 
    WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports 
ORDER BY EmpLevel, MgrID 

Por lo tanto, los CTE pueden ser una herramienta útil cuando necesita generar conjuntos de resultados temporales a los que se puede acceder en una declaración SELECCIONAR, INSERTAR, ACTUALIZAR, ELIMINAR o COMBINAR.