Informatique · Bases de données

SQL pratique

Écrire des requêtes justes, puis rapides : jointures, regroupements, index, transactions et vues.

Déjà à l'aise ? Aller au quiz

Le point de départ

Une base ne range pas tout dans une seule table. Les clients sont dans une table, les commandes dans une autre, reliées par une clé.

users 1 Alice 2 Bob 3 Charlie orders 10 · 1 11 · 2 12 · NULL orders.user_id = users.id

Après cette fiche, tu sais

  • choisir entre INNER, LEFT, RIGHT et FULL JOIN, et prévoir les lignes du résultat ;
  • regrouper avec GROUP BY et filtrer les groupes avec HAVING ;
  • protéger une base avec les contraintes (clé primaire, clé étrangère, UNIQUE, CHECK) ;
  • expliquer pourquoi un index accélère une recherche, et quand il ne sert à rien ;
  • écrire une transaction et dire ce que garantissent les propriétés ACID ;
  • créer une vue et savoir ce qu'elle contient vraiment.
Charlie n'a jamais rien commandé. Un LEFT JOIN le garde ; si j'ajoute WHERE orders.total > 150, apparaît-il encore ?

Je dirais que oui, il reste ?

Garde ta réponse en tête : on la vérifie dans la scène 1, avec les vraies lignes.

Recoller les morceaux

Notion 1 · Les jointures

Recoller les morceaux

Une jointure associe chaque ligne d'une table aux lignes de l'autre qui vérifient la condition écrite après ON.1

users (les clients)
idname
1Alice
2Bob
3Charlie
orders (les commandes, en CHF)
iduser_idtotal
101100
112200
12NULL50

orders.user_id est une clé étrangère vers users.id. Charlie n'a rien commandé, et la commande 12 n'a pas de client (user_id vaut NULL).

INNER JOIN

Seulement les paires : Charlie et la commande 12 disparaissent.

LEFT JOIN

Toute la table écrite avant JOIN. Sans correspondance, les colonnes de droite valent NULL.

RIGHT JOIN

A RIGHT JOIN B donne les mêmes lignes que B LEFT JOIN A. On l'écrit donc presque toujours en LEFT JOIN.3

FULL OUTER JOIN

Les paires, plus les orphelins des deux côtés. MySQL ne le connaît pas, SQLite seulement depuis la version 3.39 (2022).3, 4

Les jointures en un coup d'œil

Fais défiler : les quatre jointures se succèdent, puis Alice passe une deuxième commande. Le diagramme ne change pas, le résultat, si.

Scène 1 · Jointures

INNER JOIN

users orders Charlie 0 commande Alice Bob cmd 12 sans client SELECT users.name, orders.total FROM users INNER JOIN orders ON users.id = orders.user_id; 2 lignes name total

Une seule requête

Pour changer de jointure, il suffit de changer un mot. Le reste de la requête ne bouge pas.

Le gabarit des quatre jointuresSQL
SELECT users.name, orders.total
FROM users
LEFT JOIN orders            -- ou INNER, RIGHT, FULL OUTER
  ON users.id = orders.user_id;

Attention à l'ordre

Sans ORDER BY, l'ordre des lignes n'est pas garanti : PostgreSQL peut sortir la commande 12 avant Charlie.

Piège : le LEFT JOIN annulé par le WHERE

… LEFT JOIN orders ON users.id = orders.user_id WHERE orders.total > 150 ne renvoie que Bob. Pour Charlie, total vaut NULL, et NULL > 150 n'est pas vrai : la ligne disparaît.

LEFT JOIN orders
  ON users.id = orders.user_id AND orders.total > 150;   -- Alice (NULL), Bob (200), Charlie (NULL)

NULL n'est pas zéro

NULL veut dire « aucune valeur ». Ce n'est pas 0 : une comparaison avec NULL n'est jamais vraie.

0 une valeur : zéro NULL aucune valeur NULL > 150 : inconnu, ligne exclue
Réponse à ma question : non. Le WHERE filtre après la jointure, et la ligne de Charlie ne passe pas le test. La condition sur la table de droite va dans le ON.
Exercice · prévoir

Combien de lignes ?

