> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8c05c8a2.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation de référence sur la clause WITH

# Clause WITH

ClickHouse prend en charge les expressions de table communes ([CTE](https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL)), les expressions scalaires communes et les requêtes récursives.

<div id="common-table-expressions">
  ## Expressions de table communes
</div>

Les expressions de table communes représentent des sous-requêtes nommées.
Elles peuvent être référencées par leur nom partout dans une requête `SELECT` où une expression de table est autorisée.
Les sous-requêtes nommées peuvent être référencées par leur nom dans la portée de la requête courante ou dans les portées des sous-requêtes enfants.

Chaque référence à une expression de table commune dans une requête `SELECT` est toujours remplacée par la sous-requête de sa définition si la CTE n'est pas explicitement définie comme matérialisée (voir [Expressions de table communes matérialisées](#materialized-common-table-expressions)).
La récursivité est évitée en masquant la CTE courante du processus de résolution des identifiants.

Veuillez noter que les CTE ne garantissent pas les mêmes résultats partout où elles sont appelées, car la requête est réexécutée à chaque utilisation.

<div id="common-table-expressions-syntax">
  ### Syntaxe
</div>

```sql theme={null}
WITH <identifier> AS [MATERIALIZED] <subquery expression>
```

<div id="common-table-expressions-example">
  ### Exemple
</div>

Voici un exemple de cas où une sous-requête est réexécutée :

```sql theme={null}
WITH cte_numbers AS
(
    SELECT
        num
    FROM generateRandom('num UInt64', NULL)
    LIMIT 1000000
)
SELECT
    count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers)
```

Si les CTE transmettaient exactement les résultats, et pas seulement un morceau de code, vous verriez toujours `1000000`

Cependant, comme nous faisons référence deux fois à `cte_numbers`, des nombres aléatoires sont générés à chaque fois et nous obtenons donc des résultats aléatoires différents : `280501, 392454, 261636, 196227`, etc.

<div id="materialized-common-table-expressions">
  ## Expressions de table communes matérialisées
</div>

Par défaut, ClickHouse intègre la sous-requête d'une CTE à chaque référence, et la réexécute donc à chaque fois.
L'ajout du mot-clé `MATERIALIZED` indique à ClickHouse d'exécuter la sous-requête de la CTE **exactement une fois**, de stocker les résultats dans une table temporaire, puis d'utiliser cette table pour toutes les références.
Cela est particulièrement utile lorsque la même CTE est référencée plusieurs fois dans une requête (par exemple, dans des auto-jointures ou plusieurs sous-requêtes `IN`), car le calcul sous-jacent n'est effectué qu'une seule fois.

<Note>
  Les CTE matérialisées sont une fonctionnalité **expérimentale**.
  Elles nécessitent que l'[analyseur](/fr/guides/clickhouse/performance-and-monitoring/analyzer) et le paramètre `enable_materialized_cte` soient activés.
</Note>

<div id="common-table-expressions-syntax">
  ### Syntaxe
</div>

```sql theme={null}
WITH <identifier> AS MATERIALIZED (<subquery>)
SELECT ...
```

<div id="materialized-cte-when-to-use">
  ### Quand utiliser
</div>

Les CTE matérialisées sont particulièrement utiles dans les cas suivants :

* La même CTE est référencée **plus d'une fois** dans une requête.
  Sans `MATERIALIZED`, chaque référence réexécute la sous-requête de manière indépendante.
* La CTE contient des fonctions **non déterministes** comme `generateRandom`.
  La matérialisation garantit que toutes les références voient les mêmes données.
* La CTE implique des **calculs coûteux** (agrégations, jointures, lecture de grands volumes de données) qu'il ne faut pas répéter.

<Tip>
  Si une CTE matérialisée n'est référencée qu'une seule fois, ClickHouse la réintègre automatiquement sous forme de sous-requête classique afin d'éviter tout surcoût inutile.
</Tip>

<div id="materialized-common-table-expressions-examples">
  ### Exemples
</div>

**Exemple 1 :** Auto-jointure sur une CTE matérialisée

Sans `MATERIALIZED`, les deux côtés de la jointure exécuteraient la sous-requête indépendamment.
Avec `MATERIALIZED`, la table n’est parcourue qu’une seule fois et les deux côtés de la jointure lisent dans la même table temporaire.

