Documentation PostgreSQL 14.22 » Langage SQL » Requêtes » Expressions de table

Précédent | Niveau supérieur | Suivant
7.1. Aperçu | Sommaire | 7.3. Listes de sélection

## 7.2. Expressions de table

Une expression de table calcule une table. L'expression de table contient une clause `FROM` qui peut être suivie des clauses `WHERE`, `GROUP BY` et `HAVING`. Les expressions triviales de table font simplement référence à une table sur le disque, une table de base, mais des expressions plus complexes peuvent être utilisées pour modifier ou combiner des tables de base de différentes façons.

Les clauses optionnelles `WHERE`, `GROUP BY` et `HAVING` dans l'expression de table spécifient un tube de transformations successives réalisées sur la table dérivée de la clause `FROM`. Toutes ces transformations produisent une table virtuelle fournissant les lignes à passer à la liste de sélection qui choisira les lignes à afficher de la requête.

### 7.2.1. Clause `FROM`

La clause `FROM` dérive une table à partir d'une ou plusieurs tables données dans une liste de référence dont les tables sont séparées par des virgules.

    FROM reference_table [, reference_table [, ...]]

Une référence de table pourrait être un nom de table (avec en option le nom du schéma) ou de table dérivée, telle qu'une sous-requête, une construction `JOIN` ou une combinaison complexe de ces possibilités. Si plus d'une référence de table est listée dans la clause `FROM`, les tables sont jointes en croisé (autrement dit, cela réalise un produit cartésien de leurs lignes ; voir ci-dessous). Le résultat de la liste `FROM` est une table virtuelle intermédiaire pouvant être sujette aux transformations des clauses `WHERE`, `GROUP BY` et `HAVING`, et est finalement le résultat des expressions de table.

Lorsqu'une référence de table nomme une table qui est la table parent d'une table suivant la hiérarchie de l'héritage, la référence de table produit les lignes non seulement de la table, mais aussi des descendants de cette table, sauf si le mot-clé `ONLY` précède le nom de la table. Néanmoins, la référence produit seulement les colonnes qui apparaissent dans la table nommée... Toute colonne ajoutée dans une sous-table est ignorée.

Au lieu d'écrire `ONLY` avant le nom de la table, vous pouvez écrire `*` après le nom de la table pour indiquer spécifiquement que les tables filles sont incluses. Il n'y a plus de vraie raison pour encore utiliser cette syntaxe, car chercher dans les tables descendantes est maintenant le comportement par défaut. C'est toutefois supporté pour compatibilité avec des versions plus anciennes.

#### 7.2.1.1. Tables jointes

Une table jointe est une table dérivée de deux autres tables (réelles ou dérivées) suivant les règles du type de jointure particulier. Les jointures internes (inner), externes (outer) et croisées (cross) sont disponibles. La syntaxe générale d'une table jointe est :

    T1 type_jointure T2 [ condition_jointure ]

Des jointures de tous types peuvent être chaînées ensemble ou imbriquées : une des deux tables ou les deux tables peuvent être des tables jointes. Des parenthèses peuvent être utilisées autour des clauses `JOIN` pour contrôler l'ordre de jointure. Dans l'absence des parenthèses, les clauses `JOIN` s'imbriquent de gauche à droite.

Types de jointures

