7.2. Expressions de table

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 tablees 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.

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.

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.

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 ».

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, « Fonctions et opérateurs de tableaux ») 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 fournies 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(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 *.

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.

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;