INNER JOIN
Seulement les paires : Charlie et la commande 12 disparaissent.
Informatique · Bases de données
Écrire des requêtes justes, puis rapides : jointures, regroupements, index, transactions et vues.
Déjà à l'aise ? Aller au quizLe 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é.
Après cette fiche, tu sais
Je dirais que oui, il reste ?
Recoller les morceaux
Notion 1 · Les jointures
Une jointure associe chaque ligne d'une table aux lignes de l'autre qui vérifient la condition écrite après ON.1
| id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| id | user_id | total |
|---|---|---|
| 10 | 1 | 100 |
| 11 | 2 | 200 |
| 12 | NULL | 50 |
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).
Seulement les paires : Charlie et la commande 12 disparaissent.
Toute la table écrite avant JOIN. Sans correspondance, les colonnes de droite valent NULL.
A RIGHT JOIN B donne les mêmes lignes que B LEFT JOIN A. On l'écrit donc presque toujours en LEFT JOIN.3
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
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.
INNER JOIN
Une seule requête
Pour changer de jointure, il suffit de changer un mot. Le reste de la requête ne bouge pas.
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.
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
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.
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
Qui filtre quoi ?
Reprenons la table ventes : 8 lignes au départ, puis 3 groupes par classe après le GROUP BY.
WHERE choisit les lignes avant de former les groupes ; HAVING choisit les groupes après le calcul des agrégats.
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 :
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.
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.
Le SGBD part de la table ventes, 8 lignes. Une jointure se ferait à ce moment-là, avant tout le reste.
Chaque ligne est testée, une par une : les deux jus sont éliminés, il reste 6 lignes.
Les lignes restantes forment un groupe par classe : 2M1 a 2 ventes, 2M2 n'en a plus qu'une, 3M1 en a 3.
Ce sont les groupes qui sont testés : 2M2, une seule vente puisque son jus a été retiré, disparaît.
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.
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.
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
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
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.
Identifie chaque ligne : valeur unique, jamais NULL. Une seule clé primaire par table, qui peut porter sur plusieurs colonnes.
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.
Interdit les doublons dans la colonne. Une table peut avoir plusieurs contraintes UNIQUE.
NOT NULL : la colonne doit toujours avoir une valeur. CHECK : une condition sur la ligne, par exemple un montant positif.
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.
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
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
Créer et supprimer
-- 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;
Fais défiler : les deux méthodes cherchent le même dossard et comptent leurs lectures. Touche un dossard pour relancer la course.
Dossard 11
Pourquoi l'index reste muet
L'index est trié sur les valeurs de email : il ne sait rien de LOWER(email).
Trois pièges avec les index
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'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
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
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
Soit toutes les modifications sont validées (COMMIT), soit aucune (ROLLBACK). Une transaction interrompue avant son COMMIT est annulée.6
La transaction fait passer la base d'un état valide à un autre : à la fin, les contraintes (clés, CHECK…) sont respectées.6
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
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.
Le virement
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.
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.
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 vue s'interroge comme une table, mais elle ne stocke aucune donnée : sa requête est réexécutée à chaque utilisation.2
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
Une requête compliquée s'écrit une fois, puis s'utilise par son nom.
On donne accès à la vue plutôt qu'à la table, sans les colonnes sensibles.
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
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.
CREATE MATERIALIZED VIEW, dans PostgreSQL ou Oracle, fait l'inverse : elle stocke son résultat, qu'il faut rafraîchir.8Passer à la vitesse supérieure
Notion 7 · Requêtes rapides
Une requête rapide se prépare : elle lit le moins de données possible.
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 ….
Compare la colonne telle quelle, WHERE created_at >= '2026-01-01', plutôt qu'une fonction appliquée à la colonne.
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.
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
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
Moins de colonnes lues, moins de données transférées.
Huit lignes de SQL qu'on croise tout le temps. Range chacune.
Entraîne-toi avant le quiz
Entraînement
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.
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.
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.
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.
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
Douze questions pour finir
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.