Mêmes tables. Prévois le nombre de lignes du résultat, puis vérifie avec la scène.

Grouper, puis filtrer

Notion 2 · Regrouper et compter

GROUP BY et HAVING

Les fonctions d'agrégation résument plusieurs lignes en une seule valeur : COUNT (nombre), SUM (somme), AVG (moyenne), MIN et MAX.1 Avec GROUP BY, le calcul se fait groupe par groupe.

Un exemple

On ajoute une deuxième commande d'Alice (id 13, 80 CHF). Puis on compte et on additionne par client.

Commandes par clientSQL
SELECT users.name,
  COUNT(orders.id) AS nb,
  SUM(orders.total) AS depense
FROM users
LEFT JOIN orders
  ON orders.user_id = users.id
GROUP BY users.id, users.name
HAVING COUNT(orders.id) >= 1
ORDER BY depense DESC;
 name  | nb | depense
-------+----+---------
 Bob   |  1 |     200
 Alice |  2 |     180
Bob · 1 commande 200 CHF Alice · 2 commandes 180 CHF Charlie · 0 commande : exclu par HAVING

Qui filtre quoi ?

Reprenons la table ventes : 8 lignes au départ, puis 3 groupes par classe après le GROUP BY.

8 lignes FROM 6 lignes WHERE 3 groupes GROUP BY 2 groupes HAVING WHERE filtre les lignes, HAVING filtre les groupes

WHERE ou HAVING ?

WHERE choisit les lignes avant de former les groupes ; HAVING choisit les groupes après le calcul des agrégats.

Ce qui ne passe pas

WHERE COUNT(*) >= 2 provoque une erreur (PostgreSQL : aggregate functions are not allowed in WHERE). Ici, HAVING écarte Charlie, qui n'a aucune commande.

Dans quel ordre ?

L'ordre dans lequel le SGBD traite logiquement une requête n'est pas l'ordre d'écriture :

  1. FROM et JOIN
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY
  7. LIMIT

C'est pourquoi PostgreSQL refuse, dans le WHERE, un alias défini dans le SELECT (SQLite l'accepte). L'ORDER BY, lui, peut utiliser l'alias depense.

Une requête, pas à pas

Fais défiler : la requête s'exécute clause par clause, dans l'ordre logique du SGBD, sur les ventes du kiosque du gymnase (prix en CHF). Surveille la ligne surlignée : elle ne suit pas l'ordre d'écriture.

SELECT classe, SUM(prix) AS total FROM ventes WHERE article <> 'jus' GROUP BY classe HAVING COUNT(*) >= 2 ORDER BY total DESC; 1. FROM ventes : 8 lignes idclassearticleprix classetotal
Étape 1 sur 6 · FROM

Le SGBD part de la table ventes, 8 lignes. Une jointure se ferait à ce moment-là, avant tout le reste.

Étape 2 sur 6 · WHERE

Chaque ligne est testée, une par une : les deux jus sont éliminés, il reste 6 lignes.

Étape 3 sur 6 · GROUP BY

Les lignes restantes forment un groupe par classe : 2M1 a 2 ventes, 2M2 n'en a plus qu'une, 3M1 en a 3.

Étape 4 sur 6 · HAVING

Ce sont les groupes qui sont testés : 2M2, une seule vente puisque son jus a été retiré, disparaît.

Étape 5 sur 6 · SELECT

Un groupe, une ligne : 2M1 fait 5 + 2 = 7, 3M1 fait 7 + 2 + 5 = 14. L'alias total naît ici, après le WHERE.

Étape 6 sur 6 · ORDER BY

Le tri vient en dernier et peut utiliser l'alias : 3M1 (14 CHF) passe devant 2M1 (7 CHF).

Piège : COUNT(*) compte aussi les lignes vides

Après le LEFT JOIN, Charlie a une ligne dont les colonnes de orders valent NULL. COUNT(*) compte la ligne et donne 1 ; COUNT(orders.id) ignore le NULL et donne 0, le bon nombre de commandes.

Charlie · orders.id = NULL COUNT(*) = 1 COUNT(orders.id) = 0

Une autre règle

