logo

TechCodeview

Expresión de tabla común (CTE) en SQL Server

Usaremos las expresiones de tabla comunes o CTE de SQL Server para facilitar las uniones y subconsultas complejas. También proporciona una forma de consultar datos jerárquicos, como una jerarquía organizativa. Este artículo ofrece una descripción completa de CTE, tipos de CTE, ventajas, desventajas y cómo usarlos en SQL Server.

¿Qué es CTE en SQL Server?

Una CTE (Expresión de tabla común) es un conjunto de resultados único que solo existe mientras dura la consulta. . Nos permite hacer referencia a datos dentro del alcance de ejecución de una única instrucción SELECT, INSERT, UPDATE, DELETE, CREATE VIEW o MERGE. Es temporal porque su resultado no se puede almacenar en ningún lugar y se perderá tan pronto como se complete la ejecución de una consulta. Llegó por primera vez con la versión SQL Server 2005. Un DBA siempre prefirió utilizar CTE como alternativa a una subconsulta/vista. Siguen el estándar ANSI SQL 99 y son compatibles con SQL.

Sintaxis CTE en SQL Server

La sintaxis de CTE incluye un nombre de CTE, una lista de columnas opcional y una declaración/consulta que define la expresión de tabla común (CTE). Después de definir el CTE, podemos usarlo como vista en una consulta SELECCIONAR, INSERTAR, ACTUALIZAR, ELIMINAR y FUSIONAR.

La siguiente es la sintaxis básica de CTE en SQL Server:

 WITH cte_name (column_names) AS (query) SELECT * FROM cte_name;

En esta sintaxis:

  • Primero hemos especificado el nombre CTE al que se hará referencia más adelante en una consulta.
  • El siguiente paso es crear una lista de columnas separadas por comas. Garantiza que el número de columnas en los argumentos de definición de CTE y el número de columnas en la consulta deben ser los mismos. Si no hemos definido las columnas de argumentos del CTE, utilizará las columnas de consulta que definen el CTE.
  • Después de eso, usaremos la palabra clave AS después del nombre de la expresión y luego definiremos una instrucción SELECT cuyo conjunto de resultados llena el CTE.
  • Finalmente, usaremos el nombre CTE en una consulta como SELECT, INSERT, UPDATE, DELETE y MERGE.

Se debe tener en cuenta al escribir la definición de consulta CTE; No podemos utilizar las siguientes cláusulas:

  1. ORDER BY a menos que también utilice como cláusula TOP
  2. EN
  3. Cláusula OPCIÓN con sugerencias de consulta
  4. PARA NAVEGAR

La siguiente imagen es la representación de la definición de consulta CTE.

CTE en SQL Server

Aquí, la primera parte es una expresión CTE que contiene una consulta SQL que se puede ejecutar de forma independiente en SQL. Y la segunda parte es la consulta que utiliza el CTE para mostrar el resultado.

Ejemplo

Entendamos cómo funciona CTE en SQL Server usando varios ejemplos. Aquí vamos a utilizar una tabla ' cliente ' para una demostración. Supongamos que esta tabla contiene los siguientes datos:

CTE en SQL Server

En este ejemplo, el nombre CTE es clientes_en_nuevayork , la subconsulta que define el CTE devuelve las tres columnas nombre del cliente, correo electrónico, y estado . Como resultado, el CTE customer_in_newyork devolverá a todos los clientes que viven en el estado de Nueva York.

Después de definir el CTE clientes_en_nuevayork, lo hemos referenciado en el SELECCIONAR declaración para obtener los detalles de aquellos clientes que se encuentran en Nueva York.

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York') SELECT c_name, email, state FROM customers_in_NewYork;

Después de ejecutar la declaración anterior, obtendrá el siguiente resultado. Aquí, podemos ver que el resultado devuelve solo la información del cliente que se encuentra en el estado de Nueva York.

CTE en SQL Server

CTE múltiples

En algunos casos, necesitaremos crear varias consultas CTE y unirlas para ver los resultados. Podemos utilizar el concepto de múltiples CTE en este escenario. Necesitamos usar el operador de coma para crear múltiples consultas CTE y fusionarlas en una sola declaración. El operador de coma ',' debe ir precedido del nombre del CTE para distinguir varios CTE.

Múltiples CTE nos ayudan a simplificar consultas complejas que eventualmente se unen. Cada pieza compleja tenía su propio CTE, al que luego se podía hacer referencia y unirlo fuera de la cláusula CON.

NOTA: La definición de CTE múltiple se puede definir usando UNION, UNION ALL, JOIN, INTERSECT o EXCEPT.

La siguiente sintaxis lo explica más claramente: 

 WITH cte_name1 (column_names) AS (query), cte_name2 (column_names) AS (query) SELECT * FROM cte_name UNION ALL SELECT * FROM cte_name;

Ejemplo

Entendamos cómo funcionan múltiples CTE en SQL Server. Aquí, vamos a utilizar lo anterior ' cliente ' Mesa para una demostración.

En este ejemplo, hemos definido los dos nombres CTE. clientes_en_nuevayork y clientes_en_california . Luego, el conjunto de resultados de subconsultas de estos CTE completa el CTE. Finalmente, usaremos los nombres de CTE en una consulta que devolverá todos los clientes que se encuentran en Nueva York y Estado de California .

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York'), customers_in_California AS (SELECT * FROM customer WHERE state = 'California') SELECT c_name, email, state FROM customers_in_NewYork UNION ALL SELECT c_name, email, state FROM customers_in_California;

Estado de Nueva York y California.

CTE en SQL Server

¿Por qué necesitamos CTE?

