Des sous-requêtes partout ! Et les bonnes pratiques !

Manipulation de données en SQL

Mona Khalil

Data Scientist, Greenhouse Software

Autant de sous-requêtes que vous voulez…

Peut inclure plusieurs sous-requêtes dans SELECT, FROM, WHERE

SELECT 
    s.stage,
    ROUND(s.avg_goals,2) AS avg_goal,
    (SELECT AVG(home_goal + away_goal)
     FROM match WHERE season = '2013/2014') AS overall_avg
FROM 
    (SELECT
         stage,
         AVG(home_goal + away_goal) AS avg_goals
     FROM match
     WHERE season = '2013/2014'
     GROUP BY stage) AS s
WHERE 
    s.avg_goals > (SELECT AVG(home_goal + away_goal) 
                   FROM match WHERE season = '2013/2014');

Formatez vos requêtes

Alignez SELECT, FROM, WHERE et GROUP BY

SELECT
    col1,
    col2,
    col3
FROM table1
WHERE col1 = 2;

Annotez vos requêtes

/* Cette requête filtre col1 = 2
et ne sélectionne que des données de table1 */
SELECT
    col1,
    col2,
    col3
FROM table1
WHERE col1 = 2;

Annotez vos requêtes

SELECT
    col1,
    col2,
    col3
FROM table1 -- cette table compte 10 000 lignes
WHERE col1 = 2; -- Filtrer WHERE valeur 2

Indentez vos requêtes

Indentez vos sous-requêtes !

SELECT
    col1,
    col2,
    col3
FROM table1
WHERE col1 IN
        (SELECT id
         FROM table2
         WHERE year = 1991);

Indentez vos requêtes

SELECT 
  date, 
  hometeam_id, 
  awayteam_id,
  CASE WHEN hometeam_id = 8455 AND home_goal > away_goal 
            THEN 'Victoire de Chelsea à domicile'
       WHEN awayteam_id = 8455 AND home_goal < away_goal
            THEN 'Victoire de Chelsea à l'extérieur'
       WHEN hometeam_id = 8455 AND home_goal < away_goal
            THEN 'Défaite de Chelsea à domicile'
       WHEN awayteam_id = 8455 AND home_goal > away_goal
            THEN 'Défaite de Chelsea à l'extérieur'
       WHEN (hometeam_id = 8455 OR awayteam_id = 8455) 
            AND home_goal = away_goal THEN 'Match nul de Chelsea'
       END AS outcome
FROM match
WHERE hometeam_id = 8455 OR awayteam_id = 8455;

Guide de style SQL de Holywell

Cette sous-requête est-elle nécessaire ?

Les sous-requêtes exigent de la puissance de calcul
- Quelle est la taille de votre base de données ?
- Quelle est la taille de la table interrogée ?
La sous-requête est-elle vraiment nécessaire ?

Filtrez correctement chaque sous-requête !

Surveillez vos filtres !

SELECT 
    s.stage,
    ROUND(s.avg_goals,2) AS avg_goal,
    (SELECT AVG(home_goal + away_goal)
     FROM match WHERE season = '2013/2014') AS overall_avg
FROM 
    (SELECT
         stage,
         AVG(home_goal + away_goal) AS avg_goals
     FROM match
     WHERE season = '2013/2014'
     GROUP BY stage) AS s
WHERE 
    s.avg_goals > (SELECT AVG(home_goal + away_goal) 
                   FROM match WHERE season = '2013/2014');

Passons à la pratique !

Manipulation de données en SQL