El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència

HOME

Les Expressions de Taula Comunes (CTEs) són una característica poderosa de SQL que permet definir subconsultes temporals que poden ser referenciades dins de la consulta principal. Les CTEs milloren la llegibilitat i mantenibilitat del codi SQL, especialment quan es treballa amb consultes complexes.

Què és una CTE?

Una CTE és una subconsulta que es defineix amb la clàusula WITH i es pot referenciar dins de la consulta principal. Les CTEs són útils per dividir consultes complexes en parts més manejables i per reutilitzar subconsultes dins de la mateixa consulta.

Sintaxi bàsica

La sintaxi bàsica d'una CTE és la següent:

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

Exemple bàsic

Suposem que tenim una taula employees amb les següents columnes: id, name, department, i salary. Volem obtenir els empleats del departament de 'Vendes' amb un salari superior a 50,000.

WITH sales_employees AS (
    SELECT id, name, salary
    FROM employees
    WHERE department = 'Vendes' AND salary > 50000
)
SELECT id, name, salary
FROM sales_employees;

CTEs recursives

Les CTEs recursives són una extensió de les CTEs que permeten referenciar-se a si mateixes. Són especialment útils per treballar amb dades jeràrquiques, com ara arbres o grafs.

Sintaxi de CTE recursiva

WITH RECURSIVE cte_name AS (
    -- Part no recursiva
    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 recursiu

Suposem que tenim una taula employees amb les columnes id, name, manager_id, i volem obtenir una llista jeràrquica de tots els empleats i els seus managers.

WITH RECURSIVE employee_hierarchy AS (
    -- Part no recursiva: selecciona els empleats sense manager
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Part recursiva: selecciona els empleats amb manager
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT id, name, manager_id
FROM employee_hierarchy;

Exemples pràctics

Exemple 1: Filtrant dades amb una CTE

WITH high_salary_employees AS (
    SELECT id, name, salary
    FROM employees
    WHERE salary > 70000
)
SELECT id, name, salary
FROM high_salary_employees;

Exemple 2: Utilitzant una CTE recursiva per calcular una jerarquia

WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN org_chart o ON e.manager_id = o.id
)
SELECT id, name, manager_id
FROM org_chart;

Exercicis pràctics

Exercici 1: Filtrant dades amb una CTE

Enunciat: Crea una CTE que seleccioni tots els empleats del departament de 'Enginyeria' amb un salari superior a 60,000 i després consulta aquesta CTE per obtenir els resultats.

Solució:

WITH engineering_employees AS (
    SELECT id, name, salary
    FROM employees
    WHERE department = 'Enginyeria' AND salary > 60000
)
SELECT id, name, salary
FROM engineering_employees;

Exercici 2: Utilitzant una CTE recursiva per calcular una jerarquia

Enunciat: Crea una CTE recursiva que generi una llista jeràrquica de tots els empleats i els seus managers, començant pels empleats sense manager.

Solució:

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT id, name, manager_id
FROM employee_hierarchy;

Conclusió

Les Expressions de Taula Comunes (CTEs) són una eina poderosa per simplificar i organitzar consultes SQL complexes. Les CTEs recursives, en particular, són molt útils per treballar amb dades jeràrquiques. Amb la pràctica, les CTEs poden millorar significativament la llegibilitat i mantenibilitat del vostre codi SQL.

Expressions de taula comunes (CTEs)

Continguts

Què és una CTE?

Sintaxi bàsica

Exemple bàsic

CTEs recursives

Sintaxi de CTE recursiva

Exemple recursiu

Exemples pràctics

Exemple 1: Filtrant dades amb una CTE

Exemple 2: Utilitzant una CTE recursiva per calcular una jerarquia

Exercicis pràctics

Exercici 1: Filtrant dades amb una CTE

Exercici 2: Utilitzant una CTE recursiva per calcular una jerarquia

Conclusió

Curs de PostgreSQL

Mòdul 1: Introducció a PostgreSQL

Mòdul 2: Operacions bàsiques de SQL

Mòdul 3: Consultes SQL avançades

Mòdul 4: Disseny de bases de dades i normalització

Mòdul 5: Funcionalitats avançades de PostgreSQL

Mòdul 6: Optimització i millora del rendiment

Mòdul 7: Seguretat i gestió d'usuaris

Mòdul 8: Treballant amb JSON i funcionalitats NoSQL

Mòdul 9: Extensions i eines avançades

Mòdul 10: Estudis de cas i aplicacions del món real