Chaque colonne du SELECT est soit dans le GROUP BY, soit dans un agrégat. SELECT name, COUNT(*) FROM users GROUP BY city est refusée par PostgreSQL : quel nom afficher pour un groupe de plusieurs clients ?

Des règles que la base fait respecter

Notion 3 · Les contraintes

La base refuse les données fausses

Une contrainte est une règle que le SGBD vérifie à chaque INSERT et UPDATE. Si elle n'est pas respectée, l'instruction échoue : la donnée fausse n'entre pas.

Deux tables bien gardées

Deux tables bien gardéesSQL
CREATE TABLE users (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city  TEXT
);
CREATE TABLE orders (
  id      INTEGER PRIMARY KEY,
  user_id INTEGER
          REFERENCES users(id)
          ON DELETE SET NULL,
  total   NUMERIC NOT NULL
          CHECK (total >= 0)
);

Une table, quatre règles

Chaque colonne peut porter une règle. La base les vérifie à chaque écriture.

id email total PRIMARY KEY UNIQUE · NOT NULL CHECK total ≥ 0 : un montant négatif est refusé

PRIMARY KEY

Identifie chaque ligne : valeur unique, jamais NULL. Une seule clé primaire par table, qui peut porter sur plusieurs colonnes.

REFERENCES (clé étrangère)

La valeur doit exister dans la table visée, ou être NULL : c'est l'intégrité référentielle. ON DELETE dit quoi faire : refuser (par défaut), CASCADE ou SET NULL.

UNIQUE

Interdit les doublons dans la colonne. Une table peut avoir plusieurs contraintes UNIQUE.

NOT NULL et CHECK

NOT NULL : la colonne doit toujours avoir une valeur. CHECK : une condition sur la ligne, par exemple un montant positif.

Et l'index ?Une clé primaire ou une contrainte UNIQUE crée automatiquement un index (voir la notion 4).2

Piège : SQLite ne vérifie pas les clés étrangères par défaut

Il faut exécuter PRAGMA foreign_keys = ON; à chaque connexion.4 Sans cela, une commande avec user_id = 99 est acceptée, même si le client 99 n'existe pas (testé). PostgreSQL, MySQL (InnoDB) et Oracle les vérifient par défaut.

Clé étrangère

Une commande ne peut viser qu'un client existant, ou NULL.

users 1 Alice 2 Bob orders user_id 1 user_id 99 refusé : client 99 absent
Exercice · classer

Acceptée ou refusée par PostgreSQL ?

Les deux tables ci-dessus, avec Alice (alice@mail.ch), Bob (bob@mail.ch), Charlie et les commandes 10 à 12. Huit instructions, toutes testées dans PostgreSQL 18.

Chercher sans tout lire

Notion 4 · Les index

Trouver sans tout lire

Sans index, SELECT * FROM users WHERE email = 'bob@mail.ch' lit toute la table, ligne par ligne : c'est un parcours complet (full table scan). Un index est une structure triée qui mène directement aux lignes cherchées, comme l'index d'un livre.

Pourquoi si peu de lectures ?

Un index B-tree est un arbre trié. Un nœud contient souvent des centaines de clés : chaque niveau divise la recherche par plusieurs centaines, et 4 ou 5 niveaux suffisent pour des millions de lignes.5

5 | 9 1 2 3 4 5 6 7 8 9 10 11 12 Racine, puis feuille : on descend sans tout lire.

Créer et supprimer

Créer et supprimer un indexSQL
-- Index simple
CREATE INDEX idx_users_city ON users(city);
-- Index sur deux colonnes
CREATE INDEX idx_users_name_city ON users(name, city);
-- Index unique (pas de doublon)
CREATE UNIQUE INDEX idx_email ON users(email);
-- Supprimer un index (MySQL : DROP INDEX … ON users;)
DROP INDEX idx_users_city;

La course : parcours complet contre index

Fais défiler : les deux méthodes cherchent le même dossard et comptent leurs lectures. Touche un dossard pour relancer la course.

Scène 2 · Index

Dossard 11

SELECT nom FROM coureurs WHERE dossard = 11; sans index table dans l'ordre d'arrivée lignes lues : 0 / 12 avec index B-tree trié par dossard 5 | 9 ligne de la table : ? lectures : 0

