BD II: SQL II

Langage Manipulation de Données 2 : requêtes imbriquées

Équipe BD

2025-09-26

Opérateurs ensemblistes

Opérateurs ensemblistes: propriétés requises

Le résultat d’une requête SQL est un ensemble de tuples… donc une relation.

Les opérateurs ensemblistes (\(\cap\), \(\cup\), \(\setminus\)) permettent de combiner les résultats de deux requêtes (ou plus).

Important

Les requêtes combinées doivent avoir des schémas compatibles :

même nombre de colonnes,
même type de données pour les colonnes de même position.

`INTERSECT`, `UNION`, `EXCEPT`

INTERSECT : tuples de l’intersection des résultats des requêtes.
```
<requete1> 
INTERSECT 
<requete2>
```
UNION : tuples de l’union des résultats des requêtes.
```
<requete1> 
UNION 
<requete2>
```
EXCEPT : tuples de la différence ensembliste des résultats des requêtes
```
<requete1> 
EXCEPT 
<requete2>
```

Note

UNION ne conserve pas les tuples doublons entre les deux requêtes.

Pour les conserver, il faut utiliser l’opérateur UNION ALL.

Exemple pour `EXCEPT`

Considérons les tables de schémas suivants :

`Fournisseur (NomF : varchar, VilleF : varchar, AdresseF : varchar)` 

`Piece(NomP : varchar, Prix : money, Couleur : varchar)`

`Livraison(NumLiv : integer, NomP : varchar, NomF : varchar, DateLiv : date, Quantite : numeric)`

Fournisseurs qui ont livré la pièce x22 mais pas la pièce x21

SELECT NomF 
FROM Livraison 
WHERE NomP='x22'

EXCEPT 

SELECT NomF
FROM Livraison 
WHERE NomP='x21';

Requêtes imbriquées dans la clause `WHERE`

Sous-requête dans la clause `WHERE`

Dans la clause WHERE d’une requête, on peut utiliser une autre requête, appelée sous-requête ou requête imbriquée.
La sous-requête est exécutée pour chaque tuple formé par la clause FROM. Ce tuple sera sélectionné en fonction du test effectué sur le résultat de la sous-requête.
Dans une sous-requête, on peut se référer aux tables nommées dans la clause FROM de la requête de niveau supérieur.

Opérateurs de sous-requête

IN, EXISTS, ALL, ANY sont des fonctions (ou opérations) qui prennent en argument une sous-requête.
Elles effectuent des tests sur l’ensemble des résultats d’une sous-requête.

Mise en garde

Attention au comportement de chaque opérateur si le résultat de la sous-requête est vide ou s’il contient la valeur NULL : il faut appliquer la logique trivalente à l’évaluation de l’opérateur.

Opérateur `IN`

L’opérateur IN permet de tester si une valeur est présente dans le résultat d’une sous-requête.

<attribut> [NOT] IN (<sous-requete>)

Evaluée à vraie si <attribut> appartient au résultat de la sous-requête.

Mise en garde

Si le résultat de la sous-requête est vide, IN renvoie faux, NOT IN renvoie vrai.

Si <attribut> vaut NULL, IN et NOT IN renvoient UNKNOWN.

L’évaluation de IN s’effectue par un OU entre des tests \(=\). Pour les valeurs NULL du résultat de la sous-requête, le test d’égalité retourne UNKNOWN. Donc si <attribut> est présent dans le résultat, IN retourne True sinon UNKNOWN.

L’évaluation de NOTIN s’effectue par un ET entre des tests \(\neq\). Donc si le résultat de la sous-requête contient au moins une valeur NULL, NOT IN retourne UNKNOWN.

Exemple 1 de sous-requête avec `IN`

Lister les villes de fournisseurs qui ont livré la pièce ‘x21’.

SELECT DISTINCT VilleF 

FROM Fournisseur 

WHERE NomF IN ( 
  SELECT NomF  
  FROM Livraison 
  WHERE NomP = 'x21'
) ;

est équivalente à :

SELECT DISTINCT F.VilleF 

FROM Fournisseur F, Livraison L 

WHERE (F.NomF = L.NomF) AND 
  (L.NomP = 'x21');

Exemple 2 de sous-requête avec `IN`

Note

Pratique à utiliser sous la forme NOT IN pour coder l’opération de différence.

