Ch. 1-2 — Requêtes et jointures

Toute la première partie de R2.06 s'appuie sur une base unique de gestion de commandes : produits(pno, design, prix, poids, couleur), fournisseurs(fno, nom, adr, ville) et cmds(cno, fno, pno, qute). Gardez ces trois tables en tête : toutes les requêtes de la ressource s'y lisent concrètement.

Trois sous-langages, un seul objet

SQL (Structured Query Language) est un langage textuel de définition et de manipulation des bases relationnelles, normalisé plusieurs fois par l'ANSI et conçu pour rester proche du langage naturel. Il se découpe en trois parties :

• le DDL (Data Definition Language) : CREATE, ALTER, DROP — la structure ;
• le DML (Data Manipulation Language) : SELECT, INSERT, UPDATE, DELETE — les données ;
• le DCL (Data Control Language) : GRANT, REVOKE — les droits.

Deux conventions dès le départ : le langage est insensible à la casse, mais les données sont case-sensitive ; et une chaîne de caractères s'écrit toujours entre quotes.

La forme générale du SELECT

SELECT [DISTINCT] colonnes        -- projection
FROM tables                       -- jointures
WHERE condition                   -- restriction (sur les lignes)
GROUP BY colonnes                 -- regroupements
HAVING condition                  -- test sur les groupes
ORDER BY colonnes [ASC | DESC]    -- tri
LIMIT n OFFSET m;                 -- pagination

L'ordre d'exécution est celui de la lecture : on filtre les lignes (WHERE), on regroupe (GROUP BY), on filtre les groupes (HAVING), on projette, on trie. Le n-ième critère de tri n'est utilisé qu'en cas d'égalité des précédents.

La restriction : WHERE

Les opérateurs =, <>, <, <=, >, >= combinés par AND, OR, NOT sont polymorphes : ils fonctionnent sur des chaînes, des entiers, des réels et des dates. Deux raccourcis d'écriture évitent les OR à répétition : col IN (X, Y) équivaut à col = X OR col = Y, et col BETWEEN X AND Y équivaut à col >= X AND col <= Y.

Pour les recherches approximatives, LIKE remplace une partie de la chaîne : % remplace zéro à plusieurs caractères, _ exactement un (sauf chez Microsoft) ; Postgres ajoute les expressions régulières avec SIMILAR TO. Tous ces opérateurs peuvent être préfixés par NOT : NOT BETWEEN, NOT IN, NOT LIKE.

Enfin, une case non remplie porte la méta-valeur NULL, testée uniquement par IS NULL ou IS NOT NULL — jamais par =.

La projection et les doublons

SQL n'élimine pas les doublons sauf ordre explicite : SELECT ALL est le défaut, SELECT DISTINCT demande des lignes distinctes. Un DISTINCT coûte nettement plus cher qu'un ALL : on ne l'emploie que si nécessaire.

Les jointures : SQL-89 contre SQL-92

Dès qu'une requête porte sur plusieurs tables, il faut les joindre. En SQL-89, tout se passait dans le WHERE :

-- SQL-89 : critères de jointure et de restriction mélangés
SELECT f.nom, c.pno, c.qute
FROM fournisseurs AS f, cmds AS c
WHERE f.fno = c.fno AND c.qute > 100;

Cette notation ne facilite ni la lisibilité, ni l'optimiseur. SQL-92 sépare les critères : la jointure est exprimée dans le FROM, la restriction reste dans le WHERE — pour un résultat strictement identique :

-- SQL-92 : la jointure est explicite
SELECT f.nom, c.pno, c.qute
FROM fournisseurs f INNER JOIN cmds c ON f.fno = c.fno
WHERE c.qute > 100;

Le nom complet d'une colonne ambiguë s'écrit nomTable.nomColonne, raccourci par un alias. Plusieurs jointures se chaînent en suivant les clés étrangères — fournisseurs → cmds → produits — et une table peut être jointe à elle-même : les couples de fournisseurs de la même ville s'obtiennent avec deux alias f1 et f2 comparés sur f1.ville = f2.ville.

Les jointures externes et l'anti-jointure

La jointure interne ne fournit que les lignes qui vérifient le critère. Pour garantir toutes les lignes d'une table directrice, même sans correspondance, les jointures externes complètent les colonnes manquantes à NULL : LEFT OUTER JOIN (la table de gauche est directrice), RIGHT, FULL, et CROSS JOIN pour le produit cartésien. Les mots INNER et OUTER sont facultatifs.

SELECT p.pno, design, qute, poids
FROM produits p LEFT JOIN cmds c ON c.pno = p.pno
WHERE poids < 20;

L'anti-jointure en découle : pour trouver ceux qui n'ont pas de correspondance, on joint à gauche puis on garde les lignes dont la clé de droite est restée vide.

SELECT p.pno
FROM produits p LEFT JOIN cmds c ON c.pno = p.pno
WHERE c.pno IS NULL;

USING (num) et NATURAL JOIN raccourcissent l'écriture quand les colonnes portent le même nom — mais rendent la requête fragile aux renommages de colonnes.

Calculs et regroupements

SQL calcule ligne par ligne à l'affichage — SELECT pno, design, prix * 1.206 AS prixTTC FROM produits — grâce à des centaines de fonctions (chaînes, dates, mathématiques) dont les noms dépendent du SGBD. Il calcule aussi en colonne, sur des regroupements de lignes, avec cinq agrégats : AVG, SUM, MAX, MIN, COUNT.

SELECT fno, COUNT(*) AS nbre FROM cmds GROUP BY fno;
SELECT fno, COUNT(*)        FROM cmds GROUP BY fno HAVING COUNT(*) < 3;

Trois règles syntaxiques à ne jamais transgresser :

1. jamais de colonne simple dans un HAVING ;
2. jamais de fonction d'agrégat dans un WHERE (les tests sur colonnes vont dans WHERE, les tests sur agrégats dans HAVING) ;
3. toutes les colonnes du SELECT qui ne sont pas des agrégats doivent figurer dans le GROUP BY.

Attention aussi à la sémantique de COUNT : COUNT(*) compte toutes les lignes, COUNT(fno) les valeurs non nulles, COUNT(DISTINCT fno) les valeurs distinctes non nulles. Et un test peut porter sur un calcul sans l'afficher : HAVING COUNT(*) > 1 sans sélectionner le compteur.

A retenir

• WHERE restreint les lignes, SELECT projette, GROUP BY/HAVING regroupent et testent, ORDER BY trie, LIMIT/OFFSET paginent ;
• DISTINCT s'applique aux lignes et coûte cher : on ne l'emploie que si nécessaire ;
• SQL-92 explicite la jointure dans le FROM — à préférer systématiquement au SQL-89 ;
• « tous, même sans correspondance » → LEFT JOIN ; « ceux qui n'ont pas… » → LEFT JOIN … IS NULL ;
• COUNT(*), COUNT(col) et COUNT(DISTINCT col) ne comptent pas la même chose.