9. 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 sections 9.12 à 9.23 complètent ce socle : grammaire complète, jointures en pratique, opérations ensemblistes, expressions de table communes (récursives comprises), regroupements (ROLLUP, CUBE, GROUPING SETS), fonctions de fenêtrage, verrous, indications d'optimiseur, lecture d'EXPLAIN, recherche plein texte et SELECT … INTO. Les résultats affichés sont ceux de miraj-cli.

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);

9.1 Synopsis#

SELECT [DISTINCT] item [, item ...]
    [FROM table [jointure ...]]
    [WHERE condition]
    [GROUP BY expr [, expr ...]]
    [HAVING condition]
    [WINDOW nom AS (fenêtre) [, ...]]
    [QUALIFY condition]
    [ORDER BY expr [ASC|DESC] [, ...]]
    [LIMIT n [OFFSET m] | LIMIT m, n
     | [OFFSET m {ROW | ROWS}] [FETCH {FIRST | NEXT} [n] {ROW | ROWS} {ONLY | WITH TIES}]]

Ce synopsis est la forme de base ; la grammaire complète (WITH, UNION, verrous, OVER, etc.) est donnée au §9.12.

9.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;

+--------+---------+---------+
| nom    | balance | marge   |
+--------+---------+---------+
| Alice  |  100.50 | 110.550 |
| Bob    |  -20.00 | -22.000 |
| Chloé  |    0.00 |   0.000 |
| David  |    NULL |    NULL |
| Émile  |   75.25 |  82.775 |
| Fatima |   10.00 |  11.000 |
+--------+---------+---------+

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

Une colonne sans alias porte le texte de son expression (solde * 1.1). Le produit de deux décimaux a autant de décimales que ses deux opérandes réunis (DECIMAL(10,2) * 1.1 : trois).

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;

+-------------+
| ville       |
+-------------+
| NULL        |
| Alger       |
| Constantine |
| Oran        |
+-------------+

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

DISTINCTROW est un synonyme de DISTINCT ; ALL (défaut) garde les doublons.

COUNT(DISTINCT expr) compte les valeurs distinctes non NULL plutôt que les lignes ; avec plusieurs expressions, COUNT(DISTINCT a, b, ...) compte les combinaisons distinctes dont aucune expression n'est NULL :

SELECT COUNT(DISTINCT ville), COUNT(DISTINCT ville, solde) FROM clients;
-- 3 et 4 : (Constantine, NULL) et (NULL, 10.00) ne comptent pas

9.3 FROM et jointures#

Types de jointure pris en charge#

Colonnes fusionnées de USING et NATURAL.

• SELECT * rend d'abord chaque colonne commune une seule fois, dans l'ordre de la table de gauche, puis les autres colonnes de gauche, puis celles de droite. Pour RIGHT JOIN, la table de droite est la table conservée : colonnes communes dans son ordre, puis ses autres colonnes, puis celles de gauche.
• La colonne fusionnée vaut celle de la table conservée : la gauche pour INNER et LEFT JOIN, la droite pour RIGHT JOIN (jamais NULL du fait de la jointure). Ses métadonnées (table d'origine annoncée au client) sont celles de cette colonne.
• Une référence non qualifiée (id, dans la liste, WHERE, GROUP BY, ORDER BY, un ON suivant) désigne la colonne fusionnée, sans ambiguïté ; a.id et b.id restent accessibles (b.id vaut NULL sans correspondance d'un LEFT JOIN). t.* n'est pas fusionné : il rend toutes les colonnes de t.
• Dans une chaîne (a JOIN b USING (x) JOIN c USING (y)), chaque jointure fusionne avec le résultat qui la précède : les colonnes de la dernière passent en tête. Un NATURAL JOIN après une jointure ON dont les deux tables portent une colonne commune est ambigu (1052).
• Une vue CREATE VIEW v AS SELECT * FROM a LEFT JOIN b USING (id) a les colonnes fusionnées ; elle est refusée (1060) si une autre colonne existe des deux côtés sans être de la jointure. SHOW CREATE VIEW rend le texte écrit (NATURAL JOIN, USING, *).

CREATE TABLE n1 (id INT, v INT, z INT);
CREATE TABLE n2 (w INT, v INT, id INT);
INSERT INTO n1 VALUES (1, 10, 100), (2, 20, 200);
INSERT INTO n2 VALUES (7, 10, 1), (8, 99, 3);

SELECT * FROM n1 LEFT JOIN n2 USING (id);

+----+----+-----+------+------+
| id | v  | z   | w    | v    |
+----+----+-----+------+------+
|  1 | 10 | 100 |    7 |   10 |
|  2 | 20 | 200 | NULL | NULL |
+----+----+-----+------+------+

SELECT * FROM n1 NATURAL JOIN n2;           -- égalité sur id et v

+----+----+-----+---+
| id | v  | z   | w |
+----+----+-----+---+
|  1 | 10 | 100 | 7 |
+----+----+-----+---+

SELECT * FROM n1 NATURAL RIGHT JOIN n2;     -- colonnes communes dans l'ordre de n2

+----+----+---+------+
| v  | id | w | z    |
+----+----+---+------+
| 10 |  1 | 7 |  100 |
| 99 |  3 | 8 | NULL |
+----+----+---+------+

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)

Résultat de la troisième requête :

+-------+----+---------+
| nom   | id | montant |
+-------+----+---------+
| Alice |  1 |   10.00 |
| Alice |  2 |   20.50 |
| Bob   |  3 |    5.00 |
| NULL  |  4 |    1.00 |
| Chloé |  5 |    NULL |
+-------+----+---------+

Les résultats des autres types de jointure sont montrés au §9.13.

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;
-- (Alice, Chloé) et (Bob, Émile)

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;

+-------+--------+
| ville | total  |
+-------+--------+
| Oran  | 100.50 |
| Alger |  55.25 |
| NULL  |  10.00 |
+-------+--------+

Sans ORDER BY, l'ordre des groupes n'est pas garanti (ici, l'ordre de première apparition).

Une liste de noms après l'alias renomme ses colonnes (FROM (SELECT ville, SUM(solde) FROM clients GROUP BY ville) AS t (ville, total)) : autant de noms que de colonnes (erreur 1222), et pas sur un SELECT * (erreur 1235). Un constructeur de table VALUES (...), (...) s'y écrit aussi (§9.14).

Non pris en charge#

• 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 §9.9) :

  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;

9.4 WHERE#

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

Les comparaisons de textes (=, <, IN, BETWEEN…), GROUP BY, DISTINCT, ORDER BY et les jointures suivent l'interclassement des opérandes (celui de la colonne contre un littéral, celui d'un COLLATE explicite) : WHERE code = 'a1' ne rend pas 'A1' sur une colonne utf8mb4_bin, WHERE nom COLLATE utf8mb4_unicode_ci = 'strasse' rend 'Straße'. Deux interclassements sans vainqueur se comparent sous le plus large, sans erreur (voir Interclassements).

SELECT nom FROM clients WHERE solde BETWEEN 0 AND 100 AND ville IN ('Oran', 'Alger');
-- Chloé, Émile
SELECT nom FROM clients WHERE nom LIKE '_h%' OR nom REGEXP '^[A-E]';
-- Alice, Bob, Chloé, David
SELECT nom FROM clients WHERE ville IS NULL;
-- Fatima
SELECT 'Crème' LIKE '%creme%';
-- 1

Un index trigramme sur la colonne (chapitre 7, « Index plein texte et trigrammes ») sert col LIKE '%…%' sans changer le SQL.

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 14 (§14.6, « 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).

9.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;

+-------------+----------+
| ville       | COUNT(*) |
+-------------+----------+
| NULL        |        1 |
| Alger       |        2 |
| Constantine |        1 |
| Oran        |        2 |
+-------------+----------+

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

+-------+---+
| ville | c |
+-------+---+
| Alger | 2 |
| Oran  | 2 |
+-------+---+

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;

+-------------+----------+
| nom         | COUNT(*) |
+-------------+----------+
| ALGER       |        1 |
| ALGER       |        1 |
| CONSTANTINE |        1 |
| ORAN        |        1 |
| ORAN        |        1 |
+-------------+----------+

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é.

9.6 GROUP_CONCAT#

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

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. LIMIT ne garde que n valeurs, après en avoir passé m, une fois les doublons éliminés et les valeurs triées ; les clauses s'écrivent dans cet ordre (ORDER BY, SEPARATOR, LIMIT).

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 ' / ') AS ids FROM clients
GROUP BY ville ORDER BY ville;

+-------------+-------+
| ville       | ids   |
+-------------+-------+
| NULL        | 6     |
| Alger       | 5 / 2 |
| Constantine | 4     |
| Oran        | 3 / 1 |
+-------------+-------+

-- Le premier nom (ordre alphabétique) de chaque ville
SELECT ville, GROUP_CONCAT(nom ORDER BY nom LIMIT 1) AS premier FROM clients GROUP BY ville ORDER BY ville;

+-------------+---------+
| ville       | premier |
+-------------+---------+
| NULL        | Fatima  |
| Alger       | Bob     |
| Constantine | David   |
| Oran        | Alice   |
+-------------+---------+

La longueur du résultat est bornée par la variable group_concat_max_len (au-delà, la valeur est tronquée avec un avertissement).

9.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;

+--------+--------+
| nom    | solde  |
+--------+--------+
| Alice  | 100.50 |
| Émile  |  75.25 |
| Fatima |  10.00 |
| Chloé  |   0.00 |
| Bob    | -20.00 |
| David  |   NULL |
+--------+--------+

Les NULL sont classés en premier en ordre croissant, en dernier en ordre décroissant.

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;          -- Alice, Émile, Fatima
SELECT nom FROM clients ORDER BY solde DESC LIMIT 1 OFFSET 4;  -- Bob
SELECT nom FROM clients ORDER BY solde DESC LIMIT 4, 1;        -- équivalent : LIMIT 1 OFFSET 4

La pagination du SQL standard est acceptée à la place de LIMIT, partout où LIMIT l'est (sous-requête, table dérivée, membre ou fin d'une union, expression de table, vue, INSERT … SELECT, CREATE TABLE … AS SELECT, routine, instruction préparée) :

SELECT nom FROM clients ORDER BY solde DESC OFFSET 4 ROWS;                         -- Bob, David
SELECT nom FROM clients ORDER BY solde DESC FETCH FIRST 3 ROWS ONLY;               -- LIMIT 3
SELECT nom FROM clients ORDER BY solde DESC OFFSET 4 ROWS FETCH NEXT 1 ROW ONLY;   -- LIMIT 1 OFFSET 4
SELECT ville FROM clients ORDER BY ville FETCH FIRST 2 ROWS WITH TIES;

+-------+
| ville |
+-------+
| NULL  |
| Alger |
| Alger |
+-------+

La deuxième ligne (Alger) a un ex æquo, rendu en plus des deux lignes demandées.

• FIRST et NEXT sont synonymes, comme ROW et ROWS ; le nombre omis vaut 1 (FETCH FIRST ROW ONLY). ROW ou ROWS est obligatoire après le décalage d'OFFSET.
• WITH TIES rend, après les n lignes, celles qui suivent tant qu'elles sont ex æquo avec la dernière sur toutes les clés de l'ORDER BY : même valeur selon l'interclassement de la clé ('B' et 'b' sous un interclassement insensible à la casse), deux NULL égaux entre eux. Les ex æquo sautés par OFFSET ne reviennent pas ; FETCH FIRST 0 ROWS WITH TIES ne rend rien. Un ORDER BY de constantes laisse toutes les lignes ex æquo : toutes sont rendues.
• WITH TIES exige un ORDER BY dans le même bloc (celui de l'union entière pour une union) : erreur 4180 sinon.
• LIMIT et OFFSET … ROWS / FETCH ne se combinent pas dans un même bloc : erreur de syntaxe (1064).
• Avec WITH TIES, le tri n'est pas borné : toutes les lignes sont triées avant la coupe (pas de tri limité aux n premières, ni de limite poussée aux fragments d'une table partitionnée).

9.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 §9.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 : le plus gros solde de chaque ville
SELECT nom, solde FROM clients c WHERE solde = (
    SELECT MAX(solde) FROM clients v WHERE v.ville = c.ville
);