Pourquoi l'index reste muet

L'index est trié sur les valeurs de email : il ne sait rien de LOWER(email).

index sur email (trié) alice@mail.ch bob@mail.ch email = 'bob@mail.ch' LOWER(email) = 'bob…' pas de lien avec l'index

Trois pièges avec les index

Une fonction sur la colonne

WHERE LOWER(email) = 'bob@mail.ch' n'utilise pas l'index sur email : pour le SGBD, LOWER(email) est une autre valeur. Solution : un index sur l'expression LOWER(email) (PostgreSQL, Oracle, SQLite), ou des e-mails stockés déjà en minuscules.5

L'ordre des colonnes d'un index composite

L'index (name, city) sert pour WHERE name = … et pour WHERE name = … AND city = …, mais en général pas pour WHERE city = … seul : un annuaire trié par nom de famille n'aide pas à chercher un prénom.5

Chaque index a un coût

Il doit être mis à jour à chaque INSERT, UPDATE et DELETE, et il occupe de la place. On indexe les colonnes souvent utilisées dans WHERE, JOIN et ORDER BY, pas toutes.

Tout ou rien

Notion 5 · Transactions

Tout ou rien : les propriétés ACID

Une transaction regroupe plusieurs instructions qui doivent réussir ensemble, ou pas du tout. Exemple : un virement de 100 CHF du compte 1 vers le compte 2. Si seul le débit passe, 100 CHF disparaissent.1

Atomicité

Soit toutes les modifications sont validées (COMMIT), soit aucune (ROLLBACK). Une transaction interrompue avant son COMMIT est annulée.6

Cohérence

La transaction fait passer la base d'un état valide à un autre : à la fin, les contraintes (clés, CHECK…) sont respectées.6

Isolation

Des transactions simultanées ne se gênent pas : en général, une transaction ne voit pas les modifications non validées des autres.6

Durabilité

Une fois le COMMIT confirmé, les modifications sont enregistrées de façon permanente et survivent à une panne.6

Le déroulement

Une transaction commence par BEGIN et se termine soit par COMMIT, soit par ROLLBACK.

BEGIN UPDATE × 2 COMMIT tout est validé ROLLBACK une erreur annule tout

Le virement

Le virementSQL
BEGIN;                       -- ou START TRANSACTION; (norme)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                      -- tout est validé

-- En cas d'erreur avant COMMIT :
ROLLBACK;                    -- tout est annulé

Selon le système

BEGIN fonctionne dans PostgreSQL, MySQL et SQLite. Sans BEGIN, PostgreSQL et MySQL valident chaque instruction seule (mode autocommit).3 Dans Oracle, une transaction commence d'elle-même à la première instruction.6

SAVEPOINT nom, puis ROLLBACK TO nom, n'annulent que la fin de la transaction.

Piège : une erreur n'annule pas forcément la transaction toute seule

Le compte 1 n'a que 80 CHF, et une contrainte CHECK (balance >= 0) interdit le découvert : le premier UPDATE échoue.

Compte 1 : 80 CHF débit de 100 refusé Compte 2 : +100 CHF crédit validé sans débit Testé dans SQLite, si le programme continue.

SQLite exécute le second UPDATE, puis le COMMIT valide l'argent qui apparaît sans débit. PostgreSQL refuse toute instruction jusqu'à la fin du bloc (current transaction is aborted) et transforme le COMMIT en ROLLBACK. Dans les deux cas, c'est au programme d'envoyer ROLLBACK.

Exercice · prévoir

Que contient la base ?

Le virement de 100 CHF, quatre scénarios. Que valent les comptes à la fin ?

Une requête qui porte un nom

Notion 6 · Les vues

Une requête enregistrée sous un nom

Une vue s'interroge comme une table, mais elle ne stocke aucune donnée : sa requête est réexécutée à chaque utilisation.2

orders users commandes_detail aucune donnée name, total
Une vue sur les commandesSQL
CREATE VIEW commandes_detail AS
SELECT orders.id, users.name, orders.total
FROM orders
JOIN users ON users.id = orders.user_id;

