La expresión de tabla común (CTE) es una característica poderosa en SQL Server que te permite crear conjuntos de registros temporales y nombrados dentro de una consulta. A diferencia de las tablas regulares, las CTE no se almacenan como objetos en la base de datos y solo duran durante la ejecución de la consulta.

Una CTE puede ser referenciada múltiples veces e incluso hacer referencia a sí misma, lo que la convierte en una herramienta versátil para consultas complejas. Veamos algunos ejemplos para entender cómo funcionan las CTE.

Creando la Base de Datos y las Tablas

Antes de adentrarnos en las CTE, primero creemos una base de datos de muestra y las tablas con las que trabajaremos:

CREATE DATABASE CTE_Learning;

USE CTE_Learning;

CREATE TABLE Empleado (
    IdEmpleado INT IDENTITY(1,1),
    NombreEmpleado NVARCHAR(150),
    ApellidoEmpleado NVARCHAR(150),
    IdGerente INT,
    FechaRegistro DATETIME
);

CREATE TABLE Proyecto (
    IdProyecto INT IDENTITY(1,1),
    NombreProyecto NVARCHAR(150)
);

CREATE TABLE EmpleadoProyecto (
    IdEmpleadoProyecto INT IDENTITY(1,1),
    IdEmpleado INT,
    IdProyecto INT
);

INSERT INTO Empleado (NombreEmpleado, ApellidoEmpleado, IdGerente, FechaRegistro)
VALUES ('Samir', 'Sethi', 0, GETDATE()),
       ('Vivek', 'Johari', 1, GETDATE()),
       ('Avinash', 'Dubey', 2, GETDATE()),
       ('Sunny', 'Rana', 3, GETDATE()),
       ('Himanshu', 'Aggarwal', 3, GETDATE());

INSERT INTO Proyecto (NombreProyecto)
VALUES ('Proyecto1'),
       ('Proyecto2'),
       ('Proyecto3');

INSERT INTO EmpleadoProyecto (IdEmpleado, IdProyecto)
VALUES (1, 1),
       (1, 2),
       (1, 3),
       (2, 1),
       (2, 2),
       (3, 1),
       (4, 2);

Usando las Expresiones de Tabla Comunes (CTE)

La sintaxis para crear una CTE es la siguiente:

WITH NombreCTE (ListaColumnas) AS (
    DefinicionConsultaCTE
)

Aquí, NombreCTE es el nombre de la CTE, ListaColumnas es la lista de nombres de columnas únicas devueltas por la consulta de la CTE, y DefinicionConsultaCTE es la consulta SQL utilizada en la CTE.

Supongamos que queremos recuperar los detalles de los empleados que están asignados a más de un proyecto. Podemos usar una CTE para lograr esto:

WITH CTE_InfoProyecto (IdEmpleado, TotalProyectos) AS (
    SELECT IdEmpleado, COUNT(IdEmpleado)
    FROM EmpleadoProyecto
    GROUP BY IdEmpleado
    HAVING COUNT(IdEmpleado) > 1
)
SELECT E.IdEmpleado, E.NombreEmpleado + ' ' + E.ApellidoEmpleado, E.FechaRegistro
FROM Empleado E
INNER JOIN CTE_InfoProyecto ON E.IdEmpleado = CTE_InfoProyecto.IdEmpleado;

En el ejemplo anterior, creamos una CTE llamada CTE_InfoProyecto para obtener los detalles de los empleados que están asignados a más de un proyecto. Luego unimos esta CTE con la tabla Empleado para recuperar la información deseada.

Las CTE también se pueden usar en conjunto entre sí dentro de una sola consulta. Veamos un ejemplo:

WITH CTE_InfoProyecto (IdEmpleado, TotalProyectos) AS (
    SELECT IdEmpleado, COUNT(IdEmpleado)
    FROM EmpleadoProyecto
    GROUP BY IdEmpleado
    HAVING COUNT(IdEmpleado) > 1
),
CTE_EmpleadoProyecto (IdEmpleado, NombreProyecto) AS (
    SELECT CTE_InfoProyecto.IdEmpleado, NombreProyecto
    FROM CTE_InfoProyecto
    INNER JOIN EmpleadoProyecto ON CTE_InfoProyecto.IdEmpleado = EmpleadoProyecto.IdEmpleado
    INNER JOIN Proyecto ON EmpleadoProyecto.IdProyecto = Proyecto.IdProyecto
)
SELECT E.IdEmpleado, E.NombreEmpleado + ' ' + E.ApellidoEmpleado, E.FechaRegistro, CTE_EmpleadoProyecto.NombreProyecto
FROM Empleado E
INNER JOIN CTE_EmpleadoProyecto ON E.IdEmpleado = CTE_EmpleadoProyecto.IdEmpleado;

En este ejemplo, tenemos dos CTEs, CTE_InfoProyecto y CTE_EmpleadoProyecto, dentro de la misma cláusula WITH. Separamos las dos CTEs con una coma. Esto nos permite recuperar datos jerárquicos o realizar uniones complejas dentro de una sola consulta.

Por último, las CTEs pueden hacer referencia a sí mismas, lo que nos permite recuperar datos jerárquicos. Veamos un ejemplo:

WITH CTE_NivelGerente (IdEmpleado, NombreEmpleado, IdGerente, NivelTrabajo) AS (
    SELECT E.IdEmpleado, E.NombreEmpleado + ' ' + E.ApellidoEmpleado, E.IdGerente, 0
    FROM Empleado E
    WHERE E.IdEmpleado = 1
    UNION ALL
    SELECT M.IdEmpleado, M.NombreEmpleado + ' ' + M.ApellidoEmpleado, M.IdGerente, CTE_NivelGerente.NivelTrabajo + 1
    FROM Empleado M
    INNER JOIN CTE_NivelGerente ON M.IdGerente = CTE_NivelGerente.IdEmpleado
)
SELECT IdEmpleado, NombreEmpleado, IdGerente, NivelTrabajo
FROM CTE_NivelGerente;

En este ejemplo, utilizamos una CTE llamada CTE_NivelGerente para recuperar los datos jerárquicos de los empleados en una organización. La CTE comienza con el empleado de nivel superior (IdEmpleado = 1) y se une recursivamente consigo misma para recuperar los empleados en cada nivel.

Las expresiones de tabla comunes (CTEs) son una herramienta poderosa en SQL Server que te permiten crear conjuntos de registros temporales y nombrados dentro de una consulta. Proporcionan una forma flexible y eficiente de realizar consultas complejas y recuperar datos jerárquicos. Comprender cómo usar las CTEs puede mejorar en gran medida tus habilidades en SQL Server y mejorar el rendimiento de tus consultas.