PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 12.18 » Langage SQL » Requêtes » Expressions de table

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 la section intitulée « 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'identifieurs 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.18) 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 la section intitulée « Clause WHERE » est

WHERE condition_recherche

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 la section intitulée « 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.20 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.59.

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)
)
     

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.21 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 syntaxiquement équivalentes seront à coup sûr évaluées en une seule passe sur les données. Par conséquent, elles verront le même ordre de tri, 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.