```sql theme={null}
SET enable_materialized_cte = 1;

CREATE TABLE users (uid Int16, name String, age Int16) ENGINE = Memory;
INSERT INTO users VALUES (1231, 'John', 33), (6666, 'Ksenia', 48), (8888, 'Alice', 50);

WITH
    a AS MATERIALIZED (SELECT * FROM users WHERE name = 'Alice')
SELECT count() FROM a AS l JOIN a AS r ON l.uid = r.uid;
```

```response theme={null}
┌─count()─┐
│       1 │
└─────────┘
```

**Exemple 2 :** Résultats déterministes avec des fonctions non déterministes

Les CTE classiques avec `generateRandom` produisent des résultats différents à chaque référence.
La matérialisation de la CTE garantit la cohérence :

```sql theme={null}
SET enable_materialized_cte = 1;

WITH cte_numbers AS MATERIALIZED
(
    SELECT num
    FROM generateRandom('num UInt64', NULL)
    LIMIT 1000000
)
SELECT count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers);
```

Comme les deux références s’appuient sur les mêmes données matérialisées, le résultat est toujours `1000000`.

**Exemple 3 :** Chaînage de CTE matérialisées

Les CTE matérialisées peuvent faire référence à d’autres CTE matérialisées.
ClickHouse résout les dépendances et les matérialise dans le bon ordre :

```sql theme={null}
SET enable_materialized_cte = 1;

WITH
    a AS MATERIALIZED (SELECT uid, name FROM users),
    b AS MATERIALIZED (SELECT uid FROM a)
SELECT count() FROM b AS l LEFT SEMI JOIN b AS r ON l.uid = r.uid;
```

```response theme={null}
┌─count()─┐
│       3 │
└─────────┘
```

L’ordre des définitions de CTE n’a pas d’importance — il est possible de faire référence à des CTE définies plus loin :

```sql theme={null}
SET enable_materialized_cte = 1;

WITH
    b AS MATERIALIZED (SELECT uid FROM a),
    a AS MATERIALIZED (SELECT uid FROM users)
SELECT count() FROM b AS l LEFT SEMI JOIN b AS r ON l.uid = r.uid;
```

```response theme={null}
┌─count()─┐
│       3 │
└─────────┘
```

<div id="materialized-cte-restrictions">
  ### Restrictions
</div>

* **Paramètre expérimental requis** : le paramètre `enable_materialized_cte` doit être activé.
* **Analyseur requis** : les CTE matérialisées fonctionnent uniquement lorsque l’[analyseur](/fr/guides/clickhouse/performance-and-monitoring/analyzer) est activé (`enable_analyzer = 1`).
* **Non pris en charge avec `RECURSIVE`** : la combinaison des mots-clés `MATERIALIZED` et `RECURSIVE` n’est pas autorisée et entraîne une exception `UNSUPPORTED_METHOD`.
* **Les CTE corrélées sont interdites** : une CTE matérialisée ne peut pas référencer des colonnes provenant de portées de requête englobantes.

<div id="common-scalar-expressions">
  ## Expressions scalaires communes
</div>

ClickHouse vous permet de déclarer des alias pour des expressions scalaires arbitraires dans la clause `WITH`.
Les expressions scalaires communes peuvent être référencées n'importe où dans la requête.

