Cours de bases de données - SQL : fondements et pratiques

Nous appellerons donnée toute valeur numérisée décrivant de manière élémentaire un fait, une mesure, une réalité. Ce peut être une chaîne de caractères (« bouvier »), un entier (365), une date (12/07/1998). Cette valeur est toujours associée au contexte permettant de savoir quelle information elle représente. Un mot comme « bouvier » par exemple peut désigner, entre autres, un gardien de troupeau, un aimable petit insecte, ou le nom d'un écrivain célèbre. Il ne prend un peu de sens que si l'on sait l'interpréter. Une donnée se présente toujours en association avec un contexte interprétatif qui permet de lui donner un sens.

Les données ne tombent pas du ciel, et elles ne sont pas mises en vrac dans un espace de stockage. Elles sont issues d'un domaine applicatif, et décrivent des objets, des faits ou des concepts (on parle plus généralement d'entités). On les organise de manière à ce que ces entités soient correctement et uniformément représentées, ainsi que les liens que ces entités ont les unes avec les autres. Si je prends par exemple l'énoncé Nicolas Bouvier est un écrivain suisse auteur du récit de voyage culte « L'usage du monde » paru en 1963, je peux en extraire le prénom et le nom d'une personne, sa nationalité (données décrivant une première entité), et au moins un de ses ouvrages (seconde entité, décrite par un titre et une année de parution). J'ai de plus une notion d'auteur qui relie la première à la seconde. Tout cela constitue autant d'informations indissociables les uns des autres, constituant une ébauche d'une base de données consacrée aux écrivains et à leurs œuvres.

La représentation de ces données et leur association donne à la base une structure qui aide à distinguer précisément et sans ambiguité les informations élémentaires constituant cette base: nom, prénom, année de naissance, livre(s) publié(s), etc. Une base sans structure n'a aucune utilité. Une base avec une structure incorrecte ou incomplète est une source d'ennuis infinis. Nous verrons comment la structure doit être très sérieusement définie pendant la phase de conception.

Une base de données est un ensemble (potentiellement volumineux, mais pas forcément) de telles informations conformes à une structure pré-définie au moment de la conception, avec, de plus, une caractéristique essentielle : on souhaite les mémoriser de manière persistante. La persistance désigne la capacité d'une base à exister indépendamment des applications qui la manipulent, ou du système qui l'héberge. On peut arrêter toutes les machines un soir, et retrouver la base de données le lendemain. Cela implique qu'une base est toujours stockée sur un support comme les disques magnétiques qui préservent leur contenu même en l'absence d'alimentation électrique.

Important: Les supports persistants (disques, SSD) sont très lents par rapport aux capacités d'un processeur et de sa mémoire interne. La nécessité de stocker une base sur un tel support soulève donc de redoutables problèmes de performance, et a mené à la mise au point de techniques très sophistiquées, caractéristiques des systèmes de gestion de données. Ces techniques sont étudiées dans un cours consacré aux aspects systèmes (en L3).

On en arrive donc à la définition suivante:

Remarquons qu'une organisation consistant à stocker nos données dans un (ou plusieurs) fichier(s) sur le disque de notre ordinateur personnel peut très bien être considéré comme conforme à cette définition, sous réserve qu'elles soient un tant soit peu structurées. Les fichiers produits par votre traitement de texte préféré par exemple ne font pas l'affaire: on y trouve certes des données, mais pas leur association à un contexte interprétatif non ambigu. Ecrire avec ce traitement de texte une phrase comme « L'usage du monde est un livre de Nicolas Bouvier paru en 1963 » constitue un énoncé trop flou pour qu'un système puisse automatiquement en extraire (sans recourir à des techniques très sophistiquées et en partie incertaines) le nom de l'auteur, le titre de son livre, ou sa date de parution.

Un fichier de base de données a nécessairement une structure qui permet d'une part de distinguer les données les unes des autres, et d'autre part de représenter leurs liens. Prenons l'exemple de l'une des structures les plus simples et les plus répandues, les fichiers CSV. Dans un fichier CSV, les données élémentaires sont réprésentés par des « champs » délimités par des points-virgule. Les champs sont associés les uns aux autres par le simple fait d'être placés dans une même ligne. Les lignes en revanche sont indépendantes les unes des autres. On peut placer autant de lignes que l'on veut dans un fichier, et même changer leur ordre sans que cela modifie en quoi que ce soit l'information représentée.

Voici l'exemple de nos données, représentées en CSV.

"Bouvier" ; "Nicolas"; "L'usage du monde" ; 1963

On comprend bien que le premier champ est le nom, le second le prénom, etc. Il paraît donc cohérent d'ajouter de nouvelles lignes comme:

"Bouvier"   ; "Nicolas"; "L'usage du monde" ; 1963
"Stevenson" ; "Robert-Louis"  ; "Voyage dans les Cévennes avec un âne" ; 1879

On a donné une structure régulière à nos informations, ce qui va permettre de les interroger et de les manipuler avec précision. On les stocke dans un fichier sur disque, et nous sommes donc en cours de constitution d'une véritable base de données. On peut en fait généraliser ce constat: une base de données est toujours un ensemble de fichiers, stockés sur une mémoire externe comme un disque, dont le contenu obéit à certaines règles de structuration.

Peut-on se satisfaire de cette solution et imaginer que nous pouvons construire des applications en nous appuyant directement sur des fichiers structurés, par exemple des fichiers CSV? C'est la méthode illustrée par la figure 1. Dans une telle situation, chaque utilisateur applique des programmes au fichier, pour en extraire des données, pour les modifier, pour les créer.

Figure 1 : Une approche simpliste avec accès direct aux fichiers de la base

Cette approche n'est pas totalement inenvisageable, mais soulève en pratique de telles difficultés que personne (personne de censé en tout cas) n'a recours à une telle solution. Voici un petit catalogue de ces difficultés.

• Lourdeur d'accès aux données. En pratique, pour chaque accès, même le plus simple, il faudrait écrire un programme adapté à la structure du fichier. La production et la maintenance de tels programmes seraient extrêmement coûteuses.
• Risques élevés pour l'intégrité et la sécurité. Si tout programmeur peut accéder directement aux fichiers, il est impossible de garantir la sécurité et l'intégrité des données. Quelqu'un peut très bien par exemple, en toute bonne foi, faire une fausse manœuvre qui rend le fichier illisible.
• Pas de contrôle de concurrence. Dans un environnement où plusieurs utilisateurs accèdent aux même fichiers, comme illustré par exemple sur la Figure 1, des problèmes de concurrence d'accès se posent, notammment pour les mises à jour. Comment gérer par exemple la situation où deux utilisateurs souhaitent en même temps ajouter une ligne au fichier?

• Performances. Tant qu'un fichier ne contient que quelques centaines de lignes, on peut supposer que les performances ne posent pas de problème, mais que faire quand on atteint les Gigaoctets (1,000 Mégaoctets), ou même le Téraoctet (1,000 Gigaoctets)? Maintenir des performances acceptables suppose la mise en œuvre d'algorithmes ou de structures de données demandant des compétences très avancées, probablement hors de portée du développeur d'application qui a, de toute façon, mieux à faire.

  Chacun de ces problèmes soulève de redoutables difficultés techniques.

Leur combinaison nécessite la mise en place de systèmes d'une très grande complexité, capable d'offrir à la fois un accès simple, sécurisé, performant au contenu d'une base, et d'accomplir le tour de force de satisfaire de tels accès pour des dizaines, centaines ou même milliers d'utilisateurs simultanés, le tout en garantissant l'intégrité de la base même en cas de panne. De tels systèmes sont appelés Systèmes de Gestion de Bases de Données, SGBD en bref.

1. Accès aux fichiers de la base, garantissant leur intégrité, contrôlant les opérations concurrentes, optimisant les recherches et mises à jour.
2. Interactions avec les applications et utilisateurs, grâce à des langages d'interrogation et de manipulation à haut niveau d'abstraction.

Avec un SGBD, les applications n'ont plus jamais accès directement aux fichiers, et ne savent d'ailleurs même pas qu'ils existent, quelle est leur structure et où ils sont situés. L'architecture classique est celle illustrée par la figure 2. Le SGBD apparaît sous la forme d'un serveur, c'est-à-dire d'un processus informatique prêt à communiquer avec d'autres (les « clients ») via le réseau. Ce serveur est hébergé sur une machine (la « machine serveur ») et est le seul à pouvoir accéder aux fichiers contenant les données, ces fichiers étant le plus souvent stockés sur le disque de la machine serveur.

Figure 2 : Architecture classique, avec serveur du SGBD

Les applications utilisateurs, maintenant, accèdent à la base via le programme serveur auquel elles sont connectés. Elles transmettent des commandes (d'où le nom « d'applications clientes ») que le serveur se charge d'appliquer. Ces applications bénéficient donc des puissants algorithmes implantés par le SGBD dans son serveur, comme par exemple la capacité à gérer les accès concurrents, où à satisfaire avec efficacité des recherches portant sur de très grosses bases.

Cette architecture est à peu près universellement adoptée par tous les SGDB de tous les temps et de toutes les catégories. Les notions suivantes, et le vocabulaire associé, sont donc très importantes à retenir.

Définition (architecture client serveur)

• Programme serveur. Un SGBD est instancié sur une machine sous la forme d'un programme serveur qui gère une ou plusieurs bases de données, chacune constituée de fichiers stockés sur disque. Le programme serveur est seul responsable de tous les accès à une base, et de l'utilisation des ressources (mémoire, disques) qui servent de support à ces accès.
• Clients (programmes). Les programmes (ou applications) clients se connectent au programme serveur via le réseau, lui transmettent des requêtes et recoivent des données en retour. Ils ne disposent d'aucune information directe sur la base.

Malgré que ceci soit le cas dans la majorité des cas d'usage, certaines utilisations de base de données ne nécessite pas de gérer plusieurs clients en parallèle. Pour ces utilisations, des SGDBs légers existent où le client unique et serveur sont fusionnés dans le même programme. C'est notamment le cas de SQLite, que nous utiliserons en TP.

Le fait que le serveur de données s'interpose entre les fichiers et les programmes clients a une conséquence extrêmement importante: ces clients, n'ayant pas accès aux fichiers, ne voient les données que sous la forme que veut bien leur présenter le serveur. Ce dernier peut donc choisir le mode de représentation qui lui semble le plus approprié, la seule condition étant de pouvoir aisément convertir le format des fichiers vers la représentation « publique ».

En d'autres termes, on peut s'abstraire de la complexité et de la lourdeur des formats de fichiers avec tous leurs détails compliqués de codages, de gestion de la mémoire, d'adressage, et proposer une représentation simple et intuitive aux applications. Une des propriétés les plus importantes des SGBD est donc la distinction entre plusieurs niveaux d'abstraction pour la réprésentation des données. Il nous suffira ici de distinguer deux niveaux: le niveau logique et le niveau physique.

Les structures du niveau logique définissent une modélisation des données: on peut envisager par exemple des structures de graphe, d'arbre, de listes, etc. Le modèle relationnel se caractérise par une modélisation basée sur une seule structure, la table. Cela apporte au modèle une grande simplicité puisque toutes les données ont la même forme et obéissent aux même contraintes. Cela a également quelques inconvénients en limitant la complexité des données représentables. Pour la grande majorité des applications, le modèle relationnel a largement fait la preuve de sa robustesse et de sa capacité d'adaptation. C'est lui que nous étudions dans l'ensemble du cours.

La Fig. 3 illustre les niveaux d'abstraction dans l'architecture d'un système de gestion de données. Les programmes clients ne voient que le niveau logique, c'est-à-dire des tables si le modèle de données est relationnel (il en existe d'autres, nous ne les étudions pas ici). Le serveur est en charge du niveau physique, de la conversion des données vers le niveau logique, et de toute la machinerie qui permet de faire fonctionner le système: mémoire, disques, algorithmes et structures de données. Tout cela est, encore une fois, invisible (et c'est tant mieux) pour les programmes clients qui peuvent se concentrer sur l'accès à des données présentées le plus simplement possible.