Lister les noms de pièces pour lesquelles il n’y a jamais eu de livraison.

SELECT NomP
FROM Piece P 
WHERE NomP NOT IN  (
  SELECT NomP
  FROM  Livraison
);

Equivalence en algèbre relationnelle :

\[\pi_{\text{NomP}}(\text{Piece}) - \pi_{\text{NomP}}(\text{Livraison})\]

Exemple 3 de sous-requête avec `IN`

Les capitales dont la population est plus grande que la moitié de la population du pays.

SELECT name FROM city
WHERE id IN (
  SELECT capital 
  FROM country
  WHERE population>=population_country*.5
);

est équivalente à :

SELECT name 
FROM country JOIN city 
  ON capital=id
WHERE population>=population_country*.5;

Opérateur ALL et ANY : `ANY`, `ALL`

Ce sont des quantificateurs qui s’utilisent dans des tests.

ANY : évalué à vrai si au moins un résultat de la sous-requête vérifie le test.

<attributs> =|<>|<=|<|>|=> ANY (<sous-requete>)

ALL : évalué à vraie si tous les résultats de la sous-requête vérifient le test.

<attributs> =|<>|<=|<|>|=> ALL (<sous-requete>)

Note

<attributs>: peut être une liste d’attributs

Exemple de sous-requête avec `ANY`

Alternative au IN.

Lister les noms de fournisseurs qui ont livré la pièce ‘x21’.

SELECT NomF
FROM Fournisseur
WHERE NomF = ANY (
    SELECT NomF
    FROM Livraison
    WHERE NomP = 'x21' 
);

Mise en garde

Si le résultat de la sous-requête est vide, ANY renvoie False.

Si l’attribut NomF vaut NULL, le résultat de la comparaison avec ANY est UNKNOWN.

L’évaluation de ANY s’effectue par un OU entre des tests =|<>|<=|<|>|=>. Pour les valeurs NULL du résultat de la sous-requête, le test retourne UNKNOWN. Donc si le test est vrai pour un tuple du résultat, ANY retourne True sinon UNKNOWN.

Exemple 1 de sous-requête avec `ALL`

La ville la plus peuplée de chaque pays.

SELECT c1.name 
FROM city as c1
WHERE population >= ALL (
  SELECT c2.population 
  FROM city as c2
  WHERE c1.countrycode=c2.countrycode
);

Mise en garde

Si le résultat de la sous-requête est vide, ALL renvoie True.

Si l’attribut population vaut NULL, le résultat de la comparaison avec ALL est UNKNOWN.

L’évaluation de ALL s’effectue par un ET entre des tests =|<>|<=|<|>|=>. Pour les valeurs NULL du résultat de la sous-requête, le test retourne UNKNOWN. Donc si le résultat de la sous-requête contient au moins une valeur NULL, ALL retourne UNKNOWN.

Exemple 2 de sous-requête avec `ALL`

Les pays dont toutes les villes ont plus de 1 000 000 habitants.

La requête :

SELECT co.name_country
FROM country co
WHERE 1000000 <= ALL (
  SELECT ci.population 
  FROM city ci
  WHERE ci.countrycode=co.countrycode
)

renvoie également les pays qui n’ont pas de ville !

On peut corriger en ajoutant un opérateur EXISTS sur la sous-requête.

Mais la meilleure solution est d’utiliser les fonctions d’agrégation que nous verrons plus tard.

Opérateur EXISTS

C’est un quantificateur qui retourne un booléen.

EXISTS retourne vrai si le résultat de la sous-requête n’est pas vide.

[NOT] EXISTS (<sous-requete>)

Mise en garde

Si le résultat de la sous-requête ne contient que la valeur NULL, EXISTS renvoie vrai car le résultat de la sous-requête contient au moins un tuple.

Exemple 1 de sous-requête avec `EXISTS`

Pays qui n’ont pas de ville

SELECT co.name_country
FROM country co
WHERE NOT EXISTS(
  SELECT * 
  FROM city ci
  WHERE ci.countrycode=co.countrycode
);

équivalent à

SELECT name_country
FROM country
EXCEPT
SELECT name_country
FROM country natural join city;

Note

NOT EXISTS permet souvent d’écrire une alternative à EXCEPT.

Exemple 2 de sous-requête avec `EXISTS`

Nom des Fournisseurs ayant livré la pièce x21 mais qui n’ont jamais livré la pièce a22.

