Les sous-requêtes

Base pour les exemples

Pour expliquer les notions en jeu, nous allons utiliser la base présentée ci-dessous par ses trois tables.

La table COMPANY

Elle est définie par le code :

CREATE TABLE "COMPANY" (
	`id`	INTEGER NOT NULL,
	`nom`	TEXT NOT NULL,
	`age`	INTEGER,
	`adresse`	TEXT NOT NULL,
	`salaire`	INTEGER NOT NULL,
	`service`	INTEGER,
	PRIMARY KEY(id),
	FOREIGN KEY(`service`) REFERENCES SERVICE
)

La table SERVICE

Elle est définie par le code :

CREATE TABLE "SERVICE" (
	`id_service`	INTEGER NOT NULL,
	`denomination`	TEXT NOT NULL,
	`chef_de_service`	INTEGER NOT NULL,
	`adresse`	TEXT,
	PRIMARY KEY(id_service),
	FOREIGN KEY(`chef_de_service`) REFERENCES COMPANY
)

Son contenu est le suivant :

La table DOSSIER

Son contenu :

Le code de création :

CREATE TABLE `DOSSIER` (
	`id_tache`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`NumeroDossier`	INTEGER NOT NULL,
	`id_personne`	INTEGER NOT NULL,
	FOREIGN KEY(`id_personne`) REFERENCES COMPANY
)

Source

Vous pouvez télécharger ici cette base pour tester les exemples qui suivent. Prenez le temps de tester chacune des requêtes proposées.

Qu'est-ce qu'une sous-requête ?

Un premier exemple

Dans la table COMPANY, on aimerait avoir les noms et salaires des personnes qui gagnent plus que Teddy.

On écrit une première requête, qui constituera la requête interne, sélectionnant le salaire de Teddy : SELECT salaire FROM COMPANY where nom = "Teddy". Puis on englobe cette requête dans une requête sélectionnant les noms et salaires des personnes pour lesquelles le salaire est supérieur au résultat de la requête interne.

Ce qui donne :

  
select nom, salaire from COMPANY where salaire > 
(SELECT salaire FROM COMPANY where nom = "Teddy");  

In, Not in

Afficher les noms des personnes travaillant rue de Marseille (c'est à dire travaillant pour un service dont l'adresse est rue de Marseille).

 
SELECT nom FROM COMPANY where COMPANY.service IN
(SELECT SERVICE.id_service from SERVICE where adresse = "rue de Marseille");

SELECT nom FROM COMPANY where COMPANY.service NOT IN
(SELECT SERVICE.id_service from SERVICE where adresse = "rue de Marseille");

Exists

Dans la requête ci-dessous, on utilise la table COMPANY dans la requête principale et dans la requête interne. Et le champ service de cette table apparaît aux deux niveaux. Pour savoir de quelle ligne la valeur service est tirée (ligne de la requête principale ou de la requête interne), on renomme la table COMPANY avec un pseudo : la table COMPANY au niveau principal est renommée ici C, elle est renommée CC au niveau sous-requête.

Une ligne de la table COMPANY C est affichée si son attribut "service" est tel qu'il existe au moins une ligne avec ce service où le salaire est supérieur à 40 000.

 
select distinct service from COMPANY as C 
where exists (select * from COMPANY as CC where salaire > 40000 and CC.service = C.service);

La requête permet donc de savoir qu'il existe des salaires supérieurs à 40000 dans deux services (services d'identifiant 1 et service d'identifiant 2).

Pour connaître les services dans lesquels tous les salaires sont d'au plus 40000, on pourra donc écrire :

 
select distinct service from COMPANY as C where 
not exists (select * from COMPANY as CC where salaire > 40000 and CC.service = C.service);

select distinct service from COMPANY as C where 
(select max(salaire) from COMPANY as CC where   CC.service = C.service) <= 40000 ;

Tous les...

A l'aide de la table DOSSIER, on aimerait obtenir les identifiants des personnes ayant travaillé sur tous les dossiers.

Première solution

On veut sélectionner les personnes ayant travaillé sur tout dossier. Il est équivalent de sélectionner les personnes pour lesquelles il n'existe pas de dossier sur lequel la personne n'a pas travaillé.

Cela peut se traduire par :

 
select distinct D1.id_personne from DOSSIER as D1 where 
not exists 
( 
    select D2.NumeroDossier from DOSSIER as D2 
    where not exists 
        (
         select * from DOSSIER as D3 
         where D3.id_personne = D1.id_personne and 
         D3.NumeroDossier  =D2.NumeroDossier 
         )
 ) 

D1.id_personne et D2.NumeroDossier étant fixés, la demande la plus interne sélectionne les lignes faisant apparaître cette personne et ce dossier. La requête du niveau au-dessus sélectionne donc les dossiers pour lesquels la personne D1.id_personne n'a pas travaillé. Et la requête la plus externe sélectionne donc les personnes pour lesquelles il n'existe pas de dossier sur lesquels elles n'ont pas travaillé.

Deuxième solution

Une personne ayant travaillé sur tous les dossiers est une personne dont le nombre de dossiers traités est égal au nombre total de dossiers existants.

 
select distinct id_personne from DOSSIER   
group by id_personne
having count(distinct NumeroDossier) = ( select count( distinct NumeroDossier) from DOSSIER )