Figure 3 : Illustration des niveaux logique et physique

Signalons pour finir cette courte présentation que les niveaux sont en grande partie indépendants, dans le sens où l'on peut modifier complètement l'organisation du niveau physique sans avoir besoin de changer qui que ce soit aux applications qui accèdent à la base. Cette indépendance logique-physique est très précieuse pour l'administration des bases de données.

Un modèle, ce n'est pas seulement une ou plusieurs structures pour représenter l'information indépendamment de son format de stockage, c'est aussi un ou plusieurs langages pour interroger et, plus généralement, interagir avec les données (insérer, modifier, détruire, déplacer, protéger, etc.). Le langage permet de construire les commandes transmises au serveur.

Le modèle relationnel s'est construit sur des bases formelles (mathématiques) rigoureuses, ce qui explique en grande partie sa robustesse et sa stabilité depuis l'essentiel des travaux qui l'ont élaboré, dans les années 70-80. Deux langages d'interrogation, à la fois différents, complémentaires et équivalents, ont alors été définis:

1. Un langage déclaratif, basé sur la logique mathématique.
2. Un langage procédural, et plus précisément algébrique, basé sur la théorie des ensembles.

Un langage est déclaratif quand il permet de spécifier le résultat que l'on veut obtenir, sans se soucier des opérations nécessaires pour obtenir ce résultat. Un langage algébrique, au contraire, consiste en un ensemble d'opérations permettant de transformer une ou plusieurs tables en entrée en une table - le résultat - en sortie.

Ces deux approches sont très différentes. Elles sont cependant parfaitement complémentaires. l'approche déclarative permet de se concentrer sur le raisonnement, l'expression de requêtes, et fournit une définition rigoureuse de leur signification. L'approche algébrique nous donne une boîte à outil pour calculer les résultats.

Le langage SQL, assemblant les deux approches, a été normalisé sur ces bases. Il est utilisé depuis les années 1970 dans tous les systèmes relationnels, et il paraît tellement naturel et intuitif que même des systèmes construits sur une approche non relationnelle tendent à reprendre ses constructions.

Le terme SQL désigne plus qu'un langage d'interrogation, même s'il s'agit de son principal aspect. La norme couvre également les mises à jour, la définition des tables, les contraintes portant sur les données, les droits d'accès. SQL est donc le langage à connaître pour interagir avec un système relationnel.

Figure 4 : L'interface « modèle / langage » d'un système relationnel

La Fig. 4 étend le schéma précédent en introduisant SQL, qui apparaît comme le constituant central pour établir une communication entre une application et un système relationnel. Les parties grisées de cette figure sont celles couvertes par le cours. Nous allons donc étudier le modèle relationnel (représentation des données sous forme de table), le langage d'interrogation SQL sous ses deux formes, déclarative et algébrique, et l'interaction avec ce langage via un langage de programmation permettant de développer des applications.

Tout cela consitue à peu près tout ce qu'il est nécessaire de connaître pour concevoir, implanter, alimenter et interroger une base de données relationnelle, que ce soit directement ou par l'intermédiaire d'un langage de programmation.

Cette base de données décrit les pérégrinations de quelques voyageurs plus ou moins célèbres. Ces voyageurs occupent occasionnellement des logements pendant des périodes plus ou moins longues, et y exercent (ou pas) quelques activités.

Voici le schéma de la base.

• Voyageur (idVoyageur, nom, prénom, ville, région)
• Séjour (idSéjour, idVoyageur, codeLogement, début, fin)
• Logement (code, nom, capacité, type, lieu)
• Activité (codeLogement, codeActivité, description)

La table des voyageurs

Dans la table Voyageur, les voyageurs sont identifiés par un numéro séquentiel nommé idVoyageur, incrémenté de 10 en 10. On indique la ville et la région de résidence.

La table Logement

La table Logement est également très simple, voici son contenu.

L'information nommée région dans la table des voyageurs s'appelle maintenant lieu dans la table Logement. Ce n'est pas tout à fait cohérent, mais corrrespond à des situations couramment rencontrées où la même information apparaît sous des noms différents.

La table des séjours

Les séjours sont identifiés par un numéro séquentiel incrémenté par unités. Le début et la fin sont des numéros de semaine dans l'année (on fait simple, ce n'est pas une base pour de vrai).

Séjour référence le logement et le voyageur par leur identifiant. On peut voir que la valeur de idVoyageur (ou codeLogement) dans cette relation est toujours la valeur de l'un des identifiants de Voyageur (respectivement Logement).

Connaissant un séjour, je connais donc les identifiants du logement et du voyageur, et je peux trouver la description complète de ces derniers dans leur table respective.

La table Activité

Cette table contient les activités associées aux logements.

Dans les requêtes relationnelles, les variables ne désignent pas des valeurs individuelles, mais des nuplets libres. Une variable-nuplet \(t\) a donc des composants \(a_1, a_2, \dots a_n\) que l'on désigne par \(t.a_1, t.a_2, \cdots, t.a_n\). Par souci de simplicité, on nomme souvent les variables comme les attributs du schéma, mais ce n'est pas une obligation.

Commençons par étudier les requêtes utilisant une seule variable. Leur forme générale est

select [distinct] t.a1, t.a2, ..., t.an
from T as t
where <condition>

Ce « bloc » SQL comprend trois clauses: le from définit la variable libre et ce que nous appellerons la portée de cette variable, le where exprime les conditions sur la variable libre, enfin le select, accompagné du mot-clé optionnel distinct, construit le nuplet constituant le résultat.

L'interprétation est la suivante: je veux constituer tous les nuplets fermés \((t.a_1, t.a_2, \cdots, t.a_n)\) dont les valeurs satisfont les deux points suivants:

• La première, la variable \(t\) est un nuplet de la relation \(T\). Nous appelons donc cette partie la portée.
• La seconde, les conditions sur \(t\) définies le where , c'est à dire une formule logique sur \(t\), que nous appellons la condition.

Important
La portée définit les variables libres de la formule, celles pour lesquelles on va chercher l'affectation qui satisfait la condition, et à partir desquelles on va construire le nuplet-résultat.

Voyons maintenant le cas général où on s'autorise à utiliser plusieurs variables. Pour simplifier la notation, nous allons étudier les requêtes avec exactement deux variables. Il est facile ensuite de généraliser. Voici la forme d'une telle requête SQL.

select [distinct] t1.a1, ..., t1.an, t2.b1, ..., t2.bm
from T1 as t1, T2 as t2
where <condition>

L'interprétation est exactement la même que pour les requêtes mono-variables, légèrement généralisée: parmi toutes les affectations possibles des variables \(t_1\) et \(t_2\), on ne conserve que celles qui satisfont la condition exprimée par le reste de la formule.

Il n'y a rien de plus à comprendre. Il suffit de considérer toutes les affectations possibles de \(t_1\) et \(t_2\) et de ne garder que celles pour lesquelles la formule de condition est satisfaite.

Voici quelques exemples. On veut les noms des logements où on peut pratiquer le ski. Nous avons besoin de deux variables:

• la première s'affecte aux nuplets de la table Activité; on ne veut que ceux dont le code est Ski.
• la seconde s'affecte aux nuplets de la table Logement

Enfin, une condition doit lier les deux variables: on veut qu'elles soient relatives au même logement, et donc que le code logement soit identique. C'est ce qu'on appelle une jointure.

Voici la requête SQL. Remarquons au passage que le nom que l'on donne aux variables n'a aucune importance. Nous utilisons l pour le logement, a pour l'activité.

select l.code, l.nom
from Logement as l, Activité as a
where l.code = a.codeLogement
and   a.codeActivité = 'Ski'

Les seules affectations de \(l\) et \(a\) satisfaisant la formule sont marquées par des croix dans les tables ci-dessous (les champs concernés ont de plus été mis en gras). Prenez, si nécessaire, le temps de bien comprendre que d'une part la formule de condition est bien satisfaite, et d'autre part qu'il n'y a pas d'autre solution possible.

A partir de ces deux affectations, on construit le résultat.

Pour maîtriser cette partie de SQL (sans doute la plus couramment utilisée), il faut bien comprendre le mécanisme mis en œuvre. Pour construire un nuplet du résultat, nous avons besoin de 1, 2 ou plus nuplets provenant de la base. Il faut identifier ces nuplets, les conditions qu'ils doivent satisfaire, et les valeurs qu'ils partagent. Ici:

• nous avons besoin d'un nuplet de la relation Activité, tel que le code soit Ski;
• nous avons besoin d'un nuplet de la relation Logement, puisque nous souhaitons obtenir le nom du logement en sortie;
• enfin ces nuplets doivent être relatifs au même logement, et partager donc la même valeur sur l'attribut qui identifie ce logement, respectivement code dans Logement et codeLogement dans Activité.

Ce raisonnement est très général et permet d'exprimer des requêtes SQL puissantes. Les seules conditions sont de formuler rigoureusement la requête et de comprendre le schéma de la base.

Prenons un autre exemple montrant que l'on peut utiliser la même portée pour des variables différentes. On veut obtenir les paires de logements qui sont du même type. Puisqu'il nous faut deux logements, nous avons besoin de deux variables, ayant chacune pour portée la table Logement. Ces deux variables doivent partager la même valeur pour l'attribut type. Les deux variables ont été nommées respectivement \(l_1\) et \(l_2\). La syntaxe SQL est donnée ci-dessous.

select distinct l1.nom as nom1, l2.nom as nom2
from Logement as l1, Logement as l2
where l1.type = l2.type

Il existe plusieurs affectations de l1 et l2 pour lesquelles la formule est satisfaite. La première est donnée ci-dessous: l1 est affectée à la seconde ligne et l2 à la quatrième.

Mais la formule est également satisfaite si on inverse les affectations: l1 est à la quatrième ligne et l2 à la seconde.

Et, surprise, elle est également satisfaite si les deux variables sont affectées au même nuplet.

Pour éviter les inversions et auto-égalités, on peut ajouter une condition:

select distinct l1.nom as nom1, l2.nom as nom2
from Logement as l1, Logement as l2
where l1.type = l2.type
and l1.nom < l2.nom

Le résultat de cette requête est alors:

Interprétation d'une requête SQL

En résumé, quelle que soit sa complexité, l'interprétation d'une requête SQL peut toujours se faire de la manière suivante.

• Chaque variable du from peut être affectée à tous les nuplets de sa portée.
• Le where définit une condition sur ces variables: seules les affectations satisfaisant cette condition sont conservées
• Le nuplet résultat est construit à partir de ces affectations

Remarquez que ce mode d'interrogation n'indique en aucune manière, même de très loin, comment le résultat est calculé. On est (pour insister) dans une approche purement déclarative où le système est totalement libre de déterminer la méthode d'évaluation de chaque requête la plus efficace.

Reprenons simplement la requête qui demande les logements où l'on peut faire du ski. La requête donnée précédemment est la suivante:

select l.nom
from Logement as l, Activité as a
where l.code = a.codeLogement
and   a.codeActivité = 'Ski'

On remarque que la variable libre \(a\) n'est pas utilisée dans la construction du nuplet-résultat (qui ne contient que l.nom). On pourrait donc affecter le nuplet a à une variable liée, ce qui revient à formuler la requête légèrement différemment: « donnez-moi le nom des logements pour lesquels il existe une activité Ski ». Voici la syntaxe en SQL, où l'on utilise le mot-clé exists pour vérifier qu'une sous-requête a au moins un résultat.

select distinct l.nom
from Logement as l
where exists (select ''
              from Activité as a
              where l.code = a.codeLogement
              and a.codeActivité = 'Ski')

On a introduit la sous-requête suivante.

select ''
from Activité as a
where l.code = a.codeLogement
and a.codeActivité = 'Ski'

Cette sous-requête retourne un résultat dès que l'on a trouvé au moins un nuplet qui satisfait les conditions demandées, à savoir un code activité égal à Ski, et le même code logement que celui de la variable \(l\).

