Sous-requêtes

C'est mal au niveau performances. S'écrit toujours entre parenthèses. Mais plus faciles à lire.

Sous-requêtes renvoyant une unique valeur

Opérateurs : =, +, >, >=, <, <=, ...

SELECT ...
FROM ...
WHERE attribut = (SELECT ... FROM ... WHERE ...);

Exemple :

SELECT numEtudiant
FROM note
WHERE note > (SELECT AVG(note) FROM note WHERE numEpreuve = 2)
AND numEpreuve = 2;

Un ensemble de valeurs

Opérateurs : * IN, NOT IN : vrai si valeur fait partie d'un ensemble attribut IN (1, 2, 3, 4) attribut IN (SELECT ...) Exemple : SELECT * FROM etudiant WHERE numEtudiant NOT IN (SELECT numEtudiant FROM note WHERE numEpreuve = 2); * ANY, ALL : teste si une égalité est vraie pour un élément (ANY) ou pour plusieurs éléments d'un ensemble. attribut < ANY (5, 6, 7, 8) attribut >= ALL (SELECT ...) (= ANY équivalent à IN et != ALL équivalent à NOT IN) * EXISTS, NOT EXISTS : si la sous-requête renvoie quelquechose

Exemple :
    SELECT numEtudiant
    FROM etudiant
    WHERE numEpreuve = 1
    AND NOT EXISTS (SELECT numEtudiant
        FROM numEpreuve = 1
        AND n2.note > n1.note);

Comparer des lignes

ROW(attribut, attribut) = (SELECT a, b, ... FROM ...)
(attribut, attribut) = (SELECT a, b, ... FROM ...)

Comparer des tableaux

Même chose mais utilise opérateurs IN,ANY, ...

(attribut, attribut) IN (SELECT a, b, ... FROM ...)

Exemple :
    SELECT numEpreuve, numEtudiant
    FROM note
    WHERE (numEpreuve, note) IN (SELECT numEpreuve, MAX(note) FROM note GROUP BY numEpreuve) ORDER BY numEpreuve;

Se débarrasser des sous-requêtes

Toutes les requêtes avec un NOT IN peuvent être réecrites avec des jointures. Mais impossible de s'en passer si il y a un calcul d'aggrégat dans la sous-requête.

Exemples SQL

#1. La liste des examens pour lesquels il n'y a pas d'épreuves prévues.
#1.1 avec sous-requête
select numExamen
from examen
where numExamen not in (select numExamen from epreuve);

#1.2 sans sous-requêtes
select e.numExamen
from examen e
left join epreuve ep on e.numExamen = ep.numExamen
where ep.numEpreuve is null;


#2. Pour chaque étudiant, le numéro de l'épreuve qu'il a le moins réussi.
#2.1
select numEtudiant, numEpreuve
from note
where (numEtudiant, note) IN (
	select numEtudiant, min(note)
	from note
	group by numEtudiant
);
#2.2 pas possible sans sous-requête !


#3. Épreuve avec la meilleure moyenne.
#3.1 Version bourrine
select numEpreuve, avg(note) as avg
from note
group by numEpreuve
having avg = (
    select max(moy)
    from (
        select numEpreuve, avg(note) as moy
        from note
        group by numEpreuve
    ) as moyennes
);

#3.2 Version incomplète
select numEpreuve, avg(note) moy
from note
group by numEpreuve
order by moy desc limit 1;

#3.3 Version gagnante
select numEpreuve, avg(note) as moy
from note
group by numEpreuve
having moy >= ALL (
	select avg(n2.note)
	from note n2
	group by n2.numEpreuve
);

#3.4 -> voir vues.sql

#4. Quels étudiants ont une note à toutes les épreuves notées ?
select n1.numEtudiant
from note n1
(select distinct numEpreuve, n1.numEtudiant from note)
in (select distinct numEpreuve, numEtudiant from note);