Descubre el Poder de las Common Table Expressions (CTE) en SQL

 

En el mundo del análisis de datos y la gestión de bases de datos, SQL (Structured Query Language) sigue siendo una herramienta indispensable. Hoy, queremos compartir un truco impresionante que elevará tus habilidades en SQL: el uso de las Common Table Expressions (CTE). Este método no solo te permitirá simplificar consultas complejas, sino que también hará que tus datos sean más manejables y comprensibles.

¿Qué es una Common Table Expression (CTE)?

Una CTE, o Common Table Expression, es una función en SQL que permite definir una consulta temporal cuyo resultado puede ser referenciado dentro de la misma consulta. Las CTE se utilizan principalmente para mejorar la legibilidad y organización de las consultas SQL, especialmente cuando se trabaja con consultas recursivas o muy complejas.

Beneficios de Usar CTE

1. Claridad y Organización: Las CTE hacen que las consultas largas y complicadas sean más legibles y fáciles de entender.
2. Reutilización de Código: Puedes definir una CTE una vez y referenciarla múltiples veces en tu consulta principal.
3. Facilidad en Consultas Recursivas: Las CTE son ideales para manejar estructuras de datos jerárquicas, como árboles genealógicos o organigramas.

Ejemplo Práctico: Encontrar la Jerarquía de Empleados

Imagina que tienes una tabla de empleados en tu empresa y quieres listar todos los empleados junto con su jerarquía. Aquí es donde una CTE recursiva brilla.

Estructura de la Tabla employees

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    manager_id INT
);

Datos de Ejemplo

INSERT INTO employees (employee_id, employee_name, manager_id) VALUES
(1, 'CEO', NULL),
(2, 'CTO', 1),
(3, 'CFO', 1),
(4, 'Engineer', 2),
(5, 'Accountant', 3);

Consulta con WITH

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT 
        employee_id, 
        employee_name, 
        manager_id, 
        employee_name AS hierarchy
    FROM 
        employees
    WHERE 
        manager_id IS NULL
    
    UNION ALL
    
    SELECT 
        e.employee_id, 
        e.employee_name, 
        e.manager_id, 
        CONCAT(eh.hierarchy, ' -> ', e.employee_name)
    FROM 
        employees e
    INNER JOIN 
        EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)

SELECT * FROM EmployeeHierarchy;

Explicación del Código

1. Definición del CTE:

   • La primera parte del WITH selecciona el nodo raíz de la jerarquía, en este caso, el CEO.
   • La segunda parte del WITH se une recursivamente con el CTE para construir la jerarquía completa.

2. Consulta Principal:

   • Selecciona todos los datos generados por el CTE.

Resultados Esperados

employee_id | employee_name | manager_id | hierarchy
------------|---------------|------------|-----------------------
1           | CEO           | NULL       | CEO
2           | CTO           | 1          | CEO -> CTO
3           | CFO           | 1          | CEO -> CFO
4           | Engineer      | 2          | CEO -> CTO -> Engineer
5           | Accountant    | 3          | CEO -> CFO -> Accountant