-- Utilisation, comme une table :
SELECT name, total
FROM commandes_detail
WHERE total >= 100;
 name  | total
-------+-------
 Alice |   100
 Bob   |   200

Simplifier

Une requête compliquée s'écrit une fois, puis s'utilise par son nom.

Protéger

On donne accès à la vue plutôt qu'à la table, sans les colonnes sensibles.

Découpler

Si la structure des tables change, on adapte la vue, et le code qui l'utilise continue de fonctionner.

Protéger : la vue choisit les colonnes

users vue id name city email (caché) id name city

La vue ne montre que ce qu'elle choisit : la colonne email reste dans la table.

Une vue ne connaît que les colonnes de son SELECT : un filtre sur une colonne absente de la vue provoque une erreur.

Vue matérialiséeCREATE MATERIALIZED VIEW, dans PostgreSQL ou Oracle, fait l'inverse : elle stocke son résultat, qu'il faut rafraîchir.8

Passer à la vitesse supérieure

Notion 7 · Requêtes rapides

Quatre habitudes

Une requête rapide se prépare : elle lit le moins de données possible.

1. Regarder le plan

EXPLAIN montre si le SGBD lit toute la table ou utilise un index. SQLite : EXPLAIN QUERY PLAN SELECT ….7 PostgreSQL et MySQL : EXPLAIN SELECT ….8, 3 Oracle : EXPLAIN PLAN FOR SELECT ….

2. Une condition que l'index peut lire

Compare la colonne telle quelle, WHERE created_at >= '2026-01-01', plutôt qu'une fonction appliquée à la colonne.

3. Les colonnes utiles seulement

SELECT id, name plutôt que SELECT * : moins de données à lire et à transférer, et une requête qui ne casse pas si l'on ajoute une colonne.

4. Paginer avec un ordre défini

ORDER BY name LIMIT 20 (PostgreSQL, MySQL, SQLite), ou ORDER BY name FETCH FIRST 20 ROWS ONLY (norme SQL).2 Sans ORDER BY, « les 20 premières lignes » ne veulent rien dire.

Le plan, dans SQLite 3.43

