7. Requêtes SELECT

Ce chapitre décrit l'instruction SELECT : liste de sélection, FROM et jointures, WHERE, GROUP BY / HAVING, ORDER BY, LIMIT / OFFSET, sous-requêtes et UNION.

Les exemples s'appuient sur deux tables, remplies une fois pour tout le chapitre :

CREATE TABLE clients (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    nom    VARCHAR(50) NOT NULL,
    ville  VARCHAR(50),
    solde  DECIMAL(10,2)
);

INSERT INTO clients (nom, ville, solde) VALUES
    ('Alice', 'Oran', 100.50),
    ('Bob', 'Alger', -20.00),
    ('Chloé', 'Oran', 0.00),
    ('David', 'Constantine', NULL),
    ('Émile', 'Alger', 75.25),
    ('Fatima', NULL, 10.00);

CREATE TABLE commandes (
    id        INT PRIMARY KEY,
    client_id INT,
    montant   DECIMAL(10,2)
);

INSERT INTO commandes VALUES
    (1, 1, 10.00), (2, 1, 20.50), (3, 2, 5.00), (4, 9, 1.00), (5, 3, NULL);

7.1 Synopsis#

SELECT [DISTINCT] item [, item ...]
    [FROM table [jointure ...]]
    [WHERE condition]
    [GROUP BY expr [, expr ...]]
    [HAVING condition]
    [ORDER BY expr [ASC|DESC] [, ...]]
    [LIMIT n [OFFSET m] | LIMIT m, n]

7.2 Liste de sélection#

Chaque item est une expression, avec un alias facultatif (AS alias, ou un alias sans AS) :

SELECT nom, solde AS balance, solde * 1.1 marge FROM clients;
SELECT * FROM clients;                -- toutes les colonnes
SELECT clients.* , 1 AS actif FROM clients;   -- toutes les colonnes d'une table nommée, plus une expression

DISTINCT#

DISTINCT élimine les lignes du résultat identiques sur tous les items sélectionnés (une valeur NULL est considérée égale à une autre NULL pour cette élimination, contrairement à = dans un WHERE) :

SELECT DISTINCT ville FROM clients ORDER BY ville;
SELECT DISTINCT ville, solde FROM clients WHERE ville IS NOT NULL ORDER BY ville, solde;

COUNT(DISTINCT expr) compte les valeurs distinctes plutôt que les lignes :

SELECT COUNT(DISTINCT ville) FROM clients;

7.3 FROM et jointures#

Types de jointure pris en charge#

INNER JOIN, LEFT JOIN, RIGHT JOIN et CROSS JOIN peuvent tous porter un alias sur chaque table, et enchaîner plusieurs jointures dans un même FROM.

SELECT c.nom, o.montant FROM clients c JOIN commandes o ON o.client_id = c.id;

SELECT c.nom, COUNT(o.id) FROM clients c LEFT JOIN commandes o ON o.client_id = c.id
GROUP BY c.id, c.nom ORDER BY c.id;

SELECT c.nom, o.id, o.montant FROM clients c RIGHT JOIN commandes o ON o.client_id = c.id
ORDER BY o.id;   -- toutes les commandes, y compris celles sans client existant (id = 9)

SELECT nom, montant FROM clients JOIN commandes USING (id) ORDER BY id;

Auto-jointure#

Une table peut être jointe à elle-même ; chaque occurrence a besoin d'un alias distinct :

SELECT a.nom, b.nom
FROM clients a
JOIN clients b ON a.ville = b.ville AND a.id < b.id;

Table dérivée (sous-requête dans FROM)#

Une sous-requête peut remplacer une table dans FROM ou dans une jointure ; elle a besoin d'un alias :

SELECT t.ville, t.total
FROM (
    SELECT ville, SUM(solde) AS total
    FROM clients
    GROUP BY ville
) t
WHERE t.total > 0;

Non pris en charge#

• NATURAL JOIN (sous toute forme) est reconnu par le parseur mais rejeté à l'exécution (erreur
  1. : joindre explicitement avec ON ou USING à la place.
• Il n'y a pas de FULL [OUTER] JOIN ; un équivalent se construit avec un LEFT JOIN et un RIGHT JOIN combinés par UNION (voir §7.8) :

  SELECT c.nom, o.id FROM clients c LEFT JOIN commandes o ON o.client_id = c.id
  UNION
  SELECT c.nom, o.id FROM clients c RIGHT JOIN commandes o ON o.client_id = c.id;

7.4 WHERE#

WHERE filtre les lignes produites par FROM avant tout regroupement. Opérateurs disponibles :