SELECT DISTINCT NomF 
FROM Livraison L1 
WHERE L1.NomP='x21' AND 
  NOT EXISTS (
    SELECT * 
    FROM Livraison L2 
    WHERE L2.NomP='a22' AND L2.NomF=L1.NomF
  );

Exemple 3 de sous-requête avec `EXISTS`

Les régions qui ont au moins une langue officielle :

SELECT DISTINCT region 
FROM country AS co
WHERE EXISTS (
  SELECT * 
  FROM countrylanguage AS cl
  WHERE co.countrycode = cl.countrycode  AND cl.isofficial
);

équivalent à

SELECT DISTINCT region 
FROM country AS co join world.countrylanguage as cl 
on co.countrycode = cl.countrycode and cl.isofficial;

Sous-requête scalaire

Renvoie un tuple unique avec une colonne unique (un scalaire).
Le plus souvent écrite avec une fonction d’agrégation (SUM, AVG, COUNT, MAX, MIN, …) que nous étudierons dans la suite du cours.
Utilisation possible dans une clause WHERE.

Exemple : lister les noms de pays dont la densité est inférieure à la densité mondiale.

SELECT name_country , population_country / surfacearea as population_density
FROM country
WHERE population_country / surfacearea <= (
  SELECT sum(population_country) / sum(surfacearea)
  FROM country
)
order by name_country;

Note

Ne pas confondre la densité mondiale moyenne (environ \(40\ \text{h/km}^2\) d’après la BDD world, en fait \(55\ \text{h/km}^2\) en 2025) avec la densité moyenne des pays (environ \(400\ \text{h/km}^2\)).

Requêtes imbriquées dans la clause `FROM`

Tables dérivés

Dans une clause FROM, on peut écrire une sous-requête qui renvoie une table nommée avec un alias : on parle de table dérivée, dans le sens qu’elle dérive des tables existantes.

Note

Il s’agit d’une composition de requêtes.

Exemple de sous-requête dans la clause `FROM`

Lister les pays (code, nom, espérance de vie) dont le PNB a progressé de 10% et dont la population de la capitale est supérieure à 2 millions.

SELECT bo.countrycode, name_country, lifeexpectancy
FROM 
  (
    SELECT countrycode, name_country, lifeexpectancy, capital
    FROM country
    WHERE gnpold >0 AND gnp / gnpold > 1.1  -- test gnpold >0 pour gérer les valeurs nulles
  ) AS bo 
  JOIN 
  (
    SELECT id, countrycode
    FROM city 
    WHERE population > 2000000
  ) AS bc  
  ON bc.id=bo.capital ;

Opérateur `LATERAL`

Note

Dans l’exemple précédent, chaque table dérivée est calculée indépendemment puis la jointure est effectuée.

Avec l’opérateur LATERAL, on peut faire dépendre le calcul d’une table dérivée du résultat des tables précédentes du FROM.

Evaluation de `LATERAL`

La table dérivée est calculée pour chaque tuple \(u\) obtenu par les opérations précédentes du FROM.

Puis chaque tuple \(v\) la table dérivée est concaténé à \(u\) par produit cartésien.

On peut aussi concaténer \(u\) et \(v\) avec une jointure.

Exemple 1 avec `LATERAL`

Pour chaque pays, donner la liste des villes de son térritoire.

Solution avec LATERAL

SELECT co.name_country, sr.name 
FROM country co, LATERAL (  
  SELECT name       
  FROM city  ci            
  WHERE ci.countrycode=co.countrycode
) sr;

équivalent à

Avec jointure sur une table dérivée

SELECT co.name_country, sr.name 
FROM country co NATURAL JOIN (  
  SELECT countrycode, name       
  FROM city
) sr

ou bien sans table dérivée

SELECT name_country, name 
FROM country co JOIN city ci ON ci.countrycode=co.countrycode
ORDER BY name_country;

Exemple 2 avec `LATERAL`

Plus intéressant : pour chaque pays, donner la ville la plus peuplée.

SELECT co.name_country, sr.name 
FROM country co, LATERAL (  
  SELECT name       
  FROM city ci            
  WHERE ci.countrycode=co.countrycode
  ORDER BY ci.population DESC
  LIMIT 1
) sr
ORDER BY co.name_country;

