Ch. 3-4 — Sous-requêtes et mise à jour

Jusqu'ici, les comparaisons du WHERE utilisaient des constantes écrites à la main. Les sous-requêtes remplacent ces constantes par le résultat d'un autre SELECT : une requête peut ainsi dépendre d'autres requêtes. Parallèlement, quatre ordres modifient les données : INSERT, UPDATE, DELETE et TRUNCATE.

Sous-requête à valeur unique

La sous-requête renvoie une seule valeur, utilisable partout où une constante est légale — dans un WHERE comme dans un HAVING :

SELECT nom
FROM fournisseurs
WHERE ville = (SELECT ville FROM fournisseurs WHERE fno = 10);

SELECT fno
FROM cmds
GROUP BY fno
HAVING AVG(qute) > (SELECT AVG(qute) FROM cmds);

Sous-requête renvoyant un ensemble

La sous-requête renvoie cette fois une colonne unique avec plusieurs lignes. Trois opérateurs ensemblistes la consomment :

• IN : appartenance à la liste renvoyée ;
• > ALL : supérieur à toutes les valeurs — « supérieur à chacun des produits livrés par le fno 19 » ;
• > ANY : supérieur à au moins une des valeurs — « moins cher qu'au moins un spectacle de Delon ».

SELECT DISTINCT fno
FROM cmds
WHERE qute > ALL (SELECT qute FROM cmds WHERE fno = 19);

Deux équivalences à connaître, qui font le pont avec les jointures :

-- champ1 IN (SELECT champ1 FROM table2)
SELECT DISTINCT table1.* FROM table1
INNER JOIN table2 ON table1.champ1 = table2.champ1;

-- champ1 NOT IN (SELECT champ1 FROM table2)
SELECT table1.* FROM table1
LEFT JOIN table2 ON table1.champ1 = table2.champ1
WHERE table2.champ1 IS NULL;

La sous-requête corrélative

EXISTS teste si une sous-requête renvoie au moins un résultat. La sous-requête y fait référence à une colonne de la requête englobante : elle est corrélative, recalculée pour chaque ligne. EXISTS n'a d'ailleurs pas de sens sans corrélation — et peu importe ce que la sous-requête renvoie, d'où le SELECT 1 conventionnel.

SELECT f.nom, f.ville
FROM fournisseur f
WHERE EXISTS (SELECT 1
              FROM cmds c
              WHERE c.fno = f.fno AND c.qute > 5);

La forme négative est la plus puissante. « Quantité supérieure à chacune des quantités du 19 » se reformule : « il n'existe pas de produit du 19 en quantité supérieure ou égale » :

SELECT DISTINCT c1.fno
FROM cmds c1
WHERE NOT EXISTS (SELECT 1
                  FROM cmds c2
                  WHERE c2.fno = 19 AND c2.qute >= c1.qute);

Le raisonnement double la mise pour le quotient de l'algèbre relationnelle : « les fournisseurs qui livrent tous les produits » devient « ceux pour lesquels il n'existe aucun produit qu'ils ne livrent pas » — deux NOT EXISTS imbriqués. Inversement, ne mettez pas du corrélatif partout : « les fournisseurs d'au moins un produit fourni aussi par un fournisseur d'un produit rouge » s'écrit très bien avec des IN imbriqués.

Les opérateurs ensemblistes

Entre deux SELECT : UNION (réunion, qui supprime les doublons — UNION ALL les garde), INTERSECT (intersection) et EXCEPT (différence). INTERSECT et EXCEPT sont rarement implémentés ; sur l'exemple des TD, empiler « produits lourds » et « produits commandés par le fournisseur 15 » s'écrit avec UNION ALL.

Côté théorie : tout arbre relationnel est codable en SQL, mais pas toujours en une requête « simple » — le quotient, justement, exige deux sous-requêtes.

INSERT : en extension et en intension

-- Extension : valeurs littérales. Les colonnes non citées sont mises à NULL.
INSERT INTO produits (pno, design, prix) VALUES (8, 'ecrou', 5);

-- Intension : le contenu vient d'un SELECT.
INSERT INTO petites_commandes (pno, qute)
SELECT pno, qute FROM cmds WHERE qute < 5;

Une constante se manipule seule : SELECT 1; ou SELECT 1+1; fonctionnent, et une constante peut accompagner une sélection — c'est ainsi qu'on remplit la table braderie avec SELECT pno, 100 FROM produits WHERE prix > 100 AND prix < 500 : le prix fixe à 100 € est une constante insérée ligne à ligne.

UPDATE, DELETE, TRUNCATE

UPDATE modifie plusieurs lignes simultanément, et l'expression du SET peut être une constante, une expression arithmétique (SET prix = prix * 1.05) ou le résultat d'un SELECT. Il est au moins aussi complexe qu'un SELECT :

UPDATE cmds
SET qute = (SELECT qute FROM modifs WHERE cmds.cno = modifs.cno)
WHERE cmds.cno IN (SELECT cno FROM modifs);

DELETE FROM … WHERE … efface aussi plusieurs lignes, sans réinitialiser les numéros automatiques — et il effectue une suppression logique : l'ordre d'insertion n'est respecté par aucun SGBD (attention à Access, qui appuie ses formulaires sur un ORDER BY). Pour vider une table en la laissant exister, TRUNCATE produits; est bien plus rapide (rien n'est journalisé) et, avec l'option RESTART IDENTITY, remet les numéros automatiques à zéro.

Enfin, l'extension SQL 2005 ajoute à INSERT, UPDATE et DELETE une clause RETURNING qui renvoie les lignes modifiées — UPDATE produits SET prix = prix * 1.05 WHERE couleur = 'rouge' RETURNING *; évite un SELECT supplémentaire pour identifier les lignes touchées ou les id générés. Postgres étend encore la syntaxe (FROM dans les mises à jour) mais ce n'est pas dans la norme : à éviter.

A retenir

• sous-requête scalaire (une valeur), ensembliste (IN, ANY, ALL) ou corrélative (EXISTS, recalculée ligne à ligne) ;
• IN ↔ INNER JOIN, NOT IN ↔ LEFT JOIN … IS NULL : trois écritures, un même résultat ;
• le « tous les… » se traite par double négation avec NOT EXISTS imbriqués ;
• quatre ordres de mise à jour : INSERT (extension/intension), UPDATE, DELETE, TRUNCATE — d'apparence simples, ils deviennent complexes dès que plusieurs tables interviennent.