SELECT nom FROM clients WHERE solde BETWEEN 0 AND 100 AND ville IN ('Oran', 'Alger');
SELECT nom FROM clients WHERE nom LIKE '_h%' OR nom REGEXP '^[A-E]';
SELECT nom FROM clients WHERE ville IS NULL;

Logique à trois valeurs et NULL#

Une comparaison avec NULL (y compris NULL = NULL) ne vaut jamais TRUE ni FALSE, mais UNKNOWN ; une ligne n'est retenue par WHERE que si sa condition vaut TRUE (UNKNOWN, comme FALSE, exclut la ligne). Tester l'absence de valeur demande donc IS NULL / IS NOT NULL, jamais = NULL :

SELECT nom FROM clients WHERE solde = NULL;      -- ne renvoie jamais rien
SELECT nom FROM clients WHERE solde IS NULL;      -- David

Le chapitre 9 (§9.5, « Logique à trois valeurs ») détaille ce comportement et ses pièges habituels (NOT IN avec un NULL dans la liste, NOT sur une condition UNKNOWN, agrégats qui ignorent les NULL).

7.5 GROUP BY et HAVING#

GROUP BY regroupe les lignes qui partagent les mêmes valeurs des expressions listées ; les fonctions d'agrégat (COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT, ...) sont alors calculées par groupe. HAVING filtre les groupes, comme WHERE filtre les lignes (et peut donc porter sur un agrégat, ce que WHERE ne peut pas).

SELECT ville, COUNT(*) FROM clients GROUP BY ville ORDER BY ville;
SELECT ville, COUNT(*) c FROM clients GROUP BY ville HAVING c > 1 ORDER BY ville;

Colonne citée hors GROUP BY#

Le mode SQL de MIRAJ ne comprend pas, par défaut, l'équivalent de la restriction ONLY_FULL_GROUP_BY : une colonne de la liste de sélection qui n'est ni un agrégat ni citée dans GROUP BY est acceptée, et rend une valeur d'une des lignes du groupe (laquelle n'est pas garantie ; l'activer dans sql_mode rétablit l'erreur 1055). Exemple :

SELECT ville, nom, COUNT(*) FROM clients GROUP BY ville;

Ici nom n'est ni agrégée ni dans GROUP BY : pour le groupe 'Oran' (Alice et Chloé), la valeur de nom rendue est celle d'une des deux lignes du groupe, sans garantie de laquelle. N'utiliser cette forme que lorsque la colonne est en réalité constante dans chaque groupe (par exemple une colonne fonctionnellement dépendante de la colonne groupée), sous peine d'un résultat qui dépend de l'ordre interne des lignes.

Résolution d'un nom dans GROUP BY, HAVING et ORDER BY#

Un nom sans qualificatif de table (ville, pas clients.ville) peut désigner soit une colonne d'une table du FROM, soit un alias de la liste SELECT. La règle de résolution diffère selon la clause :

• GROUP BY : une colonne réelle d'une table lue l'emporte sur un alias de même nom, tant qu'elle existe sans ambiguïté entre les tables du FROM. L'alias n'est utilisé que si aucune colonne de ce nom n'existe, ou pour lever une ambiguïté entre plusieurs tables qui portent chacune une colonne de ce nom.
• HAVING et ORDER BY : à l'inverse, un alias de la liste SELECT est prioritaire sur une colonne de table du même nom.
• Dans tous les cas, un nom qualifié (t.col) reste toujours une colonne de table, jamais un alias.

-- l'alias `nom` masque la colonne réelle `ville` pour ORDER BY, mais GROUP BY groupe sur la
-- colonne réelle `nom` de clients : chaque client a un nom distinct, donc un groupe par ligne,
-- pas un groupe par ville comme le donnerait un regroupement sur l'alias
SELECT UPPER(ville) AS nom, COUNT(*) FROM clients WHERE ville IS NOT NULL GROUP BY nom ORDER BY nom;

Un nom porté par plusieurs tables du FROM est ambigu (erreur 1052) sauf s'il désigne sans ambiguïté un alias de la liste SELECT, ou s'il est qualifié.

7.6 GROUP_CONCAT#

GROUP_CONCAT([DISTINCT] expr [, expr ...] [ORDER BY clé [ASC|DESC] [, ...]] [SEPARATOR 'chaîne'])

Concatène, pour chaque groupe, les valeurs de expr (les valeurs NULL sont ignorées), dans l'ordre indiqué par la clause ORDER BY interne à GROUP_CONCAT (indépendante d'un éventuel ORDER BY de la requête), séparées par SEPARATOR (, par défaut). DISTINCT élimine les valeurs répétées avant concaténation.

SELECT GROUP_CONCAT(nom) FROM clients WHERE ville = 'Oran';
-- Alice,Chloé

