SQL

Qu’est-ce que SQL ?

SQL, prononcé “sequel” ou “S-Q-L”, est le Structured Query Language utilisé pour lire et écrire des données dans des bases de données relationnelles. Standardisé en 1986, il est donc plus ancien que le web, et reste aujourd’hui le moyen par lequel presque toutes les applications communiquent avec leurs données.

SQL se divise en deux parties. Le DDL (data definition language) permet de créer et de modifier la structure : CREATE TABLE, ALTER TABLE, DROP INDEX. Le DML (data manipulation language) permet de manipuler les données elles-mêmes : SELECT, INSERT, UPDATE, DELETE. Vous passerez la majeure partie de votre temps sur le DML, l’instruction SELECT étant de loin la plus courante.

Il est important de comprendre dès le départ que SQL n’est pas un langage de programmation au sens habituel. Il n’y a pas de boucles dans le langage cœur, ni de variables dans une requête simple. Vous décrivez le résultat souhaité, et la base de données détermine comment le produire.

Déclaratif et basé sur les ensembles

Dans un langage comme JavaScript ou Python, vous généreriez un rapport en itérant :

const result = [];
for (const author of authors) {
  let count = 0;
  for (const post of posts) {
    if (post.author_id === author.id) count++;
  }
  if (count > 0) result.push({ author, count });
}

En SQL, vous exprimez cette même intention en une seule expression :

SELECT a.name, COUNT(p.id) AS posts
FROM authors a
LEFT JOIN posts p ON p.author_id = a.id
GROUP BY a.id, a.name
HAVING COUNT(p.id) > 0;

Il n’y a pas de boucle. Vous avez déclaré que vous vouliez chaque auteur avec son nombre de posts. La base de données peut satisfaire cette demande via un index scan, un hash join ou tout autre mécanisme — et si vous ajoutez un index demain, la même requête peut devenir plus rapide sans changer un seul caractère.

C’est l’état d’esprit set-based (basé sur les ensembles) : les instructions opèrent sur des ensembles complets de lignes simultanément. Une fois que le déclic a lieu, SQL devient concis d’une manière que les boucles permettent rarement.

SELECT : choisir les colonnes

Chaque lecture commence par SELECT, qui liste les colonnes que vous souhaitez récupérer.

SELECT id, title, published_at
FROM posts;

SELECT * retourne toutes les colonnes, ce qui est pratique lors de l’exploration, mais nommez vos colonnes dans les requêtes de vos applications. Les listes explicites sont stables lorsque le schéma change, évitent le transfert de colonnes volumineuses et inutilisées, et rendent l’intention évidente. Vous pouvez également calculer de nouvelles colonnes et les renommer :

SELECT title,
       LENGTH(body) AS body_length,
       COALESCE(published_at, created_at) AS visible_at
FROM posts;

AS permet de donner un alias à une colonne. Utilisez-le pour rendre les résultats lisibles et pour nommer les colonnes calculées, ce qui est important lorsqu’une bibliothèque client mappe les lignes en objets.

Filtrage avec WHERE

WHERE permet de ne conserver que les lignes qui satisfont une condition. Il supporte les comparaisons habituelles ainsi que AND, OR, NOT, IN, BETWEEN et LIKE.

SELECT id, title
FROM posts
WHERE author_id = 7
  AND published_at IS NOT NULL
  AND title ILIKE '%sql%';

Deux détails méritent d’être retenus. Premièrement, LIKE est sensible à la casse sur de nombreuses bases de données alors que ILIKE (Postgres) ne l’est pas ; le LIKE de MySQL est insensible à la casse avec les collations habituelles. Deuxièmement, un caractère générique au début, tel que '%sql', empêche l’utilisation d’un index classique, car il n’y a pas de préfixe sur lequel effectuer la recherche. Pour une véritable recherche textuelle, utilisez plutôt un index full-text.

Tri et pagination

ORDER BY trie le résultat, et LIMIT avec OFFSET le découpe.

SELECT id, title
FROM posts
WHERE published_at IS NOT NULL
ORDER BY published_at DESC
LIMIT 20 OFFSET 40;

Sans ORDER BY, la base de données ne garantit aucun ordre pour les lignes. Une requête qui “semble” revenir triée aujourd’hui peut changer lorsque le planificateur choisit un plan différent ; triez donc toujours explicitement lorsque l’ordre est important.

OFFSET ignore des lignes, ce qui devient coûteux lorsqu’on avance profondément dans une table volumineuse car la base de données doit tout de même les parcourir. La pagination par clés (keyset pagination) évite ce coût en mémorisant la dernière ligne consultée :