<Note>
  Si une expression scalaire commune fait référence à autre chose qu'un littéral constant, elle peut entraîner la présence de [variables libres](https://en.wikipedia.org/wiki/Free_variables_and_bound_variables).
  ClickHouse résout chaque identifiant dans la portée la plus proche possible, ce qui signifie que des variables libres peuvent faire référence à des entités inattendues en cas de conflit de noms, ou conduire à une sous-requête corrélée.
  Il est recommandé de définir une CSE sous forme de [fonction lambda](/fr/reference/functions/regular-functions/overview#arrow-operator-and-lambda) (possible uniquement lorsque l'[analyseur](/fr/guides/clickhouse/performance-and-monitoring/analyzer) est activé), en liant tous les identifiants utilisés afin d'obtenir un comportement plus prévisible lors de la résolution des identifiants d'expression.
</Note>

<div id="common-table-expressions-syntax">
  ### Syntaxe
</div>

```sql theme={null}
WITH <expression> AS <identifier>
```

<div id="materialized-common-table-expressions-examples">
  ### Exemples
</div>

**Exemple 1 :** Utiliser une expression constante comme "variable"

```sql theme={null}
WITH '2019-08-01 15:23:00' AS ts_upper_bound
SELECT *
FROM hits
WHERE
    EventDate = toDate(ts_upper_bound) AND
    EventTime <= ts_upper_bound;
```

**Exemple 2 :** Utiliser des fonctions d’ordre supérieur pour lier les identifiants

```sql theme={null}
WITH
    '.txt' as extension,
    (id, extension) -> concat(lower(id), extension) AS gen_name
SELECT gen_name('test', '.sql') as file_name;
```

```response theme={null}
   ┌─file_name─┐
1. │ test.sql  │
   └───────────┘
```

**Exemple 3 :** Utilisation de fonctions d’ordre supérieur avec des variables libres

Les requêtes d’exemple suivantes montrent que les identifiants non liés sont résolus vers une entité dans la portée la plus proche.
Ici, `extension` n’est pas liée dans le corps de la fonction lambda `gen_name`.
Bien que `extension` soit définie comme `'.txt'` en tant qu’expression scalaire commune dans la portée de définition et d’utilisation de `generated_names`, elle est résolue comme une colonne de la table `extension_list`, car elle est disponible dans la sous-requête `generated_names`.

```sql theme={null}
CREATE TABLE extension_list
(
    extension String
)
ORDER BY extension
AS SELECT '.sql';

WITH
    '.txt' as extension,
    generated_names as (
        WITH
            (id) -> concat(lower(id), extension) AS gen_name
        SELECT gen_name('test') as file_name FROM extension_list
    )
SELECT file_name FROM generated_names;
```

```response theme={null}
   ┌─file_name─┐
1. │ test.sql  │
   └───────────┘
```

**Exemple 4 :** Retrait du résultat de l’expression sum(bytes) de la liste des colonnes de la clause SELECT

```sql theme={null}
WITH sum(bytes) AS s
SELECT
    formatReadableSize(s),
    table
FROM system.parts
GROUP BY table
ORDER BY s;
```

**Exemple 5 :** Utilisation du résultat d'une sous-requête scalaire

```sql theme={null}
/* this example would return TOP 10 of most huge tables */
WITH
    (
        SELECT sum(bytes)
        FROM system.parts
        WHERE active
    ) AS total_disk_usage
SELECT
    (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
    table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;
```

**Exemple 6 :** Réutilisation d’une expression dans une sous-requête

```sql theme={null}
WITH test1 AS (SELECT i + 1, j + 1 FROM test1)
SELECT * FROM test1;
```

<div id="recursive-queries">
  ## Requêtes récursives
</div>

Le modificateur `RECURSIVE`, facultatif, permet à une requête `WITH` de faire référence à son propre résultat. Exemple :

**Exemple :** Additionner les entiers de 1 à 100

```sql theme={null}
WITH RECURSIVE test_table AS (
    SELECT 1 AS number
UNION ALL
    SELECT number + 1 FROM test_table WHERE number < 100
)
SELECT sum(number) FROM test_table;
```

```text theme={null}
┌─sum(number)─┐
│        5050 │
└─────────────┘
```

<Note>
  Les CTE récursifs s’appuient sur l’[analyseur de requêtes](/fr/guides/clickhouse/performance-and-monitoring/analyzer), introduit dans la version **`24.3`**. Si vous utilisez la version **`24.3+`** et rencontrez une exception **`(UNKNOWN_TABLE)`** ou **`(UNSUPPORTED_METHOD)`**, cela indique que l’analyseur est désactivé sur votre instance, votre rôle ou votre profil. Pour activer l’analyseur, activez le paramètre **`allow_experimental_analyzer`** ou mettez à jour le paramètre **`compatibility`** vers une version plus récente.
  À partir de la version `24.8`, l’analyseur est pleinement passé en production, et le paramètre `allow_experimental_analyzer` a été renommé en `enable_analyzer`.
</Note>

La forme générale d’une requête récursive `WITH` est toujours la suivante : un terme non récursif, puis `UNION ALL`, puis un terme récursif, seul ce dernier pouvant contenir une référence à la propre sortie de la requête. Une requête CTE récursive s’exécute comme suit :

1. Évaluez le terme non récursif. Placez le résultat de cette requête dans une table de travail temporaire.
2. Tant que la table de travail n’est pas vide, répétez ces étapes :
   1. Évaluez le terme récursif en remplaçant l’auto-référence récursive par le contenu actuel de la table de travail. Placez le résultat de cette requête dans une table intermédiaire temporaire.
   2. Remplacez le contenu de la table de travail par celui de la table intermédiaire, puis videz la table intermédiaire.

Les requêtes récursives sont généralement utilisées pour manipuler des données hiérarchiques ou structurées en arbre. Par exemple, nous pouvons écrire une requête qui effectue un parcours d’arbre :

**Exemple :** Parcours d’arbre

Commençons par créer la table de l’arbre :

```sql theme={null}
DROP TABLE IF EXISTS tree;
CREATE TABLE tree
(
    id UInt64,
    parent_id Nullable(UInt64),
    data String
) ENGINE = MergeTree ORDER BY id;

INSERT INTO tree VALUES (0, NULL, 'ROOT'), (1, 0, 'Child_1'), (2, 0, 'Child_2'), (3, 1, 'Child_1_1');
```

Nous pouvons parcourir ces arbres à l’aide de la requête suivante :

**Exemple :** Parcours d’arbre

```sql theme={null}
WITH RECURSIVE search_tree AS (
    SELECT id, parent_id, data
    FROM tree t
    WHERE t.id = 0
UNION ALL
    SELECT t.id, t.parent_id, t.data
    FROM tree t, search_tree st
    WHERE t.parent_id = st.id
)
SELECT * FROM search_tree;
```

```text theme={null}
┌─id─┬─parent_id─┬─data──────┐
│  0 │      ᴺᵁᴸᴸ │ ROOT      │
│  1 │         0 │ Child_1   │
│  2 │         0 │ Child_2   │
│  3 │         1 │ Child_1_1 │
└────┴───────────┴───────────┘
```

<div id="search-order">
  ### Ordre de parcours
</div>

Pour établir un parcours en profondeur, nous calculons pour chaque ligne de résultat un tableau des lignes déjà visitées :

**Exemple :** Parcours d’arbre en profondeur

```sql theme={null}
WITH RECURSIVE search_tree AS (
    SELECT id, parent_id, data, [t.id] AS path
    FROM tree t
    WHERE t.id = 0
UNION ALL
    SELECT t.id, t.parent_id, t.data, arrayConcat(path, [t.id])
    FROM tree t, search_tree st
    WHERE t.parent_id = st.id
)
SELECT * FROM search_tree ORDER BY path;
```

```text theme={null}
┌─id─┬─parent_id─┬─data──────┬─path────┐
│  0 │      ᴺᵁᴸᴸ │ ROOT      │ [0]     │
│  1 │         0 │ Child_1   │ [0,1]   │
│  3 │         1 │ Child_1_1 │ [0,1,3] │
│  2 │         0 │ Child_2   │ [0,2]   │
└────┴───────────┴───────────┴─────────┘
```

Pour obtenir un parcours en largeur, l’approche standard consiste à ajouter une colonne qui indique la profondeur de la recherche :

**Exemple :** parcours d’arbre en largeur

```sql theme={null}
WITH RECURSIVE search_tree AS (
    SELECT id, parent_id, data, [t.id] AS path, toUInt64(0) AS depth
    FROM tree t
    WHERE t.id = 0
UNION ALL
    SELECT t.id, t.parent_id, t.data, arrayConcat(path, [t.id]), depth + 1
    FROM tree t, search_tree st
    WHERE t.parent_id = st.id
)
SELECT * FROM search_tree ORDER BY depth;
```

```text theme={null}
┌─id─┬─link─┬─data──────┬─path────┬─depth─┐
│  0 │ ᴺᵁᴸᴸ │ ROOT      │ [0]     │     0 │
│  1 │    0 │ Child_1   │ [0,1]   │     1 │
│  2 │    0 │ Child_2   │ [0,2]   │     1 │
│  3 │    1 │ Child_1_1 │ [0,1,3] │     2 │
└────┴──────┴───────────┴─────────┴───────┘
```

<div id="cycle-detection">
  ### Détection des cycles
</div>

Commençons par créer la table du graphe :

```sql theme={null}
DROP TABLE IF EXISTS graph;
CREATE TABLE graph
(
    from UInt64,
    to UInt64,
    label String
) ENGINE = MergeTree ORDER BY (from, to);

INSERT INTO graph VALUES (1, 2, '1 -> 2'), (1, 3, '1 -> 3'), (2, 3, '2 -> 3'), (1, 4, '1 -> 4'), (4, 5, '4 -> 5');
```

Nous pouvons parcourir ce graphe avec la requête suivante :

**Exemple :** Parcours du graphe sans détection de cycles

```sql theme={null}
WITH RECURSIVE search_graph AS (
    SELECT from, to, label FROM graph g
    UNION ALL
    SELECT g.from, g.to, g.label
    FROM graph g, search_graph sg
    WHERE g.from = sg.to
)
SELECT DISTINCT * FROM search_graph ORDER BY from;
```

```text theme={null}
┌─from─┬─to─┬─label──┐
│    1 │  4 │ 1 -> 4 │
│    1 │  2 │ 1 -> 2 │
│    1 │  3 │ 1 -> 3 │
│    2 │  3 │ 2 -> 3 │
│    4 │  5 │ 4 -> 5 │
└──────┴────┴────────┘
```

Mais si l’on ajoute un cycle dans ce graphe, la requête précédente échouera avec l’erreur `Maximum recursive CTE evaluation depth` :

```sql theme={null}
INSERT INTO graph VALUES (5, 1, '5 -> 1');

WITH RECURSIVE search_graph AS (
    SELECT from, to, label FROM graph g
UNION ALL
    SELECT g.from, g.to, g.label
    FROM graph g, search_graph sg
    WHERE g.from = sg.to
)
SELECT DISTINCT * FROM search_graph ORDER BY from;
```

```text theme={null}
Code: 306. DB::Exception: Received from localhost:9000. DB::Exception: Maximum recursive CTE evaluation depth (1000) exceeded, during evaluation of search_graph AS (SELECT from, to, label FROM graph AS g UNION ALL SELECT g.from, g.to, g.label FROM graph AS g, search_graph AS sg WHERE g.from = sg.to). Consider raising max_recursive_cte_evaluation_depth setting.: While executing RecursiveCTESource. (TOO_DEEP_RECURSION)
```

La méthode standard pour gérer les cycles consiste à calculer un tableau contenant les nœuds déjà visités :

**Exemple :** parcours du graphe avec détection de cycles

```sql theme={null}
WITH RECURSIVE search_graph AS (
    SELECT from, to, label, false AS is_cycle, [tuple(g.from, g.to)] AS path FROM graph g
UNION ALL
    SELECT g.from, g.to, g.label, has(path, tuple(g.from, g.to)), arrayConcat(sg.path, [tuple(g.from, g.to)])
    FROM graph g, search_graph sg
    WHERE g.from = sg.to AND NOT is_cycle
)
SELECT * FROM search_graph WHERE is_cycle ORDER BY from;
```

```text theme={null}
┌─from─┬─to─┬─label──┬─is_cycle─┬─path──────────────────────┐
│    1 │  4 │ 1 -> 4 │ true     │ [(1,4),(4,5),(5,1),(1,4)] │
│    4 │  5 │ 4 -> 5 │ true     │ [(4,5),(5,1),(1,4),(4,5)] │
│    5 │  1 │ 5 -> 1 │ true     │ [(5,1),(1,4),(4,5),(5,1)] │
└──────┴────┴────────┴──────────┴───────────────────────────┘
```

<div id="infinite-queries">
  ### Requêtes infinies
</div>

Il est également possible d’utiliser des requêtes CTE récursives infinies si `LIMIT` est utilisé dans la requête externe :

**Exemple :** Requête CTE récursive infinie

```sql theme={null}
WITH RECURSIVE test_table AS (
    SELECT 1 AS number
UNION ALL
    SELECT number + 1 FROM test_table
)
SELECT sum(number) FROM (SELECT number FROM test_table LIMIT 100);
```

```text theme={null}
┌─sum(number)─┐
│        5050 │
└─────────────┘
```

<div id="trailing-comma">
  ## Virgule finale
</div>

Une virgule est autorisée après le dernier élément de la clause `WITH` :

```sql theme={null}
WITH
    (SELECT sum(number) FROM numbers(10)) AS total,
    total * 2 AS doubled,
SELECT total, doubled;
```