PostgreSQLLa base de données la plus sophistiquée au monde.

Version anglaise

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 une table dérivée comme une sous-requête, une construction JOIN ou une combinaison complexe de celles-ci. Si plus d'une référence de tables est listée dans la clause FROM, les tables sont jointes (autrement dit le produit cartésien de leurs lignes est réalisé ; voir ci-dessous). Le résultat du FROM forme une table virtuelle intermédiaire qui pourrait être le sujet des 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 inclues. Écrire * n'est pas nécessaire car il s'agit du comportement par défaut (sauf si vous avez choisi de modifier la configuration de sql_inheritance). Néanmoins, écrire * peut être utile pour indiquer fortement que les tables filles seront parcourues.

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 la suivante :

T1 type_jointure T2 [ condition_jointure ]

Les jointures de tous types peuvent être chainées ou imbriquées ensemble : T1 et T2 peuvent toutes les deux être des tables jointes. Des parenthèses peuvent être utilisées autour des clauses JOIN pour contrôler l'ordre de jointure. En l'absence de 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, T2. C'est aussi équivalent à FROM T1 INNER JOIN T2 ON TRUE (voir ci-dessous). C'est aussi équivalent à FROM T1, T2.

[Note]

Note

Cette dernière équivalence ne tient toutefois pas quand plus de deux tables sont présentes car JOIN a une priorité plus importante 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 la condition peut référencer 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 source 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 quelque 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 quelque 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 le plus général de condition de jointure : il prend une expression booléenne du même genre que celle utilisée dans une clause WHERE. Une paires de lignes de T1 et T2 correspondent si l'expression ON est évaluée à vraie (true) pour ces deux lignes.

La clause USING est un raccourci vous permettant de profiter de la situation particulière où les deux côtés de la jointure utilisent le même nom pour la(les) colonne(s) de jointure. Elle prend une liste séparée par des virgules des noms de colonnes communs et réalise une jointure incluant une comparaison d'égalité pour chacun d'entre eux. Par exemple, joindre T1 et T2 avec USING (a, b) produit la condition de jointure ON T1.a = T2.a AND T1.b = T2.b.

De plus, la sortie de JOIN USING supprime les colonnes redondantes : il est inutile d'afficher les deux colonnes en question, car leurs valeurs doivent être identiques. Alors que JOIN ON renvoie toutes les colonnes de T1 suivi de toutes les colonnes de T2, JOIN USING renvoie une colonne en sortie pour chaque paire de colonnes listées (dans le même ordre que celui défini), suivi par chacune des colonnes restantes de T1, suivi par chacune des colonnes restantes de T2.

Finalement, NATURAL est un raccourci de USING : il produit une liste USING correspondant à tous les noms de colonnes présents dans les deux tables en entrée. Tout comme pour USING, ces colonnes apparaîtront une fois seulement dans la table de sortie. S'il n'y a pas de noms de colonnes communs, NATURAL se comporte comme JOIN ... ON TRUE et produit une jointure croisée.

[Note]

Note

USING est plutôt à l'abri des changements de colonnes dans les relations jointes car seules les colonnes listées sont associées. NATURAL est considérablement plus risqué car n'importe quel changement de définition des relations pouvant provoquer l'apparition d'un nouveau nom de colonne commun engendrera sa présence dans la jointure.

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. Cela n'est pas important avec les jointures internes, mais est très important 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 complexe, 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, « Sous-requêtes »).

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 jointure 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 affecté un alias de table (comme dans Section 7.2.1.2, « Alias de table et de colonne »). 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éduit à une jointure pleine, surviennent quand la sous-requête implique un groupement ou un agrégat.

Uns 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 en option mais c'est une bonne pratique. Pour plus d'informations, voir Section 7.7, « Listes VALUES ».

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 qu'une colonne de table, vue ou sous-requête.

Si une fonction de table renvoie un type de données de base, la nom de la colonne de résultat correspond à celui de la fonction. Si la fonction renvoie un type composite, les colonnes résultantes ont le même nom que les attributs individuels du type.

Une fonction de table peut avoir un alias dans la clause FROM mais elle peut être laissée sans alias. Si une fonction est utilisée dans la clause FROM sans alias, le nom de la fonction est utilisé comme nom de table résultante.

Quelques exemples :

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

CREATE FUNCTION recuptruc(int) RETURNS SETOF foo 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. 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. 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(3) (part of the dblink module>) 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 *.

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 sont 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'ensembles de lignes. Les lignes résultantes sont jointes comme d'habitude aux lignes résultants 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) renvoit 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 clareté.)

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() renvoit 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, « Expressions de valeurs ») 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]

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 vous utilisez 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 à 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 étendue 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, « Fonctions d'agrégat » propose plus d'informations sur les fonctions d'agrégats disponibles.

[Astuce]

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, « DISTINCT »).

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

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

Dans cet exemple, les colonnes produit_id, 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 (but see below). La colonne s.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.

If the products table is set up so that, say, product_id is the primary key, then it would be enough to group by product_id in the above example, since name and price would be functionally dependent on the product ID, and so there would be no ambiguity about which name and price value to return for each product ID group.

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 produit_id, p.nom, (sum(v.unite) * (p.prix - p.cout)) AS profit
    FROM produits p LEFT JOIN ventes v USING (produit_id)
    WHERE v.date > CURRENT_DATE - INTERVAL '4 weeks'
    GROUP BY produit_id, 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. Traitement de fonctions Window

Si la requête contient une des fonctions Window (voir Section 3.5, « Fonctions de fenêtrage », Section 9.21, « Fonctions Window » et Section 4.2.8, « Appels de fonction de fenêtrage »), ces fonctions sont évaluées après que soient effectués les regroupements, les aggrégations, les filtrages par HAVING. C'est-à-dire que si la requête comporte des aggrégat, 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.