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.
La 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 table jointe ou une combinaison complexe de celles-ci. Si plus d'une référence de tables est listée dans la clause FROM, elle sont jointes pour former 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.
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.
Types de jointures
T1 CROSS JOIN T2
Pour chaque combinaison de lignes provenant de T1 et T2 , la table dérivée contiendra une ligne consistant de toutes les colonnes de T1 suivies de 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).
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.
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.
USING est la notation raccourcie : elle prend une liste de noms de colonnes, séparés par des virgules, que les tables jointes ont en commun, et forme une condition de jointure spécifiant l'égalité de chacune de ces paires de colonnes. De plus, la sortie de JOIN USING a une colonne pour chaque paires égales des colonnes en entrée, suivies par toutes les autres colonnes de chaque table. Du coup, USING (a, b, c) est équivalent à ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) avec l'exception que si ON est utilisé, il y aura deux colonnes a, b, puis c dans le résultat, alors qu'avec USING, il n'y en aurait eu qu'une de chaque.
Enfin, NATURAL est un format raccourci de USING : il forme une liste USING consistant exactement des noms de colonnes apparaissant à la fois dans les deux tables en entrée. Comme avec USING, ces colonnes apparaissent seulement une fois dans la table de sortie.
Les types possibles de jointures qualifiées sont :
Pour chaque ligne R1 de T1, la table jointe a une ligne pour chaque ligne de T2 satisfaisant la condition de jointure avec R1.
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 au moins une ligne pour chaque ligne de T1 quelque soient les conditions.
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 une ligne pour chaque ligne de T2 quelque soient les conditions.
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.
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 *.
La syntaxe de la Clause WHERE est
WHERE condition_recherche
où 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 quelques colonnes 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]](images/note.png)
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. Pour les jointures externes, il n'y a pas d'autres choix : elles doivent être faites dans la clause FROM. Une clause ON/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.
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 Clause GROUP BY est utilisée pour regrouper les lignes d'une table partageant 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 ligne 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é 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 utilisées dans le regroupement ne peuvent pas être référencées sauf dans les expressions d'agrégats. Voici un exemple d'expressions 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.15, « Fonctions d'agrégat » propose plus d'informations sur les fonctions d'agrégats disponibles.
![[Astuce]](images/tip.png)
Le regroupement sans expressions d'agrégats calcule effectivement l'ensemble les 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 (suivant la façon dont est conçue la table produits, le nom et le prix pourraient être totalement dépendants de l'ID du produit, donc des regroupements supplémentaires pourraient théoriquement être inutiles mais ceci n'est pas encore implémenté). 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.
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 d'une table groupée. 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 impliquent 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.
Requêtes