+-------+--------+
| nom   | solde  |
+-------+--------+
| Alice | 100.50 |
| Émile |  75.25 |
+-------+--------+

-- EXISTS / NOT EXISTS, corrélée : commandes dont le client n'existe pas
SELECT id FROM commandes o WHERE NOT EXISTS (
    SELECT 1 FROM clients c WHERE c.id = o.client_id
);
-- 4

-- IN avec sous-requête
SELECT nom FROM clients WHERE id IN (SELECT client_id FROM commandes);
-- Alice, Bob, Chloé

-- 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;

+--------+----+
| nom    | nb |
+--------+----+
| Alice  |  2 |
| Bob    |  1 |
| Chloé  |  1 |
| David  |  0 |
| Émile  |  0 |
| Fatima |  0 |
+--------+----+

9.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;

+-------------+
| ville       |
+-------------+
| NULL        |
| Alger       |
| Constantine |
| Oran        |
+-------------+

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

+-----------+
| client_id |
+-----------+
|         9 |
|         6 |
|         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 cite une colonne du résultat par son nom (celui du premier membre), sa position ou une expression de ces colonnes. Une colonne qualifiée par sa table (ORDER BY clients.id) est refusée (erreur 1250, Table 'clients' from one of the SELECTs cannot be used in global ORDER clause). 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);
-- Fatima, Alice

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 ».

9.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 7, « Langage DDL ».

9.11 Fonctionnalités non disponibles dans cette version#

État des fonctionnalités de requête de cette version :

Ce tableau reflète l'état du code au moment de la rédaction.

9.12 Grammaire complète#

Grammaire de SELECT ([ ] : facultatif, | : alternative, { } : au moins un élément) :

requête ::=
    [ WITH nom [(col, ...)] AS ( requête ) [, ...] ]
    membre [ UNION [ALL | DISTINCT] membre ... ]
    [ ORDER BY expr [ASC|DESC] [, ...] ]          -- après le dernier membre : porte sur l'union
    [ LIMIT ... ]                                  -- idem

membre ::=  select  |  ( requête )
         |  VALUES (expr, ...) [, (expr, ...) ...]     -- constructeur de table (§9.14)

select ::=
    SELECT [ /*+ indications */ ] [ DISTINCT | DISTINCTROW | ALL ]
           [ SQL_CALC_FOUND_ROWS | SQL_NO_CACHE | SQL_CACHE | HIGH_PRIORITY | STRAIGHT_JOIN ] ...
           item [, item ...]
    [ INTO @variable [, ...] | INTO OUTFILE 'fichier' ... | INTO DUMPFILE 'fichier' ]
    [ FROM DUAL | source [ jointure ... ] ]
    [ WHERE condition ]
    [ GROUP BY expr [ASC|DESC] [, ...] [WITH ROLLUP]
    | GROUP BY { expr | ROLLUP(...) | CUBE(...) | GROUPING SETS (...) } [, ...] ]
    [ HAVING condition ]
    [ WINDOW nom AS (fenêtre) [, ...] ]            -- fenêtres nommées (§9.18)
    [ QUALIFY condition ]                          -- filtre sur les fonctions de fenêtrage (§9.18)
    [ ORDER BY expr [ASC|DESC] [, ...] ]
    [ LIMIT { n | décalage, n | n OFFSET décalage } [ROWS EXAMINED m]
    | [OFFSET décalage {ROW | ROWS}] [FETCH {FIRST | NEXT} [n] {ROW | ROWS} {ONLY | WITH TIES}] ]
    [ INTO ... ]                                   -- autre place admise pour INTO (§9.23)
    [ FOR UPDATE | FOR SHARE [OF table, ...] [NOWAIT | WAIT s | SKIP LOCKED]
    | LOCK IN SHARE MODE [NOWAIT | WAIT s | SKIP LOCKED] ]
    [ INTO ... ]                                   -- ou après la clause de verrou

item    ::=  * | table.* | base.table.* | expr [[AS] alias]
source  ::=  table [PARTITION (p, ...)] [FOR SYSTEM_TIME période] [[AS] alias]
          |  JSON_TABLE(document, 'chemin' COLUMNS (...)) [AS] alias
          |  ( requête ) [AS] alias [(col, ...)]  |  ( source jointure ... )
-- FOR SYSTEM_TIME : tables versionnées (chapitre 12) ; JSON_TABLE : chapitre 10, §10.10
jointure ::=
      , source
    | [INNER | CROSS] JOIN source [ON condition | USING (col, ...)]
    | STRAIGHT_JOIN source [ON condition | USING (col, ...)]
    | LEFT [OUTER] JOIN source { ON condition | USING (col, ...) }
    | RIGHT [OUTER] JOIN source { ON condition | USING (col, ...) }
    | NATURAL [INNER | LEFT [OUTER] | RIGHT [OUTER]] JOIN source

Points de détail :