Le résultat est construit à partir du select de premier niveau, qui ne peut accéder qu'à la variable l, et pas à la variable (liée) a.

Cet exemple montre qu'il est possible d'exprimer une même requête avec des syntaxes différentes, que ce soit au niveau de la formulation en langage naturel ou de l'expression formelle (logique ou SQL).

Les quantificateurs permettent d'imbriquer des formules dans des formules, sans limitation de profondeur. En SQL, on peut de même avoir des imbrications de requêtes sans limitation. La lisibilité et la compréhension en sont quand même affectées.

Prenons une requête un peu plus complexe: je veux les noms des voyageurs qui sont allés dans les Alpes. Une première formulation, complètement « à plat » est la suivante:

select distinct v.prénom, v.nom
from Voyageur as v, Séjour as s, Logement as l
where v. idVoyageur=s.idVoyageur
and   s.codeLogement = l.code
and   l.lieu = 'Alpes'

Ni la variable s, ni la variable l ne sont utilisées pour construire le nuplet-résultat. On peut donc l'exprimer ainsi: « je veux les noms des voyageurs pour lesquels il existe un séjour dans les Alpes ». Ce qui donne:

select distinct v.prénom, v.nom
from Voyageur as v
where exists (select ''
              from Séjour as s, Logement as l
              where v. idVoyageur=s.idVoyageur
              and   s.codeLogement = l .code
              and   l.lieu = 'Alpes')

On pourrait même aller encore plus loin dans l'imbrication avec la requête suivante:

select distinct v.prénom, v.nom
from Voyageur as v
where exists (select ''
              from Séjour as s
              where v. idVoyageur=s.idVoyageur
              and exists (select ''
                          from  Logement as l
                           where s.codeLogement = l .code
                           and   l.lieu = 'Alpes')
               )

La troisième version correspond à la formulation « Les voyageurs tels qu'il existe un de leurs séjours tels que le logement existe dans les Alpes ». Elle n'est pas très naturelle, et, de plus, probablement la plus difficile à comprendre, ce qui ne plaide pas en sa faveur.

Il nous reste à découvrir les requêtes probablement les plus complexes, celle où l'on exprime une négation. Voici un premier exemple : on veut les logements qui ne proposent pas de Ski. En reprenant la requête « positive » étudiée précédemment, il suffit d'ajouter une négation devant le quantificateur existentiel.

select distinct l.nom
from Logement as l
where not exists (select ''
                 from Activité as a
                 where l.code = a.codeLogement
                 and a.codeActivité = 'Ski')

C'est la seule manière de l'exprimer correctement. Elle donne le résultat suivant:

Vous devriez être convaincus que la requête suivante est très différente (et ne correspond pas à ce que l'on souhaite). L'opérateur != signifie différent de en SQL.

select l.nom
from Logement as l
where  exists (select ''
                 from Activité as a
                 where l.code = a.codeLogement
                 and a.codeActivité != 'Ski')

Dont le résultat est:

Réfléchissez au sens de cette requête, trouvez le résultat sur notre petite base. Rappelez-vous que les quantificateurs servent à exprimer une condition sur un ensemble de nuplets, pas sur chaque nuplet en particulier.

Le not exists est la porte d'entrée pour exprimer le quantificateur universel, c'est à dire vérifier qu'une propriété soit satisfaite par tous les nuplets d'un ensemble. Supposons que l'on cherche les voyageurs qui sont allés dans tous les logements. On reformule cette requête avec deux négations: on cherche les voyageurs tels qu'il n'existe pas de logement où ils ne sont pas allés.

select distinct v.prénom, v.nom
from Voyageur as v
where  not exists (select ''
                   from Logement as l
                   where not exists  (select ''
                                      from Séjour as s
                                      where l.code = s.codeLogement
                                      and   v.idVoyageur = s.idVoyageur)
                   )

Vous devriez obtenir:

Vous savez maintenant tout sur la version déclarative de SQL, qui n'est rien d'autre qu'une syntaxe concrète pour exprimer des formules ouvertes sur une base de données. Tout ce qui peut s'exprimer par une formule logique est exprimable en SQL. Ni plus, ni moins. Inversement, tout ce qui ne s'exprime pas par une formule (boucles, incrémentations, etc.) ne s'exprime pas en SQL.

Dans un prochain chapitre, nous verrons la version procédurale, mais il est important de préciser qu'elle n'apporte rien en terme de possibilités d'expression. En d'autres termes, vous avez déjà, avec ce que nous venons d'étudier, la capacité d'exprimer toutes les requêtes possibles (à l'exception des agrégations). La version procédurale n'est qu'une manière alternative de concevoir l'interrogation d'une base relationnelle.

Prenez le temps de bien maîtriser ce qui précède, car la compréhension du sens de ce que l'on exprime avec les formules de logique des prédicats est la condition nécessaire et suffisante pour utiliser correctement SQL.

Le mécanisme de base consiste donc à se représenter les nuplets qui permettront de construire un des nuplets du résultat. Dans les cas les plus simples, un seul suffit. Pour la requête « Quelle est l'année de naissance de G. Depardieu » par exemple, on construit un nuplet du résulat à partir d'un nuplet de la table Artiste, dont l'attribut « nom » est « Depardieu », et dont l'attribut « âge » est l'information qui nous intéresse. On désigne ce nuplet par un nom, par exemple a. L'image mentale à construire est celle de la Fig. 6.

Figure 6 : Interrogation avec un seul nuplet

C'est très élémentaire (pour l'instant) mais toute la requête SQL est déjà codée dans cette représentation.

• Chaque nuplet désigné doit être défini dans le from.
• Les contraintes satisfaites par ce nuplet constituent le where (nom=”Depardieu”).
• La clause select est toujours triviale (on choisit les attributs à conserver).

Ce qui donne sur ce premier exemple:

select annéeNaissance
from Artiste as a
where a.nom='Depardieu'

Entrons dans le vif du sujet avec la requête « Titre des films avec pour acteur Depardieu ». Cette fois l'image mentale à construire est celle de la Fig. 7. Nous avons besoin, pour construire chaque nuplet du résultat, de trois nuplets de la base: un film, un artiste, un rôle. Dès que nous avons plusieurs nuplets, il faut indiquer de quelle manière ils sont liés: ici les liens sont (comme à peu près toujours) définis par le critère d'égalité des clés primaires et clés étrangères.

Figure 7 : Les nuplets impliqué dans la recherche des films avec Depardieu

On a donné un nom à chaque nuplet, soit f, r et a. La construction de la requête s'ensuit quasiment automatiquement.

select f.titre
from Artiste as a, Rôle as r, Film as f
where a.nom='Depardieu'
and a.idArtiste = r.idActeur
and r.idFilm = f.idFilm

Notez que les contraintes sur les nuplets sont soit des égalités entre attributs, soit l'égalité entre un attribut et une constante. Quand nous ajouterons la négation, un troisième type de contrainte apparaîtra, celui de l'existence ou non d'un résultat pour une sous-requête.

Remarquez également comment on se repose sur l'interpéteur SQL pour faire l'essentiel du travail: trouver les nuplets satisfaisant les constraintes, énumérer toutes les combinaisons valides à partir de la base, et construire le résultat.

Voici un exemple un peu plus compliqué qui ne change rien au raisonnement: on veut les titres de film avec Depardieu et Deneuve. L'image à construire est celle de la Fig. 8. Ici il faut concevoir qu'il nous faut deux nuplets de la table Artiste, l'un avec pour nom Depardieu (a1), et l'autre avec pour nom Deneuve (a2). Ces deux nuplets sont liés à deux nuplets distincts de la table Rôle, nommons-les r1 et r2. Ces deux derniers nuplets sont liés au même film f .

Figure 8 : Les nuplets impliqué dans la recherche des films avec Depardieu et Deneuve

À partir de la Fig. 8, la construction syntaxique de la requête SQL est encore une fois directe: énumération des variables-nuplets dans le from, contraintes dans le where, clause select selon les besoins.

select *
from Artiste as a1, Artiste as a2, Rôle as r1, Rôle as r2, Film as f
where a1.nom='Depardieu'
and a2.nom='Deneuve'
and a1.idArtiste = r1.idActeur
and a2.idArtiste = r2.idActeur
and r1.idFilm = f.idFilm
and r2.idFilm = f.idFilm

Voici deux exemples complémentaires. Le premier recherche les films réalisés par Q. Tarantino en 1994. L'image mentale est celle de la Fig. 9.

Figure 9 : Recherche les films réalisés par Q. Tarantino en 1994

La requête correspondante est bien entendu celle-ci.

select *
from Artiste as a, Film as f
where a.nom='Tarantino'
and  f.année = 1994
and a.idArtiste = f.idRéalisateur

Le second exemple recherche les films réalisés par Q. Tarantino en 1994 dans lesquels il joue lui-même dans tant qu'acteur. Je vous laisse étudier et interpréter la Fig. 10 et exprimer vous-même la requête SQL.

Figure 10 : Recherche les films réalisés par Q. Tarentino en 1994 dans lequels il joue

Que se passe-t-il en cas de requête imbriquée, et surtout en cas de nécessité d'exprimer une négation? Les principes précédents restent valables: on identifie les nuplets de la base qui permettent de produire un nuplet du résultat, on construit la requête comme précédemment, et la requête imbriquée n'est qu'une contrainte supplémentaire sur ces nuplets. La seule particularité des requêtes imbriquées est que la contrainte porte sur un ensemble, et pas sur une valeur atomique.

Prenons un exemple: je veux les titres de film avec Catherine Deneuve mais sans Gérard Depardieu. On commence par la solution partielle qui consiste à trouver les films avec Deneuve

select f.titre
from Film as f, Rôle as r, Artiste as a
where f.idFilm=r.idFilm
and r.idActeur = a.idArtiste
and a.nom='Deneuve'

Maintenant on ajoute la contrainte suivante sur le film f: dans l'ensemble des acteurs du film f, on ne doit pas trouver Gérard Depardieu. L'ensemble des acteurs du film f qui se nomment Depardieu est obtenu par une requête fonction de f, cette requête est ajoutée dans le where et on obtient la requête complète

select f.titre
from Film as f, Rôle as r, Artiste as a
where f.idFilm=r.idFilm
and r.idActeur = a.idArtiste
and a.nom='Deneuve'
and not exists (select * from Rôle as r2, Artiste as a2
                 where f.idFilm=r2.idFilm and r2.idActeur=a2.idActeur
                 and a2.nom='Depardieu')

Il faut bien être conscient que cette condition supplémentaire porte sur le film f, et que f doit impérativement intervenir dans la requête imbriquée. La requête suivante par exemple est fausse:

select f.titre
from Film as f, Rôle as r, Artiste as a
where f.idFilm=r.idFilm
and r.idActeur = a.idArtiste
and a.nom='Deneuve'
and not exists (select * from Rôle as r2, Artiste as a2
                 where r2.idActeur=a2.idActeur
                 and a2.nom='Depardieu')

La requête imbriquée est ici indépendante des nuplets de la variable principale, et on peut donc évaluer son résultat dès le début: soit il existe un acteur nommé Depardieu (quel que soit le film), le not exists est toujours faux et le résultat est toujours vide; soit il n'en existe pas, le not exists est toujours vrai et ne sert donc à rien.

Reste à discuter de la disjonction. Il existe une propriété assez utile des formules logiques: on peut toujours les mettre sous une forme dite « normale disjonctive », autrement dit comme la disjonction de conjonctions. En pratique cela implique que toute requête comprenant un « ou » peut s'écrire comme l'union de requêtes conjonctives écrites sans « ou ». Cherchons les films avec Deneuve ou Depardieu.

select f.titre
from Film as f, Rôle as r, Artiste as a
where f.idFilm=r.idFilm
and r.idActeur = a.idArtiste
and a.nom='Deneuve'
  union
select f.titre
from Film as f, Rôle as r, Artiste as a
where f.idFilm=r.idFilm
and r.idActeur = a.idArtiste
and a.nom='Depardieu'

Ce n'est pas très concis. Il est à peu près toujours possible de trouver une formulation plus condensée avec le « or ». Ici ce serait:

select f.titre
from Film as f, Rôle as r, Artiste as a
where f.idFilm=r.idFilm
and r.idActeur = a.idArtiste
and (a.nom='Deneuve' or nom='Depardieu')

Il n'existe pas de règle générale permettant de trouver la bonne formulation sans réfléchir. La bonne maîtrise des principes de logique, d'équivalence de formule et d'interprétation sont les connaissances clés.

Les principes exposés ici sont très importants. Même s'ils peuvent vous sembler parfois éloignés de vos objectifs pratiques, tout ce qui précède devrait j'espère vous convaincre que maîtriser SQL, c'est d'abord être capable d'aborder la formulation des requêtes de manière rigoureuse, pas de produire une syntaxe finalement relativement simple. À vous de jouer.

L'espace de recherche est défini dans la clause from par une ou plusieurs tables. Par « table » il ne faut pas ici comprendre forcément « une des tables de la base » courante même si c'est le cas le plus souvent rencontré. SQL est beaucoup général que cela: une table dans un from peut également être résultat d'une autre requête. On parlera de table basée et de table calculée pour distinguer ces deux cas. Ce peut également être une table stockée dans une autre base ou une table calculée à partir de tables basées dans plusieurs bases ou une combinaison de tout cela.

Voici une première requête qui ramène les immeubles dont l'id vaut 1.

select nom, adresse
from Immeuble
where id=1

Il n'aura pas échappé au lecteur attentif que le résultat est lui-même une table (calculée et non basée). Pourquoi ne pourrait-on pas interroger cette table calculée comme une autre? C'est possible en SQL comme le montre l'exemple suivant:

select *
from (select nom, adresse from Immeuble where id=1) as Koudalou

On a donc placé une requête SQL dans le from où elle définit un espace de recherche constitué de son propre résultat. Le mot-clé as permet de donner un nom temporaire au résultat. En d'autres termes Koudalou est le nom de la table calculée sur laquelle s'effectue la requête. Cette table temporaire n'existe que pendant l'exécution.

L'interprétation du from est indépendante de l'origine des tables: tables basées, tables calculées. Comme nous l'avons vu dans les chapitres précédents, il existe deux manières de spécifier l'espace de recherche avec le from. La première est la forme déclarative dans laquelle on sépare le nom des tables par des virgules.

select * from Immeuble as i, Appart as a

Dans ce cas, le nom d'une table sert à définir une variable nuplet (voir chapitre SQL, langage déclaratif) à laquelle on peut affecter tous les nuplets de la table. Les variables peuvent être explicitement nommées avec la mot-clé as (elles s'appellent i et a dans la requête ci-dessus). On peut aussi omettre le as, dans ce cas le nom de la variable est (implcitement) le nom de la table.

