# Common Table Expressions (CTE)

Les CTE sont de simples requêtes auxiliaires exécutées avant la requête principale et déclarées par une clause WITH. Elles peuvent permettre de simplifier des longues requêtes complexes en plusieurs morceaux et les rendre plus facilement réutilisables.
Les CTE peuvent:

• récupérer des données avec une requête SELECT, il faudra alors effectuer un SELECT * FROM nom_de_la_cte pour les réutiliser ailleurs
• effectuer des opérations d'écriture (INSERT, UPDATE, DELETE), on parle alors de "data modifying CTE"
• déclarer des valeurs comme s'il s'agissait d'une table temporaire en utilisant directement VALUES

Voici un exemple complet dans lequel aucune table n'est utilisée, toutes les données sont générées dans des CTE. L'objectif est d'avoir la liste des prochains jour et indiquer s'ils sont disponibles ou déjà réservés à partir d'une liste de réservations prédéfinie:

WITH RECURSIVE dates(d) AS (
        VALUES (CURRENT_DATE)
        UNION ALL
        SELECT (d + INTERVAL '1' DAY)::date
        FROM dates
        WHERE d < CURRENT_DATE + INTERVAL '7' DAY
    )
    , reservations(date_reservation) AS (
        VALUES
            (CURRENT_DATE - INTERVAL '3' DAY),
            (CURRENT_DATE + INTERVAL '1' DAY),
            (CURRENT_DATE + INTERVAL '4' DAY),
            (CURRENT_DATE + INTERVAL '5' DAY),
            (CURRENT_DATE + INTERVAL '10' DAY)
    )
    , reservations_semaine AS (
        SELECT *
        FROM reservations
        WHERE date_reservation BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '7' DAY
    )
SELECT
    d.d AS date
    , CASE WHEN rs.date_reservation IS NULL THEN 'Disponible' ELSE 'Réservé' END AS statut
FROM dates d
    LEFT JOIN reservations_semaine rs ON d.d = rs.date_reservation
ORDER BY d.d;

La clause WITH permet de déclarer 3 CTE avant le SELECT final qui forme la requête principale.

La 1e CTE se nomme dates et a pour but de lister les jours de J à J+8, elle renvoit donc des données générées. Elle part d'une base qui est la date du jour et va y ajouter un SELECT sur elle-même en ajoutant une intervalle d'un jour à chaque fois. Cette CTE est donc récursive, d'où l'utilisation du mot-clé RECURSIVE juste après WITH.

La 2e CTE reservations est simplement la liste des réservations déjà existantes. Elle n'est là que pour la démonstration car il devrait s'agir d'une table.

La 3e CTE reservations_semaine effectue un simple filtre sur les données retournées par reservations. Elle n'est également présente que pour démontrer que les CTE peuvent se référencer entre elles mais sa clause WHERE n'est pas vraiment utile compte tenu de la jointure effectuée dans la requête finale.

La requête principale est la seule dont on obtient concrètement les résultats. Elle lit depuis les CTE comme depuis des tables, en utilisant une jointure externe et un CASE pour déterminer les jours disponibles ou réservés sur la semaine à venir.

Les CTE permettent de faciliter la lecture de vos requêtes les plus complexes mais elles peuvent également améliorer les performances de traitement de données externes dans certains cas, par exemple pour effectuer plusieurs opérations d'écriture en un seul appel au SGBD.