Las Expresiones de Tabla Comunes (CTEs, por sus siglas en inglés) son una característica poderosa en SQL que permite crear tablas temporales que pueden ser referenciadas dentro de una consulta. Las CTEs son útiles para simplificar consultas complejas, mejorar la legibilidad del código y evitar la repetición de subconsultas.
¿Qué es una CTE?
Una CTE es una consulta nombrada que se define dentro de una instrucción WITH y puede ser referenciada en la consulta principal. Las CTEs son temporales y solo existen durante la ejecución de la consulta.
Sintaxis básica de una CTE
WITH cte_name AS (
SELECT column1, column2, ...
FROM table_name
WHERE condition
)
SELECT column1, column2, ...
FROM cte_name;Ejemplo práctico
Supongamos que tenemos una tabla employees con las siguientes columnas: employee_id, first_name, last_name, department_id, y salary. Queremos encontrar los empleados que ganan más que el salario promedio de su departamento.
WITH avg_salary AS (
SELECT department_id, AVG(salary) AS avg_dept_salary
FROM employees
GROUP BY department_id
)
SELECT e.employee_id, e.first_name, e.last_name, e.salary, a.avg_dept_salary
FROM employees e
JOIN avg_salary a ON e.department_id = a.department_id
WHERE e.salary > a.avg_dept_salary;En este ejemplo:
- La CTE
avg_salarycalcula el salario promedio por departamento. - La consulta principal selecciona los empleados cuyo salario es mayor que el salario promedio de su departamento.
Ventajas de usar CTEs
- Legibilidad: Las CTEs pueden hacer que las consultas complejas sean más fáciles de leer y entender.
- Reutilización: Permiten reutilizar subconsultas sin tener que repetir el código.
- Modularidad: Facilitan la división de consultas complejas en partes más manejables.
CTEs recursivas
Las CTEs también pueden ser recursivas, lo que significa que pueden referenciarse a sí mismas. Esto es útil para trabajar con datos jerárquicos, como estructuras de árbol.
Sintaxis de una CTE recursiva
WITH RECURSIVE cte_name AS (
-- Parte no recursiva
SELECT column1, column2, ...
FROM table_name
WHERE condition
UNION ALL
-- Parte 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 las columnas employee_id, first_name, last_name, y manager_id. Queremos encontrar la jerarquía de empleados comenzando desde un gerente específico.
WITH RECURSIVE employee_hierarchy AS (
-- Parte no recursiva: selecciona el gerente inicial
SELECT employee_id, first_name, last_name, manager_id
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Parte recursiva: selecciona los empleados que reportan al gerente
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;En este ejemplo:
- La parte no recursiva selecciona al gerente inicial (aquellos con
manager_idnulo). - La parte recursiva selecciona a los empleados que reportan al gerente y se une recursivamente a la CTE
employee_hierarchy.
Ejercicios prácticos
Ejercicio 1: CTE básica
Objetivo: Crear una CTE que seleccione todos los empleados cuyo salario es mayor que el salario promedio de la empresa.
Tabla: employees (columnas: employee_id, first_name, last_name, salary)
Solución:
WITH avg_salary AS (
SELECT AVG(salary) AS avg_salary
FROM employees
)
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (SELECT avg_salary FROM avg_salary);Ejercicio 2: CTE recursiva
Objetivo: Crear una CTE recursiva que muestre la jerarquía de empleados comenzando desde un gerente específico.
Tabla: employees (columnas: employee_id, first_name, last_name, manager_id)
Solución:
WITH RECURSIVE employee_hierarchy AS (
-- Parte no recursiva: selecciona el gerente inicial
SELECT employee_id, first_name, last_name, manager_id
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Parte recursiva: selecciona los empleados que reportan al gerente
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;Resumen
En esta sección, hemos aprendido sobre las Expresiones de Tabla Comunes (CTEs) y cómo pueden simplificar y mejorar la legibilidad de nuestras consultas SQL. También hemos explorado las CTEs recursivas, que son útiles para trabajar con datos jerárquicos. Con la práctica y el uso de CTEs, podrás escribir consultas SQL más eficientes y fáciles de mantener.
En el próximo módulo, profundizaremos en Funciones de ventana, una característica avanzada de SQL que permite realizar cálculos sobre un conjunto de filas relacionadas.
Curso de SQL
Módulo 1: Introducción a SQL
Módulo 2: Consultas básicas de SQL
- Instrucción SELECT
- Filtrando datos con WHERE
- Ordenando datos con ORDER BY
- Limitando resultados con LIMIT
Módulo 3: Trabajando con múltiples tablas
Módulo 4: Filtrado avanzado de datos
- Usando LIKE para coincidencia de patrones
- Operadores IN y BETWEEN
- Valores NULL y IS NULL
- Agregando datos con GROUP BY
- Cláusula HAVING
Módulo 5: Manipulación de datos
Módulo 6: Funciones avanzadas de SQL
Módulo 7: Subconsultas y consultas anidadas
- Introducción a subconsultas
- Subconsultas correlacionadas
- EXISTS y NOT EXISTS
- Usando subconsultas en cláusulas SELECT, FROM y WHERE
Módulo 8: Índices y optimización de rendimiento
- Entendiendo los índices
- Creación y gestión de índices
- Técnicas de optimización de consultas
- Análisis del rendimiento de consultas
Módulo 9: Transacciones y concurrencia
- Introducción a las transacciones
- Propiedades ACID
- Instrucciones de control de transacciones
- Manejo de concurrencia