Les Expressions de Taula Comunes (CTEs) són una característica poderosa de SQL que permet definir una taula temporal dins d'una consulta. Les CTEs poden fer que les consultes siguin més llegibles i fàcils de mantenir, especialment quan es treballa amb consultes complexes o subconsultes repetides.
Què és una CTE?
Una CTE és una consulta temporal que es defineix al començament d'una instrucció SELECT, INSERT, UPDATE o DELETE. Les CTEs es defineixen utilitzant la clàusula WITH i poden ser referenciades dins de la consulta principal.
Sintaxi bàsica
WITH cte_name AS (
-- Subconsulta
SELECT column1, column2, ...
FROM table_name
WHERE condition
)
SELECT column1, column2, ...
FROM cte_name
WHERE condition;Exemple pràctic
Suposem que tenim una taula employees amb les següents columnes: employee_id, first_name, last_name, department_id, i salary. Volem trobar els empleats que tenen un salari superior a la mitjana del seu departament.
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 aquest exemple:
- Definim una CTE anomenada
avg_salaryque calcula el salari mitjà per a cada departament. - Utilitzem aquesta CTE en la consulta principal per trobar els empleats que tenen un salari superior a la mitjana del seu departament.
Avantatges de les CTEs
- Millora de la llegibilitat: Les CTEs permeten dividir consultes complexes en parts més petites i manejables.
- Reutilització de codi: Les CTEs poden ser referenciades múltiples vegades dins de la mateixa consulta, evitant la duplicació de codi.
- Facilitat de depuració: Les CTEs permeten aïllar parts de la consulta per a una depuració més fàcil.
CTEs recursives
Les CTEs també poden ser recursives, és a dir, poden referenciar-se a si mateixes. Això és útil per treballar amb dades jeràrquiques, com ara estructures d'arbre.
Sintaxi de CTE recursiva
WITH RECURSIVE cte_name AS (
-- Part d'ancoratge
SELECT column1, column2, ...
FROM table_name
WHERE condition
UNION ALL
-- Part recursiva
SELECT column1, column2, ...
FROM table_name
JOIN cte_name ON table_name.column = cte_name.column
)
SELECT column1, column2, ...
FROM cte_name;Exemple pràctic de CTE recursiva
Suposem que tenim una taula employees amb les columnes employee_id, first_name, last_name, manager_id. Volem trobar la jerarquia de gestió per a cada empleat.
WITH RECURSIVE management_hierarchy AS (
-- Part d'ancoratge: selecciona els empleats que no tenen un manager
SELECT employee_id, first_name, last_name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Part recursiva: selecciona els empleats i els uneix amb els seus managers
SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, mh.level + 1
FROM employees e
JOIN management_hierarchy mh ON e.manager_id = mh.employee_id
)
SELECT employee_id, first_name, last_name, manager_id, level
FROM management_hierarchy
ORDER BY level, manager_id;En aquest exemple:
- La part d'ancoratge selecciona els empleats que no tenen un manager (
manager_id IS NULL). - La part recursiva uneix els empleats amb els seus managers i incrementa el nivell de jerarquia.
- La consulta final selecciona tots els empleats amb el seu nivell de jerarquia.
Exercicis pràctics
Exercici 1
Crea una CTE que calculi el salari mitjà per a cada departament i després utilitza aquesta CTE per trobar els empleats que tenen un salari inferior a la mitjana del seu departament.
Solució
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;Exercici 2
Utilitza una CTE recursiva per trobar tots els subordinats directes i indirectes d'un manager específic.
Solució
WITH RECURSIVE subordinates AS (
-- Part d'ancoratge: selecciona els empleats directament subordinats al manager amb ID 1
SELECT employee_id, first_name, last_name, manager_id
FROM employees
WHERE manager_id = 1
UNION ALL
-- Part recursiva: selecciona els empleats i els uneix amb els seus managers
SELECT e.employee_id, e.first_name, e.last_name, e.manager_id
FROM employees e
JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT employee_id, first_name, last_name, manager_id
FROM subordinates;Conclusió
Les Expressions de Taula Comunes (CTEs) són una eina poderosa per millorar la llegibilitat i mantenibilitat de les consultes SQL. Les CTEs recursives són especialment útils per treballar amb dades jeràrquiques. Practicar amb CTEs i CTEs recursives t'ajudarà a comprendre millor com estructurar i optimitzar les teves consultes SQL.
Curs de SQL
Mòdul 1: Introducció a SQL
Mòdul 2: Consultes bàsiques de SQL
Mòdul 3: Treballar amb múltiples taules
Mòdul 4: Filtratge avançat de dades
- Utilitzar LIKE per a coincidències de patrons
- Operadors IN i BETWEEN
- Valors NULL i IS NULL
- Agrupar dades amb GROUP BY
- Clàusula HAVING
Mòdul 5: Manipulació de dades
Mòdul 6: Funcions avançades de SQL
Mòdul 7: Subconsultes i consultes niades
- Introducció a les subconsultes
- Subconsultes correlacionades
- EXISTS i NOT EXISTS
- Utilitzar subconsultes en les clàusules SELECT, FROM i WHERE
Mòdul 8: Índexs i optimització del rendiment
- Comprendre els índexs
- Crear i gestionar índexs
- Tècniques d'optimització de consultes
- Analitzar el rendiment de les consultes
Mòdul 9: Transaccions i concurrència
- Introducció a les transaccions
- Propietats ACID
- Instruccions de control de transaccions
- Gestionar la concurrència
Mòdul 10: Temes avançats
Mòdul 11: SQL en la pràctica
- Casos d'ús del món real
- Millors pràctiques
- SQL per a l'anàlisi de dades
- SQL en el desenvolupament web