Al igual que las vistas de bases de datos y las tablas derivadas, las CTE pueden facilitar la escritura y la gestión de consultas complejas haciéndolas más legibles y sencillas. Podemos lograr esta característica dividiendo las consultas complejas en bloques simples que puedan reutilizarse para reescribir la consulta.

Algunos de sus casos de uso se detallan a continuación:

  • Es útil cuando necesitamos definir una tabla derivada varias veces dentro de una sola consulta.
  • Es útil cuando necesitamos crear una alternativa a una vista en la base de datos.
  • Es útil cuando necesitamos realizar el mismo cálculo varias veces en varios componentes de la consulta simultáneamente.
  • Es útil cuando necesitamos utilizar funciones de clasificación como ROW_NUMBER(), RANK() y NTILE().

Algunas de sus ventajas se detallan a continuación:

etiquetas html
  • CTE facilita el mantenimiento del código.
  • CTE aumenta la legibilidad del código.
  • Aumenta el rendimiento de la consulta.
  • CTE permite implementar consultas recursivas fácilmente.

Tipos de CTE en SQL Server

SQL Server divide las CTE (Expresiones de tabla comunes) en dos categorías amplias:

  1. CTE recursivo
  2. CTE no recursivo

CTE recursivo

Una expresión de tabla común se conoce como CTE recursiva y hace referencia a sí misma. Su concepto se basa en la recursividad, que se define como ' la aplicación de un proceso o definición recursiva repetidamente .' Cuando ejecutamos una consulta recursiva, itera repetidamente sobre un subconjunto de datos. Simplemente se define como una consulta que se llama a sí misma. Hay una condición final en algún punto, por lo que no se llama a sí misma infinitamente.

Un CTE recursivo debe tener un UNIÓN TODOS declaración y una segunda definición de consulta que hace referencia al propio CTE para ser recursiva.

Ejemplo

Entendamos cómo funciona el CTE recursivo en SQL Server. Considere la siguiente afirmación, que genera una serie de los primeros cinco números impares: 

 WITH odd_num_cte (id, n) AS ( SELECT 1, 1 UNION ALL SELECT id+1, n+2 from odd_num_cte where id <5 ) select * from odd_num_cte; < pre> <p>When we execute this recursive CTE, we will see the output as below:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-5.webp" alt="CTE in SQL Server"> <p>The below example is the more advanced recursive CTE. Here, we are going to use the &apos; <strong>jtp_employees</strong> &apos; table for a demonstration that contains the below data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-6.webp" alt="CTE in SQL Server"> <p>This example will display the hierarchy of employee data. Here table provides a reference to that person&apos;s manager for each employee. The reference is itself an employee id within the same table.</p> <pre> WITH cte_recursion (EmpID, FirstName, LastName, MgrID, EmpLevel) AS ( SELECT EmployeeID, FirstName, LastName, ManagerID, 1 FROM jtp_employees WHERE ManagerID IS NULL UNION ALL SELECT emp.EmployeeID, emp.FirstName, emp.LastName, emp.ManagerID, r.EmpLevel + 1 FROM jtp_employees emp INNER JOIN cte_recursion r ON emp.ManagerID = r.EmpID ) SELECT FirstName + &apos; &apos; + LastName AS FullName, EmpLevel, (SELECT FirstName + &apos; &apos; + LastName FROM jtp_employees WHERE EmployeeID = cte_recursion.MgrID) AS Manager FROM cte_recursion ORDER BY EmpLevel, MgrID </pre> <p>This CTE will give the following output where we can see the hierarchy of employee data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-7.webp" alt="CTE in SQL Server"> <h3>Non-Recursive CTE</h3> <p>A common table expression that doesn&apos;t reference itself is known as a non-recursive CTE. A non-recursive CTE is simple and easier to understand because it does not use the concept of recursion. According to the CTE Syntax, each CTE query will begin with a &apos; <strong>With</strong> &apos; clause followed by the CTE name and column list, then AS with parenthesis.</p> <h2>Disadvantages of CTE</h2> <p>The following are the limitations of using CTE in SQL Server:</p> <ul> <li>CTE members are unable to use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc.</li> <li>The CTE can only be referenced once by the Recursive member.</li> <li>We cannot use the table variables and CTEs as parameters in stored procedures.</li> <li>We already know that the CTE could be used in place of a view, but a CTE cannot be nested, while Views can.</li> <li>Since it&apos;s just a shortcut for a query or subquery, it can&apos;t be reused in another query.</li> <li>The number of columns in the CTE arguments and the number of columns in the query must be the same.</li> </ul> <hr></5>

Este CTE dará el siguiente resultado donde podremos ver la jerarquía de datos de los empleados:

CTE en SQL Server

CTE no recursivo

Una expresión de tabla común que no hace referencia a sí misma se conoce como CTE no recursiva. Un CTE no recursivo es simple y más fácil de entender porque no utiliza el concepto de recursividad. Según la sintaxis CTE, cada consulta CTE comenzará con un ' Con ' cláusula seguida del nombre CTE y la lista de columnas, luego AS con paréntesis.

Desventajas de la CTE

Las siguientes son las limitaciones del uso de CTE en SQL Server:

  • Los miembros de CTE no pueden utilizar cláusulas de palabras clave como Distinto, Agrupar por, Tener, Superior, Unirse, etc.
  • El miembro recursivo solo puede hacer referencia al CTE una vez.
  • No podemos utilizar las variables de tabla y CTE como parámetros en procedimientos almacenados.
  • Ya sabemos que el CTE podría usarse en lugar de una vista, pero un CTE no se puede anidar, mientras que las Vistas sí.
  • Dado que es solo un atajo para una consulta o subconsulta, no se puede reutilizar en otra consulta.
  • El número de columnas de los argumentos CTE y el número de columnas de la consulta deben ser los mismos.