select * from Immeuble, Appart

Un cas où le as est obligatoire est l'auto-jointure: on veut désigner deux nuplets de la même table. Exemple: on veut les paires d'appartement du même immeuble.

select a1.no, a2.no
from Appart as a1, Appart as a2
where a1.idImmeuble = a2.idImmeuble

En l'absence du as et de l'utilisation du nom de la variable comme préfixe, il y aurait ambiguité sur le nom des attributs.

La deuxième forme du from définit l'espace de recherche par une opération algébrique, nous aborderons ce point dans le chapitre suivant.

Dernière précision au sujet du from: l'ordre dans lequel on énumère les tables n'a aucune importance.

La clause where permet d'exprimer des conditions portant sur les nuplets désignés par la clause from. Ces conditions suivent en général la syntaxe expr1 [not] \(\Theta\) expr2, où expr1 et expr2 sont deux expressions construites à partir de noms d'attributs, de constantes et de fonctions, et \(\Theta\) est l'un des opérateurs de comparaison classique < > <= >= != ou =.

Les conditions se combinent avec les connecteurs booléens and or et not. SQL propose également un prédicat in qui teste l'appartenance d'une valeur à un ensemble. Il s'agit (du moins tant qu'on n'utilise pas les requêtes imbriquées) d'une facilité d'écriture pour remplacer le or. La requête

select *
from Personne
where profession='Acteur'
or profession='Rentier'

s'écrit de manière équivalente avec un in comme suit:

select *
from Personne
where profession in ('Acteur', 'Rentier')

Pour les chaînes de caractères, SQL propose l'opérateur de comparaison like, avec deux caractères de substitution:

• le « % » remplace n'importe quelle sous-chaîne;
• le « _ » remplace n'importe quel caractère.

L'expression _ou%ou est donc interprétée par le like comme toute chaîne commençant par un caractère suivi de « ou » suivi de n'importe quelle chaîne suivie une nouvelle fois de « ou ».

select *
from Immeuble
where nom like '_ou%ou'

Il est également possible d'exprimer des conditions sur des tables calculées par d'autre requêtes SQL incluses dans la clause where et habituellement désignées par le terme de « requêtes imbriquées ». On pourra par exemple demander la liste des personnes dont l'appartement fait partie de la table calculée des appartements situés au-dessus du troisième niveau.

select * from Personne
where idAppart in (select id from Appart where niveau > 3)

Avec les requêtes imbriquées, on entre dans le monde incertain des requêtes qui semblent claires mais finissent par ne plus l'être du tout. La difficulté vient souvent du fait qu'il faut raisonner simultanément sur plusieurs requêtes qui, de plus, sont souvent interdépendantes (les données sélectionnées dans l'une servent de paramètre à l'autre). Il est très souvent possible d'éviter les requêtes imbriquées comme nous l'expliquons dans ce chapitre.

En théorie, dans une table relationnelle, tous les attributs ont une valeur. En pratique, certaines valeurs peuvent être inconnues ou manquantes: on dit qu'elles sont à null. Le null n'est pas une valeur spéciale, c'est une absence de valeur.

Il est impossible de déterminer quoi que ce soit à partir d'une valeur à null. Dans le cas des comparaisons, la présence d'un null renvoie un résultat qui n'est ni true ni false mais unknown, une valeur booléenne intermédiaire. Reprenons à nouveau la table Personne avec un des prénoms à null. La requête suivante devrait ramener tous les nuplets.

select *
from Personne
where prénom like '%'

Mais la présence d'un null empêche l'inclusion du nuplet correspondant dans le résultat.

Cependant la condition like n'a pas été évaluée à true comme le montre la requête suivante.

select  *
from Personne
where prénom not like  '%'

On obtient un résultat vide, ce qui montre bien que le like appliqué à un null ne renvoie pas false (car sinon on aurait not false = true). C'est d'ailleurs tout à fait normal puisqu'il n'y a aucune raison de dire qu'une absence de valeur ressemble à n'importe quelle chaîne.

Les tables de vérité de la logique trivaluée de SQL sont définies de la manière suivante. Tout d'abord on affecte une valeur aux trois constantes logiques:

• true vaut 1
• false vaut 0
• unknown vaut 0.5

Les connecteurs booléens s'interprètent alors ainsi:

• val1 and val2, = min(val1 val2)
• val1 or val2 = max(val1, val2)
• not val1 = 1 - val1.

On peut vérifier notamment que not unknown vaut toujours unknown. Ces définitions sont claires et cohérentes. Cela étant il faut mieux prévenir de mauvaises surprises avec les valeurs à null, soit en les interdisant à la création de la table, soit en utilisant le test is null (ou son complément is not null). La requête ci-dessous ramène tous les nuplets de la table, même en présence de null.

select *
from Personne
where prénom like '%'
or prénom is null

Attention le test valeur = null n'a pas de sens. On ne peut pas être égal à une absence de valeur.

Finalement, une fois obtenus les nuplets du from qui satisfont le where, on crée à partir de ces nuplets le résultat final avec les expressions du select.

Si on indique explicitement les attributs au lieu d'utiliser *, leur nombre détermine le nombre de colonnes de la table calculée. Le nom de chaque attribut dans cette table est par défaut l'expression du select mais on peut indiquer explicitement ce nom avec as. Voici un exemple qui illustre également une fonction assez utile, la concaténation de chaînes.

select concat(prénom, ' ', nom) as 'nomComplet'
from Personne

Le résultat montre que l'une des valeurs est à null. Logiquement toute opération appliquée à un null renvoie un null en sortie puisqu'on ne peut calculer aucun résultat à partir d'une valeur inconnue. Ici c'est le prénom de l'une des personnes qui manque. La concaténation du prénom avec le nom est une opération qui « propage » cette valeur à null. Dans ce cas, il faut utiliser une fonction (spécifique à chaque système) à qui remplace la valeur à null par une valeur de remplacement. Voici la version MySQL (fonction ifnull(attribut, remplacement)).

select concat(ifnull(prénom,' '), ' ', nom) as 'nomComplet'
from Personne

Une « expression » dans la clause select désigne ici, comme dans tout langage, une construction syntaxique qui prend une ou plusieurs valeurs en entrée et produit une valeur en sortie. Dans sa forme la plus simple, une expression est simplement un nom d'attribut ou une constante comme dans l'exemple suivant.

select surface, niveau, 18 as 'EurosParm2'
from Appart

Les attributs surface et niveau proviennent de Appart alors que 18 est une constante qui sera répétée autant de fois qu'il y a de nuplets dans le résultat. De plus, on peut donner un nom à cette colonne avec la commande as. Voici un second exemple qui montre une expression plus complexe. L'utilisateur (certainement un agent immobilier avisé et connaissant bien SQL) calcule le loyer d'un appartement en fonction d'une savante formule qui fait intervenir la surface et le niveau.

select no, surface, niveau,
        (surface * 18) * (1 + (0.03 * niveau)) as loyer
from Appart

SQL fournit de très nombreux opérateurs et fonctions de toute sorte qui sont clairement énumérées dans la documentation de chaque système. Elles sont particulièrement utiles pour des types de données un peu délicat à manipuler comme les dates.

Une extension rarement utilisée consiste à effectuer des tests sur la valeur des attributs à l'intérieur de la clause select avec l'expression case dont la syntaxe est:

case
  when test then expression
  [when ...]
  else  expression
end

Ces tests peuvent être utilisés par exemple pour effectuer un décodage des valeurs quand celles-ci sont difficiles à interpréter ou quand on souhaite leur donner une signification dérivée. La requête ci-dessous classe les appartements en trois catégories selon la surface.

 select no, niveau, surface,
       case when surface <= 50 then 'Petit'
            when surface > 50 and surface <= 100 then 'Moyen'
            else 'Grand'
       end as categorie
from Appart

La jointure est une opération indispensable dès que l'on souhaite combiner des données réparties dans plusieurs tables. Nous avons déjà étudié en détail la conception et l'expression des jointures. On va se contenter ici de montrer quelques exemples en forme de récapitulatif, sur notre base d'immeubles.

SQL renvoie les nuplets du résultat sans se soucier de la présence de doublons. Si on cherche par exemple les surfaces des appartements avec

select surface
from Appart

on obtient le résultat suivant.

On a autant de fois une valeur qu'il y a de nuplets dans le résultat intermédiaire après exécution des clauses from et where. En général, on ne souhaite pas conserver ces nuplets identiques dont la répétition n'apporte aucune information. Le mot-clé distinct placé juste après le select permet d'éliminer ces doublons.

select distinct surface
from Appart

Le distinct est à éviter quand c'est possible car l'élimination des doublons peut entraîner des calculs coûteux. Il faut commencer par calculer entièrement le résultat, puis le trier ou construire une table de hachage, et enfin utiliser la structure temporaire obtenue pour trouver les doublons et les éliminer. Si le résultat est de petite taille cela ne pose pas de problème. Sinon, on risque de constater une grande différence de temps de réponse entre une requête sans distinct et la même avec distinct.

On peut demander explicitement le tri du résultat sur une ou plusieurs expressions avec la clause order by qui vient toujours à la fin d'une requête select. La requête suivante trie les appartements par surface puis, pour ceux de surface identique, par niveau.