Jointure croisée (cross join)

      T1 CROSS JOIN T2

  Pour chaque combinaison possible de lignes provenant de `T1` et `T2` (c'est-à-dire un produit cartésien), la table jointe contiendra une ligne disposant de toutes les colonnes de `T1` suivies par toutes les colonnes de `T2`. Si les tables ont respectivement N et M lignes, la table jointe en aura N * M.

  `FROM `T1` CROSS JOIN `T2`` est équivalent à `FROM `T1` INNER JOIN `T2` ON TRUE` (voir ci-dessous). C'est aussi équivalent à : `FROM `T1`, `T2``.

  ### Note

  Cette dernière équivalence ne convient pas exactement quand plusieurs tables apparaissent, car `JOIN` lie de façon plus profonde que la virgule. Par exemple, `FROM `T1` CROSS JOIN `T2` INNER JOIN `T3` ON `condition`` n'est pas identique à `FROM `T1`, `T2` INNER JOIN `T3` ON `condition``, car `condition` peut faire référence à `T1` dans le premier cas, mais pas dans le second.

Jointures qualifiées (qualified joins)

      T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON expression_booleenne
      T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( liste des colonnes jointes )
      T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2

  Les mots `INNER` et `OUTER` sont optionnels dans toutes les formes. `INNER` est la valeur par défaut ; `LEFT`, `RIGHT` et `FULL` impliquent une jointure externe.

  La condition de la jointure est spécifiée dans la clause `ON` ou `USING`, ou implicitement par le mot `NATURAL`. La condition de jointure détermine les lignes des deux tables sources considérées comme « correspondante », comme l'explique le paragraphe ci-dessous.

  Les types possibles de jointures qualifiées sont :

  `INNER JOIN`

    Pour chaque ligne R1 de T1, la table jointe a une ligne pour chaque ligne de T2 satisfaisant la condition de jointure avec R1.

  `LEFT OUTER JOIN`

    Tout d'abord, une jointure interne est réalisée. Puis, pour chaque ligne de T1 qui ne satisfait pas la condition de jointure avec les lignes de T2, une ligne jointe est ajoutée avec des valeurs NULL dans les colonnes de T2. Du coup, la table jointe a toujours au moins une ligne pour chaque ligne de T1, quelles que soient les conditions.

  `RIGHT OUTER JOIN`

    Tout d'abord, une jointure interne est réalisée. Puis, pour chaque ligne de T2 qui ne satisfait pas la condition de jointure avec les lignes de T1, une ligne jointe est ajoutée avec des valeurs NULL dans les colonnes de T1. C'est l'inverse d'une jointure gauche : la table résultante aura toujours une ligne pour chaque ligne de T2, quelles que soient les conditions.

  `FULL OUTER JOIN`

    Tout d'abord, une jointure interne est réalisée. Puis, pour chaque ligne de T1 qui ne satisfait pas la condition de jointure avec les lignes de T2, une ligne jointe est ajoutée avec des valeurs NULL dans les colonnes de T2. De plus, pour chaque ligne de T2 qui ne satisfait pas la condition de jointure avec les lignes de T1, une ligne jointe est ajoutée avec des valeurs NULL dans les colonnes de T1.

  La clause `ON` est le type de condition de jointure le plus utilisé : elle prend une valeur booléenne du même type que celle utilisée dans une clause `WHERE`. Une paire de lignes provenant de `T1` et de `T2` correspondent si l'expression de la clause `ON` vaut true.

  La clause `USING` est un raccourci qui vous permet de prendre avantage d'une situation spécifique où les deux côtés de la jointure utilisent le même nom pour la colonne jointe. Elle prend une liste de noms de colonnes partagées, en séparant les noms par des virgules et forme une condition de jointure qui inclut une comparaison d'égalité entre chaque. Par exemple, joindre `T1` et `T2` avec `USING (a, b)` produit la même condition de jointure que la condition `ON `T1`.a = `T2`.a AND `T1`.b = `T2`.b`.

  De plus, la sortie de `JOIN USING` supprime les colonnes redondantes : il n'est pas nécessaire d'imprimer les colonnes de correspondance, puisqu'elles doivent avoir des valeurs identiques. Alors que `JOIN ON` produit toutes les colonnes de `T2`, `JOIN USING` produit une seule colonne pour chaque paire de colonnes listées (dans l'ordre listé), suivi par chaque colonne restante provenant de `T1`, suivi par chaque colonne restante provenant de `T2`.

  Enfin, `NATURAL` est un raccourci de `USING` : il forme une liste `USING` constituée de tous les noms de colonnes apparaissant dans les deux tables en entrée. Comme avec `USING`, ces colonnes apparaissent une fois seulement dans la table en sortie. S'il n'existe aucun nom commun de colonne, `NATURAL JOIN` se comporte comme `JOIN ... ON TRUE` et produit une jointure croisée.

  ### Note

  `USING` est raisonnablement protégé contre les changements de colonnes dans les relations jointes, car seuls les noms de colonnes listés sont combinés. `NATURAL` est considéré comme plus risqué, car toute modification de schéma causant l'apparition d'un nouveau nom de colonne correspondant fera en sorte de joindre la nouvelle colonne.

Pour rassembler tout ceci, supposons que nous avons une table `t1` :

     no | nom
    ----+------
      1 | a
      2 | b
      3 | c

et une table `t2` :

     no | valeur
    ----+-------
      1 | xxx
      3 | yyy
      5 | zzz

Nous obtenons les résultats suivants pour les différentes jointures :

    => SELECT * FROM t1 CROSS JOIN t2;
     no | nom | no | valeur
    ----+-----+----+-------
      1 | a   |  1 | xxx
      1 | a   |  3 | yyy
      1 | a   |  5 | zzz
      2 | b   |  1 | xxx
      2 | b   |  3 | yyy
      2 | b   |  5 | zzz
      3 | c   |  1 | xxx
      3 | c   |  3 | yyy
      3 | c   |  5 | zzz
    (9 rows)

    => SELECT * FROM t1 INNER JOIN t2 ON t1.no = t2.no;
     no | nom | no | valeur
    ----+-----+----+-------
      1 | a   |  1 | xxx
      3 | c   |  3 | yyy
    (2 rows)

    => SELECT * FROM t1 INNER JOIN t2 USING (no);
     no | nom | valeur
    ----+-----+-------
      1 | a   | xxx
      3 | c   | yyy
    (2 rows)

    => SELECT * FROM t1 NATURAL INNER JOIN t2;
     no | nom | valeur
    ----+-----+-------
      1 | a   | xxx
      3 | c   | yyy
    (2 rows)

    => SELECT * FROM t1 LEFT JOIN t2 ON t1.no = t2.no;
     no | nom | no | valeur
    ----+-----+----+-------
      1 | a   |  1 | xxx
      2 | b   |    |
      3 | c   |  3 | yyy
    (3 rows)

    => SELECT * FROM t1 LEFT JOIN t2 USING (no);
     no | nom | valeur
    ----+-----+-------
      1 | a   | xxx
      2 | b   |
      3 | c   | yyy
    (3 rows)

    => SELECT * FROM t1 RIGHT JOIN t2 ON t1.no = t2.no;
     no | nom | no | valeur
    ----+-----+----+-------
      1 | a   |  1 | xxx
      3 | c   |  3 | yyy
        |     |  5 | zzz
    (3 rows)

    => SELECT * FROM t1 FULL JOIN t2 ON t1.no = t2.no;
     no | nom | no | valeur
    ----+-----+----+-------
      1 | a   |  1 | xxx
      2 | b   |    |
      3 | c   |  3 | yyy
        |     |  5 | zzz
    (4 rows)

