7.2. Expressions de table

FROM reference_table [, reference_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.

Les jointures de tous les types peuvent être chaînées ensemble ou imbriquées : soit les deux soit une des deux, parmi T1 et T2, peuvent être des tables. Les 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 sont imbriquées 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)

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 référence de la table pour la requête courante -- il n'est plus possible de référencer la table avec son nom d'origine. Du coup :

SELECT * FROM ma_table AS m WHERE ma_table.a > 5;

n'est pas valide suivant le standard SQL. Dans PostgreSQL™, ceci amènera une erreur si la variable add_missing_from est désactivée (off, valeur par défaut). S'il est activé (on), une référence vers une table implicite sera ajoutée à la clause FROM, de façon à ce que la requête soit exécutée comme si elle était écrite ainsi :

SELECT * FROM ma_table AS m, ma_table AS ma_table WHERE ma_table.a > 5;

Cela résultera en une jointure croisée, ce qui n'est habituellement pas ce que vous voulez.

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 en utilisant n'importe laquelle de ces formes, l'alias cache le nom original à 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 (voir la 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 qu'une colonne de table, vue ou sous-requête.

Si une fonction de table renvoie un type de données de base, la colonne de résultat est nommée comme 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 exécute une requête distante (voir contrib/dblink). 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 *.