select *
from Appart
order by surface, niveau

Par défaut, le tri est en ordre ascendant. On peut inverser l'ordre de tri d'un attribut avec le mot-clé desc.

select *
from Appart
order by surface desc,  niveau desc

Bien entendu, on peut trier sur des expressions au lieu de trier sur de simples noms d'attribut.

Reprenons l'exemple de la requête trouvant la surface et le niveau de l'appartement de M. Simplet. On peut l'exprimer avec une requête imbriquée de deux manières. La première est la forme déclarative classique.

select surface, niveau
from Appart as a, Personne as p
where p.prénom='Barnabé' and p.nom='Simplet'
and a.id = p.idAppart

On remarque qu'aucun attribut de la table Personne n'est utilisé pour construire le résultat. On peut donc utiliser une sous-requête (ou requête imbriquée).

select surface, niveau
from Appart
where  id  in (select idAppart
               from Personne
               where prénom='Barnabé' and nom='Simplet')

Le mot-clé in exprime la condition d'appartenance de l'identifiant de l'appartement à l'ensemble d'identifiants constitué avec la requête imbriquée. Il doit y avoir correspondance entre le nombre et le type des attributs auxquels s'applique la comparaison par in. L'exemple suivant montre une comparaison entre des paires d'attributs (ici on cherche des informations sur les propriétaires).

select prénom, nom, surface, niveau
from   Appart as a, Personne as p
where  a.id = p.idAppart
and    (p.id, p.idAppart)
          in (select idPersonne, idAppart from Propriétaire)

Il est bien entendu assez direct de réécrire la requête ci-dessus comme une jointure classique (exercice). Parfois l'expression avec requête imbriquée peut s'avérer plus naturelle. Supposons que l'on cherche les immeubles dans lesquels on trouve un appartement de 50 m2. Voici l'expression avec requête imbriquée.

select *
from Immeuble
where id in (select idImmeuble from Appart where surface=50)

La requête directement réécrite en jointure donne le résultat suivant:

select i.*
from   Immeuble as i,Appart as a
where  i.id=a.idImmeuble
and    surface=50

On obtient deux fois le même immeuble puisqu'il peut être associé à deux appartements différents de 50 m2. Il suffit d'ajouter un distinct après le select pour régler le problème, mais on peut considérer que dans ce cas la requête imbriquée est plus appropriée. Attention cependant: il n'est pas possible de placer dans le résultat des attributs appartenant aux tables des requêtes imbriquées.

Le principe général des requêtes imbriquées est d'exprimer des conditions sur des tables calculées par des requêtes. Cela revient, dans le cadre formel qui soutient SQL, à appliquer une quantification sur une collection constituée par une requête.

Ces conditions sont les suivantes:

• exists R: renvoie true si R n'est pas vide false sinon.
• t in R où est un nuplet dont le type (le nombre et le type des attributs) est celui de R: renvoie true si t appartient à R false sinon.
• v cmp any R où cmp est un comparateur SQL (< > = etc.): renvoie true si la comparaison avec au moins un des nuplets de la table R renvoie true.
• v cmp all R où cmp est un comparateur SQL (< > = etc.): renvoie true si la comparaison avec tous les nuplets de la table R renvoie true.

De plus toutes ces expressions peuvent être préfixées par not pour obtenir la négation. La richesse des expressions possibles permet d'effectuer une même interrogation en choisissant parmi plusieurs syntaxes possibles. En général, tout ce qui n'est pas basé sur une négation not in ou not exists peut s'exprimer sans requête imbriquée.

Le all peut se réécrire avec une négation puisque si une propriété est toujours vraie il n'existe pas de cas où elle est fausse. La requête ci-dessous applique le all pour chercher le niveau le plus élevé de l'immeuble 1.

select * from Appart
    where idImmeuble=1
    and    niveau >= all (select niveau from Appart where idImmeuble=1)

Le all exprime une comparaison qui vaut pour toutes les nuplets ramenés par la requête imbriquée. La formulation avec any s'écrit:

select * from Appart
    where idImmeuble=1
    and   not (niveau < any (select niveau from Appart where idImmeuble=1))

Rien de nouveau du point de vue expressif: on peut prendre les étages tels qu'il n'existe pas un niveau supérieur.

select * from Appart as a1
    where a1.idImmeuble=1
    and    not exists (select *
                    from Appart as a2
                    where a2.idImmeuble=1 and a1.niveau < a2.niveau)

Attention aux valeurs à null dans ce genre de situation: toute comparaison avec une de ces valeurs renvoie unknown et cela peut entraîner l'échec du all. Il n'existe pas d'expression avec jointure qui puisse exprimer ce genre de condition.

