El Proyecto | Sobre nosotros | Contribuir | Donaciones | Licencia
HOME Mis cursos Finalizados
◄ Anterior
◄

Expresiones de Tabla Común (CTEs)

Siguiente ►
►

Las Expresiones de Tabla Común (CTEs, por sus siglas en inglés) son una característica poderosa de SQL que permite crear consultas más legibles y organizadas. Las CTEs se utilizan para definir una tabla temporal que puede ser referenciada dentro de una consulta SELECT, INSERT, UPDATE o DELETE. Son especialmente útiles para dividir consultas complejas en partes más manejables y para mejorar la legibilidad del código SQL.

¿Qué es una CTE?

Una CTE es una consulta nombrada que se define dentro de una instrucción WITH. Esta consulta puede ser referenciada posteriormente en la misma instrucción SQL. Las CTEs pueden ser recursivas o no recursivas.

Sintaxis Básica

La sintaxis básica de una CTE es la siguiente:

WITH cte_name AS (
    -- Consulta SQL
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT column1, column2
FROM cte_name;

Ejemplo Práctico

Supongamos que tenemos una tabla employees con la siguiente estructura:

employee_id first_name last_name department_id salary
1 John Doe 10 50000
2 Jane Smith 20 60000
3 Alice Johnson 10 55000
4 Bob Brown 30 70000

Queremos obtener una lista de empleados junto con el nombre de su departamento. Para esto, podemos usar una CTE para simplificar la consulta:

WITH department_names AS (
    SELECT department_id, 
           CASE 
               WHEN department_id = 10 THEN 'HR'
               WHEN department_id = 20 THEN 'Finance'
               WHEN department_id = 30 THEN 'Engineering'
               ELSE 'Unknown'
           END AS department_name
    FROM employees
)
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN department_names d ON e.department_id = d.department_id;

Explicación del Código

  1. Definición de la CTE: La CTE department_names se define utilizando la cláusula WITH. Esta CTE selecciona department_id y asigna un nombre de departamento basado en el department_id.
  2. Consulta Principal: La consulta principal selecciona los campos employee_id, first_name, last_name de la tabla employees y el department_name de la CTE department_names. Se realiza un JOIN entre employees y department_names basado en department_id.

CTEs Recursivas

Las CTEs recursivas son útiles para trabajar con datos jerárquicos, como estructuras de árbol. Una CTE recursiva se define con una consulta de anclaje y una consulta recursiva.

Sintaxis de una CTE Recursiva

WITH RECURSIVE cte_name AS (
    -- Consulta de anclaje
    SELECT column1, column2
    FROM table_name
    WHERE condition
    UNION ALL
    -- Consulta recursiva
    SELECT column1, column2
    FROM table_name
    JOIN cte_name ON table_name.column = cte_name.column
)
SELECT column1, column2
FROM cte_name;

Ejemplo Práctico de CTE Recursiva

Supongamos que tenemos una tabla employees con una columna manager_id que indica el jefe de cada empleado. Queremos obtener una lista de todos los empleados y sus jefes directos e indirectos.

WITH RECURSIVE employee_hierarchy AS (
    -- Consulta de anclaje
    SELECT employee_id, first_name, last_name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- Consulta recursiva
    SELECT e.employee_id, e.first_name, e.last_name, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT employee_id, first_name, last_name, manager_id
FROM employee_hierarchy;

Explicación del Código

  1. Consulta de Anclaje: Selecciona los empleados que no tienen jefe (manager_id IS NULL).
  2. Consulta Recursiva: Selecciona los empleados y los une con la CTE employee_hierarchy en base a manager_id.
  3. Consulta Principal: Selecciona todos los campos de la CTE employee_hierarchy.

Ejercicios Prácticos

Ejercicio 1: Uso Básico de CTE

Objetivo: Crear una CTE para seleccionar empleados con un salario superior a 55000 y mostrar sus nombres y salarios.

Tabla employees:

employee_id first_name last_name department_id salary
1 John Doe 10 50000
2 Jane Smith 20 60000
3 Alice Johnson 10 55000
4 Bob Brown 30 70000

Solución:

WITH high_salary_employees AS (
    SELECT first_name, last_name, salary
    FROM employees
    WHERE salary > 55000
)
SELECT first_name, last_name, salary
FROM high_salary_employees;

Ejercicio 2: CTE Recursiva

Objetivo: Crear una CTE recursiva para obtener la jerarquía de empleados y sus jefes.

Tabla employees:

employee_id first_name last_name manager_id
1 John Doe NULL
2 Jane Smith 1
3 Alice Johnson 2
4 Bob Brown 2

Solución:

WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, first_name, last_name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.first_name, e.last_name, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT employee_id, first_name, last_name, manager_id
FROM employee_hierarchy;

Conclusión

Las Expresiones de Tabla Común (CTEs) son una herramienta poderosa para simplificar y organizar consultas SQL complejas. Las CTEs no recursivas permiten dividir consultas en partes más manejables, mientras que las CTEs recursivas son ideales para trabajar con datos jerárquicos. Practicar con CTEs te ayudará a escribir consultas más claras y eficientes en PostgreSQL.

◄ Anterior
◄
Siguiente ►
►

Curso de PostgreSQL

Módulo 1: Introducción a PostgreSQL

Módulo 2: Operaciones Básicas de SQL

Módulo 3: Consultas Avanzadas de SQL

Módulo 4: Diseño de Bases de Datos y Normalización

Módulo 5: Características Avanzadas de PostgreSQL

Módulo 6: Ajuste de Rendimiento y Optimización

Módulo 7: Seguridad y Gestión de Usuarios

Módulo 8: Trabajando con JSON y Características NoSQL

Módulo 9: Extensiones y Herramientas Avanzadas

Módulo 10: Estudios de Caso y Aplicaciones del Mundo Real

El Proyecto | Sobre nosotros | Contribuir | Donaciones | Licencia
© Copyright 2024. Todos los derechos reservados