SELECT id, title
FROM posts
WHERE published_at < :last_published_at
ORDER BY published_at DESC
LIMIT 20;

NULL et la logique trivalente

NULL ne signifie pas zéro ou une chaîne vide. Cela signifie inconnu, et SQL utilise une logique trivalente : chaque condition est évaluée comme vraie, fausse ou inconnue. Les lignes ne passent une clause WHERE que lorsque la condition est vraie ; ainsi, l’état inconnu est traité comme faux pour le filtrage.

SELECT id FROM posts WHERE published_at = NULL;     -- never matches
SELECT id FROM posts WHERE published_at IS NULL;    -- correct
SELECT id FROM posts WHERE published_at IS NOT NULL;

Conséquences à surveiller :

• NULL = NULL est inconnu, pas vrai. Utilisez IS NULL ou IS NOT DISTINCT FROM.
• NULL + 1 est NULL ; utilisez COALESCE(x, 0) pour substituer une valeur par défaut.
• NOT IN (1, 2, NULL) n’est jamais vrai, car la comparaison avec un élément inconnu produit un résultat inconnu. Préférez NOT EXISTS lorsque la liste peut contenir des NULL.
• Les agrégats ignorent les NULL : COUNT(column) compte les valeurs non nulles, tandis que COUNT(*) compte les lignes.

La nullabilité est une décision de conception. Déclarez vos colonnes NOT NULL, à moins que les valeurs manquantes ne soient réellement significatives.

Les JOINs, avec un schéma

Une jointure combine des lignes de deux tables basées sur une colonne commune. Supposons que authors contienne Ada, Grace et Linus, et que posts contienne deux lignes pour Ada et une pour Grace. Les différents types de jointures produisent des résultats distincts.

authors            posts
-------            ----------------------------
id name            id author_id title
1  Ada             10 1         Joins
2  Grace           11 1         Indexes
3  Linus           12 2         NULLs

INNER JOIN  ->  posts 10, 11, 12        (only matches)
LEFT JOIN   ->  posts 10, 11, 12, Linus (all authors, NULL post)
RIGHT JOIN  ->  posts 10, 11, 12        (all posts)
FULL JOIN   ->  posts 10, 11, 12, Linus (both sides)

-- Every author, including those with no posts.
SELECT a.name, p.title
FROM authors a
LEFT JOIN posts p ON p.author_id = a.id
ORDER BY a.name;

La condition de jointure se trouve dans ON. Pour les jointures externes (outer joins), l’ajout d’un filtre sur la table de droite dans WHERE transforme silencieusement la jointure externe en jointure interne (inner join), car les lignes NULL ne passent pas le filtre. Placez ces conditions dans la clause ON lorsque vous souhaitez que les lignes non correspondantes soient conservées.

-- Keeps authors with no published posts.
SELECT a.name, p.title
FROM authors a
LEFT JOIN posts p
  ON p.author_id = a.id
 AND p.published_at IS NOT NULL;

Une self join utilise la même table deux fois avec des alias différents, ce qui est utile pour les hiérarchies. Une cross join associe chaque ligne de la première table à chaque ligne de la seconde ; c’est rarement le résultat recherché par accident.

GROUP BY et HAVING

Les agrégats regroupent plusieurs lignes en une seule : COUNT, SUM, AVG, MIN, MAX. GROUP BY définit les groupes.

SELECT a.name          AS author,
       COUNT(p.id)     AS post_count,
       MAX(p.created_at) AS latest
FROM authors a
LEFT JOIN posts p ON p.author_id = a.id
GROUP BY a.id, a.name
HAVING COUNT(p.id) > 0
ORDER BY post_count DESC;

La règle est que chaque colonne sélectionnée doit soit figurer dans le GROUP BY, soit être enveloppée dans un agrégat. Les bases de données qui appliquent cette règle (Postgres, et MySQL 8 par défaut) vous protègent contre des résultats arbitraires.

WHERE filtre les lignes avant le regroupement, et HAVING filtre les groupes après. Une condition sur un agrégat appartient donc à HAVING, et une condition sur une colonne simple appartient généralement à WHERE pour plus d’efficacité.

SELECT author_id, COUNT(*) AS posts
FROM posts
WHERE published_at IS NOT NULL   -- filter rows
GROUP BY author_id
HAVING COUNT(*) > 5;             -- filter groups

Sous-requêtes et CTE

Une sous-requête est une requête imbriquée dans une autre. Elle peut apparaître dans SELECT, FROM ou WHERE.

SELECT title
FROM posts
WHERE author_id IN (
  SELECT id FROM authors WHERE name = 'Ada'
);

