7.2. Expressions de table

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.

Des jointures de tout type peuvent être chaînées ou imbriquées : T1 et T2 peuvent être des tables jointes (l'une, l'autre ou les deux). 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.

Pour résumer, supposons que nous ayons une table t1

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

et une table t2

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

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

=> SELECT * FROM t1 CROSS JOIN t2;
 num | nom | num | 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.num = t2.num;
 num | nom | num | valeur
-----+-----+-----+-------
   1 | a   |   1 | xxx
   3 | c   |   3 | yyy
(2 rows)

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

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

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

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

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

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
 num | nom | num | 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. Par exemple :

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

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. Celui-ci sera ensuite utilisé pour faire référence à la table dérivée dans la suite de l'exécution. On appelle cela un alias de table.

Pour créer un alias de table, il suffit d'écrire

FROM reference_table AS alias

ou

FROM reference_table alias

Le mot clé AS n'est pas nécessaire. alias peut être n'importe quel identifiant.

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

SELECT * FROM nom_de_table_tres_long n JOIN un_autre_nom_assez_long u ON n.id = u.num;

L'alias devient le nouveau nom de la 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 une syntaxe SQL valide. En réalité, (c'est une extension du standard proposée par PostgreSQL) une référence de table implicite est 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;

ce qui aboutira à une jointure croisée. Et ce n'est habituellement pas ce qui est attendu.

Les alias de table sont essentiellement disponibles pour faciliter l'écriture de requêtes mais ils apparaissent nécessaires pour joindre une table avec elle-même, par exemple

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

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

Les parenthèses sont utilisées pour lever les ambiguités. L'instruction suivante affectera le résultat de la jointure à l'alias b, contrairement à l'exemple précédent :

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 des tables :

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

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

Quand un alias est appliqué à la sortie d'une clause JOIN, quelqu'en soit la forme, 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.

7.2.1.3. Sous-requêtes

Une sous-requête spécifiant une table dérivée doit être écrite entre parenthèses et doit se voir affecter un alias de table. (Voir 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.

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 basiques (types scalaires) ou de types de données composites (lignes de table). Elles sont utilisées de la même façon qu'une table, une vue ou une sous-requête de la clause FROM d'une requête. Les colonnes retourné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 basique, 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. Dans le cas contraire, 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 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 qui renvoient des ensembles de colonnes différents suivant la façon dont elles sont appelées. Pour ce faire, 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 cette façon, le système saura comment analyser et planifier la requête. Par exemple :

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

La fonction dblink exécute une requête distante (voir contrib/dblink). Elle a été déclarée avec un type de retour record, puisqu'elle peut ê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 sorte que l'analyseur sache, par exemple, comment étendre *.