La condition de jointure spécifiée avec `ON` peut aussi contenir des conditions sans relation directe avec la jointure. Ceci est utile pour quelques requêtes, mais son utilisation doit avoir été réfléchie. Par exemple :

    => SELECT * FROM t1 LEFT JOIN t2 ON t1.no = t2.no AND t2.valeur = 'xxx';
     no | nom | no | valeur
    ----+-----+----+-------
      1 | a   |  1 | xxx
      2 | b   |    |
      3 | c   |    |
    (3 rows)

Notez que placer la restriction dans la clause `WHERE` donne un résultat différent :

    => SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';
     num | name | num | value
    -----+------+-----+-------
       1 | a    |   1 | xxx
    (1 row)

Ceci est dû au fait qu'une restriction placée dans la clause `ON` est traitée avant la jointure, alors qu'une restriction placée dans la clause `WHERE` est traitée après la jointure. Ceci n'a pas d'importance avec les jointures internes, mais en a une grande avec les jointures externes.

#### 7.2.1.2. Alias de table et de colonne

Un nom temporaire peut être donné aux tables et aux références de tables complexes, nom qui sera ensuite utilisé pour référencer la table dérivée dans la suite de la requête. Cela s'appelle un alias de table.

Pour créer un alias de table, écrivez

    FROM reference_table AS alias

ou

    FROM reference_table alias

Le mot-clé `AS` n'est pas obligatoire. `alias` peut être tout identifiant.

Une application typique des alias de table est l'affectation d'identifiants courts pour les noms de tables longs, ce qui permet de garder des clauses de jointures lisibles. Par exemple :

    SELECT * FROM nom_de_table_tres_tres_long s
      JOIN un_autre_nom_tres_long a ON s.id = a.no;

L'alias devient le nouveau nom de la table en ce qui concerne la requête en cours -- il n'est pas autorisé de faire référence à la table par son nom original où que ce soit dans la requête. Du coup, ceci n'est pas valide :

    SELECT * FROM mon_table AS m WHERE mon_table.a > 5;    -- mauvais

Les alias de table sont disponibles principalement pour aider à l'écriture de requête, mais ils deviennent nécessaires pour joindre une table avec elle-même, par exemple :

    SELECT * FROM personnes AS mere JOIN personnes AS enfant ON mere.id = enfant.mere_id;

De plus, un alias est requis si la référence de la table est une sous-requête (voir la Section 7.2.1.3).

Les parenthèses sont utilisées pour résoudre les ambiguïtés. Dans l'exemple suivant, la première instruction affecte l'alias `b` à la deuxième instance de `ma_table`, mais la deuxième instruction affecte l'alias au résultat de la jonction :

    SELECT * FROM ma_table AS a CROSS JOIN ma_table AS b ...
    SELECT * FROM (ma_table AS a CROSS JOIN ma_table) AS b ...

Une autre forme d'alias de tables donne des noms temporaires aux colonnes de la table ainsi qu'à la table :

    FROM reference_table [AS] alias ( colonne1 [, colonne2 [, ...]] )

Si le nombre d'alias de colonnes spécifié est plus petit que le nombre de colonnes dont dispose la table réelle, les colonnes suivantes ne sont pas renommées. Cette syntaxe est particulièrement utile dans le cas de jointures avec la même table ou dans le cas de sous-requêtes.

Quand un alias est appliqué à la sortie d'une clause `JOIN`, l'alias cache le nom original référencé à l'intérieur du `JOIN`. Par exemple :

    SELECT a.* FROM ma_table AS a JOIN ta_table AS b ON ...

est du SQL valide, mais :

    SELECT a.* FROM (ma_table AS a JOIN ta_table AS b ON ...) AS c

n'est pas valide ; l'alias de table `a` n'est pas visible en dehors de l'alias `c`.

#### 7.2.1.3. Sous-requêtes

Une sous-requête spécifiant une table dérivée doit être enfermée dans des parenthèses et doit se voir affecter un alias de table (comme dans Section 7.2.1.2). Par exemple :

    FROM (SELECT * FROM table1) AS nom_alias

Cet exemple est équivalent à `FROM table1 AS nom_alias`. Des cas plus intéressants, qui ne peuvent pas être réduits à une jointure pleine, surviennent quand la sous-requête implique un groupement ou un agrégat.