SELECT GROUP_CONCAT(nom ORDER BY nom DESC) FROM clients WHERE ville = 'Oran';
-- Chloé,Alice

SELECT ville, GROUP_CONCAT(DISTINCT id ORDER BY id DESC SEPARATOR ' / ') FROM clients
GROUP BY ville ORDER BY ville;

7.7 ORDER BY, LIMIT et OFFSET#

ORDER BY trie le résultat par une ou plusieurs expressions, chacune ASC (défaut) ou DESC ; une position numérique (ORDER BY 1) désigne l'item correspondant de la liste de sélection.

SELECT nom, solde FROM clients ORDER BY solde DESC, nom;

LIMIT borne le nombre de lignes rendues ; OFFSET (ou la forme LIMIT décalage, nombre) saute les premières lignes du résultat trié :

SELECT nom FROM clients ORDER BY solde DESC LIMIT 3;
SELECT nom FROM clients ORDER BY solde DESC LIMIT 1 OFFSET 4;
SELECT nom FROM clients ORDER BY solde DESC LIMIT 4, 1;   -- équivalent : LIMIT 1 OFFSET 4

7.8 Sous-requêtes#

Une sous-requête (SELECT entre parenthèses) peut apparaître :

• comme valeur scalaire, partout où une expression est attendue (elle doit rendre au plus une ligne et une colonne ; plus d'une ligne à l'exécution est l'erreur « sous-requête à une seule ligne ») ;
• dans un test [NOT] IN (sous-requête) ;
• dans un test [NOT] EXISTS (sous-requête) ;
• comme table dérivée dans FROM (voir §7.3).

Une sous-requête peut être corrélée : elle cite une colonne d'une table de la requête externe et est alors réévaluée pour chaque ligne de celle-ci.

-- scalaire, corrélée
SELECT nom, age FROM clients c WHERE solde = (
    SELECT MAX(solde) FROM clients v WHERE v.ville = c.ville
);

-- EXISTS / NOT EXISTS, corrélée
SELECT id FROM commandes o WHERE NOT EXISTS (
    SELECT 1 FROM clients c WHERE c.id = o.client_id
);

-- IN avec sous-requête
DELETE FROM commandes WHERE client_id NOT IN (SELECT id FROM clients);

-- sous-requête dans la liste de sélection
SELECT c.nom, (SELECT COUNT(*) FROM commandes o WHERE o.client_id = c.id) AS nb
FROM clients c ORDER BY c.id;

7.9 UNION#

requête_select UNION [ALL] requête_select [UNION [ALL] requête_select ...]
[ORDER BY ...] [LIMIT ...]

UNION combine les résultats de plusieurs SELECT de même nombre de colonnes ; UNION (sans ALL) élimine les lignes en double du résultat combiné, UNION ALL les conserve toutes.

SELECT ville FROM clients WHERE id < 4
UNION
SELECT ville FROM clients WHERE id > 3
ORDER BY ville;

SELECT client_id FROM commandes
UNION ALL
SELECT id FROM clients WHERE id > 5
ORDER BY 1 DESC LIMIT 3;

Un ORDER BY ou un LIMIT final, après le dernier membre, s'applique au résultat combiné de l'union entière, pas au seul dernier SELECT ; il peut citer une colonne d'un membre qualifiée par son nom de table d'origine (ORDER BY clients.id) aussi bien que par sa position ou son alias. Chaque membre peut être parenthésé pour lui donner son propre ORDER BY / LIMIT, appliqué avant la combinaison :

(SELECT nom FROM clients ORDER BY id DESC LIMIT 1)
UNION
(SELECT nom FROM clients ORDER BY id LIMIT 1);

Un ORDER BY ou un LIMIT placé sur un membre non parenthésé suivi d'un UNION est rejeté par la grammaire (erreur de syntaxe 1064) : parenthéser le membre pour lever l'ambiguïté entre « tri de ce membre » et « tri de l'union entière ».

7.10 Utiliser une vue#

Une vue se lit exactement comme une table, dans FROM, une jointure ou une sous-requête :

SELECT * FROM v_clients_actifs WHERE ville = 'Oran';

La création, la modification et le rafraîchissement des vues (vue ordinaire ou CACHED) sont décrits au chapitre 5, « Langage DDL ».

7.11 Fonctionnalités non disponibles dans cette version#

D'après une vérification dans le code du parseur et de l'exécuteur (le fichier readme.txt du projet est daté sur plusieurs de ces points) :

Ce tableau reflète l'état du code au moment de la rédaction ; se reporter au chapitre 16 (« Limites connues ») pour la liste à jour des fonctionnalités hors DML/SELECT (transactions, privilèges, réseau, etc.).