Les exemples de requêtes imbriquées donnés précédemment pouvaient être évalués indépendamment de la requête principale, ce qui permet au système (s'il le juge nécessaire) d'exécuter la requête en deux phases. La clause exists fournit encore un nouveau moyen d'exprimer les requêtes vues précédemment en basant la sous-requête sur une ou plusieurs valeurs issues de la requête principale. On parle alors de requêtes correlées.

Voici encore une fois la recherche de l'appartement de M. Barnabé Simplet exprimée avec exists:

select * from Appart
where  exists  (select * from Personne
                where  prénom='Barnabé' and nom='Simplet'
                and    Personne.idAppart=Appart.id)

On obtient donc une nouvelle technique d'expression qui permet d'aborder le critère de recherche sous une troisième perspective: on conserve un appartement si, pour cet appartement, l'occupant s'appelle Barnabé Simplet. Il s'agit assez visiblement d'une jointure mais entre deux tables situées dans des requêtes (ou plutôt des « blocs ») distinctes. La condition de jointure est appelée corrélation d'où le nom de ce type de technique.

Les jointures dans lesquelles le résultat est construit à partir d'une seule table peuvent d'exprimer avec exists ou in. Voici quelques exemples reprenant des requêtes déjà vues précédemment.

Qui habite un appartement de plus de 200 m2?

Avec in:

select prénom, nom, profession
from Personne
where idAppart in (select id from Appart where surface >= 200)

Avec exists:

select prénom, nom, profession
from Personne  p
where exists (select * from Appart a
              where a.id=p.idAppart
              and surface >= 200)

Qui habite le Barabas?

Avec in:

select prénom, nom, no, surface, niveau
from   Personne as p, Appart as a
where  p.idAppart=a.id
and a.idImmeuble in
         (select id from Immeuble
          where  nom='Barabas')

Avec exists:

select prénom, nom, no, surface, niveau
from Personne as p, Appart as a
where  p.idAppart=a.id
and exists (select * from Immeuble i
            where  i.id=a.idImmeuble
            and i.nom='Barabas')

Important
Dans une sous-requête associée à la clause exists peu importent les attributs du select puisque la condition se résume à: cette requête ramène-t-elle au moins un nuplet ou non? On peut donc systématiquement utiliser select * ou select ''

Enfin rien n'empêche d'utiliser plusieurs niveaux d'imbrication au prix d'une forte dégradation de la lisibilité. Voici la requête « De quel(s) appartement(s) Alice Grincheux est-elle propriétaire et dans quel immeuble? » écrite avec plusieurs niveaux.

select i.nom, no, niveau, surface
from  Immeuble as i, Appart as a
where  a.idImmeuble= i.id
and    a.id in
             (select idAppart
              from Propriétaire
              where idPersonne in
                       (select id
                        from Personne
                        where  nom='Grincheux'
                        and prénom='Alice'))

En résumé une jointure entre les tables R et S de la forme:

select R.*
from R S
where R.a = S.b

peut s'écrire de manière équivalente avec une requête imbriquée:

select [distinct] *
from R
where R.a in (select S.b from S)

ou bien encore sous forme de requête corrélée:

select [distinct] *
from R
where exists (select S.b from S where S.b = R.a)

Le choix de la forme est matière de goût ou de lisibilité, ces deux critères relevant de considérations essentiellement subjectives.

Les sous-requêtes sont en revanche irremplaçables pour exprimer des négations. On utilise alors not in ou (de manière équivalente) not exists. Voici un premier exemple avec la requête: donner les appartements sans occupant.

select * from Appart
where  id not in (select idAppart from Personne)

On obtient comme résultat.

La négation est aussi un moyen d'exprimer des requêtes courantes comme celle recherchant l'appartement le plus élevé de son immeuble. En SQL, on utilisera typiquement une sous-requête pour prendre le niveau maximal d'un immeuble, et on utilisera cet niveau pour sélectionner un ou plusieurs appartements, le tout avec une requête correlée pour ne comparer que des appartements situés dans le même immeuble.

select *
from Appart as a1
where niveau =  (select max(niveau) from Appart as a2
                where a1.idImmeuble=a2.idImmeuble)

Il existe en fait beaucoup de manières d'exprimer la même chose. Tout d'abord cette requête peut en fait s'exprimer sans la fonction max() avec la négation: si a est l'appartement le plus élevé, c'est qu'il n'existe pas de niveau plus elevé que a. On utilise alors habituellement une requête dite « corrélée » dans laquelle la sous-requête est basée sur une ou plusieurs valeurs issues des tables de la requête principale.

select *
from Appart as a1
where not exists  (select * from Appart as a2
                   where a2.niveau > a1.niveau
                   and a1.idImmeuble = a2.idImmeuble)

Autre manière d'exprimer la même chose: si le niveau est le plus élevé, tous les autres sont situés à un niveau inférieur. On peut utiliser le mot-clé all qui indique que la comparaison est vraie avec tous les éléments de l'ensemble constitué par la sous-requête.

select *
from Appart as a1
where niveau >= all (select niveau from Appart as a2
                where a1.idImmeuble=a2.idImmeuble)

Dernier exemple de négation: quels sont les personnes qui ne possèdent aucun appartement même partiellement? Les deux formulations ci-dessous sont équivalentes, l'une s'appuyant sur not in, et l'autre sur not exists.

select *
from Personne
where id not in (select idPersonne from Propriétaire)

select *
from Personne as p1
where not exists (select * from Propriétaire as p2
                  where p1.id=p2.idPersonne)

Le rôle du group by est de partitionner le résultat d'un bloc select from where en fonction d'un critère (un ou plusieurs attributs, ou plus généralement une expression sur des attributs). Pour bien analyser ce qui se passe pendant une requête avec group by on peut décomposer l'exécution d'une requête en deux étapes. Prenons l'exemple de celle permettant de vérifier que la somme des quote-part des propriétaires est bien égale à 100 pour tous les appartements.

select  idAppart, sum(quotePart) as totalQP
from    Propriétaire
group by idAppart

Dans une première étape le système va constituer les groupes. On peut les représenter avec un tableau comprenant, pour chaque nuplet, d'une part la (ou les) valeur(s) du (ou des) attribut(s) de partitionnement (ici idAppart), d'autre part l'ensemble de nuplets dans lesquelles on trouve cette valeur. Ces nuplets « imbriqués » sont séparés par des points-virgule dans la représentation ci-dessous.

Le groupe associé à l'appartement 100 est constitué de deux copropriétaires. Le tableau ci-dessus n'est donc pas une table relationnelle dans laquelle chaque cellule ne peut contenir qu'une seule valeur.

Pour se ramener à une table relationnelle, on transforme durant la deuxième étape chaque groupe de nuplets en une valeur par application d'une fonction d'agrégation. La fonction count() compte le nombre de nuplets dans chaque groupe, max() donne la valeur maximale d'un attribut parmi l'ensemble des nuplets du groupe, etc. La liste des fonctions d'agrégation est donnée ci-dessous:

• count(expression), Compte le nombre de nuplets pour lesquels expression est not null.
• avg(expression), Calcule la moyenne de expression.
• min(expression), Calcule la valeur minimale de expression.
• max(expression), Calcule la valeur maximale de expression.
• sum(expression), Calcule la somme de expression.
• std(expression), Calcule l'écart-type de expression.

Dans la norme SQL l'utilisation de fonctions d'agrégation pour les attributs qui n'apparaissent pas dans le group by est obligatoire. Une requête comme:

select  id, surface, max(niveau) as niveauMax
from    Appart
group by surface

sera rejetée parce que le groupe associé à une même surface contient deux appartements différents (et donc deux valeurs différentes pour id), et qu'il n'y a pas de raison d'afficher l'un plutôt que l'autre.

Calculons la surface totale des appartements, groupés par immeuble. Décomposons: nous avons d'abord besoin du bloc « select - from - where » avec les identifants d'immeubles et les surfaces d'appartement.

select idImmeuble, surface
from Appart

On ajoute à cette requête la clause group by pour grouper par immeuble. On obtient alors (en phase intermédiaire) deux groupes d'appartements, un pour chaque immeuble. Il reste à appliquer une fonction d'agrégation pour ramener ces groupes à une valeur atomique.

select idImmeuble, sum(surface) as totalSurface
from Appart
group by idImmeuble

On pourrait aussi appliquer d'autres fonctions d'agrégation:

select idImmeuble, min(niveau) as minEtage, max(niveau) as maxEtage,
         sum(surface) as totalSurface
from Appart
group by idImmeuble

Revenons un moment à nos voyageurs et à leurs séjours. La requête ci-dessous doit être claire. Exécutez-la sur le site: on constate qu'un voyageur a effectué plusieurs séjours et qu'un logement a reçu plusieurs voyageurs.

select v.nom as nomVoyageur, l.nom as nomLogement
from Voyageur as v, Séjour as s, Logement as l
where v.idVoyageur = s.idVoyageur
and l.code = s.codeLogement

En ajoutant une clause group by on produit des statistiques sur le résultat de cette requête. Par exemple, en groupant sur les voyageurs

select v.nom as nomVoyageur, count(*) as 'nbSéjours'
from Voyageur as v, Séjour as s, Logement as l
where v.idVoyageur = s.idVoyageur
and l.code = s.codeLogement
group by v.idVoyageur

Ou en groupant sur les logements

select l.nom as nomLogement, count(*) as 'nbVoyageurs'
from Voyageur as v, Séjour as s, Logement as l
where v.idVoyageur = s.idVoyageur
and l.code = s.codeLogement
group by l.code

On peut aussi regrouper sur plusieurs attributs. Pour obtenir par exemple le nombre de séjours effectués par un voyageur dans un même logement.

select l.nom as nomLogement, v.nom as 'nomVoyageur', count(*) as 'nbSéjours'
from Voyageur as v, Séjour as s, Logement as l
where v.idVoyageur = s.idVoyageur
and l.code = s.codeLogement
group by l.code, v.idVoyageur

Moralité: à partir d'une requête SQL select - from -- where, aussi complexe que nécessaire, vous produisez un résultat (une table). Le group by permet d'effectuer des regroupements et des agrégations (simples) sur ce résultat. Il s'agit vraiment d'un complément au SQL.

Finalement, on peut faire porter des conditions sur les groupes, ou plus précisément sur le résultat de fonctions d'agrégation appliquées à des groupes avec la clause having. Par exemple, on peut sélectionner les appartements pour lesquels on connaît au moins deux copropriétaires.

select  idAppart, count(*) as nbProprios
from    Propriétaire
group by idAppart
having count(*) >= 2

On voit que la condition porte ici sur une propriété de l”/ensemble/ des nuplets du groupe et pas de chaque nuplet pris individuellement. La clause having est donc toujours exprimée sur le résultat de fonctions d'agrégation, par opposition avec la clause where qui ne peut exprimer des conditions que sur les nuplets pris un à un.

Important // La requête ci-dessus pourrait s'exprimer en utilisant l'alias pour éviter d'avoir à répeter deux fois le count(*) (et pour la rendre plus claire).

select  idAppart, count(*) as nbProprios
from    Propriétaire
group by idAppart
having nbProprios >= 2

Il n'est malheureusement pas sûr que l'utilisation de l'alias dans le group by soit acceptée dans tous les systèmes.

Quelques exemples pour conclure. Toujours sur la base des voyageurs: quels voyageurs ont effectué au moins deux séjours ?

select v.nom as nomVoyageur, count(*) as 'nbSéjours'
from Voyageur as v, Séjour as s, Logement as l
where v.idVoyageur = s.idVoyageur
and l.code = s.codeLogement
group by v.idVoyageur
having count(*) > 1

Quels logements proposent moins de deux activités.

select l.nom
from Logement as l, Activité as a
where l.code = a.codeLogement
group by l.code
having count(*) < 2

Voici enfin une requête un peu complexe (sur la base des immeubles) sélectionnant la surface possédée par chaque copropriétaire pour l'immeuble 1. La surface possédée est la somme des surfaces d'appartements possédés par un propriétaire, pondérées par leur quote-part. On regroupe par propriétaire et on trie sur la surface possédée.

select prénom nom,
       sum(quotePart * surface / 100) as 'surfacePossédée'
from Personne as p1, Propriétaire as p2, Appart as a
where p1.id=p2.idPersonne
and  a.id=p2.idAppart
and  idImmeuble = 1
group by p1.id
order by sum(quotePart * surface / 100)

On obtient le résultat suivant.

La projection \(\pi_{A_1, A_2, \ldots,A_k}(R)\) s'applique à une relation \(R\), et construit une relation contenant tous les nuplets de \(R\), dans lesquels seuls les attributs \(A_1, A_2, \ldots A_k\) sont conservés. La requête suivante construit une relation avec le nom des logements et leur lieu.

\[\pi_{nom, lieu}(Logement)\]

On obtient le résultat suivant, après suppression des colonnes id, capacité et type :

En SQL, le projection s'exprime avec le select suivi de la liste des attributs à projeter.

select nom, lieu
from Logement

C'est un habillage syntaxique direct de la projection.

Si on souhaite conserver tous les attributs, on peut éviter d'en énumérer la liste en la remplaçant par *.

select *
from Logement

La sélection \(\sigma_F(R)\) s'applique à une relation, \(R\), et extrait de cette relation les nuplets qui satisfont un critère de sélection, \(F\). Ce critère peut être :

• La comparaison entre un attribut de la relation, \(A\), et une constante \(a\). Cette comparaison s'écrit \(A \Theta a\), où \(\Theta\) appartient à \(\{=, <, >, \leq, \geq\}\).
• La comparaison entre deux attributs \(A_1\) et \(A_2\), qui s'écrit \(A_1 \Theta A_2\) avec les mêmes opérateurs de comparaison que précédemment.

Premier exemple : exprimer la requête qui donne tous les logements en Corse.

\[\sigma_{lieu='Corse'}(Logement)\]

On obtient donc le résultat :

La sélection a pour effet de supprimer des nuplets, mais chaque nuplet garde l'ensemble de ses attributs. Il ne peut pas y avoir de problème de doublon (pourquoi?) et il ne faut donc surtout par appliquer un distinct.

En SQL, les critères de sélection sont exprimés par la clause where.

select *
from Logement
where lieu = 'Corse'

Les chaînes de caractères doivent impérativement être encadrées par des apostrophes simples, sinon le système ne verrait pas la différence avec un nom d'attribut. Ce n'est pas le cas pour les numériques, car aucun nom d'attribut ne peut commencer par un chiffre.

select *
from Logement
where capacité = 134

Le premier opérateur binaire, et le plus utilisé, est le produit cartésien, \(\times\). Le produit cartésien entre deux relations \(R\) et \(S\) se note \(R \times S\), et permet de créer une nouvelle relation où chaque nuplet de \(R\) est associé à chaque nuplet de \(S\).

Voici deux relations, la première, \(R\), contient

et la seconde, \(S\), contient :

Et voici le résultat de \(R \times S\) :

Le nombre de nuplets dans le résultat est exactement \(|R| \times |S|\) (\(|R|\) dénote le nombre de nuplets dans la relation \(R\)).

En lui-même, le produit cartésien ne présente pas un grand intérêt puisqu'il associe aveuglément chaque nuplet de \(R\) à chaque nuplet de \(S\). Il ne prend vraiment son sens qu'associé à l'opération de sélection, ce qui permet d'exprimer des jointures, opération fondamentale qui sera détaillée plus loin.

En SQL, le produit cartésien est un opérateur cross join intégré à la clause from.

select *
from R cross join S

C'est la première fois que nous rencontrons une expression à l'intérieur du from en lieu et place de la simple énumération par une virgule. Il y a une logique certaine à ce choix: dans la mesure où R cross join S définit une nouvelle relation, la requête SQL peut être vue comme une requête sur cette seule relation, et nous sommes ramenés au cas le plus simple.

Comme illustration de ce principe, voici le résultat du produit cartésien \(Logement \times Activit\acute e\) (en supprimant l'attribut description pour gagner de la place).

C'est une relation (tout est relation en relationnel) et on peut bien imaginer interroger cette relation comme n'importe quelle autre. C'est exactement ce que fait la requête SQL suivante.

select *
from Logement cross join Activité

Jusqu'à présent, le from ne contenait que des relations « basées » (c'es-à-dire stockées dans la base). Maintenant, on a placé une relation calculée. Le principe reste le même. Rappelons que l'algèbre est un langage clos: il s'applique à des relations et produit une relation en sortie. Il est donc possible d'appliquer à nouveau des opérateurs à cette relation-résultat. C'est ainsi que l'on construit des expressions, comme nous allons le voir dans la session suivante. Nous retrouverons une autre application de cette propriété extrêmement utile quand nous étudierons les vues.

Quand les schémas des relations \(R\) et \(S\) sont complètement distincts, il n'y a pas d'ambiguité sur la provenance des colonnes dans le résultat. Par exemple on sait que les valeurs de la colonne \(A\) dans \(R \times S\) viennent de la relation \(R\). Il peut arriver (il arrive de fait très souvent) que les deux relations aient des attributs qui ont le même nom. On doit alors se donner les moyens de distinguer l'origine des colonnes dans la relation résultat en donnant un nom distinct à chaque attribut.

Voici par exemple une relation \(T\) qui a les mêmes noms d'attributs que \(R\).

Le schéma du résultat du produit cartésien \(R \times T\) a pour schéma \((A, B, A, B)\) et présente donc des ambiguités, avec les colonnes \(A\) et B en double.

La première solution pour lever l'ambiguité est d'adopter une convention par laquelle chaque attribut est préfixé par le nom de la relation d'où il provient. Le résultat de \(R \times T\) devient alors :

Cette convention pose quelques problèmes quand on crée des expressions complexes. Il existe une seconde possibilité, plus générale, pour résoudre les conflits de noms : le renommage. Il s'agit d'un opérateur particulier, dénoté \(\rho\), qui permet de renommer un ou plusieurs attributs d'une relation. L'expression \(\rho_{A \to C,B \to D}(T)\) permet ainsi de renommer \(A\) en \(C\) et \(B\) en \(D\) dans la relation \(T\). Le produit cartésien

\[R \times \rho_{A\to C,B \to D}(T)\]

ne présente alors plus d'ambiguités. Le renommage est une solution très générale, mais asez lourde à utiliser

Il est tout à fait possible de faire le produit cartésien d'une relation avec elle-même. Dans ce cas le renommage où l'utilisation d'un préfixe distinctif est impératif. Voici par exemple le résultat de \(R \times R\), dans lequel on préfixe par \(R1\) et \(R2\) respectivement les attributs venant de chacune des opérandes.

En SQL, le renommage est obtenu avec le mot-clé as. Il peut s'appliquer soit à la relation, soit aux attributs (ou bien même aux deux). Le résultat suivant est donc obtenu avec la requête:

select *
from R as R1 cross join R as R2

On obtient une relation de schéma (R1.A, R1.B, R1.A, R2.B), avec des noms d'attribut qui ne sont en principe pas acceptés par la norme SQL. Il reste à spécifier ces noms en ajoutant dans as dans la clause de projection.

select R1.a as premier_a, R1.b as premier_b, R2.a as second_a, R2.b as second_b
from R as R1 cross R as R2

Ce qui donnera donc le résultat:

Sur notre schéma, le renommage s'impose par exemple si on effectue le produit cartésien entre Voyageur et Séjour car l'attribut idVoyageur apparaît dans les deux tables. Essayez la requête:

select Voyageur.idVoyageur, Séjour.idVoyageur
from Voyageur cross join Séjour

Elle vous renverra une erreur comme Encountered duplicate field name: “idVoyageur”. Il faut nommer explicitement les attributs pour lever l'ambiguité.

select Voyageur.idVoyageur as idV1, Séjour.idVoyageur as idV2
from Voyageur cross join Séjour

Il existe deux autres opérateurs binaires, qui sont à la fois plus simples et moins fréquemment utilisés.

Le premier est l'union. L'expression \(R \cup S\) crée une relation comprenant tous les nuplets existant dans l'une ou l'autre des relations \(R\) et \(S\). Il existe une condition impérative : les deux relations doivent avoir le même schéma, c'est-à-dire même nombre d'attributs, mêmes noms et mêmes types.

L'union des relations \(R(A,B)\) et \(S(C,D)\) données en exemple ci-dessus est donc interdite (on ne saurait pas comment nommer les attributs dans le résultat). En revanche, en posant \(S' = \rho_{C\to A,D\to B}(S)\), il devient possible de calculer \(R \cup S'\), avec le résultat suivant :

Comme pour la projection, il faut penser à éviter les doublons. Donc le nuplet (x,y) qui existe à la fois dans \(R\) et dans \(S'\) ne figure qu'une seule fois dans le résultat.

L'union est un des opérateurs qui existe dans SQL depuis l'origine. La requête suivante effectue l'union des lieux de la table Logement et des régions de la table Voyageur. Pour unifier les schémas, on a projeté sur cet unique attribut, et on a effectué un renommage.

select lieu from Logement
  union
select région as lieu from Voyageur

On obtient le résultat suivant.

Notez que certains noms comme « Corse » apparaîssent deux fois: vous savez maintenant comment éliminer les doublons avec SQL.

Comme l'union, la différence s'applique à deux relations qui ont le même schéma. L'expression \(R -S\) a alors pour résultat tous les nuplets de \(R\) qui ne sont pas dans \(S\).

Voici la différence de \(R\) et \(S'\), les deux relations étant définies comme précédemment.

En SQL, la différence est obtenue avec except.

select A, B from R
   except
select C as A, D as B from S

La différence est le seul opérateur algébrique qui permet d'exprimer des requêtes comportant une négation (on veut « rejeter » quelque chose, on « ne veut pas » des nuplets ayant telle propriété). La contrainte d'identité des schémas rend cet opérateur très peu pratique à utiliser, et on lui préfère le plus souvent la construction logique du SQL « déclaratif », not exists.

Afin de comprendre l'intérêt de cet opérateur, regardons le produit cartésien \(\rm{Logement} \times \rm{Activit\acute e}\), dont le résultat est rappelé ci-dessous.

Si vous regardez attentivement cette relation, vous noterez que le résultat comprend manifestement un grand nombre de nuplets qui ne nous intéressent pas. C'est le cas de toutes les lignes pour lesquelles le code (provenant de la table Logement) et le codeLogement (provenant de la table Activité) sont distincts. Cela ne présente pas beaucoup de sens (à priori) de rapprocher des informations sur l'hôtel Génépi, dans les Alpes, avec l'activité de plongée en Corse.

Si, en revanche, on considère le produit cartésien comme un résultat intermédiaire, on voit qu'il permet d'associer des nuplets initialement répartis dans des tables distinctes. Sur notre exemple, on rapproche les informations générales sur un logement et la liste des activités de ce logement.

La sélection qui effectue une rapprochement pertinent est celle qui ne conserve que les nuplets partageant la même valeur pour les attributs code et codeLogement, soit:

\[\sigma_{code=codeLogement}(\rm{Logement} \times \rm{Activit\acute e})\]

Prenez bien le temps de méditer cette opération de sélection: nous ne voulons conserver que les nuplets de \(\rm{Logement} \times \rm{Activit\acute e}\) pour lesquelles l'identifiant du logement (provenant de Logement) est identique à celui provenant de Activité. En regardant le produit cartésien ci-dessous, vous devriez pouvoir vous convaincre que cela revient à conserver les nuplets qui ont un sens: chacune contient des informations sur un logement et sur une activité dans ce même logement.

On obtient le résultat ci-dessous.

On a donc effectué une composition de deux opérations (un produit cartésien, une sélection) afin de rapprocher des informations réparties dans plusieurs relations, mais ayant des liens entre elles (toutes les informations dans un nuplet du résultat sont relatives à un seul logement). Cette opération est une jointure, que l'on peut directement, et simplement, noter :

\[\rm{Logement} \Join_{code=codeLogement} \rm{Activit\acute e}\]

La jointure consiste donc à rapprocher les nuplets de deux relations pour lesquelles les valeurs d'un (ou plusieurs) attributs sont identiques. De fait, dans la plupart des cas, ces attributs communs sont (1) l'identifiant de l'une des relations et (2) une référence à cet identifiant dans l'autre relation. Dans l'exemple ci-dessus, c'est le cas pour code (identifiant de Logement) et codeLogement (référence dans Activité).

La notation de la jointure, \(R \Join_F S\), est un racourci pour \(\sigma_F(R \times S)\).

Si on n'exprime pas de critère de rapprochement, la jointure est équivalente à un produit cartésien.

Initialement, SQL ne proposait pour effectuer la jointure que la version déclarative.

select *
from Logement as l, Activité as a
where l.code=a.codeLogement

En 1992, la révision de la norme a introduit l'opérateur algébrique qui, comme le produit cartésien, et pour les mêmes raisons, prend place dans le from.

select *
from Logement join Activité on (code=codeLogement)

Il s'agit donc d'une manière alternative d'exprimer une jointure. Laquelle est la meilleure? Aucune, puisque toutes les deux ne sont que des spécifications, et n'imposent en aucun cas au système une méthode particulière d'exécution. Il est d'ailleurs exclu pour un système d'appliquer aveuglément la définition de la jointure et d'effectuer un produit cartésien, puis une sélection, car il existe des algorithmes d'évaluation bien plus efficaces.

Il faut être attentif aux ambiguités dans le nommage des attributs qui peut survenir dans la jointure au même titre que dans le produit cartésien. Les solutions à employer sont les mêmes : on préfixe par le nom de la relation ou par un synonyme, ou bien on renomme des attributs avant d'effectuer la jointure.

Supposons que l'on veuille obtenir les voyageurs et les séjours qu'ils ont effectués. La jointure s'exprime en principe comme suit:

select *
from Voyageur join Séjour on (idVoyageur=idVoyageur)

Le système renvoie une erreur: La clause de jointure on (idVoyageur=idVoyageur) est clairement ambigüe. Pour MySQL, le message est par exemple Column “idVoyageur” in on clause is ambiguous. Nouvelle tentative:

select *
from Voyageur join Séjour on (Voyageur.idVoyageur=Séjour.idVoyageur)

Nouveau message d'erreur (cette fois, sous MySQL: Encountered duplicate field name: “idVoyageur”). La liste des noms d'attribut dans le nuplet-résultat obtenu avec select * comprend encore deux fois idVoyageur.

Première solution: on renomme les attributs du nuplet résultat. Cela suppose d'énumérer tous les attributs.

select V.idVoyageur as idV1, V.nom, S.idVoyageur as idV2, début, fin
from Voyageur as V join Séjour as S  on (V.idVoyageur=S.idVoyageur)

Cette première solution consiste à effectuer un renommage après la jointure. Une autre solution est d'effectuer le renommage avant la jointure.

select *
from (select idVoyageur as idV1, nom from Voyageur) as V
               join
     (select idVoyageur as idV2, début, fin from Séjour) as S
           on (V.idV1=S.idV2)

En algèbre, la requête ci-dessus correspond à l'expression suivante:

\[(\rho_{idVoyageur \to idV1} (\pi_{idVoyageur, nom}Voyageur) \Join_{idV1=idV2} \rho_{idVoyageur \to idV2} (\pi_{idVoyageur, d\acute ebut, fin}S\acute ejour))\]

On voit que le from commence à contenir des expressions de plus en plus complexes. Dans ses premières versions, SQL ne permettait pas des constructions algébriques dans le from, ce qui avait l'avantage d'éviter des constructions qui ressemblent de plus en plus à de la programmation. Rappelons qu'il existe une syntaxe alternative à la requête ci-dessus, dans la forme déclarative de SQL étudiée au chapitre précédent.

select V.idVoyageur as idV1, V.nom, S.idVoyageur as idV2, début, fin
from Voyageur as V, Séjour as S
where V.idVoyageur= S.idVoyageur

Bref, vous commencez à avoir l'embarras du choix.

La jointure dite « naturelle »

Il reste à vrai dire, avec SQL, un troisième choix, la jointure dite « naturelle ». Elle s'applique uniquement quand les attributs de jointure ont des noms identiques dans les deux tables. C'est le cas ici, (l'attribut de jointure est idVoyageur, que ce soit dans Logement ou dans Séjour). La jointure naturelle s'effectue alors automatiquement sur ces attributs communs, et ne conserve que l'un des attributs dans le résultat, ce qui élimine l'ambiguité. La syntaxe devient alors très simple.

select *
from Voyageur as V natural join Séjour

Si les attributs de jointures sont nommés différemment, la jointure naturelle devient plus délicate à utiliser puisqu'il faut au préalable effectuer des renommages pour faire coïncider les noms des attributs à comparer.

À partir de là, vous savez comment effectuer plusieurs jointures. Un exemple devrait suffire: supposons que l'on veuille les noms des voyageurs et les noms des logements qu'ils ont visités. La requête algébrique devient un peu compliquée. On va s'autoriser une construction en plusieurs étapes.

Tout d'abord on effectue un renommage sur la table Voyageur pour éviter les futures ambiguités.

\[V2 := \rho_{idVoyageur\to idV, nom \to nomVoyageur} (Voyageur)\]

Opération semblable sur les logements.

\[L2 := \rho_{nom \to nomLogement} (Logement)\]

Et finalement, voici la requête algébrique complète, utilisant V2 et L2.

\[\pi_{nomVoyageur, nomLogement} (\rm{L2}) \Join_{code=codeLogement} \rm{S\acute ejour} \Join_{idVoyageur=idV} V2\]

En SQL, il faut tout écrire avec une seule requête. Allons-y

select nomVoyageur, nomLogement
from ( (select idVoyageur as idV, nom as nomVoyageur from Voyageur) as V
                 join
            Séjour as S on idV=idVoyageur)
             join
         (select code, nom as nomLogement from Logement) as L
             on codeLogement = code

Ce n'est pas très lisible… Pour comparaison, la version déclarative de ces jointures.

select V.nom as nomVoyageur, L.nom as nomLogement
from   Voyageur as V, Séjour as S, Logement as L
where  V.idVoyageur = S.idVoyageur
and    S.codelogement = L. code

À vous de voir quel style (ou mélange des styles) vous souhaitez adopter.

Regardons d'abord comment on peut généraliser les critères de sélection de l'opérateur \(\sigma\). Jusqu'à présent on a vu comment sélectionner des nuplets satisfaisant un critère de sélection, par exemple : « les logements de type “Hôtel” ». Maintenant supposons que l'on veuille retrouver les hôtels dont la capacité est supérieure à 100. On peut exprimer cette requête par une composition :

\[\sigma_{capacit\acute e>100}(\sigma_{type='H\hat otel'}(Logement))\]

Ce qui revient à pouvoir exprimer une sélection avec une conjonction de critères. La requête précédente est donc équivalente à celle ci-dessous, où le \(\land\) dénote le “et”.

\[\sigma_{capacit\acute e>100 \land type='H\hat otel'}(Logement)\]

La composition de plusieurs sélections revient à exprimer une conjonction de critères de recherche. De même la composition de la sélection et de l'union permet d'exprimer la disjonction. Voici la requête qui recherche les logements qui sont en Corse, ou dont la capacité est supérieure à 100.

\[\sigma_{capacit\acute e>100}(Logement) \cup \sigma_{lieu='Corse'}(Logement)\]

Ce qui permet de s'autoriser la syntaxe suivante, où le “\(\lor\)” dénote le “ou”.

\[\sigma_{capacit\acute e>100\ \lor\ lieu='Corse'}(Logement)\]

Enfin la différence permet d'exprimer la négation et « d'éliminer » des nuplets. Par exemple, voici la requête qui sélectionne les logements dont la capacité est supérieure à 200 mais qui ne sont pas en Corse. .

\[\sigma_{capacit\acute e>100}(Logement) - \sigma_{lieu='Corse'}(Logement)\]

Cette requête est équivalente à une sélection où on s'autorise l'opérateur “\(\not=\)” :

\[\sigma_{capacit\acute e>100 \land lieu \not='Corse'}(Logement)\]

Important
Attention avec les requêtes comprenant une négation, dont l'interprétation est parfois subtile. D'une manière générale, l'utilisation du “\(\not=\)” n'est pas équivalente à l'utilisation de la différence, l'exemple précédent étant une exception. Voir la prochaine section.

En résumé, les opérateurs d'union et de différence permettent de définir une sélection \(\sigma_F\) où le critère \(F\) est une expression booléenne quelconque. Attention cependant : si toute sélection avec un “ou” peut s'exprimer par une union, l'inverse n'est pas vrai.

Les requêtes dites conjonctives constituent l'essentiel des requêtes courantes. Intuitivement, il s'agit de toutes les recherches qui s'expriment avec des “et”, par opposition à celles qui impliquent des “ou” ou des “not”. Dans l'algèbre, ces requêtes sont toutes celles qui peuvent s'écrire avec seulement trois opérateurs : \(\pi\), \(\sigma\), \(\times\) (et donc, indirectement, \(\Join\)).

Les plus simples sont celles où on n'utilise que \(\pi\) et \(\sigma\). En voici quelques exemples.

• Nom des logements en Corse : \(\pi_{nom}(\sigma_{lieu='Corse'}(Logement))\)
• Code des logements où l'on pratique la voile. \(\pi_{codeLogement}(\sigma_{codeActivité='Voile'}(Activité))\)
• Nom et prénom des clients corses \(\pi_{nom,prénom}(\sigma_{région='Corse'}(Voyageur))\)

Des requêtes légèrement plus complexes - et extrêmement utiles - sont celles qui impliquent la jointure. On doit utiliser la jointure dès que les attributs nécessaires pour évaluer une requête sont réparties dans au moins deux relations. Ces « attributs nécessaires » peuvent être :

• Soit des attributs qui figurent dans le résultat ;
• Soit des attributs sur lesquels on exprime un critère de sélection.

Considérons par exemple la requête suivante : « Donner le nom et le lieu des logements où l'on pratique la voile ». Une analyse très simple suffit pour constater que l'on a besoin des attributs lieu et nom qui apparaîssent dans la relation Logement, et de codeActivité qui apparaît dans Activité.

Donc il faut faire une jointure, de manière à rapprocher les nuplets de Logement et de Activité. Il reste donc à déterminer le (ou les) attribut(s) sur lesquels se fait ce rapprochement. Ici, comme dans la plupart des cas, la jointure permet de « recalculer » l'association entre les relations Logement et Activité. Elle s'effectue donc par appariement de la identifiant d'une part (dans Logement), de la référence à cet identifiant d'autre part.

\[\pi_{nom,lieu}(Logement \Join_{code=codeLogement} (\sigma_{codeActivit\acute e='Voile'}(\text{Activit\acute e})) )\]

En pratique, la grande majorité des opérations de jointure s'effectue sur des attributs qui sont des identifiants dans une relations, et une référence à cet identifiant dans l'autre. Il ne s'agit pas d'une règle absolue, mais elle résulte du fait que la jointure permet le plus souvent de reconstituer le lien entre des informations qui sont naturellement associées (comme un logement et ses activités, ou un logement et ses clients), mais qui ont été réparties dans plusieurs relations au moment de la conception de la base.

Voici quelques autres exemples qui illustrent cet état de fait :

• Nom des clients qui sont allés à Tabriz (en supposant connu le code, ta, de cet hôtel) : \[\pi_{nom} (\text{Voyageur} \Join_{idVoyageur=idVoyageur} \sigma_{codeLogement='ta'} (\rm{S\acute ejour}))\]
• Quels lieux a visité le client 30 : \[\pi_{lieu} (\sigma_{idVoyageur=30} (\text{S\acute ejour}) \Join_{codeLogement=code} (\text{Logement}))\]
• Nom des clients qui ont eu l'occasion de faire de la voile : \[\pi_{nom} (\texttt{Voyageur} \Join_{idVoyageur=idVoyageur} (\rm{S\acute ejour} \Join_{codeLogement=codeLogement} \sigma_{codeActivit\acute e='Voile'}(\rm{Activit\acute e})))\]

La dernière requête comprend deux jointures, portant à chaque fois sur des identifiants. Encore une fois ce sont les identifiants et leurs références qui définissent les liens entre les relations, et elle servent donc naturellement de support à l'expression des requêtes.

Voici maintenant un exemple qui montre que cette règle n'est pas systématique. On veut exprimer la requête qui recherche les noms des clients qui sont partis en vacances dans leur lieu de résidence, ainsi que le nom de ce lieu.

Ici on a besoin des informations réparties dans les relations Logement, Séjour et Voyageur. Voici l'expression algébrique :

\[\pi_{nom, lieu} (\text{Voyageur} \Join_{idVoyageur=idVoyageur \land r\acute egion=lieu} (\text{Séjour} \Join_{codeLogement=code} \text{Logement}))\]

Les jointures avec la relation Séjour se font sur les couples (identifiant, référence), mais on a en plus un critère de rapprochement relatif à l'attribut lieu de Voyageur et de Logement.

Pour finir, voici quelques exemples de requêtes impliquant les deux opérateurs \(\cup\) et \(-\). Leur utilisation est moins fréquente, mais elle peut s'avérer absolument nécessaire puisque ni l'un ni l'autre ne peuvent s'exprimer à l'aide des trois opérateurs « conjonctifs » étudiés précédemment. En particulier, la différence permet d'exprimer toutes les requêtes où figure une négation : on veut sélectionner des données qui ne satisfont pas telle propriété, ou tous les « untels » sauf les “x” et les “y”, etc.

Illustration concrète sur la base de données avec la requête suivante : quels sont les codes des logements qui ne proposent pas de voile ?

\[\pi_{code}(\text{Logement}) - \pi_{codeLogement}(\sigma_{codeActivit\acute e='Voile'}(\text{Activité}))\]

Comme le suggère cet exemple, la démarche générale pour construire une requête du type « Tous les \(O\) qui ne satisfont pas la propriété \(p\) » est la suivante :

• Construire une première requête \(A\) qui sélectionne tous les \(O\).
• Construire une deuxième requête \(B\) qui sélectionne tous les \(O\) qui satisfont \(p\).
• Finalement, faire \(A - B\).

Les requêtes \(A\) et \(B\) peuvent bien entendu être arbitrairement complexes et mettre en œuvre des jointures, des sélections, etc. La seule contrainte est que le résultat de \(A\) et de \(B\) comprenne le même nombre d'attributs (et, en théorie, les mêmes noms, mais on peut s'affranchir de cette contrainte).

Important
Attention à ne pas considérer que l'utilisation du comparateur \(\not=\) est équivalent à la différence. La requête suivante par exemple ne donne pas les logements qui ne proposent pas de voile

\[\pi_{codeLogement}(\sigma_{codeActivit\acute e\ \not=\ 'Voile'}(\text{Activité}))\]

Pas convaincu(e)? Réfléchissez un peu plus, faites le calcul concret. C'est l'un de pièges à éviter.

Voici quelques exemples complémentaires qui illustrent ce principe.

• Régions où il y a des clients, mais pas de logement. \[\pi_{r\acute egion} (\text{Voyageur}) - \pi_{r\acute egion}(\rho_{lieu \to r\acute egion} (\text{Logement}))\]
• Identifiant des logements qui n'ont pas reçu de client tibétain. \[\pi_{code}(\text{Logement}) - \pi_{codeLogement} (\text{Séjour} \Join_{idVoyageur=idVoyageur} \sigma_{r\acute egion='Tibet'} (\text{Voyageur}))\]
• Id des clients qui ne sont pas allés en Corse. \[\pi_{idVoyageur}(\text{Voyageur}) - \pi_{idVoyageur}(\sigma_{lieu='Corse'}(\text{Logement}) \Join_{code=codeLogement} \text{Séjour})\]

La dernière requête construit l'ensemble des idVoyageur pour les clients qui ne sont pas allés en Corse. Pour obtenir le nom de ces clients, il suffit d'ajouter une jointure (exercice).

La différence peut être employée pour calculer le complément d'un ensemble. Prenons l'exemple suivant : on veut les ids des clients et les logements où ils ne sont pas allés. En d'autres termes, parmi toutes les associations Voyageur/Logement possibles, on veut justement celles qui ne sont pas représentées dans la base !

C'est un des rares cas où le produit cartésien seul est utile : il permet justement de constituer « toutes les associations possibles ». Il reste ensuite à en soustraire celles qui sont dans la base avec l'opérateur \(-\).

\[(\pi_{idVoyageur}(\text{Voyageur}) \times \pi_{code}(\text{Logement})) - \pi_{idVoyageur, codeLogement} (\text{Séjour})\]

Enfin la différence est nécessaire pour les requêtes qui font appel à la quantification universelle : celles où l'on demande par exemple qu'une propriété soit toujours vraie. À priori, on ne voit pas pourquoi la différence peut être utile dans de tels cas. Cela résulte simplement de l'équivalence suivante : une propriété est vraie pour tous les éléments d'un ensemble si et seulement si il n'existe pas un élément de cet ensemble pour lequel la propriété est fausse. La quantification universelle s'exprime par une double négation.

En pratique, on se ramène toujours à la seconde forme pour exprimer des requêtes. Prenons un exemple : quels sont les clients dont tous les séjours ont eu lieu en Corse? On l'exprime également par “quels sont clients pour lesquels il n'existe pas de séjour dans un lieu qui soit différent de la Corse. Ce qui donne l'expression suivante :

\[\pi_{idVoyageur} (\text{Séjour}) - \pi_{idVoyageur}(\sigma_{lieu \not='Corse'}(\text{Séjour}))\]

Pour finir, voici une des requêtes les plus complexes, la division. L'énoncé (en français) est simple, mais l'expression algébrique ne l'est pas du tout. L'exemple est le suivant : on veut les ids des clients qui sont allés dans tous les logements.

Traduit avec (double) négation, cela donne : les ids des clients tels qu'il n'existe pas de logement où ils ne soient pas allés. Ce qui donne l'expression algébrique suivante :

\[\pi_{idVoyageur}(\text{Voyageur}) - \pi_{idVoyageur} ((\pi_{idVoyageur}(\text{Voyageur}) \times \pi_{code}(\text{Logement})) - \pi_{idVoyageur, idLogement} (\text{Séjour}))\]

Explication: on réutilise l'expression donnant les clients et les logements où ils ne sont pas allés (voir plus haut) :

\[\pi_{idVoyageur}(\text{Voyageur}) \times \pi_{code}(\text{Logement})) - \pi_{idVoyageur, idLogement} (\text{Séjour})\]

On obtient un ensemble \(B\). Il reste à prendre tous les clients, sauf ceux qui sont dans \(B\).

\[\pi_{idVoyageur}(\text{Voyageur}) - B\]

Ce type de requête est rare (heureusement) mais illustre la capacité de l'algèbre à exprimer par de simples manipulations ensemblistes des opérations complexes.