SCAN users lit toute la table, ligne par ligne SEARCH users USING INDEX quelques lectures
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE city = 'Lausanne';
-- SEARCH users USING INDEX idx_users_city (city=?)

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE LOWER(email) = 'bob@mail.ch';
-- SCAN users (l'index sur email ne sert pas)

Ne lire que les colonnes utiles

SELECT id, name id name city email date note SELECT * id name city email date note

Moins de colonnes lues, moins de données transférées.

Les erreurs de la page suivante, on les voit dans presque toutes les copies. Trie-les : c'est le meilleur moyen de ne plus les faire.
Exercice · trier

Correct ou piège ?

Huit lignes de SQL qu'on croise tout le temps. Range chacune.

Entraîne-toi avant le quiz

Entraînement

Quatre exercices corrigés

Tables users et orders du début de la fiche (commandes 10, 11 et 12 ; Alice et Charlie habitent Lausanne, Bob Genève). Cherche d'abord, puis ouvre la correction.

1Liste les clients qui n'ont jamais commandé.Voir le corrigé
SELECT users.name
FROM users
LEFT JOIN orders ON orders.user_id = users.id
WHERE orders.id IS NULL;

Résultat : Charlie. Le LEFT JOIN garde tous les clients ; ceux sans commande ont orders.id à NULL, et le WHERE ne garde qu'eux. Ici, le WHERE sur la table de droite est voulu : on cherche justement les lignes complétées par NULL.

2Affiche le chiffre d'affaires par ville, seulement pour les villes au-dessus de 150 CHF.Voir le corrigé
SELECT users.city, SUM(orders.total) AS ca
FROM users
JOIN orders ON orders.user_id = users.id
GROUP BY users.city
HAVING SUM(orders.total) > 150;

Sans le HAVING, on obtient Genève 200 et Lausanne 100. Le HAVING ne garde que Genève : une condition sur une somme se met dans HAVING, car elle porte sur le groupe.

3La table users compte deux millions de lignes et un index sur email. Pourquoi WHERE LOWER(email) = 'bob@mail.ch' est-elle lente ?Voir le corrigé

L'index est trié sur les valeurs de email, pas sur celles de LOWER(email) : le SGBD ne peut pas s'en servir et parcourt toute la table. EXPLAIN le confirme (dans SQLite : SCAN users au lieu de SEARCH … USING INDEX). Deux solutions : créer un index sur l'expression, CREATE INDEX idx_email_min ON users(LOWER(email));, ou enregistrer les e-mails en minuscules dès l'insertion.

4Pendant un virement, le serveur tombe en panne entre les deux UPDATE. Que contient la base au redémarrage ? Et si la panne survient juste après le COMMIT ?Voir le corrigé

Panne avant le COMMIT : la transaction n'a jamais été validée, elle est annulée ; les deux comptes ont leur solde d'avant le virement (atomicité). Panne juste après un COMMIT confirmé : les deux modifications sont conservées, car le SGBD les a écrites sur un support permanent avant de confirmer (durabilité).

À retenir

La fiche en huit lignes

INNER JOINseulement les paires qui correspondent.
LEFT / RIGHT JOINtoute une table, NULL en face s'il n'y a pas de correspondance.
FULL OUTER JOINtout le monde (absent de MySQL).
GROUP BY / HAVINGWHERE filtre les lignes, HAVING filtre les groupes.
ContraintesPRIMARY KEY, REFERENCES, UNIQUE, NOT NULL, CHECK : la base refuse les données fausses.
Indexarbre trié : quelques lectures au lieu de toute la table, mais un coût à chaque écriture.
TransactionBEGIN … COMMIT, ou ROLLBACK : tout ou rien (ACID).
Vueune requête enregistrée, recalculée à chaque utilisation.

Douze questions pour finir

Dernière étape : le quiz. Douze questions tirées au hasard, différentes à chaque partie. Si tu te trompes, lis bien l'explication.
Quiz final

Teste-toi

Sources

  1. The PostgreSQL Global Development Group, PostgreSQL 18 Documentation : 2.6 « Joins Between Tables », 2.7 « Aggregate Functions », 3.4 « Transactions ». jointures, agrégats, transactions (postgresql.org), consulté le 8 octobre 2026.
  2. The PostgreSQL Global Development Group, PostgreSQL 18 Documentation : « CREATE VIEW », « SELECT » (LIMIT et FETCH FIRST), « Unique Indexes ». vues, SELECT, index uniques (postgresql.org), consulté le 8 octobre 2026.
  3. Oracle, MySQL 8.4 Reference Manual : « JOIN Clause », « DROP INDEX Statement », « START TRANSACTION, COMMIT, and ROLLBACK Statements », « EXPLAIN Statement ». jointures, DROP INDEX, transactions, EXPLAIN (dev.mysql.com), consulté le 8 octobre 2026.
  4. SQLite, Release 3.39.0 (2022, ajout de RIGHT et FULL OUTER JOIN) et SQLite Foreign Key Support. 3.39.0, clés étrangères (sqlite.org), consulté le 8 octobre 2026.
  5. Markus Winand, Use The Index, Luke! : « The Search Tree (B-Tree) », « Concatenated Keys », « Case-Insensitive Search ». B-tree, index composites, fonctions (use-the-index-luke.com), consulté le 8 octobre 2026.
  6. Oracle, Database Concepts, documentation d'Oracle Database, version 26, chapitre « Transactions ». docs.oracle.com, consulté le 8 octobre 2026.
  7. SQLite, EXPLAIN QUERY PLAN : les sorties « SCAN » et « SEARCH … USING INDEX ». EXPLAIN QUERY PLAN (sqlite.org), consulté le 8 octobre 2026.
  8. The PostgreSQL Global Development Group, PostgreSQL 18 Documentation : « EXPLAIN » et « CREATE MATERIALIZED VIEW ». EXPLAIN, vues matérialisées (postgresql.org), consulté le 8 octobre 2026.

Fiche relue et sourcée en septembre 2026, refaite en récit en octobre 2026. Requêtes testées avec SQLite 3.43 et PostgreSQL 18.

Glisser pour continuer vers NoSQL et Redis
Gymnase 2