Une common table expression (CTE) permet de nommer une sous-requête avec WITH, ce qui est généralement plus lisible que l’imbrication et permet de réutiliser le résultat.

WITH published AS (
  SELECT id, author_id, title
  FROM posts
  WHERE published_at IS NOT NULL
)
SELECT a.name, COUNT(p.id) AS posts
FROM authors a
LEFT JOIN published p ON p.author_id = a.id
GROUP BY a.id, a.name;

Les CTE peuvent être chaînées, et une CTE récursive se référence elle-même pour parcourir un arbre, comme des catégories ou des fils de commentaires. Privilégiez une CTE dès qu’une requête commence à s’imbriquer sur plus d’un niveau ; la lisibilité est également un facteur de performance.

Écriture de lignes

INSERT ajoute des lignes, UPDATE les modifie et DELETE les supprime.

INSERT INTO posts (author_id, title, body)
VALUES (7, 'Learning SQL', 'SQL is declarative.');

UPDATE posts
SET published_at = now()
WHERE id = 42;

DELETE FROM posts
WHERE id = 42;

Donnez toujours une clause WHERE à UPDATE et DELETE. Sans celle-ci, toutes les lignes sont affectées. Une bonne habitude consiste à exécuter le SELECT correspondant au préalable pour confirmer le nombre de lignes. Un UPDATE peut également faire référence à des valeurs existantes :

UPDATE posts
SET title = title || ' (updated)'
WHERE author_id = 7;

INSERT peut ajouter plusieurs lignes à la fois et peut être alimenté par une requête :

INSERT INTO posts (author_id, title)
SELECT id, 'Welcome' FROM authors;

Transactions

Une transaction regroupe des instructions afin qu’elles réussissent toutes ou échouent toutes ensemble. C’est ce qui permet de maintenir la cohérence d’une modification en plusieurs étapes.

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

Si une instruction échoue, ou si vous décidez d’annuler l’opération, ROLLBACK annule chaque modification effectuée depuis BEGIN. Sans transaction, un plantage entre deux mises à jour entraînerait une perte d’argent. Regroupez les écritures liées, gardez vos transactions courtes et n’attendez jamais une action utilisateur ou un appel réseau alors qu’une transaction est ouverte.

Clés et contraintes

Les contraintes sont des règles appliquées par la base de données pour vous :

• PRIMARY KEY — identifie uniquely chaque ligne ; crée également un index et implique NOT NULL.
• FOREIGN KEY — exige que la valeur existe dans une autre table, préservant ainsi l’intégrité référentielle.
• UNIQUE — interdit les doublons dans une colonne ou une combinaison de colonnes.
• NOT NULL — exige une valeur.
• CHECK — exige qu’une condition soit respectée, comme price >= 0.
• DEFAULT — fournit une valeur lorsqu’aucune n’est renseignée.

CREATE TABLE posts (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id    bigint NOT NULL REFERENCES authors (id) ON DELETE CASCADE,
  title        text NOT NULL,
  body         text NOT NULL,
  published_at timestamptz,
  created_at   timestamptz NOT NULL DEFAULT now()
);

Une clé primaire supplétive, telle qu’une colonne d’identité, est stable et compacte. Une clé naturelle, comme une adresse e-mail, a un sens mais peut évoluer ; privilégiez donc une clé supplétive accompagnée d’une contrainte UNIQUE sur la clé naturelle.

Les index au niveau conceptuel

Un index est une structure distincte et triée qui associe les valeurs d’une colonne à l’emplacement des lignes. Sans index, trouver des lignes nécessite de scanner l’intégralité de la table ; avec un index, la base de données peut accéder directement aux correspondances.

CREATE INDEX posts_author_published_idx
  ON posts (author_id, published_at DESC);

Conceptuellement, imaginez un annuaire téléphonique trié par nom de famille. Rechercher un nom est rapide car l’annuaire est ordonné ; filtrer sur une colonne qui n’est pas la clé de tri reviendrait à lire chaque page. Les index sacrifient la vitesse d’écriture et l’espace de stockage au profit de la vitesse de lecture, car chaque insertion et mise à jour doit les maintenir à jour.

Indexez les colonnes que vous utilisez pour les filtres et les jointures, privilégiez les index composites qui correspondent à la structure réelle de vos requêtes, et vérifiez avec EXPLAIN si le planificateur les utilise réellement. Avoir plus d’index n’est pas forcément mieux ; l’important est d’avoir les bons index.

Les fonctions de fenêtrage en une seule fois