Une sous-requête peut aussi être une liste `VALUES` :

    FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
         AS noms(prenom, nom)

De nouveau, un alias de table est requis. Affecter des noms d'alias aux colonnes de la liste `VALUES` est optionnel, mais c'est une bonne pratique. Pour plus d'informations, voir Section 7.7.

#### 7.2.1.4. Fonctions de table

Les fonctions de table sont des fonctions produisant un ensemble de lignes composées de types de données de base (types scalaires) ou de types de données composites (lignes de table). Elles sont utilisées comme une table, une vue ou une sous-requête de la clause `FROM` d'une requête. Les colonnes renvoyées par les fonctions de table peuvent être incluses dans une clause `SELECT`, `JOIN` ou `WHERE` de la même manière que les colonnes d'une table, vue ou sous-requête.

Les fonctions de table peuvent aussi être combinées en utilisant la syntaxe `ROWS FROM`, avec les résultats renvoyés dans des colonnes parallèles ; le nombre de lignes résultantes dans ce cas est celui du résultat de fonction le plus large. Les résultats ayant moins de colonnes sont alignés avec des valeurs NULL.

    appel_fonction [WITH ORDINALITY] [[AS] alias_table [(alias_colonne [, ... ])]]
    ROWS FROM( appel_fonction [, ... ] ) [WITH ORDINALITY] [[AS] alias_table [(alias_colonne [, ... ])]]

Si la clause `WITH ORDINALITY` est ajoutée, une colonne supplémentaire de type `bigint` sera ajoutée aux colonnes de résultat de la fonction. Cette colonne numérote les lignes de l'ensemble de résultats de la fonction, en commençant à 1. (Ceci est une généralisation de la syntaxe du standard SQL pour `UNNEST ... WITH ORDINALITY`.) Par défaut, la colonne ordinale est appelée `ordinality`, mais un nom de colonne différent peut être affecté en utilisant une clause `AS`.

La fonction de table `UNNEST` peut être appelée avec tout nombre de paramètres tableaux, et envoie un nombre correspondant de colonnes comme si la fonction `UNNEST` avait été appelée sur chaque paramètre séparément (Section 9.19) et combinée en utilisant la construction `ROWS FROM`.

    UNNEST( expression_tableau [, ... ] ) [WITH ORDINALITY] [[AS] alias_table [(alias_colonne [, ... ])]]

Si aucun `alias_table` n'est précisé, le nom de la fonction est utilisé comme nom de table ; dans le cas d'une construction `ROWS FROM()`, le nom de la première fonction est utilisé.

Si des alias de colonnes ne sont pas fournis pour une fonction renvoyant un type de données de base, alors le nom de la colonne est aussi le même que le nom de la fonction. Pour une fonction renvoyant un type composite, les colonnes résultats obtiennent les noms des attributs individuels du type.

Quelques exemples :

    CREATE TABLE truc (trucid int, trucsousid int, trucnom text);

    CREATE FUNCTION recuptruc(int) RETURNS SETOF truc AS $$
        SELECT * FROM truc WHERE trucid = $1;
    $$ LANGUAGE SQL;

    SELECT * FROM recuptruc(1) AS t1;

    SELECT * FROM truc
        WHERE trucsousid IN (
                            SELECT trucsousid
                            FROM recuptruc(truc.trucid) z
                            WHERE z.trucid = truc.trucid);

    CREATE VIEW vue_recuptruc AS SELECT * FROM recuptruc(1);
    SELECT * FROM vue_recuptruc;

Dans certains cas, il est utile de définir des fonctions de table pouvant renvoyer des ensembles de colonnes différentes suivant la façon dont elles sont appelées. Pour supporter ceci, la fonction de table est déclarée comme renvoyant le pseudotype `record` sans paramètres `OUT`. Quand une telle fonction est utilisée dans une requête, la structure de ligne attendue doit être spécifiée dans la requête elle-même, de façon à ce que le système sache comment analyser et planifier la requête. Cette syntaxe ressemble à ceci :

    appel_fonction [AS] alias (définition_colonne [, ... ])
    appel_fonction AS [alias] (définition_colonne [, ... ])
    ROWS FROM( ... appel_fonction AS (définition_colonne [, ... ]) [, ... ] )

Lorsque la syntaxe `ROWS FROM()` n'est pas utilisée, la liste `définition_colonne` remplace la liste d'alias de colonnes qui aurait été autrement attachée à la clause `FROM` ; les noms dans les définitions de colonnes servent comme alias de colonnes. Lors de l'utilisation de la syntaxe `ROWS FROM()`, une liste `définition_colonne` peut être attachée à chaque fonction membre séparément ; ou s'il existe seulement une fonction membre et pas de clause `WITH ORDINALITY`, une liste `column_definition` peut être écrite au lieu de la liste d'alias de colonnes suivant `ROWS FROM()`.

Considérez cet exemple :

    SELECT *
        FROM dblink('dbname=mabd', 'SELECT proname, prosrc FROM pg_proc')
          AS t1(proname nom, prosrc text)
        WHERE proname LIKE 'bytea%';

