• SQL > requêtes imbriquées et complexes

      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 (WHERE ou HAVING).

       

      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 and NOT IN return NULL which is not an acceptable condition for WHERE 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 :

      • simple : Elle évaluée avant la requête principale
      • correlée : Elle est évaluée pour chaque ligne de la requête principale

      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 secours

      f3            Cotons tiges

       

      Dans la requête précédente, quelles sous-requêtes sont correlées et lesquelles ne le sont pas ?

 

Aucun commentaire

 

Laissez un commentaire