Evaluation : si la table dérivée est vide, le tuple \(u\) de la table country n’est pas retenu car \(\lbrace u\rbrace \times \emptyset = \emptyset\).

Donc les pays sans ville ne sont pas affichés.

Les CTEs avec `WITH`

Clause `WITH`

Une clause WITH permet d’écrire des requêtes intermédiaires et d’alléger l’écriture d’une requête complexe.

WITH r AS (
  SELECT ...
  FROM ...
  WHERE ...
), s AS (
  SELECT ...
  FROM ...
  WHERE ...    -- références possibles à r 
)
SELECT ...
FROM   ...     -- références possibles à r 
WHERE ...

Note

Ces expressions/requêtes sont appelées Common Table Expressions ou CTEs ou ETCs en français.

Les CTEs peuvent être des requêtes SELECT, mais aussi des expressions de manipulation (mise à jour, insertion, suppression) de données.

Les CTEs permettent de coller plus fidèlement à l’algèbre relationnelle que les requêtes imbriquées.

Avantages des CTEs

Note

Les CTEs permettent de composer des requêtes de même que les requêtes imbriquées dans les clauses WHERE et FROM.

Par rapport à ces deux autres techniques, les avantages sont :

amélioration de la lisibilité et de la maintenance des requêtes complexes.
une ETC peut être utilisée plusieurs fois dans une même requête.

Exemple de requête avec `WITH`

Lister les pays (code, nom, espérance de vie) dont le PNB a progressé de 10% et dont la population de la capitale est supérieure à 2 millions.

WITH better_off AS (
  SELECT countrycode, name_country, lifeexpectancy, capital
  FROM country
  WHERE gnpold >0 AND gnp / gnpold > 1.1  -- gnpold >0 pour gérer les valeurs nulles
),
big_capital AS (
  SELECT id, countrycode
  FROM city 
  WHERE population > 2000000
)
SELECT bo.countrycode, name_country, lifeexpectancy
FROM better_off bo JOIN big_capital bc ON
  bc.id=bo.capital ;

Fin

Requêtes SQL imbriquées

BD II: SQL II

Opérateurs ensemblistes

Opérateurs ensemblistes: propriétés requises

INTERSECT, UNION, EXCEPT

Exemple pour EXCEPT

Requêtes imbriquées dans la clause WHERE

Sous-requête dans la clause WHERE

Opérateurs de sous-requête

Opérateur IN

Exemple 1 de sous-requête avec IN

Exemple 2 de sous-requête avec IN

Exemple 3 de sous-requête avec IN

Opérateur ALL et ANY : ANY, ALL

Exemple de sous-requête avec ANY

Exemple 1 de sous-requête avec ALL

Exemple 2 de sous-requête avec ALL

Opérateur EXISTS

Exemple 1 de sous-requête avec EXISTS

Exemple 2 de sous-requête avec EXISTS

Exemple 3 de sous-requête avec EXISTS

Sous-requête scalaire

Requêtes imbriquées dans la clause FROM

Tables dérivés

Exemple de sous-requête dans la clause FROM

Opérateur LATERAL

Evaluation de LATERAL

Exemple 1 avec LATERAL

Exemple 2 avec LATERAL

Les CTEs avec WITH

Clause WITH

Avantages des CTEs

Exemple de requête avec WITH

Fin

`INTERSECT`, `UNION`, `EXCEPT`

Exemple pour `EXCEPT`

Requêtes imbriquées dans la clause `WHERE`

Sous-requête dans la clause `WHERE`

Opérateur `IN`

Exemple 1 de sous-requête avec `IN`

Exemple 2 de sous-requête avec `IN`

Exemple 3 de sous-requête avec `IN`

Opérateur ALL et ANY : `ANY`, `ALL`

Exemple de sous-requête avec `ANY`

Exemple 1 de sous-requête avec `ALL`

Exemple 2 de sous-requête avec `ALL`

Exemple 1 de sous-requête avec `EXISTS`

Exemple 2 de sous-requête avec `EXISTS`

Exemple 3 de sous-requête avec `EXISTS`

Requêtes imbriquées dans la clause `FROM`

Exemple de sous-requête dans la clause `FROM`

Opérateur `LATERAL`

Evaluation de `LATERAL`

Exemple 1 avec `LATERAL`

Exemple 2 avec `LATERAL`

Les CTEs avec `WITH`

Clause `WITH`

Exemple de requête avec `WITH`