La fonction dblink (qui fait partie du module dblink) exécute une requête distante. Elle déclare renvoyer le type `record`, car elle pourrait être utilisée pour tout type de requête. L'ensemble de colonnes réelles doit être spécifié dans la requête appelante de façon à ce que l'analyseur sache, par exemple, comment étendre `*`.

Cet exemple utilise `ROWS FROM` :

    SELECT *
    FROM ROWS FROM
        (
            json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]')
                AS (a INTEGER, b TEXT),
            generate_series(1, 3)
        ) AS x (p, q, s)
    ORDER BY p;

      p  |  q  | s
    -----+-----+---
      40 | foo | 1
     100 | bar | 2
         |     | 3

Il joint deux fonctions en une seule cible `FROM`. `json_to_recordset()` doit renvoyer deux colonnes, la première de type `integer` et la seconde de type `text`. Le résultat de `generate_series()` est utilisé directement. La clause `ORDER BY` trie les valeurs de la colonne en tant qu'entiers.

#### 7.2.1.5. Sous-requêtes `LATERAL`

Les sous-requêtes apparaissant dans la clause `FROM` peuvent être précédées du mot-clé `LATERAL`. Ceci leur permet de référencer les colonnes fournies par les éléments précédents dans le `FROM`. (Sans `LATERAL`, chaque sous-requête est évaluée indépendamment et ne peut donc pas référencer les autres éléments de la clause `FROM`.)

Les fonctions renvoyant des ensembles et apparaissant dans le `FROM` peuvent aussi être précédées du mot-clé `LATERAL`, mais, pour les fonctions, le mot-clé est optionnel. Les arguments de la fonction peuvent contenir des références aux colonnes fournies par les éléments précédents dans le `FROM`.

Un élément `LATERAL` peut apparaître au niveau haut dans la liste `FROM` ou dans un arbre de jointures (`JOIN`). Dans ce dernier cas, cela peut aussi faire référence à tout élément qui est sur le côté gauche d'un `JOIN`, alors qu'il est positionné sur sa droite.

Quand un élément `FROM` contient des références croisées `LATERAL`, l'évaluation se fait ainsi : pour chaque ligne d'un élément `FROM` fournissant les colonnes référencées, ou pour chaque ensemble de lignes de plusieurs éléments `FROM` fournissant les colonnes, l'élément `LATERAL` est évalué en utilisant cette valeur de ligne ou cette valeur d'ensemble de lignes. Les lignes résultantes sont jointes comme d'habitude aux lignes résultant du calcul. C'est répété pour chaque ligne ou ensemble de lignes provenant de la table source.

Un exemple trivial de `LATERAL` est

    SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;

Ceci n'est pas vraiment utile, car cela revient exactement au même résultat que cette écriture plus conventionnelle :

    SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;

`LATERAL` est principalement utile lorsqu'une colonne référencée est nécessaire pour calculer la colonne à joindre. Une utilisation habituelle est de fournir une valeur d'un argument à une fonction renvoyant un ensemble de lignes. Par exemple, supposons que `vertices(polygon)` renvoie l'ensemble de sommets d'un polygone, nous pouvons identifier les sommets proches des polygones stockés dans une table avec la requête suivante :

    SELECT p1.id, p2.id, v1, v2
    FROM polygones p1, polygones p2,
         LATERAL vertices(p1.poly) v1,
         LATERAL vertices(p2.poly) v2
    WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

Cette requête pourrait aussi être écrite ainsi :

    SELECT p1.id, p2.id, v1, v2
    FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
         polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
    WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

ou dans diverses autres formulations équivalentes. (Nous l'avons déjà mentionné, le mot-clé `LATERAL` est inutile dans cet exemple, mais nous l'utilisons pour plus de clarté.)

Il est souvent particulièrement utile d'utiliser `LEFT JOIN` sur une sous-requête `LATERAL`, pour que les lignes sources apparaissent dans le résultat même si la sous-requête `LATERAL` ne produit aucune ligne pour elles. Par exemple, si `get_product_names()` renvoie les noms des produits réalisés par un manufacturier, mais que quelques manufacturiers dans notre table ne réalisent aucun produit, nous pourrions les trouver avec cette requête :

    SELECT m.name
    FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
    WHERE pname IS NULL;

### 7.2.2. Clause `WHERE`

La syntaxe de la clause `WHERE` est

    WHERE condition_recherche

où `condition_recherche` est toute expression de valeur (voir la Section 4.2) renvoyant une valeur de type `boolean`.