• Un JOIN sans ON ni USING est un produit cartésien ; un LEFT ou RIGHT JOIN sans ON ni USING est une erreur de syntaxe (1064).
• SELECT sans FROM (ou FROM DUAL) est permis ; le mot DUAL n'est reconnu qu'à la place de la première table.
• SQL_NO_CACHE, SQL_CACHE et HIGH_PRIORITY sont acceptés et sans effet sur le résultat ; SQL_CALC_FOUND_ROWS fait compter à FOUND_ROWS() les lignes que le SELECT rendrait sans son LIMIT (chapitre 10, §10.9) ; DISTINCTROW équivaut à DISTINCT.
• Un groupe de jointures entre parenthèses en tête de FROM (FROM (a LEFT JOIN b ON ...)) est accepté ; il est mis à plat, les parenthèses ne changent pas le sens.
• GROUP BY expr ASC|DESC est accepté mais n'impose aucun tri : écrire un ORDER BY.
• LIMIT accepte un entier, un paramètre ? d'une instruction préparée ou une variable locale d'une routine ; LIMIT 18446744073709551615 (idiome « toutes les lignes ») est accepté.
• LIMIT n ROWS EXAMINED m borne le nombre de lignes examinées par toute l'instruction.
• OFFSET m ROWS et FETCH FIRST n ROWS ONLY | WITH TIES (§9.7) acceptent les mêmes valeurs que LIMIT. FETCH n'est pas un mot réservé : il reste utilisable comme nom de table, de colonne ou d'alias ; il n'ouvre la clause que suivi de FIRST ou NEXT, d'un nombre facultatif, de ROW ou ROWS, puis de ONLY ou WITH.
• QUALIFY n'est pas un mot réservé : qualify reste utilisable comme nom de table, de colonne ou d'alias. Juste après une table, il ouvre la clause quand il est suivi d'une condition (FROM t QUALIFY ROW_NUMBER() OVER (...) = 1) et sert d'alias sinon (FROM t qualify WHERE ..., FROM t qualify, u) ; pour nommer qualify une table suivie de la clause, écrire AS qualify.
• WINDOW n'est pas un mot réservé : window reste utilisable comme nom de table, de colonne ou d'alias ; il n'ouvre la clause que suivi d'un nom, de AS et d'une parenthèse (FROM t WINDOW w AS (...)).
• INTO (variables, fichier) n'est permis que dans le premier SELECT de l'instruction (dans une union, il reçoit le résultat de l'union entière), une seule fois, jamais dans une expression de table ni une sous-requête (1064).

Toutes les formes ci-dessous supposent les tables clients et commandes de l'en-tête du chapitre.

9.13 Jointures en pratique#

Récapitulatif des types de jointure#

Exemples et résultats#

INNER JOIN : les clients sans commande et les commandes sans client disparaissent.

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

+-------+----+
| nom   | id |
+-------+----+
| Alice |  1 |
| Alice |  2 |
| Bob   |  3 |
| Chloé |  5 |
+-------+----+

LEFT JOIN : tous les clients ; NULL là où il n'y a pas de commande.

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

+--------+------+
| nom    | id   |
+--------+------+
| Alice  |    1 |
| Alice  |    2 |
| Bob    |    3 |
| Chloé  |    5 |
| David  | NULL |
| Émile  | NULL |
| Fatima | NULL |
+--------+------+

RIGHT JOIN : toutes les commandes ; la commande 4 référence un client inexistant (id 9).

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

+-------+----+
| nom   | id |
+-------+----+
| Alice |  1 |
| Alice |  2 |
| Bob   |  3 |
| NULL  |  4 |
| Chloé |  5 |
+-------+----+

CROSS JOIN : le produit cartésien de 6 clients par 5 commandes donne 30 lignes ; à réserver aux cas voulus (tables de paramètres, génération de combinaisons).

Anti-jointure (les clients sans aucune commande) : LEFT JOIN ... IS NULL ou NOT EXISTS, qui donnent le même résultat.

SELECT c.nom FROM clients c LEFT JOIN commandes o ON o.client_id = c.id WHERE o.id IS NULL ORDER BY c.id;
SELECT nom FROM clients c WHERE NOT EXISTS (SELECT 1 FROM commandes o WHERE o.client_id = c.id) ORDER BY id;

+--------+
| nom    |
+--------+
| David  |
| Émile  |
| Fatima |
+--------+

Jointure avec une table dérivée (agrégat par client, puis jointure) ; passer en LEFT JOIN conserve les clients sans commande :

SELECT c.nom, t.total
FROM clients c
LEFT JOIN (SELECT client_id, SUM(montant) AS total FROM commandes GROUP BY client_id) t
       ON t.client_id = c.id
ORDER BY c.id;

+--------+-------+
| nom    | total |
+--------+-------+
| Alice  | 30.50 |
| Bob    |  5.00 |
| Chloé  |  NULL |
| David  |  NULL |
| Émile  |  NULL |
| Fatima |  NULL |
+--------+-------+

Jointures multiples : elles s'enchaînent de gauche à droite dans le même FROM, avec un alias distinct par occurrence d'une table (sans alias distinct : erreur 1066) ; les types peuvent être mélangés (JOIN, LEFT JOIN, RIGHT JOIN). Une condition ON peut citer les tables placées avant elle :

SELECT c.nom, o.id AS cmd, x.id AS suivante
FROM clients c
JOIN commandes o ON o.client_id = c.id
LEFT JOIN commandes x ON x.client_id = c.id AND x.id > o.id
ORDER BY o.id;

+-------+-----+----------+
| nom   | cmd | suivante |
+-------+-----+----------+
| Alice |   1 |        2 |
| Alice |   2 |     NULL |
| Bob   |   3 |     NULL |
| Chloé |   5 |     NULL |
+-------+-----+----------+

Auto-jointure : les paires de clients de la même ville (voir §9.3) ; l'alias est obligatoire pour distinguer les deux occurrences. a.id < b.id évite les paires symétriques et les paires d'un client avec lui-même :

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

+-------+-------+
| nom   | nom   |
+-------+-------+
| Alice | Chloé |
| Bob   | Émile |
+-------+-------+

FULL OUTER JOIN émulé : LEFT JOIN puis RIGHT JOIN réunis par UNION (qui élimine les doublons ; l'ORDER BY final porte sur l'union entière) :

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
ORDER BY 1, 2;

+--------+------+
| nom    | id   |
+--------+------+
| NULL   |    4 |
| Alice  |    1 |
| Alice  |    2 |
| Bob    |    3 |
| Chloé  |    5 |
| David  | NULL |
| Émile  | NULL |
| Fatima | NULL |
+--------+------+

Avec UNION (et non UNION ALL), deux lignes réellement identiques des deux côtés sont fusionnées : si des lignes en double sont significatives, ajouter une colonne d'identité (ici o.id, c.id) ou construire l'union avec UNION ALL et un WHERE o.id IS NULL sur la partie droite.

USING : condition d'égalité sur des colonnes de même nom ; la colonne peut être citée sans qualificatif. SELECT * rend la colonne commune une seule fois, en tête, puis les autres colonnes de chaque table (§9.3).

SELECT * FROM clients c1 JOIN clients c2 USING (ville) WHERE c1.id < c2.id;

+-------+----+-------+--------+----+-------+-------+
| ville | id | nom   | solde  | id | nom   | solde |
+-------+----+-------+--------+----+-------+-------+
| Oran  |  1 | Alice | 100.50 |  3 | Chloé |  0.00 |
| Alger |  2 | Bob   | -20.00 |  5 | Émile | 75.25 |
+-------+----+-------+--------+----+-------+-------+

Quel type de jointure choisir#

• Les lignes d'une table qui n'ont pas de correspondance doivent-elles rester dans le résultat ? Non : INNER JOIN. Oui, celles de la table principale : LEFT JOIN (écrire la table principale à gauche ; RIGHT JOIN est son symétrique et n'apporte rien de plus).
• Chercher les lignes sans correspondance : LEFT JOIN ... WHERE droite.clé IS NULL ou NOT EXISTS ; NOT IN (sous-requête) est piégeux avec NULL (voir plus bas).
• Simplement tester l'existence d'une correspondance sans dupliquer les lignes de gauche : WHERE EXISTS (...) ou IN (sous-requête) plutôt qu'une jointure, qui multiplie la ligne de gauche par le nombre de correspondances (Alice apparaît deux fois dans le premier exemple).
• Il faut des valeurs agrégées de l'autre table : agréger d'abord dans une table dérivée puis joindre, ce qui évite de multiplier les lignes avant l'agrégat.

Comportement de NULL dans les jointures#

• Une condition ON a.x = b.y n'est jamais vraie quand l'un des côtés est NULL : deux NULL ne se joignent pas entre eux. Fatima (ville NULL) ne se joint à aucun client dans la jointure par ville ci-dessus, pas même à elle-même.

• Dans un LEFT JOIN, les colonnes du côté droit sans correspondance valent NULL. Une condition WHERE sur une colonne de la table de droite (WHERE o.montant > 10) élimine ces lignes (une comparaison avec NULL n'est jamais vraie) et transforme de fait le LEFT JOIN en INNER JOIN. Pour filtrer la table de droite tout en gardant toutes les lignes de gauche, mettre la condition dans le ON :

  SELECT c.nom, o.id FROM clients c LEFT JOIN commandes o ON o.client_id = c.id AND o.montant > 10 ORDER BY c.id;

  +--------+------+
  | nom    | id   |
  +--------+------+
  | Alice  |    2 |
  | Bob    | NULL |
  | Chloé  | NULL |
  | David  | NULL |
  | Émile  | NULL |
  | Fatima | NULL |
  +--------+------+

  Avec WHERE o.montant > 10 à la place, seule la ligne d'Alice reste.

• Compter les correspondances : COUNT(o.id) ignore les NULL (0 pour un client sans commande), COUNT(*) compte la ligne « sans correspondance » elle-même (1) :

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

  +--------+----+--------+
  | nom    | nb | lignes |
  +--------+----+--------+
  | Alice  |  2 |      2 |
  | Bob    |  1 |      1 |
  | Chloé  |  1 |      1 |
  | David  |  0 |      1 |
  | Émile  |  0 |      1 |
  | Fatima |  0 |      1 |
  +--------+----+--------+

• NOT IN (sous-requête) rend zéro ligne dès que la sous-requête produit un NULL (voir §9.16) ; NOT EXISTS n'a pas ce piège.
• Une colonne d'une table du côté droit d'un LEFT JOIN devient nullable pour le reste de la requête, même si elle est NOT NULL dans la table (de même pour le côté gauche d'un RIGHT JOIN).

Notes de performance propres au moteur#

• Algorithme de jointure. Lorsque la condition ON (ou USING) contient au moins une égalité entre une colonne de chaque côté (o.client_id = c.id, éventuellement combinée par AND à d'autres conditions), le moteur utilise une jointure par hachage. Sinon (ON a.solde > b.solde, OR, expression, produit cartésien) il utilise une boucle imbriquée, dont le coût est le produit des tailles. L'égalité ne doit porter que sur des colonnes de même famille de type (nombre avec nombre, chaîne avec chaîne) : comparer une chaîne à un nombre exclut la jointure par hachage.
• Ordre des jointures. Les jointures sont planifiées dans l'ordre d'écriture du FROM, sans réordonnancement fondé sur les statistiques ; STRAIGHT_JOIN n'y ajoute rien (il est traité comme JOIN). Écrire d'abord la table qui filtre le plus.
• Index. Les index (clé primaire, UNIQUE, secondaires) servent aux lectures de table par égalité avec une constante (WHERE id = 1, WHERE ville = 'Oran', voir §9.20) ; les intervalles (id > 2) parcourent la table, sauf sur une colonne VECTOR d'un index (chapitre 11).
• Jointure par sonde d'index. Pour un INNER ou un LEFT JOIN sur égalité, la table jointe est lue par son index au lieu d'être chargée en entier quand elle est grande face à la table qui la précède (au moins 1 000 lignes et huit fois plus que celle-ci) et que la colonne égalée porte la clé primaire, un index UNIQUE ou un index secondaire (clé entière, binaire ou texte égalée sous l'interclassement de l'index ; ni colonne virtuelle, ni BLOB / TEXT entier, ni index à préfixe). Quand la colonne égalée est un VECTOR (première colonne d'un index, ou après des colonnes égalées), la sonde est retenue dès que la table pilote n'est pas estimée plus grande que la table jointe. EXPLAIN montre alors eq_ref ou ref pour la table jointe, avec la colonne de la table pilote en ref (exemple au §9.20). Les indications USE, FORCE et IGNORE INDEX de la table jointe choisissent l'index sondé (§9.19). Sinon, l'appariement se fait par la jointure par hachage (ALL pour les deux tables).
• Jointures externes inutiles. Un LEFT JOIN dont aucune colonne n'est lue et qui ne peut pas multiplier les lignes (jointure sur une clé unique, ou requête DISTINCT) est retiré du plan. Cette optimisation ne s'applique pas si la table jointe est citée par *, t.*, une colonne qualifiée ou une sous-requête, ni pour une vue ou une table dérivée.
• Requêtes coûteuses. max_join_size (avec sql_big_selects) refuse un bloc SELECT dont l'estimation de lignes examinées est trop grande (erreur 1104), avant toute exécution. Les intervalles et les OR ne réduisent pas cette estimation ; EXPLAIN n'applique pas le contrôle.
• Vues et tables dérivées. Une vue est lue comme sa requête spécialisée par les conditions de l'appelant ; une expression de table commune est remplacée par sa requête (§9.15).

9.14 Opérations ensemblistes : UNION, INTERSECT, EXCEPT#

• UNION [ALL | DISTINCT] : pris en charge (§9.9). DISTINCT après UNION est le comportement par défaut, identique à l'absence de mot-clé. Le nombre de colonnes des membres doit être identique (erreur 1222 sinon) ; les noms de colonnes du résultat sont ceux du premier membre.
• INTERSECT [ALL | DISTINCT] : lignes présentes dans les deux requêtes.
• EXCEPT [ALL | DISTINCT] : lignes de la première requête absentes de la seconde. MINUS en est un synonyme, accepté dans tous les modes ; minus reste un nom de colonne ou de table possible quand il n'est pas suivi d'une requête.

-- Identifiants présents dans clients ET dans commandes.client_id
SELECT id FROM clients INTERSECT SELECT client_id FROM commandes ORDER BY id;
-- 1, 2, 3
-- Identifiants de clients sans commande
SELECT id FROM clients EXCEPT SELECT client_id FROM commandes ORDER BY id;
-- 4, 5, 6
SELECT id FROM clients MINUS SELECT client_id FROM commandes ORDER BY id;   -- même résultat

Avec ALL, les doublons comptent :

SELECT x FROM (VALUES (1), (1), (1), (2)) a (x)
INTERSECT ALL
SELECT y FROM (VALUES (1), (1), (3)) b (y);      -- 1, 1

SELECT x FROM (VALUES (1), (1), (1), (2)) a (x)
EXCEPT ALL
SELECT y FROM (VALUES (1), (1), (3)) b (y);      -- 1, 2

Règles :

• NULL : pour ces opérations, deux NULL sont égaux (contrairement à = et à IN) ; SELECT NULL INTERSECT SELECT NULL rend une ligne NULL, SELECT 1, NULL EXCEPT SELECT 1, NULL n'en rend aucune. Les chaînes se comparent selon l'interclassement de la colonne, comme pour DISTINCT : avec un interclassement insensible à la casse, 'a' INTERSECT 'A' rend a.
• Sans ALL (ou avec DISTINCT) : chaque ligne du résultat n'apparaît qu'une fois. Avec ALL : multiensembles ; une valeur présente m fois à gauche et n fois à droite apparaît min(m, n) fois avec INTERSECT ALL, max(m − n, 0) fois avec EXCEPT ALL.
• Priorité : INTERSECT lie plus fort que UNION et EXCEPT ; les autres opérations se calculent de gauche à droite. a UNION b INTERSECT c vaut a UNION (b INTERSECT c), et a EXCEPT b EXCEPT c vaut (a EXCEPT b) EXCEPT c. Les parenthèses regroupent librement : SELECT … EXCEPT (SELECT … UNION SELECT …), ((SELECT …) INTERSECT (SELECT …)) UNION ….
• Colonnes : même nombre de colonnes dans chaque membre (erreur 1222 sinon), type commun et noms du premier membre, comme pour UNION (§9.9).
• Tri et limite : un ORDER BY / LIMIT / OFFSET écrit après le dernier membre non parenthésé porte sur le résultat entier ; un membre qui a les siens s'écrit entre parenthèses ((SELECT … ORDER BY x LIMIT 2) INTERSECT SELECT …). (requête composée … LIMIT 2) ORDER BY 1 trie le résultat de la requête entre parenthèses.
• Ces requêtes s'emploient partout où UNION est permis : table dérivée, sous-requête (IN, EXISTS, scalaire), expression de table commune (WITH, sauf dans la partie récursive d'un WITH RECURSIVE : erreur 1235), INSERT … SELECT, CREATE TABLE … SELECT, vue (SHOW CREATE VIEW rend le texte écrit), corps de procédure, de fonction, de déclencheur et d'événement.
• Exécution par hachage : le membre de droite est lu en entier et ses lignes comptées par valeur, puis les lignes de gauche sont rendues dans leur ordre de lecture. EXPLAIN montre un bloc par membre (PRIMARY, puis INTERSECT, EXCEPT ou UNION) ; une suite d'intersections regroupée par priorité, ou un groupe entre parenthèses, y apparaît comme une table dérivée (DERIVED). EXPLAIN FORMAT=JSON nomme le résultat <intersect1,2>, <except1,2> ou <unit1,2,3> pour des opérations mêlées.

Constructeur de table VALUES#

VALUES (expr, ...) [, (expr, ...) ...] [ORDER BY ...] [LIMIT ... | OFFSET ... FETCH ...]

VALUES (1, 'a'), (2, 'b') est une requête qui rend une ligne par parenthèse, comme un UNION ALL d'un SELECT sans table par ligne :

• les colonnes portent le texte des expressions de la première ligne (1 et a ici) ; leur type est le type commun des lignes (VALUES (1), (2.5) rend 1.0 et 2.5) ;
• toutes les lignes ont le même nombre de valeurs (erreur 4099) ;
• aucune colonne n'y est visible, même celle d'une requête englobante (erreur 4100) ; une sous-requête écrite dans une ligne lit ses propres tables (VALUES ((SELECT MAX(id) FROM t))). Dans une routine, les variables locales et les paramètres y sont lisibles, et un autre nom y est refusé dès la création de la routine (4100) ;
• ORDER BY et LIMIT (ou OFFSET … FETCH) portent sur toutes ses lignes ; un membre trié ou limité suivi d'une opération ensembliste s'écrit entre parenthèses, comme un SELECT ;
• combiné par UNION, INTERSECT ou EXCEPT, il compte pour un seul membre : SELECT 1 UNION VALUES (1), (1) rend une ligne, SELECT 1 UNION ALL VALUES (1), (1) en rend trois.

Il s'écrit partout où une requête est permise :

VALUES (1, 'a'), (2, 'b');
SELECT v.n, c.nom FROM (VALUES (1), (3)) AS v (n) JOIN clients c ON c.id = v.n;
SELECT nom FROM clients WHERE id IN (VALUES (1), (3));
WITH t (a) AS (VALUES (1), (2)) SELECT a FROM t;
INSERT INTO cible SELECT * FROM (VALUES (1, 'a'), (2, 'b')) AS v;
CREATE TABLE paires AS VALUES (1, 'a'), (2, 'b');
CREATE VIEW couples AS VALUES (1, 'a'), (2, 'b');

Une table dérivée sans liste de noms garde les noms de la première ligne, à écrire entre accents graves quand ce ne sont pas des identifiants (v.`1`). CREATE TABLE … AS VALUES nomme ses colonnes de la même façon : une première ligne dont le texte ne fait pas un nom de colonne admis (1.5) rend l'erreur 1166 ; passer par une table dérivée avec sa liste de noms.

Dans un ON DUPLICATE KEY UPDATE, VALUES (colonne) reste la fonction qui lit la valeur proposée (chapitre 8), même entre parenthèses ; EXPLAIN montre un bloc sans table par ligne.

9.15 Expressions de table communes (WITH)#

WITH nom [(colonne, ...)] AS ( SELECT ... | VALUES (expr, ...), ... ) [, nom2 AS (...)]
SELECT ... ;

Une expression de table commune (CTE) donne un nom à une requête réutilisable dans la suite de l'instruction, y compris plusieurs fois et dans une jointure avec elle-même. Vérifié :

• plusieurs CTE séparées par des virgules, une CTE pouvant citer celles qui la précèdent ;
• une liste de colonnes après le nom renomme les colonnes (le nombre doit correspondre, erreur 1222 ; incompatible avec un corps SELECT *, erreur 1235) ;
• un corps VALUES (...), (...) est accepté (constructeur de table, §9.14) ; sans liste de colonnes, les noms de colonnes sont ceux de la première ligne (1, a) : donner une liste de colonnes ;
• le WITH écrit en tête de la requête vaut pour toute l'union (WITH c AS (…) (SELECT …) EXCEPT (SELECT …) compris) ; écrit dans un membre entre parenthèses, il ne vaut que pour ce membre ;
• WITH RECURSIVE permet à une expression de se citer elle-même (voir ci-dessous).

Le moteur remplace chaque référence à une CTE par sa requête (comme une table dérivée), et limite le nombre total de ces expansions pour borner la taille d'une requête : une CTE référencée à de nombreux niveaux imbriqués peut être refusée par une erreur de syntaxe 1064.

WITH c(v, n) AS (SELECT ville, COUNT(*) FROM clients GROUP BY ville),
     m AS (SELECT MAX(n) AS mx FROM c)
SELECT c.v FROM c, m WHERE c.n = m.mx ORDER BY c.v;

+-------+
| v     |
+-------+
| Alger |
| Oran  |
+-------+

WITH t(a, b) AS (VALUES (1, 'a'), (2, 'b')) SELECT * FROM t;

+---+---+
| a | b |
+---+---+
| 1 | a |
| 2 | b |
+---+---+

Expressions récursives (WITH RECURSIVE)#

WITH RECURSIVE nom [(colonne, ...)] AS (
    membre non récursif [UNION [ALL] membre non récursif ...]
    UNION [ALL | DISTINCT]
    membre récursif [UNION [ALL] membre récursif ...]
) SELECT ... ;

Après WITH RECURSIVE, une expression peut se citer dans son propre corps. Les membres qui ne la citent pas (au moins un, en tête) donnent les premières lignes ; chaque itération lit ensuite les membres récursifs sur les seules lignes rendues par l'itération précédente, jusqu'à ce qu'une itération n'en rende plus. Une expression d'un WITH RECURSIVE qui ne se cite pas reste ordinaire.

• Les types des colonnes sont ceux des membres non récursifs : les lignes des membres récursifs y sont converties. Pour construire un chemin qui s'allonge, élargir la colonne dans le membre non récursif (CAST(libelle AS CHAR(200))).
• Avec UNION (ou UNION DISTINCT) au lieu de UNION ALL, une ligne déjà rendue n'est ni rendue ni relue une seconde fois : un parcours de graphe avec cycles se termine.
• cte_max_recursion_depth (session ou globale, défaut 1000, de 0 à 4 294 967 295) borne le nombre d'itérations, celle qui ne rend plus rien comprise : au-delà, erreur 3636 « Recursive query aborted after n iterations ».
• Un LIMIT de l'union entière (sans ORDER BY) arrête les itérations dès qu'il est atteint.
• Erreurs : sans UNION, 3573 ; membre non récursif placé après un membre récursif, 3574 ; agrégat, fonction de fenêtrage ou GROUP BY dans un membre récursif, 3575.
• L'expression est calculée entièrement avant la lecture de la requête qui la cite, et une fois par référence (comme une table dérivée).

-- Comptes du plan comptable sous le compte 60, avec leur niveau et leur chemin
WITH RECURSIVE arbre (id, niveau, chemin) AS (
    SELECT id, 0, CAST(libelle AS CHAR(200)) FROM comptes WHERE id = 60
    UNION ALL
    SELECT c.id, a.niveau + 1, CONCAT(a.chemin, ' > ', c.libelle)
    FROM comptes c JOIN arbre a ON c.parent = a.id
) SELECT id, niveau, chemin FROM arbre ORDER BY chemin;

+------+--------+---------------------------+
| id   | niveau | chemin                    |
+------+--------+---------------------------+
|   60 |      0 | Achats                    |
|  601 |      1 | Achats > Matieres         |
| 6011 |      2 | Achats > Matieres > Acier |
+------+--------+---------------------------+

9.16 Sous-requêtes : compléments#

Formes prises en charge (§9.8) : scalaire, IN, EXISTS, corrélée, table dérivée, et sous-requête dans la liste de sélection, y compris dans une expression (EXISTS (...) comme valeur 0/1).

• Sous-requête scalaire : zéro ligne donne NULL ; plus d'une ligne est l'erreur 1242 (« Subquery returns more than 1 row »).

  SELECT nom FROM clients WHERE id = (SELECT client_id FROM commandes WHERE id = 99);   -- 0 ligne (NULL)
  SELECT (SELECT id FROM clients);                                                         -- erreur 1242

• Corrélée : peut citer une colonne de la requête externe (par alias ou par nom de table, en WHERE, dans la liste de sélection, en IN, en EXISTS), y compris dans un test de comparaison : SELECT nom FROM clients WHERE id IN (SELECT client_id FROM commandes WHERE montant > clients.solde) rend Bob.
• ANY, SOME, ALL : x op ANY (sous-requête), x op SOME (...), x op ALL (...) avec =, <> (!=), <, <=, >, >= ; la sous-requête, corrélée ou non, produit une colonne. ANY est vrai si la comparaison l'est pour une ligne au moins, ALL si elle l'est pour toutes. Sans ligne, ANY est faux et ALL vrai. La logique est à trois valeurs : un NULL parmi les valeurs rend le résultat inconnu quand aucune ligne ne tranche (5 > ALL (SELECT 1 UNION ALL SELECT NULL) vaut NULL), si bien que x <> ALL (S) équivaut à x NOT IN (S) et x = ANY (S) à x IN (S). Le <=> ne se quantifie pas.
• Constructeur de ligne : (a, b) = (1, 2), ROW(a, b) <> ROW(1, 2), comparaisons d'ordre (lexicographiques : (a, b) < (c, d) vaut a < c OR (a = c AND b < d)), (a, b) IN ((1, 'x'), ...), (a, b) [NOT] IN (SELECT x, y ...) et (a, b) op ANY | ALL (SELECT x, y ...). Les deux côtés doivent avoir le même nombre d'éléments, sinon erreur 1241 (« Operand should contain N column(s) ») ; un constructeur de ligne isolé (SELECT ROW(1, 2)) est refusé de la même façon. (a, b) = (SELECT ...) (sous-requête scalaire à plusieurs colonnes) n'est pas pris en charge : écrire (a, b) IN (SELECT ...).

• LIMIT dans une sous-requête IN : accepté par ce moteur.

  SELECT nom FROM clients WHERE id IN (SELECT client_id FROM commandes ORDER BY id LIMIT 2);

  Rend Alice (les deux premières commandes sont celles du client 1).

• IN et NULL : x IN (S) vaut TRUE si une valeur correspond ; sinon UNKNOWN (et non FALSE) si S contient un NULL. Le piège est dans NOT IN : dès que S contient un NULL, le test ne vaut jamais TRUE, et la requête ne rend aucune ligne.

  SELECT nom FROM clients WHERE id NOT IN (SELECT client_id FROM commandes UNION ALL SELECT NULL);
  -- 0 ligne
  
  SELECT nom FROM clients c WHERE NOT EXISTS (SELECT 1 FROM commandes o WHERE o.client_id = c.id) ORDER BY id;
  -- David, Émile, Fatima

  Préférer NOT EXISTS, ou ajouter WHERE col IS NOT NULL dans la sous-requête de NOT IN.

• Sous-requête non corrélée : elle ne dépend d'aucune colonne externe ; le résultat est le même qu'une valeur constante (WHERE solde > (SELECT AVG(solde) FROM clients) rend Alice et Émile).
• Table dérivée : l'alias est obligatoire (§9.3). LATERAL (table dérivée qui cite une autre table du même FROM) est non pris en charge (1064) ; utiliser une sous-requête corrélée dans la liste de sélection, ou une table dérivée agrégée puis jointe.

9.17 GROUP BY, HAVING et regroupements : compléments#

• GROUP BY accepte des expressions, des positions (GROUP BY 1) et des alias (règles de résolution au §9.5). Les valeurs NULL forment un seul groupe.
• Fonctions d'agrégat prises en charge dans SELECT ... GROUP BY : COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT (§9.6), STD / STDDEV / STDDEV_POP, VARIANCE (le détail figure au chapitre 10). COUNT(DISTINCT expr) et SUM(DISTINCT expr) filtrent les valeurs distinctes ; COUNT(DISTINCT a, b, ...) compte les combinaisons distinctes sans NULL.
• Sans GROUP BY, une fonction d'agrégat produit toujours une ligne, même sur une table vide (COUNT(*) rend 0, les autres NULL) ; avec GROUP BY sur zéro ligne, le résultat est vide.
• HAVING peut citer un alias de la liste de sélection (§9.5) ; une condition sur une colonne non agrégée relève plutôt de WHERE, plus efficace car elle filtre avant le regroupement.
• WITH ROLLUP : GROUP BY a, b WITH ROLLUP ajoute, après chaque groupe, une ligne de sous-total où les colonnes de regroupement résumées valent NULL, puis un total général en dernier. Sans ORDER BY, les lignes sortent dans cet ordre (clés croissantes, NULL d'abord, chaque sous-total derrière son groupe) ; les agrégats (dont COUNT(DISTINCT ...) et GROUP_CONCAT), HAVING, ORDER BY, DISTINCT, LIMIT, les fonctions de fenêtrage, les sous-requêtes, les tables dérivées et les vues s'appliquent aux lignes de sous-total comme aux autres. Sur une table vide, le résultat est vide (pas même de total).
• GROUPING(expr [, ...]) distingue un NULL réel d'un NULL de sous-total : 1 si la ligne résume l'expression, 0 sinon ; avec plusieurs arguments, un masque de bits (le premier argument est le bit de poids fort). Les arguments doivent figurer dans le GROUP BY (erreur 3580), sans appel GROUPING() dans un WHERE ni hors regroupement (1111) ; sans ROLLUP, il vaut toujours 0.

• ROLLUP(...), CUBE(...) et GROUPING SETS (...) (SQL standard, extension de MIRAJ) : ROLLUP(a, b) équivaut à WITH ROLLUP, CUBE(a, b) produit tous les sous-ensembles de colonnes, GROUPING SETS ((a), (b), ()) liste les ensembles voulus (un ensemble imbriqué peut être un ROLLUP ou un CUBE). Plusieurs éléments séparés par des virgules se croisent (GROUP BY a, ROLLUP(b)). Les expressions, alias et positions du GROUP BY ordinaire sont admis. WITH ROLLUP ne se combine pas avec ROLLUP(...) dans le même GROUP BY (1064).

  SELECT ville, COUNT(*) AS n, GROUPING(ville) AS total
  FROM clients WHERE ville IS NOT NULL GROUP BY ville WITH ROLLUP;

  +-------------+---+-------+
  | ville       | n | total |
  +-------------+---+-------+
  | Alger       | 2 |     0 |
  | Constantine | 1 |     0 |
  | Oran        | 2 |     0 |
  | NULL        | 5 |     1 |
  +-------------+---+-------+

  SELECT ville, solde > 0 AS positif, COUNT(*) FROM clients WHERE ville IS NOT NULL
  GROUP BY GROUPING SETS ((ville), (positif), ());

  +-------------+---------+----------+
  | ville       | positif | COUNT(*) |
  +-------------+---------+----------+
  | Alger       |    NULL |        2 |
  | Constantine |    NULL |        1 |
  | Oran        |    NULL |        2 |
  | NULL        |    NULL |        1 |
  | NULL        |       0 |        2 |
  | NULL        |       1 |        2 |
  | NULL        |    NULL |        5 |
  +-------------+---------+----------+

  Les trois premières lignes regroupent par ville, les trois suivantes par positif (la quatrième est le groupe de David, dont solde > 0 vaut NULL), la dernière est le total ; GROUPING() distingue ces NULL les uns des autres. Une vue qui contient un ROLLUP n'a pas la condition du WHERE appelant descendue dans sa définition : le filtre extérieur ne change pas les totaux.

• DISTINCT s'applique à la ligne entière ; SELECT DISTINCT ville, solde distingue les couples (§9.2), NULL compris. ORDER BY classe les NULL en premier en ordre croissant et en dernier en ordre décroissant :

  SELECT id, montant FROM commandes ORDER BY montant;

  +----+---------+
  | id | montant |
  +----+---------+
  |  5 |    NULL |
  |  4 |    1.00 |
  |  3 |    5.00 |
  |  1 |   10.00 |
  |  2 |   20.50 |
  +----+---------+

9.18 Fonctions de fenêtrage#

Syntaxe : fonction(...) OVER ([PARTITION BY expr, ...] [ORDER BY expr [ASC|DESC], ...] [cadre]), où le cadre est ROWS | RANGE suivi de borne ou de BETWEEN borne AND borne, une borne étant UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING ou UNBOUNDED FOLLOWING ; dans un cadre RANGE sur une date, aussi INTERVAL n unité PRECEDING et INTERVAL n unité FOLLOWING. À la place de la parenthèse, OVER nom désigne une fenêtre de la clause WINDOW (ci-dessous).

Fonctions prises en charge :

Non pris en charge (erreur 1235 sauf indication) : COUNT(DISTINCT ...), SUM(DISTINCT ...) et GROUP_CONCAT avec OVER ; toute autre fonction utilisée avec OVER. Le décalage de LAG/LEAD et le rang de NTILE / NTH_VALUE doivent être des constantes entières (NTILE et NTH_VALUE : au moins 1). FILTER (WHERE ...) et le cadre GROUPS ne sont pas pris en charge.

Cadre par défaut : sans ORDER BY, toute la partition ; avec ORDER BY, de la première ligne de la partition à la ligne courante et ses ex æquo (RANGE UNBOUNDED PRECEDING à CURRENT ROW). C'est pourquoi LAST_VALUE(...) sans cadre explicite rend la ligne courante et non la dernière de la partition : écrire ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Une fonction de fenêtrage se place dans la liste de sélection, dans l'ORDER BY ou dans la clause QUALIFY ; dans WHERE elle est refusée (erreur 1111). Pour filtrer sur son résultat, écrire QUALIFY (ci-dessous) ou l'encapsuler dans une table dérivée ou une CTE.

Le meilleur de chaque ville (n premiers par groupe) :

SELECT nom, ville, solde FROM (
    SELECT nom, ville, solde,
           ROW_NUMBER() OVER (PARTITION BY ville ORDER BY solde DESC) AS rn
    FROM clients WHERE ville IS NOT NULL
) t WHERE rn = 1 ORDER BY ville;

-- Même résultat, sans table dérivée
SELECT nom, ville, solde FROM clients WHERE ville IS NOT NULL
QUALIFY ROW_NUMBER() OVER (PARTITION BY ville ORDER BY solde DESC) = 1
ORDER BY ville;

+-------+-------------+--------+
| nom   | ville       | solde  |
+-------+-------------+--------+
| Émile | Alger       |  75.25 |
| David | Constantine |   NULL |
| Alice | Oran        | 100.50 |
+-------+-------------+--------+

David est premier de Constantine : dans un tri décroissant, la valeur NULL est classée en dernier, et il est seul dans son groupe.

Cumul (somme courante) :

SELECT id, montant, SUM(montant) OVER (ORDER BY id) AS cumul FROM commandes ORDER BY id;

+----+---------+-------+
| id | montant | cumul |
+----+---------+-------+
|  1 |   10.00 | 10.00 |
|  2 |   20.50 | 30.50 |
|  3 |    5.00 | 35.50 |
|  4 |    1.00 | 36.50 |
|  5 |    NULL | 36.50 |
+----+---------+-------+

Somme par partition, rang et valeur précédente :

SELECT id, nom, SUM(solde) OVER (PARTITION BY ville) AS tot_ville FROM clients WHERE ville IS NOT NULL ORDER BY id;
SELECT ville, RANK() OVER (ORDER BY COUNT(*) DESC) AS r, COUNT(*) FROM clients GROUP BY ville ORDER BY r, ville;
SELECT id, nom, solde - LAG(solde) OVER (ORDER BY id) AS diff FROM clients ORDER BY id;

La deuxième requête montre qu'une fonction de fenêtrage peut porter sur un agrégat : Alger et Oran (2 clients) sont ex æquo au rang 1, NULL et Constantine au rang 3. Les fonctions de fenêtrage sont évaluées après WHERE, GROUP BY et HAVING.

Cadre RANGE à décalage#

Sous ROWS, un décalage compte des lignes. Sous RANGE, il porte sur la valeur de la clé de tri : le cadre contient les lignes de la partition dont la clé est à cette distance au plus de celle de la ligne courante, ex æquo compris. La fenêtre doit avoir une seule clé de tri (erreur 4019 sinon, ou sans ORDER BY).

• Clé numérique : RANGE BETWEEN 10 PRECEDING AND CURRENT ROW retient les lignes dont la clé va de clé - 10 à clé. Le décalage est un entier écrit ; sur une clé qui n'est pas numérique, erreur 4021.
• Clé DATE, DATETIME ou TIMESTAMP : la borne s'écrit INTERVAL n unité PRECEDING ou INTERVAL n unité FOLLOWING, avec les unités d'INTERVAL (DAY, WEEK, MONTH, HOUR, MINUTE...) et un entier n, écrit ou non entre apostrophes (INTERVAL '1' DAY). INTERVAL sur une clé qui n'est pas une date : erreur 1235 ; sous ROWS : erreur de syntaxe 1064.
• Tri décroissant : PRECEDING va toujours vers le début de la partition, donc vers les valeurs plus grandes (ORDER BY jour DESC RANGE INTERVAL 7 DAY PRECEDING : la ligne courante et les sept jours qui la suivent dans le calendrier).
• NULL : une ligne dont la clé est NULL a pour cadre ses seuls ex æquo (les autres lignes de clé NULL) ; ces lignes n'entrent dans le cadre d'une autre ligne que par une borne UNBOUNDED.

-- Ventes des sept derniers jours, jour courant compris
CREATE TABLE ventes (jour DATE, montant DECIMAL(8,2));
INSERT INTO ventes VALUES ('2026-10-01', 10), ('2026-10-03', 20), ('2026-10-08', 5),
                          ('2026-10-09', 7), ('2026-10-20', 1);
SELECT jour, montant,
       SUM(montant) OVER (ORDER BY jour RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW) AS semaine
FROM ventes ORDER BY jour;

+------------+---------+---------+
| jour       | montant | semaine |
+------------+---------+---------+
| 2026-10-01 |   10.00 |   10.00 |
| 2026-10-03 |   20.00 |   30.00 |
| 2026-10-08 |    5.00 |   35.00 |
| 2026-10-09 |    7.00 |   32.00 |
| 2026-10-20 |    1.00 |    1.00 |
+------------+---------+---------+

Le 9 octobre, le 1er (huit jours plus tôt) sort du cadre : 20 + 5 + 7 = 32.

Fenêtres nommées : clause WINDOW#

WINDOW nom AS (fenêtre) [, nom AS (fenêtre) ...] se place après HAVING et avant QUALIFY, ORDER BY et LIMIT. Elle nomme des fenêtres que les fonctions du même bloc reprennent, dans la liste de sélection, ORDER BY ou QUALIFY ; chaque bloc (sous-requête, table dérivée, membre d'une union, expression de table commune, vue) a ses propres noms.

• OVER nom reprend la fenêtre telle quelle, cadre compris.
• OVER (nom ORDER BY ...) ou OVER (nom ROWS ...) la complète : la fenêtre reprise ne doit pas avoir de cadre (erreur 4013), la nouvelle ne peut pas redéfinir PARTITION BY (4011), ni écrire un ORDER BY si la fenêtre reprise en a un (4012).
• Une fenêtre nommée peut de même en compléter une autre définie avant elle : WINDOW w1 AS (PARTITION BY ville), w2 AS (w1 ORDER BY solde DESC).
• Nom inconnu : erreur 4009 ; nom défini deux fois : 4010. Les noms ne distinguent pas la casse.
• Une vue garde le texte écrit (SHOW CREATE VIEW montre la clause WINDOW).

SELECT nom, ville, solde,
       RANK() OVER w AS rang,
       SUM(solde) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumul
FROM clients
WINDOW w AS (PARTITION BY ville ORDER BY solde DESC)
ORDER BY ville, rang;

+--------+-------------+--------+------+--------+
| nom    | ville       | solde  | rang | cumul  |
+--------+-------------+--------+------+--------+
| Fatima | NULL        |  10.00 |    1 |  10.00 |
| Émile  | Alger       |  75.25 |    1 |  75.25 |
| Bob    | Alger       | -20.00 |    2 |  55.25 |
| David  | Constantine |   NULL |    1 |   NULL |
| Alice  | Oran        | 100.50 |    1 | 100.50 |
| Chloé  | Oran        |   0.00 |    2 | 100.50 |
+--------+-------------+--------+------+--------+

Clause QUALIFY#

QUALIFY condition (extension MIRAJ, comme Teradata, Snowflake, BigQuery et DuckDB) filtre les lignes après le calcul des fonctions de fenêtrage, et avant DISTINCT, ORDER BY et LIMIT. Ordre d'évaluation d'un bloc : FROM, WHERE, GROUP BY, HAVING, fonctions de fenêtrage, QUALIFY, liste de sélection et DISTINCT, ORDER BY, LIMIT.

• La condition peut contenir des fonctions de fenêtrage (QUALIFY RANK() OVER (PARTITION BY ville ORDER BY solde DESC) <= 2), des colonnes, des agrégats quand le bloc agrège, des sous-requêtes, et les alias de la liste de sélection (SELECT ..., ROW_NUMBER() OVER (...) AS rn ... QUALIFY rn <= 3).
• Résolution d'un nom non qualifié : comme dans HAVING (§9.5). Sans agrégat, l'alias de la liste de sélection l'emporte sur une colonne de même nom ; quand le bloc agrège, la colonne l'emporte.
• Le bloc doit calculer au moins une fonction de fenêtrage (liste de sélection, ORDER BY ou QUALIFY) : sinon erreur 1221 (Incorrect usage of QUALIFY and a query without window function).
• Une fonction de fenêtrage imbriquée dans une autre reste refusée (1111) ; nom inconnu : 1054 (in 'qualify clause').
• Dans une union, QUALIFY porte sur le membre qui l'écrit. Une vue peut contenir QUALIFY (SHOW CREATE VIEW rend le texte tel qu'écrit) ; elle n'est alors pas modifiable.

-- Les deux plus gros soldes de chaque ville, ex æquo compris
SELECT nom, ville, solde FROM clients
QUALIFY RANK() OVER (PARTITION BY ville ORDER BY solde DESC) <= 2
ORDER BY ville, solde DESC;

-- Villes des deux effectifs les plus grands (fenêtre sur agrégat)
SELECT ville, COUNT(*) AS n FROM clients GROUP BY ville
QUALIFY DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) <= 2;

+-------------+---+
| ville       | n |
+-------------+---+
| Oran        | 2 |
| Alger       | 2 |
| Constantine | 1 |
| NULL        | 1 |
+-------------+---+

Les deux effectifs distincts les plus grands sont 2 et 1 : DENSE_RANK garde donc toutes les villes. La première requête rend les six clients (aucune ville n'en a plus de deux).

SELECT nom FROM clients QUALIFY nom > 'B';
-- ERREUR 1221 (HY000) : Incorrect usage of QUALIFY and a query without window function

Premiers rangs de chaque partition (top-N)#

Quand une requête ne garde que les premiers rangs d'une fenêtre, MIRAJ ne fait parvenir à la fenêtre que les lignes utiles : les autres sont écartées au fil de la lecture, sans tri complet ni copie. Le coût devient voisin de celui d'un GROUP BY (sur 10 millions de lignes, environ 0,2 s au lieu de 16 s en édition Entreprise), et le calcul est réparti entre les fils en édition Entreprise. Le résultat est identique à celui du calcul complet.

L'optimisation s'applique :

• à une borne sur ROW_NUMBER(), RANK() ou DENSE_RANK() : rn <= k, rn < k, rn = k, rn <=> k, k >= rn, rn BETWEEN a AND k, rn IN (k1, k2, ...) (la plus grande valeur compte), k étant une constante entière, un paramètre ? ou une petite expression + - * sur eux ; plusieurs bornes : la plus serrée ;
• écrite dans le WHERE d'une requête qui lit une table dérivée, une CTE ou une vue dépliée qui calcule le rang, ou dans le QUALIFY du bloc qui le calcule ;
• quand toutes les fonctions de fenêtrage du bloc partagent la même fenêtre et ne regardent que les lignes qui précèdent : ROW_NUMBER, RANK, DENSE_RANK, LAG, FIRST_VALUE, agrégats en ROWS qui finissent au plus à CURRENT ROW (le cadre par défaut avec ORDER BY n'est admis qu'avec une borne RANK ou DENSE_RANK, qui gardent les ex æquo) ;
• sans GROUP BY, agrégat ni HAVING dans le bloc de la fenêtre ; avec une borne de la requête englobante, sans LIMIT, DISTINCT ni union non plus dans la table dérivée (avec QUALIFY, ils sont permis : ils s'appliquent après le filtre). La table dérivée ne doit pas être du côté optionnel d'une jointure externe.

RANK() <= k garde aussi les ex æquo de la k-ième ligne, DENSE_RANK() <= k toutes les lignes des k premières valeurs distinctes : la mémoire reste bornée, sauf quand une valeur a un très grand nombre d'ex æquo. EXPLAIN affiche Using window top-N (k per partition) (et Using parallel window top-N quand le calcul est réparti) ; l'optimisation se désactive par SET optimizer_switch = 'window_top_n=off'.

Fenêtre glissante (ROWS 1 PRECEDING : ligne précédente et ligne courante) :

SELECT nom, SUM(solde) OVER (ORDER BY id ROWS 1 PRECEDING) AS s FROM clients ORDER BY id;

+--------+--------+
| nom    | s      |
+--------+--------+
| Alice  | 100.50 |
| Bob    |  80.50 |
| Chloé  | -20.00 |
| David  |   0.00 |
| Émile  |  75.25 |
| Fatima |  85.25 |
+--------+--------+

9.19 Verrous de lecture et indications d'optimiseur#

Lectures verrouillantes#

SELECT ... FOR UPDATE   [OF table, ...] [NOWAIT | WAIT secondes | SKIP LOCKED]
SELECT ... FOR SHARE    [OF table, ...] [NOWAIT | WAIT secondes | SKIP LOCKED]
SELECT ... LOCK IN SHARE MODE           [NOWAIT | WAIT secondes | SKIP LOCKED]

La clause s'écrit en dernière position du bloc (après LIMIT ; seul INTO peut la suivre) et n'accepte pas OF avec LOCK IN SHARE MODE. Elle s'applique aux tables du bloc, ou à celles de OF. Les lignes lues sont tenues jusqu'à la fin de la transaction (FOR UPDATE : aucune autre transaction ne peut les écrire ni les tenir ; FOR SHARE / LOCK IN SHARE MODE : les autres lectures partagées restent admises). Une ligne déjà tenue par une autre transaction donne, selon la clause :

Une attente qui fermerait un cycle (interblocage) est refusée aussitôt par l'erreur 1213. Hors transaction (auto-validation), l'instruction attend de même, mais rend la ligne aussitôt lue : le verrou n'est pas conservé. Voir le chapitre 14 (« Transactions et concurrence », lectures verrouillantes) pour le détail.

-- session 2, pendant qu'une session 1 tient la ligne 1 par FOR UPDATE
SELECT nom FROM clients WHERE id <= 2 FOR UPDATE NOWAIT;
-- ERREUR 1205 (HY000) : Lock wait timeout exceeded; try restarting transaction
SELECT id, nom FROM clients WHERE id <= 3 FOR UPDATE SKIP LOCKED;
-- 2 Bob, 3 Chloé : la ligne 1 est écartée

Le motif habituel « lire puis écrire » :

START TRANSACTION;
SELECT nom, solde FROM clients WHERE id = 1 FOR UPDATE;
UPDATE clients SET solde = solde - 10 WHERE id = 1;
COMMIT;

Indications d'optimiseur /*+ ... */#

Un commentaire d'indications juste après SELECT est lu. Seule MAX_EXECUTION_TIME(n) a un effet : durée maximale d'exécution en millisecondes (1 à 2 147 483 647) ; au-delà, l'instruction échoue avec l'erreur 1969. Les autres indications reconnues sont lues puis ignorées ; une erreur de syntaxe dans le commentaire le fait ignorer en entier avec un avertissement, sans échec de la requête.

SELECT /*+ MAX_EXECUTION_TIME(1000) */ nom FROM clients WHERE ville = 'Oran';
-- Alice, Chloé
SELECT /*+ MAX_EXECUTION_TIME(1) */ COUNT(*) FROM lignes a, lignes b;   -- 4 millions de paires
-- ERREUR 1969 (70100) : Query was interrupted: execution time limit 0.001 sec exceeded

Sans indication, la durée est bornée par la variable max_statement_time (300 s par défaut, 0 : sans limite), avec la même erreur 1969.

Les indications d'index USE | FORCE | IGNORE {INDEX | KEY} [FOR {JOIN | ORDER BY | GROUP BY}] (…) s'écrivent après un nom de table ou son alias, éventuellement plusieurs à la suite. Elles choisissent l'index d'une jointure par sonde d'index (§9.13) sur cette table : USE et FORCE limitent la sonde aux index nommés, IGNORE écarte les index nommés (sans index utilisable, la jointure se fait par hachage). La lecture d'une table par égalité avec une constante, elle, n'en dépend pas. La requête rend toujours le même résultat qu'avec ou sans indication. Chaque nom doit désigner un index de la table (PRIMARY pour la clé primaire) qui ne soit pas IGNORED : sinon 1176 (« Key 'i' doesn't exist in table 't' », l'alias de la table s'il y en a un). Aucune indication de jointure ne modifie l'ordre des jointures.

-- lignes : 2 000 lignes, index idx_lc sur client_id
EXPLAIN SELECT c.nom, l.qte FROM clients c JOIN lignes l IGNORE INDEX (idx_lc) ON l.client_id = c.id;
-- l : type ALL, Using join buffer (hash join)   (sans l'indication : type ref, key idx_lc)
SELECT nom FROM clients c FORCE INDEX (idx_x) WHERE id = 2;
-- ERREUR 1176 (42000) : Key 'idx_x' doesn't exist in table 'c'

LIMIT n ROWS EXAMINED m est une autre borne d'exécution : l'instruction s'arrête après avoir examiné m lignes et rend le résultat partiel accompagné d'un avertissement (« The query exceeded LIMIT ROWS EXAMINED m. The query result may be incomplete »), sans erreur.

9.20 Lire un plan avec EXPLAIN#

EXPLAIN s'applique à SELECT, UPDATE, DELETE et INSERT ... SELECT (l'instruction est planifiée, jamais exécutée) ; EXPLAIN INSERT ... VALUES, EXPLAIN REPLACE ... VALUES et EXPLAIN LOAD DATA répondent par l'erreur 1235. EXPLAIN nom_de_table équivaut à DESCRIBE. La sortie est tabulaire par défaut (FORMAT=TRADITIONAL aussi) ; FORMAT=JSON rend un document JSON (ci-dessous), FORMAT=TREE l'arbre d'opérateurs (voir 9.21), et EXPLAIN ANALYZE / ANALYZE exécutent la requête (9.21). EXPLAIN EXTENDED ajoute la colonne filtered (toujours 100.00 : MIRAJ n'estime pas la sélectivité d'une condition) et laisse le texte de l'instruction en note 1003 (SHOW WARNINGS) ; EXPLAIN PARTITIONS ajoute la colonne partitions (partitions lues d'une table partitionnée, NULL sinon). Ni EXTENDED ni PARTITIONS ne se combinent avec FORMAT (1064).

Colonnes de la sortie : id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra (partitions après table avec EXPLAIN PARTITIONS, filtered après rows avec EXPLAIN EXTENDED). key_len vaut NULL (les index sont en mémoire, sans longueur de clé sérialisée).

Exemples (avec un index idx_ville sur clients(ville) et idx_cli sur commandes(client_id)) :

EXPLAIN SELECT * FROM clients WHERE id = 1;

+----+-------------+---------+--------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table   | type   | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+--------+-------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | clients | eq_ref | PRIMARY,idx_ville | PRIMARY |    NULL | const |    1 |       |
+----+-------------+---------+--------+-------------------+---------+---------+-------+------+-------+

EXPLAIN SELECT * FROM clients WHERE ville = 'Oran';

+----+-------------+---------+------+-------------------+-----------+---------+-------+------+-------+
| id | select_type | table   | type | possible_keys     | key       | key_len | ref   | rows | Extra |
+----+-------------+---------+------+-------------------+-----------+---------+-------+------+-------+
|  1 | SIMPLE      | clients | ref  | PRIMARY,idx_ville | idx_ville |    NULL | const |    2 |       |
+----+-------------+---------+------+-------------------+-----------+---------+-------+------+-------+

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

+----+-------------+-------+------+-------------------+------+---------+------+------+-------------------------------+
| id | select_type | table | type | possible_keys     | key  | key_len | ref  | rows | Extra                         |
+----+-------------+-------+------+-------------------+------+---------+------+------+-------------------------------+
|  1 | SIMPLE      | c     | ALL  | PRIMARY,idx_ville | NULL |    NULL | NULL |    6 |                               |
|  1 | SIMPLE      | o     | ALL  | PRIMARY,idx_cli   | NULL |    NULL | NULL |    5 | Using join buffer (hash join) |
+----+-------------+-------+------+-------------------+------+---------+------+------+-------------------------------+

Lecture : la table c est parcourue en entier, la table o est chargée dans un tampon de jointure et appariée par hachage. Une jointure sans égalité entre colonnes (ON a.solde > b.solde) donne deux lignes ALL sans mention de Using join buffer : boucle imbriquée. Using filesort signale un tri (ORDER BY), Using temporary un regroupement (GROUP BY) ou une union dédoublonnée.

Quand la table jointe est grande (ici lignes, 2 000 lignes, index idx_lc sur client_id), elle est lue par sonde d'index (§9.13) ; une condition sur une constante sert aussi la table pilote :

EXPLAIN SELECT c.nom, l.qte FROM clients c JOIN lignes l ON l.client_id = c.id WHERE c.ville = 'Oran';

+----+-------------+-------+------+-------------------+-----------+---------+---------+------+-------+
| id | select_type | table | type | possible_keys     | key       | key_len | ref     | rows | Extra |
+----+-------------+-------+------+-------------------+-----------+---------+---------+------+-------+
|  1 | SIMPLE      | c     | ref  | PRIMARY,idx_ville | idx_ville |    NULL | const   |    2 |       |
|  1 | SIMPLE      | l     | ref  | PRIMARY,idx_lc    | idx_lc    |    NULL | c7.c.id |   40 |       |
+----+-------------+-------+------+-------------------+-----------+---------+---------+------+-------+

Une sous-requête corrélée de la liste de sélection, une table dérivée et une union :

EXPLAIN SELECT c.nom, (SELECT COUNT(*) FROM commandes o WHERE o.client_id = c.id) FROM clients c;

+----+--------------------+-------+------+-------------------+------+---------+------+------+-------------+
| id | select_type        | table | type | possible_keys     | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------+------+-------------------+------+---------+------+------+-------------+
|  1 | SIMPLE             | c     | ALL  | PRIMARY,idx_ville | NULL |    NULL | NULL |    6 |             |
|  2 | DEPENDENT SUBQUERY | o     | ALL  | PRIMARY,idx_cli   | NULL |    NULL | NULL |    5 | Using where |
+----+--------------------+-------+------+-------------------+------+---------+------+------+-------------+

EXPLAIN SELECT * FROM (SELECT ville, COUNT(*) n FROM clients GROUP BY ville) t WHERE n > 1;

+----+-------------+------------+------+-------------------+------+---------+------+------+------------------------------+
| id | select_type | table      | type | possible_keys     | key  | key_len | ref  | rows | Extra                        |
+----+-------------+------------+------+-------------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | <derived2> | ALL  | NULL              | NULL |    NULL | NULL | NULL |                              |
|  2 | DERIVED     | clients    | ALL  | PRIMARY,idx_ville | NULL |    NULL | NULL |    6 | Using temporary; Using where |
+----+-------------+------------+------+-------------------+------+---------+------+------+------------------------------+

EXPLAIN SELECT id FROM clients UNION SELECT client_id FROM commandes;

+----+-------------+-----------+------+-------------------+------+---------+------+------+-----------------+
| id | select_type | table     | type | possible_keys     | key  | key_len | ref  | rows | Extra           |
+----+-------------+-----------+------+-------------------+------+---------+------+------+-----------------+
|  1 | PRIMARY     | clients   | ALL  | PRIMARY,idx_ville | NULL |    NULL | NULL |    6 |                 |
|  2 | UNION       | commandes | ALL  | PRIMARY,idx_cli   | NULL |    NULL | NULL |    5 | Using temporary |
+----+-------------+-----------+------+-------------------+------+---------+------+------+-----------------+

Limites de l'EXPLAIN de cette version, à connaître avant d'en tirer des conclusions :

• Il décrit les lectures de table, pas chaque opérateur : une sous-requête IN (SELECT ...) n'ajoute pas de ligne propre (sa condition apparaît en Using where) ; une table dérivée apparaît en ligne <derivedN> du bloc englobant, suivie des lectures de son contenu (DERIVED, bloc N).
• Les lectures par intervalle (id > 2, BETWEEN, IN (1, 2)) et ORDER BY id LIMIT n sont annoncées ALL (avec Using filesort pour le tri) : seule l'égalité utilise un index. Exception : une clé dont une colonne VECTOR suit des colonnes égalées est aussi lue par l'index pour IN, IS NULL, <, >, BETWEEN (range ou ref, chapitre 11).
• La colonne key_len n'est renseignée que pour une lecture par une clé qui porte une colonne VECTOR et pour une jointure par sonde d'index sur un vecteur (4 octets par dimension + 2, taille d'un entier, + 1 par colonne qui admet NULL) ; filtered (EXTENDED, ANALYZE) vaut toujours 100.

FORMAT=JSON. Une ligne, une colonne EXPLAIN, document mis en forme : {"query_block": {"select_id": 1, "nested_loop": [{"table": {...}}, ...]}}. Chaque table porte table_name, partitions (table partitionnée), access_type, possible_keys, key, used_key_parts, ref, rows, filtered et, quand une condition porte sur la table, attached_condition (texte de la condition vérifiée sur ses lignes, qu'elle soit poussée vers la lecture ou évaluée au-dessus : t.a = 10 and t.b > 5, clients.nom like '%e' or clients.solde > 1). Une table lue par une jointure par hachage est enveloppée dans block-nl-join (join_type : BNLH) ; un tri d'une seule table dans read_sorted_file / filesort, sinon filesort et temporary_table au-dessus de la boucle ; une table dérivée porte materialized (son bloc), une union union_result / query_specifications (membres suivants : operation : UNION), une sous-requête corrélée subqueries. Sans table : "table": {"message": "No tables used"}.

EXPLAIN FORMAT=JSON SELECT c.nom FROM clients c JOIN commandes o ON o.client_id = c.id WHERE c.solde > 10;

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "c",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY", "idx_ville"],
          "rows": 6,
          "filtered": 100,
          "attached_condition": "c.solde > 10.00"
        }
      },
      {
        "block-nl-join": {
          "table": {
            "table_name": "o",
            "access_type": "ALL",
            "possible_keys": ["PRIMARY", "idx_cli"],
            "rows": 5,
            "filtered": 100
          },
          "buffer_type": "flat",
          "join_type": "BNLH"
        }
      }
    ]
  }
}

Plan d'une autre connexion. SHOW EXPLAIN [FORMAT=JSON] FOR id, EXPLAIN [FORMAT=JSON] FOR CONNECTION id et SHOW ANALYZE [FORMAT=JSON] FOR id décrivent l'instruction en cours de la connexion id (colonne Id de SHOW PROCESSLIST). MIRAJ ne garde pas le plan d'une autre session : le texte de son instruction est relu et planifié dans sa base courante, sans rien exécuter (même plan que l'EXPLAIN de ce texte) ; SHOW ANALYZE rend les colonnes r_rows, filtered, r_filtered, valeurs réelles à NULL. Le texte de l'instruction est laissé en note 1003. Erreurs : identifiant qui n'est pas une constante (CONNECTION_ID(), 1+1) : 1204 ; connexion inconnue : 1094 ; connexion inactive, sa propre connexion, ou instruction sans plan (autre que SELECT, UPDATE, DELETE, INSERT ... SELECT) : 1933. Sans le privilège PROCESS, seules les connexions du même utilisateur sont visibles (1227). Colonne du JSON : SHOW EXPLAIN / SHOW ANALYZE.

Profilage. SHOW PROFILES et SHOW PROFILE [type, ...] [FOR QUERY n] [LIMIT n [OFFSET m]] rendent leurs colonnes, sans ligne : le profilage n'est jamais relevé (@@profiling reste 0, @@profiling_history_size vaut 15). information_schema.PROFILING et information_schema.OPTIMIZER_TRACE existent avec leurs colonnes, vides (@@optimizer_trace vaut enabled=off).

Conduite à tenir pour une requête lente : lire type (ALL sur une grande table ?), ajouter un index UNIQUE ou secondaire sur la colonne comparée à une constante, vérifier l'égalité de type entre les deux colonnes d'une jointure, agréger avant de joindre, et poser MAX_EXECUTION_TIME ou max_join_size pour borner les requêtes d'exploration.

9.21 EXPLAIN ANALYZE et ANALYZE : temps et lignes réels#

EXPLAIN ANALYZE <instruction> et ANALYZE [FORMAT=JSON] <instruction> exécutent l'instruction et rendent son plan avec, pour chaque opérateur, le nombre de lignes réellement produites et le temps passé. Les lignes de la requête ne sont jamais envoyées au client : seul le plan est rendu. Une erreur d'exécution (1242, dépassement de max_statement_time, KILL) est rendue telle quelle.

Exemple d'arbre (temps en millisecondes : premier résultat, puis total, tous deux inclusifs de ce qui est sous le nœud) :

EXPLAIN ANALYZE SELECT ville, COUNT(*) FROM clients GROUP BY ville ORDER BY 2 DESC LIMIT 2;

-> Sort (top 2 row(s))  (actual time=0.010..0.010 rows=2 loops=1)
    -> Project  (actual time=0.008..0.008 rows=4 loops=1)
        -> Group aggregate (hash, 1 key(s))  (actual time=0.008..0.008 rows=4 loops=1)
            -> Table scan on clients  (rows=6)  (actual time=0.002..0.003 rows=6 loops=1)

EXPLAIN FORMAT=TREE sur une jointure par sonde d'index (§9.13) :

-> Project
    -> Nested loop inner join (index lookup)
        -> Table scan on c  (rows=6)
        -> Single-row index lookup on l using PRIMARY  (rows=1)

ANALYZE (tableau) :

ANALYZE SELECT * FROM clients WHERE solde > 0;

+----+-------------+---------+------+-------------------+------+---------+------+------+--------+----------+------------+-------------+
| id | select_type | table   | type | possible_keys     | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |
+----+-------------+---------+------+-------------------+------+---------+------+------+--------+----------+------------+-------------+
|  1 | SIMPLE      | clients | ALL  | PRIMARY,idx_ville | NULL |    NULL | NULL |    6 | 6.00   |   100.00 |      50.00 | Using where |
+----+-------------+---------+------+-------------------+------+---------+------+------+--------+----------+------------+-------------+

(rows=N) est le nombre de lignes de la table (estimation du plan) ; loops=0 s'écrit (never executed) (source d'un LIMIT 0, par exemple). Les opérateurs sont : lecture (Table scan, Index lookup, Single-row index lookup, Vector index scan, avec with pushed-down filter si un prédicat est poussé dans la lecture), Filter, Project, jointures (Inner|Left|Right hash join, Nested loop ... join), Group aggregate / Aggregate, Sort, Limit, Remove duplicates, Derived table, Append (union), Window aggregate, Dependent subqueries.

Parallélisme. Une lecture répartie sur plusieurs fils devient un nœud Parallel gather|aggregate|sort (chronométré) dont les enfants sont la lecture ([parallel: degree N, M morsels]) et chaque étage (filtre, projection, jointure) : leurs lignes sont la somme de tous les fils, mais sans temps propre (la somme des temps des fils n'est pas un temps écoulé). Les lignes sont les mêmes qu'en série. Tables partitionnées (édition Cluster) : les partitions lues figurent sur la lecture.

Écritures. ANALYZE UPDATE et ANALYZE DELETE (ou EXPLAIN ANALYZE UPDATE|DELETE) exécutent vraiment l'instruction : les lignes sont modifiées ou supprimées, avec les droits, verrous, déclencheurs et la transaction d'une instruction ordinaire (un ROLLBACK les défait). Le plan décrit la lecture des lignes à modifier ; r_rows est le nombre de lignes qu'elle a examinées, r_filtered la part retenue. Un DELETE sans condition vide la table sans la lire : un seul nœud Delete all rows from t (Extra : Deleting all rows). Pour ne rien modifier, utiliser EXPLAIN seul. ANALYZE INSERT ..., ANALYZE REPLACE ... : erreur 1235. Dans un corps de routine, seul ANALYZE SELECT est admis. Une session MCP a besoin du niveau d'écriture pour ANALYZE UPDATE|DELETE.

Le plan ne porte pas d'estimation de coût ; les sous-requêtes d'expression (IN (SELECT ...), EXISTS, scalaires) ne sont pas détaillées, leur temps est inclus dans celui de l'opérateur qui les évalue ; loops vaut toujours 0 ou 1. ANALYZE TABLE t [, ...] rend une ligne analyze / status / OK par table (Error puis Operation failed pour une table inconnue), précédée avec PERSISTENT FOR ... de la ligne status / Engine-independent statistics collected : MIRAJ tient ses comptes de lignes à jour en continu, rien n'est recalculé. Surcoût : une requête ordinaire n'est pas instrumentée ; sous ANALYZE, deux lectures d'horloge par lot de 4 096 lignes et par opérateur, et un décompte des lignes examinées par les lectures à prédicat poussé.

9.22 Recherche plein texte : MATCH … AGAINST#

CREATE TABLE art (
    id INT PRIMARY KEY, famille VARCHAR(20), designation VARCHAR(100), notes TEXT,
    FULLTEXT ft (designation, notes), FULLTEXT fd (designation)
);
INSERT INTO art VALUES
    (1, 'pharma', 'Sirop contre la toux', 'Flacon de 125 ml, adulte'),
    (2, 'pharma', 'Paracétamol 500 mg', 'Comprimés contre la douleur et la fièvre'),
    (3, 'pharma', 'Sirop toux enfant', 'Goût fraise, pour enfant'),
    (4, 'hygiene', 'Savon doux', 'Peaux sensibles'),
    (5, 'pharma', 'Pastilles gorge',
        'Toux sèche et irritations de la gorge, longue notice détaillée pour adulte et adolescent');

SELECT id, MATCH (designation, notes) AGAINST ('sirop toux') AS score
FROM art
WHERE MATCH (designation, notes) AGAINST ('sirop toux')
ORDER BY score DESC
LIMIT 10;

+----+---------------------+
| id | score               |
+----+---------------------+
|  1 | 0.20757311582565308 |
|  3 | 0.20757311582565308 |
|  5 |  0.0492168664932251 |
+----+---------------------+

SELECT id FROM art WHERE MATCH (designation, notes) AGAINST ('+sirop -enfant' IN BOOLEAN MODE);  -- 1
SELECT id FROM art WHERE MATCH (designation, notes) AGAINST ('"contre la toux"' IN BOOLEAN MODE); -- 1
SELECT id FROM art WHERE MATCH (designation, notes) AGAINST ('para*' IN BOOLEAN MODE);            -- 2
SELECT id FROM art WHERE MATCH (designation, notes) AGAINST ('sirop' WITH QUERY EXPANSION);       -- 1, 2, 3, 5
SELECT id FROM art WHERE MATCH (notes) AGAINST ('toux');
-- ERREUR 1191 (HY000) : Can't find FULLTEXT index matching the column list

Les colonnes de MATCH doivent être exactement celles d'un index FULLTEXT de la table, dans un ordre quelconque (chapitre 7, « Index plein texte ») ; sinon 1191. La requête d'AGAINST est une constante (littéral, paramètre, variable, expression de constantes ; une colonne : 1210). Trois modes : naturel (par défaut), IN BOOLEAN MODE (opérateurs + - ~ < > * " ( ) @n) et WITH QUERY EXPANSION ; plus la recherche floue, extension MIRAJ (suffixe mot~1 / mot~2 en mode booléen, IN FUZZY MODE, ci-dessous). Les mots sont comparés sans casse ni accents (selon l'interclassement des colonnes), de 3 à 84 caractères, mots vides exclus. Une marque combinante (accent écrit à part, comme U+0301) ne coupe pas le mot : e + U+0301

• b forme un mot de trois caractères, trouvé par eb sous utf8mb4_unicode_ci et par éb décomposé de même sous utf8mb4_general_ci.

Lecture par l'index. Une condition WHERE MATCH … AGAINST (…) (ou > c, >= c) lit seulement les lignes qui portent les mots cherchés : EXPLAIN montre type = fulltext et le nom de l'index. Un GROUP BY au-dessus (facettes : SELECT famille, COUNT(*) … WHERE MATCH … GROUP BY famille) profite de la même lecture. ORDER BY MATCH … LIMIT sans condition WHERE MATCH lit toute la table.

EXPLAIN SELECT id FROM art WHERE MATCH (designation, notes) AGAINST ('toux');

+----+-------------+-------+----------+---------------+-----+---------+-----+------+-------------+
| id | select_type | table | type     | possible_keys | key | key_len | ref | rows | Extra       |
+----+-------------+-------+----------+---------------+-----+---------+-----+------+-------------+
|  1 | SIMPLE      | art   | fulltext | PRIMARY,ft    | ft  |       0 |     |    1 | Using where |
+----+-------------+-------+----------+---------------+-----+---------+-----+------+-------------+

Score. MATCH rend un DOUBLE, la pertinence de la ligne. La variable de session miraj_ft_ranking choisit sa formule, prise à la planification de chaque instruction :

miraj_ft_bm25_k1 (0 à 3, défaut 1,2) règle la saturation de la fréquence et miraj_ft_bm25_b (0 à 1, défaut 0,75) la normalisation par la longueur (0 : aucune). SET, SET STATEMENT … FOR, SHOW VARIABLES et @@miraj_ft_ranking les lisent et les posent ; une valeur hors bornes : 1231. Une requête préparée prend la formule de la session à chaque EXECUTE. Dans les deux modes, les opérateurs >, < et ~ ajoutent ou retranchent 1, et les poids déclarés par l'index (FULLTEXT ft (designation WEIGHT 3, notes), chapitre 7) remplacent la fréquence tf par Σ poids(colonne) × tf(colonne).

SET miraj_ft_ranking = 'bm25';
SELECT id, MATCH (designation, notes) AGAINST ('toux') AS score
FROM art WHERE MATCH (designation, notes) AGAINST ('toux') ORDER BY score DESC;

+----+--------------------+
| id | score              |
+----+--------------------+
|  1 | 0.5724514559505782 |
|  3 | 0.5389965007326871 |
|  5 | 0.4171128699137378 |
+----+--------------------+

La ligne 5, plus longue, passe derrière ; avec la formule par défaut, les trois lignes ont le même score (0.0492168664932251), chacune citant toux une fois :

SET STATEMENT miraj_ft_ranking = 'reference' FOR
  SELECT id, MATCH (designation, notes) AGAINST ('toux') FROM art WHERE MATCH (designation, notes) AGAINST ('toux');

Recherche floue (extension MIRAJ). Une faute de frappe ne vide plus le résultat :

-- mode booléen : distance d'édition admise collée au mot (0 exact, 1 ou 2)
SELECT id FROM art WHERE MATCH (designation) AGAINST ('paracetmol~1' IN BOOLEAN MODE);       -- 2
SELECT id FROM art WHERE MATCH (designation) AGAINST ('+sirrop~2 -enfant' IN BOOLEAN MODE);  -- 1
SELECT id FROM art WHERE MATCH (designation) AGAINST ('sirop~3' IN BOOLEAN MODE);
-- ERREUR 9052 (HY000) : Fuzzy distance 3 is out of range (0 to 2)
-- mode flou : lexique du mode naturel, chaque mot élargi selon sa longueur
SELECT id, MATCH (designation, notes) AGAINST ('parasetamol sirrop' IN FUZZY MODE) AS score
FROM art WHERE MATCH (designation, notes) AGAINST ('parasetamol sirrop' IN FUZZY MODE)
ORDER BY score DESC LIMIT 10;

+----+--------------------+
| id | score              |
+----+--------------------+
|  2 | 1.3999300433604143 |
|  1 | 0.8426386597031287 |
|  3 | 0.7893570582699099 |
+----+--------------------+

• La distance compte les caractères insérés, retirés, remplacés, et l'échange de deux caractères voisins (praacetamol est à 1 de paracetamol), sur les mots pliés de l'index : sans casse ni accent en interclassement insensible, exacts en _bin.
• mot~n : ~ suivi de chiffres collé au mot et suivi d'un blanc, d'un opérateur ou de la fin. ~0 : mot exact ; au-delà de 2 : 9052. sirop ~1 (blanc avant) et sirop~1toux gardent la lecture habituelle (~ opérateur). Le suffixe ne s'applique ni à une phrase ("a b"~1), ni à une troncature (a*~1, a~1*) : 1064. Les opérateurs + - > < ~ ( ) s'appliquent au mot flou comme à un mot exact. En mode naturel, ~ fait partie du mot.
• IN FUZZY MODE : les mots de 4 à 5 caractères reçoivent la distance 1, ceux de 6 et plus la distance 2 ; les phrases entre guillemets restent exactes ; pas de WITH QUERY EXPANSION (1064).
• Un mot plus court que miraj_ft_fuzzy_min_length caractères (défaut 4, de 3 à 84) est cherché exact. Au plus miraj_ft_fuzzy_max_expansions mots de l'index (défaut 50, de 1 à 1 000) sont retenus en plus du mot exact, les plus proches d'abord, puis les plus fréquents.
• Score : le mot flou compte comme un mot dont la fréquence est la somme de celles des variantes présentes dans la ligne, multipliée par (longueur − d) / longueur (d : distance de la variante la plus proche présente) ; à fréquence égale, le mot exact passe devant une variante à distance 1, elle-même devant une variante à distance 2, dans les deux formules de score. EXPLAIN est inchangé (fulltext).

Précisions : une transaction voit ses propres écritures dans MATCH, avant son COMMIT ; N et df comptent l'état courant de l'index, écritures non validées des autres sessions comprises (pertinence approchée pendant une écriture concurrente, lignes toujours justes) ; MATCH sur les colonnes d'une vue ou d'une table dérivée : 1191. Sur une table partitionnée (édition Cluster), le score est celui de la table entière (N, df et longueurs sommés sur les segments), mais la lecture n'est pas servie par l'index.

9.23 SELECT … INTO : variables et fichiers#

SELECT liste INTO @variable [, @variable ...] FROM ...
SELECT liste INTO OUTFILE 'fichier'
    [CHARACTER SET jeu]
    [{FIELDS | COLUMNS} [TERMINATED BY 'chaîne'] [[OPTIONALLY] ENCLOSED BY 'c'] [ESCAPED BY 'c']]
    [LINES [STARTING BY 'chaîne'] [TERMINATED BY 'chaîne']]
    FROM ...
SELECT liste INTO DUMPFILE 'fichier' FROM ...

La clause INTO s'écrit après la liste de sélection, après LIMIT ou après la clause de verrou (… WHERE id = 1 INTO @n, … FOR UPDATE INTO @n), une seule fois, dans le premier SELECT de l'instruction ; elle reçoit alors le résultat de l'instruction entière (union comprise). Elle est refusée dans une sous-requête ou une expression de table (1064). L'instruction ne rend pas de jeu de résultats, mais un compte de lignes.

Variables#

Chaque colonne du résultat va dans la variable de même rang ; dans une routine, une variable locale ou un paramètre peut remplacer la variable @ (et la ligne d'une boucle FOR, chapitre 7).

SELECT nom, solde INTO @n, @s FROM clients WHERE id = 1;
-- OK, 1 ligne(s) affectée(s)
SELECT @n, @s;

+-------+--------+
| @n    | @s     |
+-------+--------+
| Alice | 100.50 |
+-------+--------+

SET @n = 'x';
SELECT nom INTO @n FROM clients WHERE id = 99;   -- avertissement, @n vaut toujours 'x'
SELECT nom INTO @n FROM clients;
-- ERREUR 1172 (42000) : Result consisted of more than one row

Fichiers : INTO OUTFILE et INTO DUMPFILE#

Le fichier est écrit par le serveur, dans le dossier de la variable secure_file_priv (sans dossier fixé : erreur 1290), avec le privilège FILE ; un fichier existant n'est jamais remplacé (erreur 1086, File '...' already exists). Le nombre de lignes affectées est le nombre de lignes écrites.

• INTO OUTFILE écrit une ligne de texte par ligne du résultat. Par défaut, champs séparés par une tabulation, lignes terminées par \n, NULL écrit \N, caractères spéciaux échappés par \ ; les options FIELDS et LINES sont celles de LOAD DATA (chapitre 8, §8.8), si bien qu'un fichier écrit avec certaines options se recharge avec les mêmes.
• INTO DUMPFILE écrit la valeur d'une seule ligne, sans séparateur ni échappement (pour une valeur binaire, une image par exemple) ; plus d'une ligne : erreur 1172.

SELECT id, nom, solde FROM clients WHERE id >= 4 INTO OUTFILE 'defaut.txt';
-- OK, 3 ligne(s) affectée(s)

4	David	\N
5	Émile	75.25
6	Fatima	10.00

SELECT id, nom, solde FROM clients INTO OUTFILE 'clients.csv'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
-- OK, 6 ligne(s) affectée(s)      (1,"Alice",100.50 puis 2,"Bob",-20.00 ...)
SELECT id, nom FROM clients INTO OUTFILE 'clients.csv';
-- ERREUR 1086 (HY000) : File 'clients.csv' already exists
SELECT nom FROM clients WHERE id = 2 INTO DUMPFILE 'bob.bin';   -- le fichier contient Bob