logo

TechCodeview

CTE en SQL

El Expresiones de tabla comunes (CTE) se introdujeron en SQL estándar para simplificar varias clases de consultas SQL para las cuales una tabla derivada simplemente 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 un SELECCIONAR , INSERTAR , ACTUALIZAR , o BORRAR declaración. También puede utilizar un CTE en una vista CREATE, como parte de la consulta SELECT de la vista. Además, a partir de SQL Server 2008, puede agregar un CTE a la nueva declaración MERGE.

¿Por qué necesitamos CTE en el servidor SQL?

Una tabla común [obsoleta]CTE es una poderosa SQL construcción que ayuda a simplificar las consultas. Los CTE actúan como tablas virtuales (con registros y columnas) que se crean durante la ejecución de la consulta, se utilizan en la consulta y se eliminan después de que se ejecuta la consulta.



Utilizando el CTE

Podemos definir CTE agregando una cláusula CON directamente antes de la instrucción SELECT, INSERT, UPDATE, DELETE o MERGE. La cláusula CON puede incluir uno o más CTE separados por comas.

javafx

Sintaxis:

[CON [, …]]



::=

pruebas de compatibilidad

nombre_cte [(nombre_columna [,…])]

AS (cte_query)



Argumento

  1. Nombre de expresión: Un identificador válido para una expresión de tabla común. nombre_expresión debe ser diferente de los nombres de otras expresiones de tabla comunes definidas en la misma cláusula CON, pero nombre_expresión puede ser el mismo que el nombre de la tabla o vista base. Todas las referencias a nombre_expresión en la consulta utilizan la expresión de tabla común en lugar del objeto base.
  2. Nombre de la columna: Especifica un nombre de columna en una expresión de tabla común. No se permiten nombres duplicados dentro de una única definición CTE. La cantidad de nombres de columnas debe coincidir con la cantidad de columnas en el conjunto de resultados de CTE_query_definition. Una lista de nombres de columnas es opcional solo si todas las columnas resultantes en la definición de la consulta tienen nombres diferentes.
  3. CTE_QueryDefinition: Especifica una instrucción SELECT cuyo conjunto de resultados satisface una expresión de tabla común. La declaración SELECT para CTE_query_defining debe cumplir los mismos requisitos que la creación de una vista, excepto que un CTE no puede definir otro CTE. Para obtener más información, consulte la sección Comentarios y CREAR VISTA (Transact-SQL). Si se definen varias CTE_query_settings, las definiciones de consulta se deben combinar con uno de los operadores establecidos UNION ALL, UNION, EXCEPT o INTERSECT.

Reglas para definir y usar expresiones de tabla comunes recursivas

Las siguientes directrices se aplican a la definición de expresiones de tabla comunes recursivas:

  1. Una definición de CTE recursiva debe contener al menos dos definiciones de consulta de CTE, un miembro ancla y un miembro recursivo. Puede definir varios miembros ancla y recursivos. Sin embargo, todas las definiciones de consulta de miembros ancla deben colocarse antes de la primera definición de miembro recursivo. Todas las definiciones de consultas CTE son miembros ancla a menos que hagan referencia al propio CTE.
  2. Los elementos de anclaje deben combinarse con uno de los siguientes operadores de conjunto: UNION ALL, UNION, INTERSECT, EXCEPT. UNION ALL es el único operador de conjunto válido entre el último miembro ancla y el primer miembro recursivo al unir varios miembros recursivos. Los elementos ancla y recursivos deben tener el mismo número de columnas.
  3. El tipo de datos de las columnas del miembro recursivo debe ser el mismo que el tipo de datos de la columna correspondiente del miembro ancla.
  4. Los siguientes elementos no están permitidos en su CTE_query_definition para miembros recursivos:
Please choose a different grouping PIVOT (For database compatibility level 110 and above.  See Breaking Changes to Database Engine Features in SQL Server 2016. Offal Scalar aggregation Up LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed) subquery A hint applied to recursive references to CTEs within a CTE_query_definition.>

Crear una expresión de tabla común recursiva

Un CTE recursivo es aquel que hace referencia a sí mismo dentro de ese CTE. El CTE recursivo es útil cuando se trabaja con datos jerárquicos, ya que el 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 un CTE recursivo 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 instrucción SELECT, INSERT, UPDATE, DELETE o MERGE principal.

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, precedida por una cláusula CON que incluye un CTE denominado cteReports:

clasificación de inserción java 
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 resultar útiles cuando necesita generar conjuntos de resultados temporales a los que se puede acceder en una instrucción SELECT, INSERT, UPDATE, DELETE o MERGE.

Características y limitaciones de las expresiones de tabla comunes en Azure

La implementación actual de CTE en Azure Synapse Analytics y Analytics Platform System (PDW) tiene las siguientes características y limitaciones:

  1. Un CTE sólo se puede especificar en una declaración SELECT.
  2. Un CTE sólo se puede especificar en una declaración CREATE VIEW.
  3. Un CTE solo se puede especificar en una declaración CREATE TABLE AS SELECT (CTAS).
  4. Un CTE solo se puede especificar en una instrucción CREATE REMOTE TABLE AS SELECT (CRTAS).
  5. Un CTE solo se puede especificar en una declaración CREATE EXTERNAL TABLE AS SELECT (CETAS).