Après le traitement de la clause `FROM`, chaque ligne de la table virtuelle dérivée est vérifiée avec la condition de recherche. Si le résultat de la vérification est positif (true), la ligne est conservée dans la table de sortie, sinon (c'est-à-dire si le résultat est faux ou nul), la ligne est abandonnée. La condition de recherche référence typiquement au moins une colonne de la table générée dans la clause `FROM` ; ceci n'est pas requis, mais, dans le cas contraire, la clause `WHERE` n'aurait aucune utilité.

### Note

La condition de jointure d'une jointure interne peut être écrite soit dans la clause `WHERE` soit dans la clause `JOIN`. Par exemple, ces expressions de tables sont équivalentes :

    FROM a, b WHERE a.id = b.id AND b.val > 5

et :

    FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5

ou même peut-être :

    FROM a NATURAL JOIN b WHERE b.val > 5

Laquelle utiliser est plutôt une affaire de style. La syntaxe `JOIN` dans la clause `FROM` n'est probablement pas aussi portable vers les autres systèmes de gestion de bases de données SQL, même si cela fait partie du standard SQL. Pour les jointures externes, il n'y a pas d'autres choix : elles doivent être faites dans la clause `FROM`. La clause `ON` ou `USING` d'une jointure externe n'est pas équivalente à une condition `WHERE` parce qu'elle détermine l'ajout de lignes (pour les lignes qui ne correspondent pas en entrée) ainsi que pour la suppression de lignes dans le résultat final.

Voici quelques exemples de clauses `WHERE` :

    SELECT ... FROM fdt WHERE c1 > 5

    SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)

    SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)

    SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)

    SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100

    SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)

`fdt` est la table dérivée dans la clause `FROM`. Les lignes qui ne correspondent pas à la condition de recherche de la clause `WHERE` sont éliminées de la table `fdt`. Notez l'utilisation de sous-requêtes scalaires en tant qu'expressions de valeurs. Comme n'importe quelle autre requête, les sous-requêtes peuvent employer des expressions de tables complexes. Notez aussi comment `fdt` est référencée dans les sous-requêtes. Qualifier `c1` comme `fdt.c1` est seulement nécessaire si `c1` est aussi le nom d'une colonne dans la table d'entrée dérivée de la sous-requête. Mais qualifier le nom de colonne ajoute de la clarté même lorsque cela n'est pas nécessaire. Cet exemple montre comment le nom de colonne d'une requête externe est étendu dans les requêtes internes.

### 7.2.3. Clauses `GROUP BY` et `HAVING`

Après avoir passé le filtre `WHERE`, la table d'entrée dérivée peut être sujette à un regroupement en utilisant la clause `GROUP BY` et à une élimination de groupe de lignes avec la clause `HAVING`.

    SELECT liste_selection
        FROM ...
        [WHERE ...]
        GROUP BY reference_colonne_regroupement[,reference_colonne_regroupement]...

La clause `GROUP BY` est utilisée pour regrouper les lignes d'une table qui ont les mêmes valeurs dans toutes les colonnes précisées. L'ordre dans lequel ces colonnes sont indiquées importe peu. L'effet est de combiner chaque ensemble de lignes partageant des valeurs communes en un seul groupe de lignes représentant toutes les lignes du groupe. Ceci est fait pour éliminer les redondances dans la sortie et/ou pour calculer les agrégats s'appliquant à ces groupes. Par exemple :

    => SELECT * FROM test1;
     x | y
    ---+---
     a | 3
     c | 2
     b | 5
     a | 1
    (4 rows)

    => SELECT x FROM test1 GROUP BY x;
     x
    ---
     a
     b
     c
    (3 rows)

Dans la seconde requête, nous n'aurions pas pu écrire `SELECT * FROM test1 GROUP BY x` parce qu'il n'existe pas une seule valeur pour la colonne `y` pouvant être associée avec chaque autre groupe. Les colonnes de regroupement peuvent être référencées dans la liste de sélection, car elles ont une valeur constante unique par groupe.

En général, si une table est groupée, les colonnes qui ne sont pas listées dans le `GROUP BY` ne peuvent pas être référencées sauf dans les expressions d'agrégats. Voici un exemple d'expression d'agrégat :

    => SELECT x, sum(y) FROM test1 GROUP BY x;
     x | sum
    ---+-----
     a |   4
     b |   5
     c |   2
    (3 rows)

Ici, `sum` est la fonction d'agrégat qui calcule une seule valeur pour le groupe entier. La Section 9.21 propose plus d'informations sur les fonctions d'agrégats disponibles.

### Astuce

Le regroupement sans expressions d'agrégats calcule effectivement l'ensemble des valeurs distinctes d'une colonne. Ceci peut aussi se faire en utilisant la clause `DISTINCT` (voir la Section 7.3.3).

Voici un autre exemple : il calcule les ventes totales pour chaque produit (plutôt que le total des ventes sur tous les produits) :

    SELECT id_produit, p.nom, (sum(v.unite) * p.prix) AS ventes
        FROM produits p LEFT JOIN ventes v USING (id_produit)
        GROUP BY id_produit, p.nom, p.prix;

Dans cet exemple, les colonnes `id_produit`, `p.nom` et `p.prix` doivent être dans la clause `GROUP BY`, car elles sont référencées dans la liste de sélection de la requête (mais voir plus loin). La colonne `v.unite` n'a pas besoin d'être dans la liste `GROUP BY`, car elle est seulement utilisée dans l'expression de l'agrégat (`sum(...)`) représentant les ventes d'un produit. Pour chaque produit, la requête renvoie une ligne de résumé sur les ventes de ce produit.

