SQL - Expression Table Commune (ETC)

Bonjour là-bas, futurs magiciens SQL ! Aujourd'hui, nous allons entreprendre un voyage passionnant dans le monde des Expressions de Table Communes, ou ETC pour faire court. Ne vous inquiétez pas si vous êtes nouveaux dans le domaine de la programmation - je vais vous guider pas à pas, comme j'ai fait pour des centaines d'étudiants au fil des années. Alors, prenez votre boisson favorite, asseyez-vous confortablement, et plongeons dedans !

L'Expression de Table Commune en SQL

Imaginez que vous organisez une grande fête (car qui n'aime pas une bonne analogie ?). Vous avez une liste de tâches et vous souhaitez les découper en morceaux plus petits et plus gérables. C'est essentiellement ce que fait une Expression de Table Commune en SQL - elle nous aide à fractionner des requêtes complexes en parties plus simples et plus lisibles.

Une ETC est comme un jeu de résultats temporaire nommé que vous pouvez citer dans une instruction SELECT, INSERT, UPDATE, DELETE ou MERGE. Elle est définie dans la portée d'exécution d'une seule instruction. Pensez-y comme si vous créiez une table temporaire qui existe uniquement pour votre requête.

Regardons un exemple simple :

WITH etc_exemple AS (
SELECT 'Bonjour, ETC !' AS salutation
)
SELECT salutation FROM etc_exemple;

Dans cet exemple :

1. Nous commençons par le mot-clé WITH, qui indique le début de notre ETC.
2. Nous donnons un nom à notre ETC : etc_exemple.
3. Nous définissons ce que notre ETC contiendra : dans ce cas, une instruction SELECT qui crée une colonne appelée 'salutation' avec la valeur 'Bonjour, ETC !'.
4. Après la définition de l'ETC, nous avons notre requête principale qui utilise l'ETC.

Lorsque vous exécutez cela, vous verrez :

| salutation |
|------------|
| Bonjour, ETC ! |

N'est-ce pas génial ? Nous venons de créer notre première ETC !

La Clause WITH en MySQL

Maintenant, parlons de la clause WITH dans MySQL. C'est la baguette magique qui donne vie à nos ETC. La syntaxe générale ressemble à ceci :

WITH etc_nom [(liste_de_colonnes)] AS (requête)
SELECT * FROM etc_nom;

Voici un exemple plus pratique. Disons que nous avons une table des employés :

CREATE TABLE employes (
id INT PRIMARY KEY,
nom VARCHAR(50),
departement VARCHAR(50),
salaire DECIMAL(10, 2)
);

INSERT INTO employes VALUES
(1, 'Alice', 'RH', 50000),
(2, 'Bob', 'IT', 60000),
(3, 'Charlie', 'Finance', 55000),
(4, 'David', 'IT', 65000),
(5, 'Eve', 'RH', 52000);

Maintenant, utilisons une ETC pour trouver le salaire moyen par département :

WITH dept_moyen_salaire AS (
SELECT departement, AVG(salaire) AS salaire_moyen
FROM employes
GROUP BY departement
)
SELECT * FROM dept_moyen_salaire
ORDER BY salaire_moyen DESC;

Cette requête nous donnera :

| departement | salaire_moyen |
|-------------|---------------|
| IT          | 62500.00      |
| Finance     | 55000.00      |
| RH          | 51000.00      |

Ici, nous avons utilisé une ETC pour calculer le salaire moyen pour chaque département, puis nous avons sélectionné à partir de cette ETC pour afficher les résultats. C'est comme si nous avions créé une table temporaire avec les salaires moyens, que nous avons ensuite utilisée dans notre requête principale.

ETC à partir de Multiples Tables

Les ETC ne se limitent pas à une seule table. Nous pouvons utiliser plusieurs tables dans nos définitions d'ETC, tout comme dans les requêtes habituelles. Ajoutons une table des départements à notre exemple :

CREATE TABLE departements (
id INT PRIMARY KEY,
nom VARCHAR(50),
localisation VARCHAR(50)
);

INSERT INTO departements VALUES
(1, 'RH', 'New York'),
(2, 'IT', 'San Francisco'),
(3, 'Finance', 'Chicago');

Maintenant, utilisons une ETC pour combiner des informations des deux tables :

WITH info_departement AS (
SELECT e.departement,
AVG(e.salaire) AS salaire_moyen,
d.localisation
FROM employes e
JOIN departements d ON e.departement = d.nom
GROUP BY e.departement, d.localisation
)
SELECT * FROM info_departement
ORDER BY salaire_moyen DESC;

Cela nous donnera :

| departement | salaire_moyen | localisation      |
|-------------|---------------|-------------------|
| IT          | 62500.00      | San Francisco     |
| Finance     | 55000.00      | Chicago           |
| RH          | 51000.00      | New York          |

Dans cet exemple, notre ETC joint les tables employés et départements, calcule le salaire moyen et inclut les informations de localisation.

ETC Récursive

Maintenant, passons aux ETC récurrentes ! Ce sont comme les poupées russes du monde SQL. Une ETC récursive se réfère à elle-même, ce qui vous permet d'écrire des requêtes récurrentes, parfaites pour les données hiérarchiques ou arborescentes.

Créons un exemple simple d'une hiérarchie d'employés :

CREATE TABLE hierarchie_employes (
id INT PRIMARY KEY,
nom VARCHAR(50),
manager_id INT
);

INSERT INTO hierarchie_employes VALUES
(1, 'Gros Boss', NULL),
(2, 'Manager A', 1),
(3, 'Manager B', 1),
(4, 'Employé 1', 2),
(5, 'Employé 2', 2),
(6, 'Employé 3', 3);

Maintenant, utilisons une ETC récursive pour afficher toute la hiérarchie :

WITH RECURSIVE hierarchie_emp AS (
SELECT id, nom, manager_id, 0 AS niveau
FROM hierarchie_employes
WHERE manager_id IS NULL

UNION ALL

SELECT e.id, e.nom, e.manager_id, he.niveau + 1
FROM hierarchie_employes e
JOIN hierarchie_emp he ON e.manager_id = he.id
)
SELECT * FROM hierarchie_emp
ORDER BY niveau, id;

Cette requête produira :

| id | nom       | manager_id | niveau |
|----|-----------|------------|--------|
| 1  | Gros Boss | NULL       | 0      |
| 2  | Manager A | 1          | 1      |
| 3  | Manager B | 1          | 1      |
| 4  | Employé 1 | 2          | 2      |
| 5  | Employé 2 | 2          | 2      |
| 6  | Employé 3 | 3          | 2      |

Cette ETC récursive commence avec l'employé de niveau supérieur (Gros Boss) et puis récursivement trouve tous les employés qui rapportent à chaque manager.

Avantages des ETC

Les ETC présentent plusieurs avantages :

Avantage	Description
Lisibilité	Les ETC rendent les requêtes complexes plus lisibles en les divisant en sous-requêtes nommées.
Réutilisabilité	Vous pouvez référencer une ETC plusieurs fois au sein d'une requête.
Récurtivité	Les ETC permettent d'écrire des requêtes récurrentes, ce qui est excellent pour les données hiérarchiques.
Simplification	Elles peuvent simplifier les joints complexes et les sous-requêtes.
Maintenance	Les ETC rendent les requêtes plus faciles à entretenir et à modifier.

Inconvénients des ETC

Bien que les ETC soient puissantes, elles ont également quelques limitations :

Inconvénient	Description
Performance	Dans certains cas, les ETC peuvent ne pas performer aussi bien que les tables dérivées ou les vues.
Portée	Les ETC ne sont valides que dans la portée de l'instruction unique dans laquelle elles sont définies.
Complexité	Pour des requêtes très simples, utiliser une ETC pourrait ajouter une complexité inutile.
Support de la Base de Données	Certaines bases de données ne supportent pas les ETC, bien que la plupart des systèmes modernes le fassent.

Et voilà, amis ! Nous avons fait le tour des Expressions de Table Communes, des concepts de base aux requêtes récurrentes. Souvenez-vous, comme pour toute nouvelle compétence, maîtriser les ETC nécessite de la pratique. Alors n'ayez pas peur de les expérimenter dans vos propres requêtes. Avant de vous en rendre compte, vous écrirez des ETC comme un pro, impressionnant vos collègues et rendant vos requêtes de base de données plus propres et plus efficaces. Bonne chance dans vos requêtes !

Credits: Image by storyset