Une fonction de fenêtrage effectue un calcul sur un ensemble de lignes liées à la ligne actuelle, sans les condenser comme le fait GROUP BY. Cela facilite la création de totaux cumulés, de classements et de comparaisons par groupe.

SELECT author_id,
       title,
       published_at,
       ROW_NUMBER() OVER (
         PARTITION BY author_id
         ORDER BY published_at DESC
       ) AS rank_in_author
FROM posts
WHERE published_at IS NOT NULL;

La clause OVER définit la fenêtre : PARTITION BY divise les lignes en groupes, et ORDER BY les trie à l’intérieur de chaque groupe. Les fonctions courantes incluent ROW_NUMBER, RANK, LAG, LEAD et SUM(...) OVER (...). La différence fondamentale avec l’agrégation est que chaque ligne d’origine est conservée.

Normalisation : 1NF, 2NF, 3NF

La normalisation est le processus consistant à éliminer la redondance afin que chaque information ne soit stockée qu’une seule fois. Les trois premières formes normales sont celles que vous utiliserez le plus :

Première forme normale (1NF) — pas de groupes répétitifs ni de colonnes multi-valeurs. Une colonne tags contenant "sql,indexes" viole cette règle ; l’utilisation d’une table post_tags distincte permet de la corriger.

Deuxième forme normale (2NF) — la 1NF, plus l’absence de dépendance partielle vis-à-vis d’une partie d’une clé composite. Si une ligne de commande est identifiée par (order_id, product_id) et stocke product_name, ce nom ne dépend que de product_id, il doit donc se trouver dans products.

Troisième forme normale (3NF) — la 2NF, plus l’absence de dépendance transitive entre des colonnes qui ne sont pas des clés. Si posts stockait à la fois author_id et author_email, l’email dépendrait de l’auteur et non du post ; il doit donc se trouver dans authors.

Imaginez une table qui répète l’email de l’auteur pour chaque post. Si vous modifiez l’email, vous devez mettre à jour de nombreuses lignes ; si vous en oubliez une, les données deviennent contradictoires. Divisez la table en authors et posts pour que l’information ne soit présente qu’une seule fois. Ne dénormalisez plus tard que de manière délibérée, lorsqu’un problème de performance mesuré le justifie.

Bonnes pratiques

• Nommez explicitement les colonnes au lieu d’utiliser SELECT * dans les requêtes de l’application.
• Utilisez toujours ORDER BY lorsque l’ordre des résultats est important.
• Déclarez NOT NULL par défaut et gérez les valeurs manquantes avec COALESCE.
• Utilisez IS NULL plutôt que = NULL, et préférez NOT EXISTS à NOT IN pour les listes pouvant être nulles.
• Filtrez les lignes dans WHERE et les groupes dans HAVING.
• Utilisez des CTE pour garder les requêtes complexes lisibles.
• Ajoutez une clause WHERE aux instructions UPDATE et DELETE, et confirmez les lignes cibles au préalable.
• Regroupez les écritures liées dans une transaction et gardez-la courte.
• Ajoutez des index basés sur des modèles de requêtes réels et vérifiez-les avec EXPLAIN.
• Stockez chaque fait une seule fois et visez la troisième forme normale avant de dénormaliser.

Erreurs courantes

• Écrire WHERE column = NULL et obtenir zéro ligne.
• Utiliser NOT IN sur une liste contenant NULL et ne rien retourner sans avertissement.
• Oublier WHERE sur un UPDATE ou DELETE et modifier ainsi toutes les lignes.
• Sélectionner des colonnes non groupées et obtenir des valeurs arbitraires.
• Supposer que les lignes reviennent dans un ordre utile sans ORDER BY.
• Utiliser LIMIT ... OFFSET pour une pagination profonde et subir un coût croissant.
• Filtrer la mauvaise table dans WHERE et transformer accidentellement un LEFT JOIN en inner join.
• Indexer chaque colonne et ralentir les écritures sans gain réel pour la lecture.
• Concaténer des entrées utilisateur dans des chaînes SQL au lieu d’utiliser des paramètres, ce qui expose aux injections.
• Stocker des données répétitives dans une seule table large au lieu de les normaliser.

Et après ?

Le SQL est le fondement de toute base de données relationnelle ; la prochaine étape consiste donc à choisir un moteur et à en apprendre le dialecte ainsi que les particularités. Commencez par PostgreSQL pour sa conformité aux standards et ses types riches, ou MySQL pour son omniprésence et ses capacités de réplication. Ensuite, découvrez comment les résultats de requêtes deviennent des réponses HTTP dans le guide REST, et comment un service Node.js envoie ces requêtes via une connexion poolée dans Node.js basics.