-
SQL > requêtes imbriquées et complexes
- simple : Elle évaluée avant la requête principale
- correlée : Elle est évaluée pour chaque ligne de la requête principale
VALEURS SCALAIRES
Le résultat d’une requête est dit scalaire s’il comporte une seule ligne et une seule colonne.
Par exemple :
SELECT COUNT(*) FROM PRODUIT +----------+ | COUNT(*) | +----------+ | 4 | +----------+
Sous requêtes renvoyant une valeur scalaire
On peut placer dans une requête une sous-requête calculant un résultat scalaire.
Un tel type de sous-requête se place soit comme une colonne supplémentaire, soit comme une valeur servant à évaluer des conditions (
WHEREouHAVING).—
Unions
Les unions ont un fonctionnement proche des jointures. La différence est dans le fait que les données combinées doivent être du même type, et les tables doivent avoir les mêmes noms de colonnes. Les doublons sont éliminés, sauf si l’on précise le mot-clef ALL, auquel cas les valeurs multiples sont renvoyées autant de fois qu’elles apparaissent dans toutes les tables. Pour les mettre en place, les parenthèses sont obligatoires pour chaque requête :
(SELECT nom FROM employes_france) UNION (SELECT nom FROM employes_espagne) UNION (SELECT nom FROM employes_italie);
Sous-requêtes
La clause WHERE comprend ainsi une autre requête SQL pour limiter les résultats originels, ou pour agréger les informations de plusieurs tables. Une sous-requête peut également être placée dans une clause HAVING :
SELECT * FROM table WHERE colonne1 = ( SELECT colonne2 FROM table2); SELECT * FROM table WHERE colonne = (SELECT colonne2 FROM table2 WHERE colonne2 = (SELECT colonne3 FROM table3) );
Table temporaire
L’utilisation d’une table temporaire ne dépasse jamais la session SQL en cours, et autorise la création de tables pour créer un nouvel arrangement d’informations plus adapté à une requête particulière, pour tester la bonne insertion de données dans la base, ou pour créer un sous-ensemble d’une autre table plus léger afin de soulager le serveur SQL… On ne peut logiquement y faire appel qu’une seule fois dans la même requête.
CREATE TEMPORARY TABLE temp SELECT * FROM donnes WHERE a=1;
SELECT * FROM temp WHERE … ;—
SELECT T1.field1,..., NT.field1,... FROM ( SELECT T2.field1, .. FROM T2 WHERE ... ) AS NT /* Nested Query Result */ INNER JOIN T1 ON T1.someField = NT.someField && .... WHERE ...
—
(SELECT *
FROM post AS p
LEFT JOIN category AS c ON p.category_id = c.category_id)
UNION
(SELECT *
FROM post AS p
RIGHT JOIN category AS c ON p.category_id = c.category_id);—
Both
IN
andNOT IN
returnNULL
which is not an acceptable condition forWHERE
clause.Rewrite your query as follows:
SELECT * FROM match m WHERE NOT EXISTS ( SELECT 1 FROM email e WHERE e.id = m.id )
—
The most elegant and efficient solution with a sophisticated query optimizer would be this:
SELECT * FROM ( SELECT * FROM `table` WHERE ID IN ( SELECT id FROM `table` WHERE lang = 'EN' EXCEPT SELECT id FROM `table` WHERE lang = 'FR' ) OR table.LANG ='FR' ) t1 WHERE id = ?
This gives you the desired result, filtered by ID. In case the optimizer is however not able to push down the
id = ?
you might have to do it yourself to get decent performance:SELECT * FROM `table` WHERE id = ? AND (ID IN ( SELECT id FROM `table` WHERE lang = 'EN' AND ID = ? EXCEPT SELECT id FROM `table` WHERE lang = 'FR' AND ID = ? ) OR table.LANG ='FR')
However, if you can, I would get all the results at once and not iterate over the IDs in the first place:
SELECT * FROM `table` WHERE ID IN ( SELECT id FROM `table` WHERE lang = 'EN' EXCEPT SELECT id FROM `table` WHERE lang = 'FR' ) OR table.LANG ='FR'
This will get you all the IDs that have lang ‘EN’ and no corresponding ‘FR’ plus all the ‘FR’s. Alternatively you could also try:
SELECT * FROM `table` WHERE lang = 'FR' OR (lang = 'EN' AND ID NOT IN (SELECT ID FROM table WHERE lang = 'FR'))
or
SELECT * FROM `table` WHERE lang = 'FR' OR (table.LANG = 'EN' AND NOT EXISTS (SELECT * FROM table t1 WHERE lang = 'FR' AND t1.id = table.id))
But my guess would be the first query is fastest.
—
SELECT * FROM Table1 WHERE Table1.principal NOT IN (SELECT principal FROM table2)—
WITH T0 AS (SELECT CAST(@DateDebut AS DATE) AS JOUR),T1 AS (SELECT JOUR FROM T0 AS T0_1 UNION ALL SELECT DATEADD(DAY, 1, JOUR) AS Expr1 FROM T1 AS T1_2 WHERE (DATEADD(DAY, 1, JOUR) <= CAST(@DateFin AS DATE)))—
SELECT * FROM T1 WHERE T1.id NOT IN (SELECT...) AND T1.id NOT IN (SELECT...)
SELECT * FROM book_mast
WHERE pub_id NOT IN(
SELECT pub_id FROM publisher);
Colonne fictive
On peut ajouter une colonne dans une requête, et choisir comme valeurs pour cette colonne le résultat d’une requête. Ce type de requête est souvent une alternative à GROUP BY. Par exemple, la requête suivante nous renvoie, pour tout produit, le nombre de fournisseurs proposant ce produit :
sql> SELECT nomprod, (SELECT COUNT(*) sql> FROM PROPOSER PR sql> WHERE PR.numprod = P.numprod) AS NB_FOURNISSEURS sql> FROM PRODUIT P
+-----------------+-----------------+ | nomprod | NB_FOURNISSEURS | +-----------------+-----------------+ | Roue de secours | 1 | | Poupee Batman | 2 | | Cotons tiges | 1 | | Cornichons | 0 | +-----------------+-----------------+
Conditions complexes
On peut construire une condition en utilisant le résultat d’une requête. Pour notre exemple, déclarons d’abord une vue contenant le nombre d’articles proposés par chaque fournisseur,
sql> CREATE VIEW NB_PROD_PAR_FOU AS sql> SELECT numfou, (SELECT COUNT(*) sql> FROM PROPOSER P sql> WHERE P.numfou = F.numfou) AS NB_PROD sql> FROM FOURNISSEUR F
Ensuite, recherchons les noms des fournisseurs proposant le plus de produits :
sql> SELECT nomfou sql> FROM FOURNISSEUR F, NB_PROD_PAR_FOU N sql> WHERE F.numfou = N.numfou sql> AND NB_PROD = (SELECT MAX(NB_PROD) sql> FROM NB_PROD_PAR_FOU)
+--------+ | nomfou | +--------+ | f1 | +--------+
La requête SELECT MAX(NB_PROD) FROM NB_PROD_PAR_FOU est évaluée avant, et son résultat lui est substitué dans l’expression de la requête. Comme on a
sql> SELECT MAX(NB_PROD) FROM NB_PROD_PAR_FOU;
+--------------+ | MAX(NB_PROD) | +--------------+ | 2 | +--------------+
Alors la requête précédente, dans ce contexte, est équivalente à
sql> SELECT nomfou sql> FROM FOURNISSEUR F, NB_PROD_PAR_FOU N sql> WHERE F.numfou = N.numfou sql> AND NB_PROD = 2 +——--+ | nomfou | +——--+ | f1 | +——--+
INSERT et UPDATE
On peut placer dans des instructions de mises à jour ou d’insertions des requêtes imbriquées. Par exemple,
sql> INSERT INTO PERSONNE (numpers, nom, prenom) sql> VALUES ((SELECT MAX(numpers) + 1 FROM PERSONNE), sql> 'Darth', 'Vador');
1.7.2 Sous requêtes renvoyant une colonne
On considère une colonne comme une liste de valeurs, on peut tester l’appartance d’un élément à cette liste à l’aide de l’opérateur IN. On peut s’en servir comme une alternative aux jointures, par exemple, réécrivons la requête de la section précédente. La requête suivante nous renvoie le nombre de produits proposés par les fournisseurs proposant le plus de produits :
sql> SELECT MAX(NB_PROD) FROM NB_PROD_PAR_FOU +--------------+ | MAX(NB_PROD) | +--------------+ | 2 | +--------------+
Maintenant, recherchons les numéros des fournisseurs proposant un tel nombre de produits :
sql> SELECT N.numfou sql> FROM NB_PROD_PAR_FOU N sql> WHERE NB_PROD = (SELECT MAX(NB_PROD) sql> FROM NB_PROD_PAR_FOU) +--------+ | numfou | +--------+ | 1 | +--------+
Notons que s’il existe plusieurs fournisseurs proposant 2 produits, cette requête renverra plusieurs lignes. C’est donc par hasard qu’elle ne retourne qu’une ligne. Le numéro du fournisseur proposant le plus de produits est donc le 1. Cherchons ce fournisseur :
sql> SELECT nomfou sql> FROM FOURNISSEUR F sql> WHERE F.numfou IN (1) +--------+ | nomfou | +--------+ | f1 | +--------+
Il suffit donc dans la requête ci-dessous de remplacer le 1 par la requête qui a retourné 1. On a finalement :
SELECT nomfou FROM FOURNISSEUR F WHERE F.numfou IN (SELECT N.numfou FROM NB_PROD_PAR_FOU N WHERE NB_PROD = (SELECT MAX(NB_PROD) FROM NB_PROD_PAR_FOU) ) +--------+ | nomfou | +--------+ | f1 | +--------+
1.7.3 Sous requêtes non correlées renvoyant une table
On peut remplacer le nom d’une table dans la clause FROM par une sous-requête. Par exemple, la requête suivante renvoie une table.
SQL> SELECT 2 (SELECT COUNT(*) 3 FROM PROPOSER PR 4 WHERE PR.numfou = F.numfou 5 ) AS NB_PROD 6 FROM FOURNISSEUR F; NB_PROD ---------- 2 1 1 0
Cette table contient, pour chaque fournisseur, le nombre de produits proposés. Si l’on souhaite connaître le plus grand nombre de produits proposés, on se sert du résultat de la requête ci-dessus comme d’une table :
SQL> SELECT MAX(NB_PROD) AS MAX_NB_PROD 2 FROM 3 (SELECT 4 (SELECT COUNT(*) 5 FROM PROPOSER PR 6 WHERE PR.numfou = F.numfou 7 ) AS NB_PROD 8 FROM FOURNISSEUR F 9 ); MAX_NB_PROD ----------- 2
Ce type de requête est une alternative aux vues. Récupérons maintenant les noms des fournisseurs proposant le plus de produits (sans jointure et sans vue !) :
SELECT nomfou FROM FOURNISSEUR WHERE numfou IN (SELECT numfou FROM (SELECT numfou, (SELECT COUNT(*) FROM PROPOSER PR WHERE PR.numfou = F.numfou ) AS NB_PROD FROM FOURNISSEUR F ) N WHERE NB_PROD = (SELECT MAX(NB_PROD) FROM (SELECT numfou, (SELECT COUNT(*) FROM PROPOSER PR WHERE PR.numfou = F.numfou ) AS NB_PROD FROM FOURNISSEUR F ) N ) ); NOMFOU ------- f1
Vous constatez que la solution utilisant les vues est nettement plus simple.
1.7.4 Sous requêtes correlées
Une sous-requête peut être de deux types :
Par exemple, la requête suivante renvoie le nombre de produits livrés pour chaque fournisseur. Elle contient une sous-requête correlée.
SQL> SELECT numfou, 2 (SELECT SUM(qte) 3 FROM DETAILLIVRAISON D 4 WHERE D.numfou = F.numfou 5 ) NB_PROD_L 6 FROM FOURNISSEUR F;
NUMFOU NB_PROD_L ---------- ---------- 1 45 2 3 10 4
Cette même requête, une fois évaluée, peut server de requête non correlée si on souhaite connaître les noms de ces fournisseurs :
SELECT nomfou, NB_PROD_L FROM FOURNISSEUR F, (SELECT numfou, (SELECT SUM(qte) FROM DETAILLIVRAISON D WHERE D.numfou = F.numfou ) NB_PROD_L FROM FOURNISSEUR F ) L WHERE F.numfou = L.numfou;
NOMFOU NB_PROD_L
----------- ----------
f1 45
f2
f3 10
f4
Amusons-nous : quel sont, pour chaque fournisseur, les produits qui ont été les plus livrés ?
SQL> SELECT nomfou, nomprod 2 FROM FOURNISSEUR F, PRODUIT P, 3 (SELECT FF.numfou, PP.numprod 4 FROM FOURNISSEUR FF, PRODUIT PP 5 WHERE 6 (SELECT SUM(qte) 7 FROM DETAILLIVRAISON L 8 WHERE L.numfou = FF.numfou 9 AND L.numprod = PP.numprod 10 ) 11 = 12 (SELECT MAX(NB_PROD_L) 13 FROM 14 (SELECT numfou, SUM(qte) AS NB_PROD_L 15 FROM DETAILLIVRAISON L 16 GROUP BY numprod, numfou 17 ) Q 18 WHERE Q.numfou = FF.numfou 19 ) 20 GROUP BY numfou, numprod 21 ) M 22 WHERE M.numprod = P.numprod 23 AND M.numfou = F.numfou;
NOMFOU NOMPROD————- —————-f1 Roue de secoursf3 Cotons tigesDans la requête précédente, quelles sous-requêtes sont correlées et lesquelles ne le sont pas ?