Dans la mesure du possible, et contrairement à ce que nous avons fait jusqu’à présent, il est préférable d’utiliser un opérateur de jointure normalisé SQL2 (mot-clef JOIN) pour effectuer une jointure. En effet, les jointures faites dans la clause WHERE (ancienne syntaxe datant de 1986) ne permettent pas de faire la distinction, de prime abord, entre ce qui relève de la sélection et ce qui relève de la jointure puisque tout est regroupé dans une seule clause (la clause WHERE). La lisibilité des requêtes est plus grande en utilisant la syntaxe de l’opérateur JOIN qui permet d’isoler les conditions de sélections (clause WHERE) de celles de jointures (clauses JOIN), et qui permet également de cloisonner les conditions de jointures entre chaque couples de table. De plus, l’optimisation d’exécution de la requête est souvent plus pointue lorsque l’on utilise l’opérateur JOIN. Enfin, lorsque l’on utilise l’ancienne syntaxe, la suppression de la clause WHERE à des fins de tests pose évidemment des problèmes.
Prenons une opération de jointure entre deux tables R1 et R2 selon une expression logique E. En algèbre relationnelle, cette opération se note :
| R1 ▷◁E R2 |
Dans la section 3.4.8, nous avons vu que la jointure n’est rien d’autre qu’un produit cartésien suivi d’une sélection :
| R1 ▷◁E R2 = σE (R1 × R2) |
On peut également dire que le produit cartésien n’est rien d’autre qu’une jointure dans laquelle l’expression logique E est toujours vraie :
| R1 × R2 = R1 ▷◁true R2 |
Nous avons vu section 4.5.5 que le produit cartésien entre deux tables table_1 et table_2 peut s’écrire en SQL :
SELECT * FROM table_1, table_2
Il peut également s’écrire en utilisant le mot-clé JOIN dédié aux jointures de la manière suivante :
SELECT * FROM table_1 CROSS JOIN table_2
En fait, sous PostgreSQL, les quatre écritures suivantes sont équivalentes :
SELECT * FROM table_1, table_2 SELECT * FROM table_1 CROSS JOIN table_2 SELECT * FROM table_1 JOIN table_2 ON TRUE SELECT * FROM table_1 INNER JOIN table_2 ON TRUE
Les deux dernières écritures prendront un sens dans les sections qui suivent.
Sans compter l’opérateur CROSS JOIN, voici les trois syntaxes possibles de l’expression d’une jointure dans la clause FROM en SQL :
table_1 [ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ] JOIN table_2 ON predicat [...]
table_1 [ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ] JOIN table_2 USING (colonnes) [...]
table_1 NATURAL [ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ] JOIN table_2 [...]
Ces trois syntaxes diffèrent par la condition de jointure spécifiée par les clause ON ou USING, ou implicite dans le cas d’une jointure naturelle introduite par le mot-clé NATURAL.
La jointure externe droite peut être obtenue par une jointure externe gauche dans laquelle on inverse l’ordre des tables (et vice-versa). La jointure externe bilatérale peut être obtenue par la combinaison de deux jointures externes unilatérales avec l’opérateur ensembliste UNION que nous verrons dans la section 4.7.3.
Des jointures de n’importe quel type peuvent être chaînées les unes derrières les autres. Les jointures peuvent également être imbriquées étant donné que les tables table_1 et table_2 peuvent très bien être elles-mêmes le résultat de jointures de n’importe quel type. Les opérations de jointures peuvent être parenthésées afin de préciser l’ordre dans lequel elles sont effectuées. En l’absence de parenthèses, les jointures s’effectuent de gauche à droite.
Afin d’illustrer les opérations de jointure, considérons les tables realisateur et film définies de la manière suivante :
create table realisateur (
id_real integer primary key,
nom varchar(16),
prenom varchar(16)
);
create table film (
num_film integer primary key,
id_real integer,
titre varchar(32)
);
On notera que dans la table film, l’attribut id_real correspond à une clef étrangère et aurait dû être défini de la manière suivante : id_real integer references realisateur. Nous ne l’avons pas fait dans le but d’introduire des films dont le réalisateur n’existe pas dans la table realisateur afin d’illustrer les différentes facettes des opérations de jointure.
La table realisateur contient les lignes suivantes :
id_real | nom | prenom
---------+-----------+---------
1 | von Trier | Lars
4 | Tarantino | Quentin
3 | Eastwood | Clint
2 | Parker | Alan
La table film contient les lignes suivantes :
id_film | id_real | titre
---------+---------+----------------------------
1 | 1 | Dogville
2 | 1 | Breaking the waves
3 | 5 | Faux-Semblants
4 | 5 | Crash
5 | 3 | Chasseur blanc, coeur noir
La jointure naturelle entre les tables film et réalisateur peut s’écrire indifféremment de l’une des manières suivante :
SELECT * FROM film NATURAL JOIN realisateur SELECT * FROM film NATURAL INNER JOIN realisateur; SELECT * FROM film JOIN realisateur USING (id_real); SELECT * FROM film INNER JOIN realisateur USING (id_real);
pour produire le résultat suivant :
id_real | id_film | titre | nom | prenom
---------+---------+----------------------------+-----------+--------
1 | 1 | Dogville | von Trier | Lars
1 | 2 | Breaking the waves | von Trier | Lars
3 | 5 | Chasseur blanc, coeur noir | Eastwood | Clint
Nous aurions également pu effectuer une équi-jointure en écrivant :
SELECT * FROM film, realisateur WHERE film.id_real = realisateur.id_real; SELECT * FROM film JOIN realisateur ON film.id_real = realisateur.id_real; SELECT * FROM film INNER JOIN realisateur ON film.id_real = realisateur.id_real;
Mais la colonne id_real aurait été dupliquée :
id_film | id_real | titre | id_real | nom | prenom
---------+---------+----------------------------+---------+-----------+--------
1 | 1 | Dogville | 1 | von Trier | Lars
2 | 1 | Breaking the waves | 1 | von Trier | Lars
5 | 3 | Chasseur blanc, coeur noir | 3 | Eastwood | Clint
La jointure externe gauche entre les tables film et réalisateur permet de conserver, dans la table résultat, une trace des films dont le réalisateur n’apparaît pas dans la table realisateur. Une telle jointure peut s’écrire indifféremment comme suit :
SELECT * FROM film NATURAL LEFT JOIN realisateur; SELECT * FROM film NATURAL LEFT OUTER JOIN realisateur; SELECT * FROM film LEFT JOIN realisateur USING (id_real); SELECT * FROM film LEFT OUTER JOIN realisateur USING (id_real);
Elle produit le résultat suivant :
id_real | id_film | titre | nom | prenom
---------+---------+----------------------------+-----------+--------
1 | 1 | Dogville | von Trier | Lars
1 | 2 | Breaking the waves | von Trier | Lars
5 | 3 | Faux-Semblants | |
5 | 4 | Crash | |
3 | 5 | Chasseur blanc, coeur noir | Eastwood | Clint
Naturellement, en écrivant :
SELECT * FROM film LEFT JOIN realisateur ON film.id_real = realisateur.id_real; SELECT * FROM film LEFT OUTER JOIN realisateur ON film.id_real = realisateur.id_real;
la colonne id_real serait dupliquée :
id_film | id_real | titre | id_real | nom | prenom
---------+---------+----------------------------+---------+-----------+--------
1 | 1 | Dogville | 1 | von Trier | Lars
2 | 1 | Breaking the waves | 1 | von Trier | Lars
3 | 5 | Faux-Semblants | | |
4 | 5 | Crash | | |
5 | 3 | Chasseur blanc, coeur noir | 3 | Eastwood | Clint
La jointure externe droite entre les tables film et réalisateur permet de conserver, dans la table résultat, une trace des réalisateurs dont aucun film n’apparaît dans la table film. Une telle jointure peut s’écrire indifféremment comme suit :
SELECT * FROM film NATURAL RIGHT JOIN realisateur; SELECT * FROM film NATURAL RIGHT OUTER JOIN realisateur; SELECT * FROM film RIGHT JOIN realisateur USING (id_real); SELECT * FROM film RIGHT OUTER JOIN realisateur USING (id_real);
Elle produit le résultat suivant :
id_real | id_film | titre | nom | prenom
---------+---------+----------------------------+-----------+---------
1 | 1 | Dogville | von Trier | Lars
1 | 2 | Breaking the waves | von Trier | Lars
2 | | | Parker | Alan
3 | 5 | Chasseur blanc, coeur noir | Eastwood | Clint
4 | | | Tarantino | Quentin
La jointure externe bilatérale entre les tables film et réalisateur permet de conserver, dans la table résultat, une trace de tous les réalisateurs et de tous les films. Une telle jointure peut indifféremment s’écrire :
SELECT * FROM film NATURAL FULL JOIN realisateur; SELECT * FROM film NATURAL FULL OUTER JOIN realisateur; SELECT * FROM film FULL JOIN realisateur USING (id_real); SELECT * FROM film FULL OUTER JOIN realisateur USING (id_real);
Elle produit le résultat suivant :
id_real | id_film | titre | nom | prenom
---------+---------+----------------------------+-----------+---------
1 | 1 | Dogville | von Trier | Lars
1 | 2 | Breaking the waves | von Trier | Lars
2 | | | Parker | Alan
3 | 5 | Chasseur blanc, coeur noir | Eastwood | Clint
4 | | | Tarantino | Quentin
5 | 3 | Faux-Semblants | |
5 | 4 | Crash | |
Un groupe est un sous-ensemble des lignes d’une table ayant la même valeur pour un attribut. Par exemple, on peut grouper les films en fonction de leur réalisateur. Un groupe est déterminé par la clause GROUP BY suivie du nom du ou des attributs sur lesquels s’effectuent le regroupement.
La syntaxe d’une requête faisant éventuellement intervenir des fonctions d’agrégation, une clause GROUP BY et une clause HAVING est la suivante :
SELECT expression_1, [...,] expression_N [, fonction_agrégation [, ...] ] FROM nom_table [ [ AS ] alias ] [, ...] [ WHERE prédicat ] [ GROUP BY expression_1, [...,] expression_N ] [ HAVING condition_regroupement ]
La commande GROUP BY permet de définir des regroupements (i.e. des agrégats) qui sont projetés dans la table résultat (un regroupement correspond à une ligne) et d’effectuer des calculs statistiques, définis par les expressions fonction_agrégation [, ...], pour chacun des regroupements. La liste d’expressions expression_1, [...,] expression_N correspond généralement à une liste de colonnes colonne_1, [...,] colonne_N. La liste de colonnes spécifiée derrière la commande SELECT (expression_1, [...,] expression_N) doit être identique à la liste de colonnes de regroupement spécifiée derrière la commande GROUP BY (expression_1, [...,] expression_N). A la place des noms de colonne il est possible de spécifier des opérations mathématiques de base sur les colonnes (comme définies dans la section 4.5.4). Dans ce cas, les regroupements doivent porter sur les mêmes expressions.
Si les regroupements sont effectués selon une expression unique, les groupes sont définis par les ensembles de lignes pour lesquelles cette expression prend la même valeur. Si plusieurs expressions sont spécifiées (expression_1, expression_2, …) les groupes sont définis de la façon suivante : parmi toutes les lignes pour lesquelles expression_1 prend la même valeur, on regroupe celles ayant expression_2 identique, etc.
Un SELECT avec une clause GROUP BY produit une table résultat comportant une ligne pour chaque groupe.
DISTINCT indique à la fonction de groupe de ne prendre en compte que des valeurs distinctes. ALL indique à la fonction de groupe de prendre en compte toutes les valeurs, c’est la valeur par défaut.
Aucune des fonctions de groupe ne tient compte des valeurs NULL à l’exception de COUNT(*). Ainsi, SUM(col) est la somme des valeurs non NULL de la colonne col. De même AVG est la somme des valeurs non NULL divisée par le nombre de valeurs non NULL.
Il est tout à fait possible d’utiliser des fonctions d’agrégation sans clause GROUP BY. Dans ce cas, la clause SELECT ne doit comporter que des fonctions d’agrégation et aucun nom de colonne. Le résultat d’une telle requête ne contient qu’une ligne.
Reprenons la base de données de la séance de travaux pratiques 4.4 dont le schéma relationnel était :
Pour connaître le nombre de fois que chacun des films a été projeté on utilise la requête :
SELECT num_film, titre, COUNT(*) FROM film NATURAL JOIN projection GROUP BY num_film, titre;
Si l’on veut également connaître la date de la première et de la dernière projection, on utilise :
SELECT num_film, titre, COUNT(*), MIN(jour), MAX(jour) FROM film NATURAL JOIN projection GROUP BY num_film, titre;
Pour connaître le nombre total de films projetés au cinéma Le Fontenelle, ainsi que la date de la première et de la dernière projection dans ce cinéma, la requête ne contient pas de clause GROUP BY mais elle contient des fonctions d’agrégation :
SELECT COUNT(*), MIN(jour), MAX(jour) FROM film NATURAL JOIN projection NATURAL JOIN cinema WHERE cinema.nom = 'Le Fontenelle';
De la même façon qu’il est possible de sélectionner certaines lignes au moyen de la clause WHERE, il est possible, dans un SELECT comportant une fonction de groupe, de sélectionner certains groupes par la clause HAVING. Celle-ci se place après la clause GROUP BY.
Le prédicat dans la clause HAVING suit les mêmes règles de syntaxe qu’un prédicat figurant dans une clause WHERE. Cependant, il ne peut porter que sur des caractéristiques du groupe : fonction d’agrégation ou expression figurant dans la clause GROUP BY.
Une requête de groupe (i.e. comportant une clause GROUP BY) peut contenir à la fois une clause WHERE et une clause HAVING. La clause WHERE sera d’abord appliquée pour sélectionner les lignes, puis les groupes seront constitués à partir des lignes sélectionnées, les fonctions de groupe seront ensuite évaluées et la clause HAVING sera enfin appliquée pour sélectionner les groupes.
Pour connaître le nombre de fois que chacun des films a été projeté en ne s’intéressant qu’aux films projetés plus de 2 fois, on utilise la requête :
SELECT num_film, titre, COUNT(*) FROM film NATURAL JOIN projection GROUP BY num_film, titre HAVING COUNT(*)>2;
Si en plus, on ne s’intéresse qu’aux films projetés au cinéma Le Fontenelle, il faut ajouter une clause WHERE :
SELECT num_film, titre, COUNT(*) FROM film NATURAL JOIN projection NATURAL JOIN cinema WHERE cinema.nom = 'Le Fontenelle' GROUP BY num_film, titre HAVING COUNT(*)>2;
Les résultats de deux requêtes peuvent être combinés en utilisant les opérateurs ensemblistes d’union (UNION), d’intersection (INTERSECT) et de différence (EXCEPT). La syntaxe d’une telle requête est la suivante :
requête_1 { UNION | INTERSECT | EXCEPT } [ALL] requête_2 [...]
Pour que l’opération ensembliste soit possible, il faut que requête_1 et requête_2 aient le même schéma, c’est à dire le même nombre de colonnes respectivement du même type. Les noms de colonnes (titres) sont ceux de la première requête (requête_1).
Il est tout à fait possible de chaîner plusieurs opérations ensemblistes. Dans ce cas, l’expression est évaluée de gauche à droite, mais on peut modifier l’ordre d’évaluation en utilisant des parenthèses.
Dans une requête on ne peut trouver qu’une seule instruction ORDER BY. Si elle est présente, elle doit être placée dans la dernière requête (cf. section 4.5.6). La clause ORDER BY ne peut faire référence qu’aux numéros des colonnes (la première portant le numéro 1), et non pas à leurs noms, car les noms peuvent être différents dans chacune des requêtes sur lesquelles porte le ou les opérateurs ensemblistes.
Les opérateurs UNION et INTERSECT sont commutatifs.
Contrairement à la commande SELECT, le comportement par défaut des opérateurs ensemblistes élimine les doublons. Pour les conserver, il faut utiliser le mot-clef ALL.
Attention, il s’agit bien d’opérateurs portant sur des tables générées par des requêtes. On ne peut pas faire directement l’union de deux tables de la base de données.
L’opérateur d’union relation1 ∪ relation2 se traduit tout simplement en SQL par la requête :
SELECT * FROM relation_1 UNION SELECT * FROM relation_2
L’opérateur d’intersection R1 ∩ R2 se traduit tout simplement en SQL par la requête :
SELECT * FROM relation_1 INTERSECT SELECT * FROM relation_2
L’opérateur de différence R1 − R2 se traduit tout simplement en SQL par la requête :
SELECT * FROM relation_1 EXCEPT SELECT * FROM relation_2
Il n’existe pas de commande SQL permettant de réaliser directement une division. Prenons la requête :
Cela peut se reformuler par :
Malheureusement, le quantificateur universel (∀) n’existe pas en SQL. Par contre, le quantificateur existentiel (∃) existe : EXISTS. Or, la logique des prédicats nous donne l’équivalence suivante :
| ∀ x P(x) = ¬ ∃ x ¬ P(x) |
On peut donc reformuler le problème de la manière suivante :
Ce qui correspond à la requête SQL :
SELECT DISTINCT nom, prenom FROM individu AS acteur_tous_lars
WHERE NOT EXISTS (
SELECT * FROM ( film JOIN individu ON num_realisateur = num_individu
AND nom = 'von Trier' AND prenom = 'Lars' ) AS film_lars
WHERE NOT EXISTS (
SELECT * FROM individu JOIN jouer ON num_individu = num_acteur
AND num_individu = acteur_tous_lars.num_individu
AND num_film = film_lars.num_film
)
);
En prenant le problème d’un autre point de vue, on peut le reformuler de la manière suivante :
Ce qui peut se traduire en SQL indifféremment par l’une des deux requêtes suivantes :
SELECT acteur.nom, acteur.prenom
FROM individu AS acteur JOIN jouer ON acteur.num_individu = jouer.num_acteur
JOIN film ON jouer.num_film = film.num_film
JOIN individu AS realisateur ON film.num_realisateur = realisateur.num_individu
WHERE realisateur.nom = 'von Trier' AND realisateur.prenom = 'Lars'
GROUP BY acteur.nom, acteur.prenom
HAVING COUNT (DISTINCT film.num_film) = (
SELECT DISTINCT COUNT(*)
FROM film JOIN individu ON num_realisateur = num_individu
WHERE nom = 'von Trier' AND prenom = 'Lars'
);
SELECT DISTINCT acteur_tous_lars.nom, acteur_tous_lars.prenom
FROM individu AS acteur_tous_lars
WHERE (
SELECT DISTINCT COUNT(*)
FROM jouer JOIN film ON jouer.num_film = film.num_film
JOIN individu ON num_realisateur = num_individu
WHERE nom = 'von Trier' AND prenom = 'Lars'
AND jouer.num_acteur = acteur_tous_lars.num_individu
) = (
SELECT DISTINCT COUNT(*)
FROM film JOIN individu ON num_realisateur = num_individu
WHERE nom = 'von Trier' AND prenom = 'Lars'
);