Si la table produits est configurée de façon à ce que `id_produit` soit la clé primaire, alors il serait suffisant de grouper par la colonne `id_produit` dans l'exemple ci-dessus, car le nom et le prix seraient dépendants fonctionnellement de l'identifiant du produit, et donc il n'y aurait pas d'ambiguïté sur le nom et le prix à renvoyer pour chaque groupe d'identifiants de produits.

En SQL strict, `GROUP BY` peut seulement grouper les colonnes de la table source, mais PostgreSQL étend ceci en autorisant `GROUP BY` à grouper aussi les colonnes de la liste de sélection. Grouper par expressions de valeurs au lieu de simples noms de colonnes est aussi permis.

Si une table a été groupée en utilisant la clause `GROUP BY`, mais que seuls certains groupes sont intéressants, la clause `HAVING` peut être utilisée, comme une clause `WHERE`, pour éliminer les groupes du résultat. Voici la syntaxe :

    SELECT liste_selection FROM ... [WHERE ...] GROUP BY ... HAVING expression_booléenne

Les expressions de la clause `HAVING` peuvent référer à la fois aux expressions groupées et aux expressions non groupées (ce qui implique nécessairement une fonction d'agrégat).

Exemple :

    => SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
     x | sum
    ---+-----
     a |   4
     b |   5
    (2 rows)

    => SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';
     x | sum
    ---+-----
     a |   4
     b |   5
    (2 rows)

De nouveau, un exemple plus réaliste :

    SELECT id_produit, p.nom, (sum(v.unite) * (p.prix - p.cout)) AS profit
        FROM produits p LEFT JOIN ventes v USING (id_produit)
        WHERE v.date > CURRENT_DATE - INTERVAL '4 weeks'
        GROUP BY id_produit, p.nom, p.prix, p.cout
        HAVING sum(p.prix * s.unite) > 5000;

Dans l'exemple ci-dessus, la clause `WHERE` sélectionne les lignes par une colonne qui n'est pas groupée (l'expression est vraie seulement pour les ventes des quatre dernières semaines) alors que la clause `HAVING` restreint la sortie aux groupes dont le total des ventes dépasse 5000. Notez que les expressions d'agrégats n'ont pas besoin d'être identiques dans toutes les parties d'une requête.

Si une requête contient des appels à des fonctions d'agrégat, mais pas de clause `GROUP BY`, le regroupement a toujours lieu : le résultat est une seule ligne de regroupement (ou peut-être pas de ligne du tout si la ligne unique est ensuite éliminée par la clause `HAVING`). Ceci est vrai aussi si elle comporte une clause `HAVING`, même sans fonction d'agrégat ou `GROUP BY`.

### 7.2.4. `GROUPING SETS`, `CUBE` et `ROLLUP`

Des opérations de regroupements plus complexes que celles décrites ci-dessus sont possibles en utilisant la notion d'ensembles de regroupement. Les données sélectionnées par les clauses `FROM` et `WHERE` sont regroupées séparément pour chaque ensemble de regroupement indiqué, les agrégats calculés pour chaque ensemble de la même manière que pour la clause simple `GROUP BY`, puis le résultat est retourné. Par exemple:

    => SELECT * FROM ventes;
     produit | taille | vendus
    -------+------+-------
     Foo   | L    |  10
     Foo   | M    |  20
     Bar   | M    |  15
     Bar   | L    |  5
    (4 rows)

    => SELECT produit, taille, sum(vendus) FROM ventes GROUP BY GROUPING SETS ((produit), (taille), ());
     produit | taille | sum
    -------+------+-----
     Foo   |      |  30
     Bar   |      |  20
           | L    |  15
           | M    |  35
           |      |  50
    (5 rows)

Chaque sous-liste de `GROUPING SETS` peut indiquer 0 ou plusieurs colonnes ou expressions et est interprétée de la même manière que si elle était directement dans la clause `GROUP BY`. Un ensemble de regroupement vide signifie que toutes les lignes sont agrégées pour former un simple groupe (qui est renvoyé quand bien même aucune ligne ne serait sélectionnée), comme décrit ci-dessus dans le cas de fonctions d'agrégat sans clause `GROUP BY`.

Les références aux colonnes de regroupement ou expressions sont remplacées par des valeurs NULL dans les lignes renvoyées pour les ensembles de regroupement où ces colonnes n'apparaissent pas. Pour identifier à quel ensemble de regroupement une ligne en particulier appartient, référez-vous à Tableau 9.61.

Une notation raccourcie est fournie pour indiquer deux types classiques d'ensembles de regroupement. Une clause sous la forme

    ROLLUP ( e1, e2, e3, ... )

représente la liste indiquée d'expressions ainsi que l'ensemble des préfixes de la liste, y compris la liste vide. C'est donc équivalent à

    GROUPING SETS (
        ( e1, e2, e3, ... ),
        ...
        ( e1, e2 ),
        ( e1 ),
        ( )
    )

Cette notation est communément utilisée avec des données hiérarchiques ; par exemple, le total des salaires par département, division et sur l'ensemble de l'entreprise.

Une clause sous la forme

    CUBE ( e1, e2, ... )

représente la liste indiquée ainsi que l'ensemble des sous-ensembles possibles. De ce fait,

    CUBE ( a, b, c )

est équivalent à

    GROUPING SETS (
        ( a, b, c ),
        ( a, b    ),
        ( a,    c ),
        ( a       ),
        (    b, c ),
        (    b    ),
        (       c ),
        (         )
    )

Les éléments individuels des clauses `CUBE` ou `ROLLUP` peuvent être des expressions individuelles, ou des sous-listes d'éléments entre parenthèses. Dans ce dernier cas, les sous-listes sont traitées comme simple élément pour la génération des ensembles de regroupements individuels. Par exemple :

    CUBE ( (a, b), (c, d) )

est équivalent à

    GROUPING SETS (
        ( a, b, c, d ),
        ( a, b       ),
        (       c, d ),
        (            )
    )

et

    ROLLUP ( a, (b, c), d )

est équivalent à

    GROUPING SETS (
        ( a, b, c, d ),
        ( a, b, c    ),
        ( a          ),
        (            )
    )

Les éléments `CUBE` et `ROLLUP` peuvent être utilisés directement dans la clause `GROUP BY`, ou imbriqués à l'intérieur d'une clause `GROUPING SETS`. Si une clause `GROUPING SETS` est imbriquée dans une autre, l'effet est le même que si tous les éléments de la clause la plus imbriquée avaient été écrits directement dans la clause de niveau supérieur.

Si de multiples clauses de regroupement sont indiquées dans une simple clause `GROUP BY`, alors la liste finale des ensembles de regroupements est le produit cartésien des éléments individuels. Par exemple :

    GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))

