15. Résumé des notions SQL

ECRITURE vs EXECUTION

À droite, les clauses en rouge montrent l’ordre d’écriture d’une requête SQL.
À gauche est affiché l’ordre réel d’exécution par le SGBD.
Ces deux ordres ne coïncident pas.

Ordre d'écriture

L'ordre d'écriture des clauses dans une requête est le suivant :

1. SELECT
2. FROM
3. JOIN
4. ON
5. WHERE
6. GROUP BY
7. HAVING
8. QUALIFY
9. ORDER BY
10. LIMIT

Ordre d'Exécution des clauses dans le SGBD

L'ordre d'exécution des clauses SQL dans une requête est crucial pour comprendre comment le système de gestion de base de données (SGBD) traite la requête. Voici l'ordre d'exécution générale des principales clauses :

1. La clause FROM est d'abord traitée.
   Elle spécifie la source des données, généralement une ou plusieurs tables.
   La base de données récupère les données de la source spécifiée (emplacement des JOIN et ON).

2. La clause WHERE est ensuite évaluée.
   Elle filtre les résultats en fonction d'une condition.
   Seules les lignes qui satisfont la condition spécifiée dans WHERE sont incluses dans les résultats.

3. Si la clause GROUP BY est présente, les résultats sont regroupés en fonction des colonnes spécifiées.
   Les lignes sont regroupées en fonction des valeurs distinctes dans les colonnes spécifiées.

4. La clause HAVING est ensuite évaluée, mais seulement si GROUP BY est utilisé.
   Elle filtre les groupes résultants en fonction d'une condition.
   Seuls les groupes qui satisfont la condition spécifiée dans HAVING sont inclus dans les résultats.

5. La clause SELECT est alors traitée.
   Elle spécifie les colonnes à récupérer dans les résultats.
   Les calculs et fonctions d'agrégation spécifiés dans SELECT sont également appliqués à ce stade.

6. Si la clause DISTINCT est utilisée, elle est appliquée après SELECT.
   Elle retourne des combinaisons uniques des colonnes spécifiées.

7. La clause AS peut être utilisée pour renommer des tables ou des colonnes dans la requête.
   Elle est généralement appliquée après SELECT et DISTINCT.

8. La clause QUALIFY filtre les résultats basés sur une condition appliquée aux lignes individuelles après l'agrégation.

9. La clause ORDER BY trie les résultats en fonction d'une ou plusieurs colonnes.
   Elle est généralement appliquée en dernier, après que toutes les autres clauses aient été évaluées.

10. Enfin, la clause LIMIT est appliquée en dernier, limitant le nombre de résultats renvoyés.

3 Règles pour Organiser ses Requêtes

1. Aller à la ligne à chaque fois que tu utilises une nouvelle clause.
2. Écrire les clauses, les fonctions, opérateurs et autres mots-clefs en MAJUSCULES.
3. Ajouter des commentaires à chaque fois que tu réalises quelque chose qui n'est pas "évident".

Fonctionnel mais pas lisible :

select COUNT(*)
from 'adventureWork/factInternetSale.csv'
where productkey=2 and year(orderDate)=2011

Fonctionnel et suivant les bonnes pratiques

/* Compter le nombre de roulements à billes commandé en 2011 */
SELECT COUNT(*)
FROM 'adventureWork/factInternetSale.csv'
WHERE productkey=2 AND YEAR(orderDate)=2011  -- 2 ='roulements à billes'

Condition

Dans la clause WHERE, il est possible de spécifier une condition particulière sur une ou plusieurs colonnes. Le tableau ci-dessous répertorie les opérations les plus courantes :

Fontions

Les fonctions peuvent être employées dans toutes les clauses où elles sont nécessaires. Cependant il existe des exceptions. Les fonctions de fenêtre sont présentes dans la clause SELECT et les fonctions d'agrégation de type SUM( ), AVG( ), MIN( ), MAX( ) et COUNT( ) ne sont pas utilisées dans la clause WHERE mais plutôt dans la clause HAVING.

HAVING VS WHERE

