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.
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
-
Jointure croisée (cross join)
-
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).
-
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.
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 :
-
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 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 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.
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)
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 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 ».
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
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)
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. 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.
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 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)
Astuce
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