est équivalent à

    GROUP BY GROUPING SETS (
        (a, b, c, d), (a, b, c, e),
        (a, b, d),    (a, b, e),
        (a, c, d),    (a, c, e),
        (a, d),       (a, e)
    )

Lors de la spécificiation de plusieurs éléments de regroupement, l'ensemble final des ensembles de regroupement pourrait contenir des duplicats. Par exemple :

    GROUP BY ROLLUP (a, b), ROLLUP (a, c)

est équivalent à :

    GROUP BY GROUPING SETS (
        (a, b, c),
        (a, b),
        (a, b),
        (a, c),
        (a),
        (a),
        (a, c),
        (a),
        ()
    )

Si ces duplicats ne sont pas désirables, ils peuvent être supprimés en utilisant la clause `DISTINCT` directement sur le `GROUP BY`. De ce fait :

    GROUP BY DISTINCT ROLLUP (a, b), ROLLUP (a, c)

est équivalent à :

    GROUP BY GROUPING SETS (
        (a, b, c),
        (a, b),
        (a, c),
        (a),
        ()
    )

Ceci n'est pas identique à utiliser `SELECT DISTINCT` car les lignes en sortie pourraient toujours contenir des duplicats. Si une des colonnes non groupées contient NULL, cela ne sera plus distinguable du NULL utilisé quand la même colonne est regroupée.

### Note

La syntaxe `(a, b)` est normalement reconnue dans les expressions comme un constructeur de ligne. À l'intérieur d'une clause `GROUP BY`, cette règle ne s'applique pas au premier niveau d'expressions, et `(a, b)` est reconnu comme une liste d'expressions, comme décrit ci-dessus. Si pour une quelconque raison vous avez besoin d'un constructeur de ligne dans une expression de regroupement, utilisez `ROW(a, b)`.

### 7.2.5. Traitement de fonctions Window

Si la requête contient une des fonctions Window (voir Section 3.5, Section 9.22 et Section 4.2.8), ces fonctions sont évaluées après que sont effectués les regroupements, les agrégations, les filtrages par `HAVING`. C'est-à-dire que si la requête comporte des agrégats, `GROUP BY` ou `HAVING`, alors les enregistrements vus par les fonctions Window sont les lignes regroupées à la place des enregistrements originaux provenant de `FROM`/`WHERE`.

Quand des fonctions Window multiples sont utilisées, toutes les fonctions Window ayant des clauses `PARTITION BY` et `ORDER BY` équivalentes dans leurs définitions verront à coup sûr le même ordre des données en entrées, même si `ORDER BY` ne détermine pas de façon unique un tri. Toutefois, aucune garantie n'est faite à propos de l'évaluation de fonctions ayant des spécifications de `PARTITION BY` ou `ORDER BY` différentes. (Dans ces cas, une étape de tri est généralement nécessaire entre les passes d'évaluations de fonctions Window, et le tri ne garantit pas la préservation de l'ordre des enregistrements que son `ORDER BY` estime comme identiques.)

À l'heure actuelle, les fonctions Window nécessitent toujours des données prétriées, ce qui fait que la sortie de la requête sera triée suivant l'une ou l'autre des clauses `PARTITION BY`/`ORDER BY` des fonctions Window. Il n'est toutefois pas recommandé de s'en servir. Utilisez une clause `ORDER BY` au plus haut niveau de la requête si vous voulez être sûr que vos résultats soient triés d'une certaine façon.