La clause HAVING, associée à la clause GROUP BY, sert à effectuer des filtres sur les lignes comme la clause WHERE. Toutefois, la différence réside dans le fait que la clause HAVING effectue des filtres sur lignes agrégées (à partir des fonctions d'agrégat) alors que la clause WHERE les effectue au niveau des lignes individuelles.

JOINTURE

INNER JOIN (ou JOIN) renvoie les lignes qui ont des valeurs correspondantes dans les deux tables. Si une ligne de la première table a une correspondance dans la seconde table, elle est incluse dans les résultats affichés.

LEFT JOIN (ou LEFT OUTER JOIN) renvoie toutes les lignes de la table à gauche (première table spécifiée) et les lignes correspondantes de la table à droite (deuxième table spécifiée). Si aucune correspondance n'est trouvée dans la table de droite alors des valeurs NULL sont renvoyées.

FULL JOIN (ou FULL OUTER JOIN) renvoie toutes les lignes lorsqu'il y a une correspondance dans l'une ou l'autre des tables. Si aucune correspondance n'est trouvée alors des valeurs NULL sont renvoyées.

Les différentes syntaxes:

UNION VS UNION ALL

L'opérateur UNION élimine automatiquement les doublons des résultats combinés, ce qui peut entraîner un coût de performance supplémentaire, car le système doit identifier et supprimer les duplicatas. En revanche, l'opérateur UNION ALL ne supprime pas les doublons et renvoie toutes les lignes des résultats combinés, ce qui peut être plus rapide car il évite cette étape de déduplication.

SELECT employeeID, employeeName, department
FROM employees
UNION
SELECT employeeID, employeeName, department
FROM managers;

CTE

Une CTE (Common Table Expression) est une expression qui permet de définir une table temporaire qui pourra être utilisée dans la suite d'une requête. Cette écriture simplifie la syntaxe et améliore la lisibilité du code SQL.

Une CTE commence par la clause WITH, suivie d'un nom attribué à la table temporaire. Ensuite, la clause AS est utilisée pour spécifier la requête qui définit la table temporaire.

WITH
    CTE1 AS (
        -- Définition du premier CTE
        SELECT * FROM table1
    ), -- Une virgule car cette CTE est suivie d'une autre CTE.
    CTE2 AS (
        -- Définition du deuxième CTE
        SELECT * FROM table2
    )  -- Pas de virgule car cette CTE est suivie de la requête principale.

SELECT
    *
FROM
    CTE1
JOIN
    CTE2 ON CTE1.column = CTE2.column;

Lisibilité : Les CTE améliorent la lisibilité du code en permettant de définir une table éphémère au début de la requête. Cela peut rendre le code plus modulaire et plus facile à comprendre.

Réutilisation : Les CTE peuvent être réutilisées dans la même requête, ce qui favorise la réutilisation du code et permet de simplifier les requêtes complexes.

Organisation : Les CTE sont particulièrement utiles lorsque vous avez besoin de traiter plusieurs étapes logiques dans une requête et que chaque étape est mieux exprimée séparément.

SOUS-REQUÊTE

La sous-requête fournit un ensemble de résultats temporaires qui est utilisé par la requête principale pour accomplir une tâche spécifique.

-- SUBQUERY DANS LA CLAUSE SELECT
SELECT column1, (SELECT MAX(column2) FROM table2) AS max_value
FROM table1;

-- SUBQUERY DANS LA CLAUSE FROM
SELECT column1
FROM (SELECT * FROM table1 WHERE condition) AS temp_table;

-- SUBQUERY DANS LA CLAUSE WHERE
SELECT column1
FROM table1
WHERE column2 = (SELECT MAX(column2) FROM table1);

-- SUBQUERY DANS LA CLAUSE HAVING
SELECT column1
FROM table1
WHERE column2 = (SELECT MAX(column2) FROM table1);

Utilisation : Les sous-requêtes peuvent être utilisées dans différentes parties d'une requête, y compris dans les clauses SELECT, FROM, WHERE, HAVING et JOIN.

Flexibilité : Elles sont plus flexibles car elles peuvent être incorporées directement dans la requête principale, ce qui les rend adaptées à des scénarios variés.

Réutilisation : Les sous-requêtes peuvent être réutilisées à plusieurs endroits dans une requête, mais si vous avez besoin d'utiliser la même logique dans plusieurs requêtes, vous devrez répéter la sous-requête.