9. SELECT Queries

This chapter describes the SELECT statement: select list, FROM and joins, WHERE, GROUP BY / HAVING, ORDER BY, LIMIT / OFFSET, subqueries and UNION. Sections 9.12 to 9.23 complete this foundation: full grammar, joins in practice, set operations, common table expressions (recursive ones included), groupings (ROLLUP, CUBE, GROUPING SETS), window functions, locks, optimizer hints, reading EXPLAIN, full-text search and SELECT … INTO. The results shown are those of miraj-cli.

The examples rely on two tables, populated once for the whole chapter:

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 [join ...]]
    [WHERE condition]
    [GROUP BY expr [, expr ...]]
    [HAVING condition]
    [WINDOW name AS (window) [, ...]]
    [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}]]

This synopsis is the basic form; the full grammar (WITH, UNION, locks, OVER, etc.) is given in §9.12.

9.2 Select list#

Each item is an expression, with an optional alias (AS alias, or an alias without 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;                -- all columns
SELECT clients.* , 1 AS actif FROM clients;   -- all columns of a named table, plus an expression

A column without an alias carries the text of its expression (solde * 1.1). The product of two decimals has as many decimal places as its two operands combined (DECIMAL(10,2) * 1.1: three).

DISTINCT#

DISTINCT eliminates result rows that are identical on all selected items (a NULL value is considered equal to another NULL for this elimination, unlike = in a 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 is a synonym of DISTINCT; ALL (default) keeps duplicates.

COUNT(DISTINCT expr) counts distinct non-NULL values rather than rows; with several expressions, COUNT(DISTINCT a, b, ...) counts the distinct combinations in which no expression is NULL:

SELECT COUNT(DISTINCT ville), COUNT(DISTINCT ville, solde) FROM clients;
-- 3 and 4: (Constantine, NULL) and (NULL, 10.00) do not count

9.3 FROM and joins#

Supported join types#

Merged columns of USING and NATURAL.

• SELECT * returns each common column only once, first, in the order of the left table, then the other columns of the left table, then those of the right table. For RIGHT JOIN, the right table is the preserved table: common columns in its order, then its other columns, then those of the left table.
• The merged column takes the value of the preserved table's column: the left one for INNER and LEFT JOIN, the right one for RIGHT JOIN (never NULL because of the join). Its metadata (origin table reported to the client) are those of that column.
• An unqualified reference (id, in the list, WHERE, GROUP BY, ORDER BY, a following ON) designates the merged column, without ambiguity; a.id and b.id remain accessible (b.id is NULL when a LEFT JOIN has no match). t.* is not merged: it returns all the columns of t.
• In a chain (a JOIN b USING (x) JOIN c USING (y)), each join merges with the result that precedes it: the columns of the last one come first. A NATURAL JOIN after an ON join whose two tables carry a common column is ambiguous (1052).
• A view CREATE VIEW v AS SELECT * FROM a LEFT JOIN b USING (id) has the merged columns; it is rejected (1060) if another column exists on both sides without being part of the join. SHOW CREATE VIEW returns the text as written (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;           -- equality on id and v

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

SELECT * FROM n1 NATURAL RIGHT JOIN n2;     -- common columns in the order of n2

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

INNER JOIN, LEFT JOIN, RIGHT JOIN and CROSS JOIN can all carry an alias on each table, and chain several joins in a single 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;   -- all orders, including those without an existing client (id = 9)

Result of the third query:

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

The results of the other join types are shown in §9.13.

Self-join#

A table can be joined to itself; each occurrence needs a distinct alias:

SELECT a.nom, b.nom
FROM clients a
JOIN clients b ON a.ville = b.ville AND a.id < b.id;
-- (Alice, Chloé) and (Bob, Émile)

Derived table (subquery in FROM)#

A subquery can replace a table in FROM or in a join; it needs an 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 |
+-------+--------+

Without ORDER BY, the order of the groups is not guaranteed (here, the order of first appearance).

A list of names after the alias renames its columns (FROM (SELECT ville, SUM(solde) FROM clients GROUP BY ville) AS t (ville, total)): as many names as columns (error 1222), and not on a SELECT * (error 1235). A VALUES (...), (...) table constructor can also be written there (§9.14).

Not supported#

• There is no FULL [OUTER] JOIN; an equivalent can be built with a LEFT JOIN and a RIGHT JOIN combined by UNION (see §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 filters the rows produced by FROM before any grouping. Available operators:

Text comparisons (=, <, IN, BETWEEN...), GROUP BY, DISTINCT, ORDER BY and joins follow the collation of the operands (that of the column against a literal, that of an explicit COLLATE): WHERE code = 'a1' does not return 'A1' on a utf8mb4_bin column, WHERE nom COLLATE utf8mb4_unicode_ci = 'strasse' returns 'Straße'. Two collations with no winner are compared under the wider one, without error (see Collations).

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

A trigram index on the column (chapter 7, "Full-text and trigram indexes") serves col LIKE '%…%' without changing the SQL.

Three-valued logic and NULL#

A comparison with NULL (including NULL = NULL) is never TRUE or FALSE, but UNKNOWN; a row is retained by WHERE only if its condition is TRUE (UNKNOWN, like FALSE, excludes the row). Testing for the absence of a value therefore requires IS NULL / IS NOT NULL, never = NULL:

SELECT nom FROM clients WHERE solde = NULL;      -- never returns anything
SELECT nom FROM clients WHERE solde IS NULL;      -- David

Chapter 14 (§14.6, "Three-valued logic") details this behavior and its usual pitfalls (NOT IN with a NULL in the list, NOT on an UNKNOWN condition, aggregates that ignore NULLs).

9.5 GROUP BY and HAVING#

GROUP BY groups the rows that share the same values of the listed expressions; the aggregate functions (COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT, ...) are then computed per group. HAVING filters groups, just as WHERE filters rows (and can therefore apply to an aggregate, which WHERE cannot).

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

Column referenced outside GROUP BY#

Miraj's SQL mode does not, by default, include the equivalent of the ONLY_FULL_GROUP_BY restriction: a column of the select list that is neither an aggregate nor named in GROUP BY is accepted, and returns a value from one of the rows of the group (which one is not guaranteed; enabling it in sql_mode restores error 1055). Example:

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

Here nom is neither aggregated nor in GROUP BY: for the 'Oran' group (Alice and Chloé), the value of nom returned is that of one of the two rows of the group, with no guarantee as to which. Use this form only when the column is in fact constant within each group (for example a column functionally dependent on the grouped column), otherwise the result depends on the internal order of the rows.

Name resolution in GROUP BY, HAVING and ORDER BY#

A name without a table qualifier (ville, not clients.ville) can designate either a column of a table in the FROM or an alias from the SELECT list. The resolution rule differs depending on the clause:

• GROUP BY: a real column of a table being read takes precedence over an alias of the same name, as long as it exists unambiguously among the tables of the FROM. The alias is used only if no column of that name exists, or to resolve an ambiguity between several tables that each have a column of that name.
• HAVING and ORDER BY: conversely, an alias from the SELECT list takes precedence over a table column of the same name.
• In all cases, a qualified name (t.col) is always a table column, never an alias.

-- the alias `nom` hides the real column `ville` for ORDER BY, but GROUP BY groups on the
-- real column `nom` of clients: each client has a distinct name, hence one group per row,
-- not one group per city as grouping on the alias would give
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 |
+-------------+----------+

A name carried by several tables of the FROM is ambiguous (error 1052) unless it unambiguously designates an alias from the SELECT list, or is qualified.

9.6 GROUP_CONCAT#

GROUP_CONCAT([DISTINCT] expr [, expr ...] [ORDER BY key [ASC|DESC] [, ...]] [SEPARATOR 'string']
             [LIMIT n | LIMIT m, n | LIMIT n OFFSET m])

Concatenates, for each group, the values of expr (NULL values are ignored), in the order given by the ORDER BY clause internal to GROUP_CONCAT (independent of any ORDER BY of the query), separated by SEPARATOR (, by default). DISTINCT eliminates repeated values before concatenation. LIMIT keeps only n values, after skipping m, once duplicates are removed and values sorted; the clauses are written in this order (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 |
+-------------+-------+

-- The first name (alphabetical order) of each city
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   |
+-------------+---------+

The length of the result is bounded by the group_concat_max_len variable (beyond it, the value is truncated with a warning).

9.7 ORDER BY, LIMIT and OFFSET#

ORDER BY sorts the result by one or more expressions, each ASC (default) or DESC; a numeric position (ORDER BY 1) designates the corresponding item of the select list.

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

NULLs sort first in ascending order, last in descending order.

LIMIT bounds the number of rows returned; OFFSET (or the form LIMIT offset, count) skips the first rows of the sorted result:

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;        -- equivalent: LIMIT 1 OFFSET 4

The standard SQL pagination is accepted in place of LIMIT, wherever LIMIT is (subquery, derived table, member or end of a union, table expression, view, INSERT … SELECT, CREATE TABLE … AS SELECT, routine, prepared statement):

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

The second row (Alger) has a tie, returned in addition to the two rows requested.

• FIRST and NEXT are synonyms, as are ROW and ROWS; an omitted number means 1 (FETCH FIRST ROW ONLY). ROW or ROWS is mandatory after the OFFSET count.
• WITH TIES returns, after the n rows, those that follow as long as they tie with the last one on all the ORDER BY keys: same value under the key's collation ('B' and 'b' under a case-insensitive collation), two NULLs equal to each other. Ties skipped by OFFSET do not come back; FETCH FIRST 0 ROWS WITH TIES returns nothing. An ORDER BY of constants leaves all rows tied: all are returned.
• WITH TIES requires an ORDER BY in the same block (that of the whole union for a union): error 4180 otherwise.
• LIMIT and OFFSET … ROWS / FETCH cannot be combined in one block: syntax error (1064).
• With WITH TIES, the sort is not bounded: all rows are sorted before the cut (no sort limited to the first n, no limit pushed down to the fragments of a partitioned table).

9.8 Subqueries#

A subquery (a parenthesized SELECT) can appear:

• as a scalar value, anywhere an expression is expected (it must return at most one row and one column; more than one row at execution time is the "single-row subquery" error);
• in an [NOT] IN (subquery) test;
• in an [NOT] EXISTS (subquery) test;
• as a derived table in FROM (see §9.3).

A subquery can be correlated: it references a column of a table of the outer query and is then re-evaluated for each row of the latter.

-- scalar, correlated: the highest balance of each city
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, correlated: orders whose client does not exist
SELECT id FROM commandes o WHERE NOT EXISTS (
    SELECT 1 FROM clients c WHERE c.id = o.client_id
);
-- 4

-- IN with subquery
SELECT nom FROM clients WHERE id IN (SELECT client_id FROM commandes);
-- Alice, Bob, Chloé

-- subquery in the select list
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#

select_query UNION [ALL] select_query [UNION [ALL] select_query ...]
[ORDER BY ...] [LIMIT ...]

UNION combines the results of several SELECTs with the same number of columns; UNION (without ALL) eliminates duplicate rows from the combined result, UNION ALL keeps them all.

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

A final ORDER BY or LIMIT, after the last member, applies to the combined result of the entire union, not just to the last SELECT; it references a column of the result by its name (that of the first member), its position or an expression of those columns. A column qualified by its table (ORDER BY clients.id) is rejected (error 1250, Table 'clients' from one of the SELECTs cannot be used in global ORDER clause). Each member can be parenthesized to give it its own ORDER BY / LIMIT, applied before the combination:

(SELECT nom FROM clients ORDER BY id DESC LIMIT 1)
UNION
(SELECT nom FROM clients ORDER BY id LIMIT 1);
-- Fatima, Alice

An ORDER BY or LIMIT placed on a non-parenthesized member followed by a UNION is rejected by the grammar (syntax error 1064): parenthesize the member to resolve the ambiguity between "sort of this member" and "sort of the entire union".

9.10 Using a view#

A view is read exactly like a table, in FROM, a join or a subquery:

SELECT * FROM v_clients_actifs WHERE ville = 'Oran';

The creation, modification and refresh of views (ordinary view or CACHED) are described in chapter 7, "DDL Language".

9.11 Features not available in this version#

Status of the query features in this version:

This table reflects the state of the code at the time of writing.

9.12 Full grammar#

SELECT grammar ([ ]: optional, |: alternative, { }: at least one element):

query ::=
    [ WITH name [(col, ...)] AS ( query ) [, ...] ]
    member [ UNION [ALL | DISTINCT] member ... ]
    [ ORDER BY expr [ASC|DESC] [, ...] ]          -- after the last member: applies to the union
    [ LIMIT ... ]                                  -- same

member ::=  select  |  ( query )
         |  VALUES (expr, ...) [, (expr, ...) ...]     -- table constructor (§9.14)

select ::=
    SELECT [ /*+ hints */ ] [ DISTINCT | DISTINCTROW | ALL ]
           [ SQL_CALC_FOUND_ROWS | SQL_NO_CACHE | SQL_CACHE | HIGH_PRIORITY | STRAIGHT_JOIN ] ...
           item [, item ...]
    [ INTO @variable [, ...] | INTO OUTFILE 'file' ... | INTO DUMPFILE 'file' ]
    [ FROM DUAL | source [ join ... ] ]
    [ WHERE condition ]
    [ GROUP BY expr [ASC|DESC] [, ...] [WITH ROLLUP]
    | GROUP BY { expr | ROLLUP(...) | CUBE(...) | GROUPING SETS (...) } [, ...] ]
    [ HAVING condition ]
    [ WINDOW name AS (window) [, ...] ]            -- named windows (§9.18)
    [ QUALIFY condition ]                          -- filter on window functions (§9.18)
    [ ORDER BY expr [ASC|DESC] [, ...] ]
    [ LIMIT { n | offset, n | n OFFSET offset } [ROWS EXAMINED m]
    | [OFFSET offset {ROW | ROWS}] [FETCH {FIRST | NEXT} [n] {ROW | ROWS} {ONLY | WITH TIES}] ]
    [ INTO ... ]                                   -- other accepted place for INTO (§9.23)
    [ FOR UPDATE | FOR SHARE [OF table, ...] [NOWAIT | WAIT s | SKIP LOCKED]
    | LOCK IN SHARE MODE [NOWAIT | WAIT s | SKIP LOCKED] ]
    [ INTO ... ]                                   -- or after the lock clause

item    ::=  * | table.* | base.table.* | expr [[AS] alias]
source  ::=  table [PARTITION (p, ...)] [FOR SYSTEM_TIME period] [[AS] alias]
          |  JSON_TABLE(document, 'path' COLUMNS (...)) [AS] alias
          |  ( query ) [AS] alias [(col, ...)]  |  ( source join ... )
-- FOR SYSTEM_TIME: versioned tables (chapter 12); JSON_TABLE: chapter 10, §10.10
join ::=
      , 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

Details:

• A JOIN without ON or USING is a Cartesian product; a LEFT or RIGHT JOIN without ON or USING is a syntax error (1064).
• SELECT without FROM (or FROM DUAL) is allowed; the word DUAL is recognized only in place of the first table.
• SQL_NO_CACHE, SQL_CACHE and HIGH_PRIORITY are accepted and have no effect on the result; SQL_CALC_FOUND_ROWS makes FOUND_ROWS() count the rows the SELECT would return without its LIMIT (chapter 10, §10.9); DISTINCTROW is equivalent to DISTINCT.
• A parenthesized group of joins at the start of FROM (FROM (a LEFT JOIN b ON ...)) is accepted; it is flattened, the parentheses do not change the meaning.
• GROUP BY expr ASC|DESC is accepted but imposes no ordering: write an ORDER BY.
• LIMIT accepts an integer, a ? parameter of a prepared statement or a local variable of a routine; LIMIT 18446744073709551615 (the "all rows" idiom) is accepted.
• LIMIT n ROWS EXAMINED m bounds the number of rows examined by the whole statement.
• OFFSET m ROWS and FETCH FIRST n ROWS ONLY | WITH TIES (§9.7) accept the same values as LIMIT. FETCH is not a reserved word: it remains usable as a table, column or alias name; it opens the clause only when followed by FIRST or NEXT, an optional number, ROW or ROWS, then ONLY or WITH.
• QUALIFY is not a reserved word: qualify remains usable as a table, column or alias name. Right after a table, it opens the clause when followed by a condition (FROM t QUALIFY ROW_NUMBER() OVER (...) = 1) and serves as an alias otherwise (FROM t qualify WHERE ..., FROM t qualify, u); to name qualify a table followed by the clause, write AS qualify.
• WINDOW is not a reserved word: window remains usable as a table, column or alias name; it opens the clause only when followed by a name, AS and a parenthesis (FROM t WINDOW w AS (...)).
• INTO (variables, file) is allowed only in the first SELECT of the statement (in a union, it receives the result of the entire union), only once, never in a table expression or a subquery (1064).

All the forms below assume the clients and commandes tables from the chapter header.

9.13 Joins in practice#

Summary of join types#

Examples and results#

INNER JOIN: clients without orders and orders without a client disappear.

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: all clients; NULL where there is no order.

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: all orders; order 4 references a nonexistent client (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: the Cartesian product of 6 clients by 5 orders gives 30 rows; reserve it for intended cases (parameter tables, generating combinations).

Anti-join (clients without any order): LEFT JOIN ... IS NULL or NOT EXISTS, which give the same result.

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

Join with a derived table (aggregate per client, then join); switching to LEFT JOIN keeps the clients without orders:

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

Multiple joins: they chain from left to right in the same FROM, with a distinct alias for each occurrence of a table (without a distinct alias: error 1066); the types can be mixed (JOIN, LEFT JOIN, RIGHT JOIN). An ON condition can reference the tables placed before it:

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

Self-join: the pairs of clients in the same city (see §9.3); the alias is required to tell the two occurrences apart. a.id < b.id avoids symmetric pairs and pairs of a client with itself:

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

Emulated FULL OUTER JOIN: LEFT JOIN then RIGHT JOIN combined by UNION (which eliminates duplicates; the final ORDER BY applies to the entire union):

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

With UNION (rather than UNION ALL), two truly identical rows from both sides are merged: if duplicate rows are significant, add an identity column (here o.id, c.id) or build the union with UNION ALL and a WHERE o.id IS NULL on the right-hand part.

USING: equality condition on columns with the same name; the column can be named without a qualifier. SELECT * returns the common column only once, first, then the other columns of each 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 |
+-------+----+-------+--------+----+-------+-------+

Which join type to choose#

• Should the rows of a table that have no match stay in the result? No: INNER JOIN. Yes, those of the main table: LEFT JOIN (write the main table on the left; RIGHT JOIN is its mirror and adds nothing more).
• To find the rows without a match: LEFT JOIN ... WHERE right.key IS NULL or NOT EXISTS; NOT IN (subquery) is tricky with NULL (see below).
• To simply test for the existence of a match without duplicating the left rows: WHERE EXISTS (...) or IN (subquery) rather than a join, which multiplies the left row by the number of matches (Alice appears twice in the first example).
• If you need aggregated values from the other table: aggregate first in a derived table, then join, which avoids multiplying rows before the aggregate.

Behavior of NULL in joins#

• An ON a.x = b.y condition is never true when either side is NULL: two NULLs do not join each other. Fatima (ville NULL) joins no client in the join on ville above, not even herself.

• In a LEFT JOIN, the right-side columns without a match are NULL. A WHERE condition on a column of the right table (WHERE o.montant > 10) eliminates these rows (a comparison with NULL is never true) and in effect turns the LEFT JOIN into an INNER JOIN. To filter the right table while keeping all the left rows, put the condition in the 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 |
  +--------+------+

  With WHERE o.montant > 10 instead, only Alice's row remains.

• Counting matches: COUNT(o.id) ignores NULLs (0 for a client without orders), COUNT(*) counts the "no match" row itself (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 (subquery) returns zero rows as soon as the subquery produces a NULL (see §9.16); NOT EXISTS does not have this pitfall.
• A column of a table on the right side of a LEFT JOIN becomes nullable for the rest of the query, even if it is NOT NULL in the table (likewise for the left side of a RIGHT JOIN).

Engine-specific performance notes#

• Join algorithm. When the ON (or USING) condition contains at least one equality between a column on each side (o.client_id = c.id, possibly combined by AND with other conditions), the engine uses a hash join. Otherwise (ON a.solde > b.solde, OR, expression, Cartesian product) it uses a nested loop, whose cost is the product of the sizes. The equality must involve columns of the same type family (number with number, string with string): comparing a string to a number rules out the hash join.
• Join order. Joins are planned in the order written in the FROM, with no reordering based on statistics; STRAIGHT_JOIN adds nothing (it is treated as JOIN). Write first the table that filters the most.
• Indexes. Indexes (primary key, UNIQUE, secondary) serve table reads by equality with a constant (WHERE id = 1, WHERE ville = 'Oran', see §9.20); ranges (id > 2) scan the table, except on a VECTOR column of an index (chapter 11).
• Join by index probe. For an INNER or LEFT JOIN on equality, the joined table is read through its index instead of being loaded in full when it is large compared with the table that precedes it (at least 1,000 rows and eight times more than that one) and the equated column carries the primary key, a UNIQUE index or a secondary index (integer, binary or text key equated under the index collation; no virtual column, no whole BLOB / TEXT, no prefix index). When the equated column is a VECTOR (first column of an index, or after equated columns), the probe is chosen as soon as the driving table is not estimated larger than the joined table. EXPLAIN then shows eq_ref or ref for the joined table, with the driving table's column in ref (example in §9.20). The USE, FORCE and IGNORE INDEX hints of the joined table choose the probed index (§9.19). Otherwise, the matching is done by the hash join (ALL for both tables).
• Unnecessary outer joins. A LEFT JOIN from which no column is read and which cannot multiply rows (join on a unique key, or a DISTINCT query) is removed from the plan. This optimization does not apply if the joined table is referenced by *, t.*, a qualified column or a subquery, nor for a view or a derived table.
• Expensive queries. max_join_size (with sql_big_selects) rejects a SELECT block whose estimated number of examined rows is too large (error 1104), before any execution. Ranges and ORs do not reduce this estimate; EXPLAIN does not apply the check.
• Views and derived tables. A view is read as its query specialized by the caller's conditions; a common table expression is replaced by its query (§9.15).

9.14 Set operations: UNION, INTERSECT, EXCEPT#

• UNION [ALL | DISTINCT]: supported (§9.9). DISTINCT after UNION is the default behavior, identical to no keyword. The number of columns of the members must be identical (error 1222 otherwise); the column names of the result are those of the first member.
• INTERSECT [ALL | DISTINCT]: rows present in both queries.
• EXCEPT [ALL | DISTINCT]: rows of the first query absent from the second. MINUS is a synonym, accepted in every mode; minus remains usable as a column or table name when it is not followed by a query.

-- Identifiers present in clients AND in commandes.client_id
SELECT id FROM clients INTERSECT SELECT client_id FROM commandes ORDER BY id;
-- 1, 2, 3
-- Identifiers of clients without orders
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;   -- same result

With ALL, duplicates count:

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

Rules:

• NULL: for these operations, two NULLs are equal (unlike = and IN); SELECT NULL INTERSECT SELECT NULL returns one NULL row, SELECT 1, NULL EXCEPT SELECT 1, NULL returns none. Strings are compared according to the column's collation, as for DISTINCT: with a case-insensitive collation, 'a' INTERSECT 'A' returns a.
• Without ALL (or with DISTINCT): each row of the result appears only once. With ALL: multisets; a value present m times on the left and n times on the right appears min(m, n) times with INTERSECT ALL, max(m − n, 0) times with EXCEPT ALL.
• Precedence: INTERSECT binds tighter than UNION and EXCEPT; the other operations are evaluated left to right. a UNION b INTERSECT c is a UNION (b INTERSECT c), and a EXCEPT b EXCEPT c is (a EXCEPT b) EXCEPT c. Parentheses group freely: SELECT … EXCEPT (SELECT … UNION SELECT …), ((SELECT …) INTERSECT (SELECT …)) UNION ….
• Columns: same number of columns in each member (error 1222 otherwise), common type and names of the first member, as for UNION (§9.9).
• Sorting and limit: an ORDER BY / LIMIT / OFFSET written after the last non-parenthesized member applies to the entire result; a member that has its own is written in parentheses ((SELECT … ORDER BY x LIMIT 2) INTERSECT SELECT …). (compound query … LIMIT 2) ORDER BY 1 sorts the result of the parenthesized query.
• These queries can be used wherever UNION is allowed: derived table, subquery (IN, EXISTS, scalar), common table expression (WITH, except in the recursive part of a WITH RECURSIVE: error 1235), INSERT … SELECT, CREATE TABLE … SELECT, view (SHOW CREATE VIEW returns the text as written), body of a procedure, function, trigger and event.
• Hash execution: the right-hand member is read in full and its rows counted by value, then the left-hand rows are returned in their reading order. EXPLAIN shows one block per member (PRIMARY, then INTERSECT, EXCEPT or UNION); a series of intersections grouped by precedence, or a parenthesized group, appears there as a derived table (DERIVED). EXPLAIN FORMAT=JSON names the result <intersect1,2>, <except1,2> or <unit1,2,3> for mixed operations.

VALUES table constructor#

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

VALUES (1, 'a'), (2, 'b') is a query that returns one row per parenthesis, like a UNION ALL of one table-less SELECT per row:

• the columns carry the text of the expressions of the first row (1 and a here); their type is the common type of the rows (VALUES (1), (2.5) returns 1.0 and 2.5);
• all rows have the same number of values (error 4099);
• no column is visible in it, not even one of an enclosing query (error 4100); a subquery written in a row reads its own tables (VALUES ((SELECT MAX(id) FROM t))). In a routine, local variables and parameters are readable there, and any other name is rejected as soon as the routine is created (4100);
• ORDER BY and LIMIT (or OFFSET … FETCH) apply to all its rows; a sorted or limited member followed by a set operation is written in parentheses, like a SELECT;
• combined by UNION, INTERSECT or EXCEPT, it counts as a single member: SELECT 1 UNION VALUES (1), (1) returns one row, SELECT 1 UNION ALL VALUES (1), (1) returns three.

It can be written wherever a query is allowed:

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

A derived table without a name list keeps the names of the first row, to be written between backticks when they are not identifiers (v.`1`). CREATE TABLE … AS VALUES names its columns the same way: a first row whose text is not an allowed column name (1.5) returns error 1166; use a derived table with its name list.

In an ON DUPLICATE KEY UPDATE, VALUES (column) remains the function that reads the proposed value (chapter 8), even between parentheses; EXPLAIN shows one table-less block per row.

9.15 Common table expressions (WITH)#

WITH name [(column, ...)] AS ( SELECT ... | VALUES (expr, ...), ... ) [, name2 AS (...)]
SELECT ... ;

A common table expression (CTE) gives a name to a query that can be reused in the rest of the statement, including several times and in a join with itself. Verified:

• several CTEs separated by commas, a CTE being able to reference those that precede it;
• a column list after the name renames the columns (the number must match, error 1222; incompatible with a SELECT * body, error 1235);
• a VALUES (...), (...) body is accepted (table constructor, §9.14); without a column list, the column names are those of the first row (1, a): provide a column list;
• the WITH written at the start of the query applies to the whole union (WITH c AS (…) (SELECT …) EXCEPT (SELECT …) included); written inside a parenthesized member, it applies only to that member;
• WITH RECURSIVE lets an expression reference itself (see below).

The engine replaces each reference to a CTE by its query (like a derived table), and limits the total number of these expansions to bound the size of a query: a CTE referenced at many nested levels may be rejected with syntax error 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 |
+---+---+

Recursive expressions (WITH RECURSIVE)#

WITH RECURSIVE name [(column, ...)] AS (
    non-recursive member [UNION [ALL] non-recursive member ...]
    UNION [ALL | DISTINCT]
    recursive member [UNION [ALL] recursive member ...]
) SELECT ... ;

After WITH RECURSIVE, an expression can reference itself in its own body. The members that do not reference it (at least one, first) produce the first rows; each iteration then reads the recursive members on only the rows returned by the previous iteration, until an iteration returns none. An expression of a WITH RECURSIVE that does not reference itself remains ordinary.

• The column types are those of the non-recursive members: the rows of the recursive members are converted to them. To build a path that grows longer, widen the column in the non-recursive member (CAST(libelle AS CHAR(200))).
• With UNION (or UNION DISTINCT) instead of UNION ALL, a row already returned is neither returned nor read a second time: a graph traversal with cycles terminates.
• cte_max_recursion_depth (session or global, default 1000, from 0 to 4,294,967,295) bounds the number of iterations, including the one that returns nothing: beyond it, error 3636 "Recursive query aborted after n iterations".
• A LIMIT on the entire union (without ORDER BY) stops the iterations as soon as it is reached.
• Errors: without UNION, 3573; a non-recursive member placed after a recursive member, 3574; aggregate, window function or GROUP BY in a recursive member, 3575.
• The expression is computed entirely before the query that references it is read, and once per reference (like a derived table).

-- Accounts of the chart of accounts under account 60, with their level and path
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 Subqueries: additional notes#

Supported forms (§9.8): scalar, IN, EXISTS, correlated, derived table, and subquery in the select list, including inside an expression (EXISTS (...) as a 0/1 value).

• Scalar subquery: zero rows gives NULL; more than one row is error 1242 ("Subquery returns more than 1 row").

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

• Correlated: can reference a column of the outer query (by alias or by table name, in WHERE, in the select list, in IN, in EXISTS), including in a comparison test: SELECT nom FROM clients WHERE id IN (SELECT client_id FROM commandes WHERE montant > clients.solde) returns Bob.
• ANY, SOME, ALL: x op ANY (subquery), x op SOME (...), x op ALL (...) with =, <> (!=), <, <=, >, >=; the subquery, correlated or not, produces one column. ANY is true if the comparison is true for at least one row, ALL if it is true for all of them. With no row, ANY is false and ALL true. The logic is three-valued: a NULL among the values makes the result unknown when no row settles it (5 > ALL (SELECT 1 UNION ALL SELECT NULL) is NULL), so that x <> ALL (S) is equivalent to x NOT IN (S) and x = ANY (S) to x IN (S). <=> cannot be quantified.
• Row constructor: (a, b) = (1, 2), ROW(a, b) <> ROW(1, 2), ordering comparisons (lexicographic: (a, b) < (c, d) is a < c OR (a = c AND b < d)), (a, b) IN ((1, 'x'), ...), (a, b) [NOT] IN (SELECT x, y ...) and (a, b) op ANY | ALL (SELECT x, y ...). Both sides must have the same number of elements, otherwise error 1241 ("Operand should contain N column(s)"); a standalone row constructor (SELECT ROW(1, 2)) is rejected in the same way. (a, b) = (SELECT ...) (multi-column scalar subquery) is not supported: write (a, b) IN (SELECT ...).

• LIMIT in an IN subquery: accepted by this engine.

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

  Returns Alice (the first two orders are those of client 1).

• IN and NULL: x IN (S) is TRUE if a value matches; otherwise UNKNOWN (not FALSE) if S contains a NULL. The pitfall is in NOT IN: as soon as S contains a NULL, the test is never TRUE, and the query returns no rows.

  SELECT nom FROM clients WHERE id NOT IN (SELECT client_id FROM commandes UNION ALL SELECT NULL);
  -- 0 rows
  
  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

  Prefer NOT EXISTS, or add WHERE col IS NOT NULL in the NOT IN subquery.

• Uncorrelated subquery: it depends on no outer column; the result is the same as a constant value (WHERE solde > (SELECT AVG(solde) FROM clients) returns Alice and Émile).
• Derived table: the alias is required (§9.3). LATERAL (a derived table that references another table of the same FROM) is not supported (1064); use a correlated subquery in the select list, or an aggregated derived table then joined.

9.17 GROUP BY, HAVING and groupings: additional notes#

• GROUP BY accepts expressions, positions (GROUP BY 1) and aliases (resolution rules in §9.5). NULL values form a single group.
• Aggregate functions supported in SELECT ... GROUP BY: COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT (§9.6), STD / STDDEV / STDDEV_POP, VARIANCE (details in chapter 10). COUNT(DISTINCT expr) and SUM(DISTINCT expr) filter on distinct values; COUNT(DISTINCT a, b, ...) counts the distinct combinations without NULL.
• Without GROUP BY, an aggregate function always produces one row, even on an empty table (COUNT(*) returns 0, the others NULL); with GROUP BY on zero rows, the result is empty.
• HAVING can reference an alias from the select list (§9.5); a condition on a non-aggregated column belongs rather in WHERE, which is more efficient because it filters before grouping.
• WITH ROLLUP: GROUP BY a, b WITH ROLLUP adds, after each group, a subtotal row where the summarized grouping columns are NULL, then a grand total last. Without ORDER BY, rows come out in this order (ascending keys, NULL first, each subtotal after its group); aggregates (including COUNT(DISTINCT ...) and GROUP_CONCAT), HAVING, ORDER BY, DISTINCT, LIMIT, window functions, subqueries, derived tables and views apply to subtotal rows like to the others. On an empty table, the result is empty (not even a total).
• GROUPING(expr [, ...]) distinguishes a real NULL from a subtotal NULL: 1 if the row summarizes the expression, 0 otherwise; with several arguments, a bit mask (the first argument is the most significant bit). The arguments must appear in the GROUP BY (error 3580), with no GROUPING() call in a WHERE or outside grouping (1111); without ROLLUP, it is always 0.

• ROLLUP(...), CUBE(...) and GROUPING SETS (...) (standard SQL, MIRAJ extension): ROLLUP(a, b) is equivalent to WITH ROLLUP, CUBE(a, b) produces all subsets of columns, GROUPING SETS ((a), (b), ()) lists the wanted sets (a nested set can be a ROLLUP or a CUBE). Several comma-separated elements are crossed (GROUP BY a, ROLLUP(b)). The expressions, aliases and positions of the ordinary GROUP BY are allowed. WITH ROLLUP cannot be combined with ROLLUP(...) in the same 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 |
  +-------------+---------+----------+

  The first three rows group by city, the next three by positif (the fourth is David's group, whose solde > 0 is NULL), the last is the total; GROUPING() tells these NULLs apart. A view that contains a ROLLUP does not have the calling WHERE condition pushed down into its definition: the outer filter does not change the totals.

• DISTINCT applies to the entire row; SELECT DISTINCT ville, solde distinguishes pairs (§9.2), NULL included. ORDER BY sorts NULLs first in ascending order and last in descending order:

  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 Window functions#

Syntax: function(...) OVER ([PARTITION BY expr, ...] [ORDER BY expr [ASC|DESC], ...] [frame]), where the frame is ROWS | RANGE followed by bound or BETWEEN bound AND bound, a bound being UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING or UNBOUNDED FOLLOWING; in a RANGE frame on a date, also INTERVAL n unit PRECEDING and INTERVAL n unit FOLLOWING. In place of the parenthesis, OVER name designates a window of the WINDOW clause (below).

Supported functions:

Not supported (error 1235 unless stated otherwise): COUNT(DISTINCT ...), SUM(DISTINCT ...) and GROUP_CONCAT with OVER; any other function used with OVER. The offset of LAG/LEAD and the rank of NTILE / NTH_VALUE must be integer constants (NTILE and NTH_VALUE: at least 1). FILTER (WHERE ...) and the GROUPS frame are not supported.

Default frame: without ORDER BY, the whole partition; with ORDER BY, from the first row of the partition to the current row and its peers (RANGE UNBOUNDED PRECEDING to CURRENT ROW). This is why LAST_VALUE(...) without an explicit frame returns the current row and not the last of the partition: write ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

A window function goes in the select list, in the ORDER BY or in the QUALIFY clause; in WHERE it is rejected (error 1111). To filter on its result, write QUALIFY (below) or wrap it in a derived table or a CTE.

The top of each city (top n per group):

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;

-- Same result, without a derived table
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 is first in Constantine: in a descending sort, the NULL value is ranked last, and he is alone in his group.

Running total (cumulative sum):

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

Sum per partition, rank and previous value:

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;

The second query shows that a window function can operate on an aggregate: Alger and Oran (2 clients) are tied at rank 1, NULL and Constantine at rank 3. Window functions are evaluated after WHERE, GROUP BY and HAVING.

RANGE frame with an offset#

Under ROWS, an offset counts rows. Under RANGE, it applies to the value of the sort key: the frame contains the rows of the partition whose key is at most that distance from the one of the current row, ties included. The window must have a single sort key (error 4019 otherwise, or without ORDER BY).

• Numeric key: RANGE BETWEEN 10 PRECEDING AND CURRENT ROW keeps the rows whose key goes from key - 10 to key. The offset is a written integer; on a key that is not numeric, error 4021.
• DATE, DATETIME or TIMESTAMP key: the bound is written INTERVAL n unit PRECEDING or INTERVAL n unit FOLLOWING, with the INTERVAL units (DAY, WEEK, MONTH, HOUR, MINUTE...) and an integer n, quoted or not (INTERVAL '1' DAY). INTERVAL on a key that is not a date: error 1235; under ROWS: syntax error 1064.
• Descending sort: PRECEDING always goes towards the start of the partition, hence towards the larger values (ORDER BY jour DESC RANGE INTERVAL 7 DAY PRECEDING: the current row and the seven days that follow it in the calendar).
• NULL: a row whose key is NULL has as its frame only its ties (the other rows with a NULL key); these rows enter the frame of another row only through an UNBOUNDED bound.

-- Sales of the last seven days, current day included
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 |
+------------+---------+---------+

On October 9, the 1st (eight days earlier) leaves the frame: 20 + 5 + 7 = 32.

Named windows: the WINDOW clause#

WINDOW name AS (window) [, name AS (window) ...] goes after HAVING and before QUALIFY, ORDER BY and LIMIT. It names windows that the functions of the same block reuse, in the select list, ORDER BY or QUALIFY; each block (subquery, derived table, member of a union, common table expression, view) has its own names.

• OVER name reuses the window as is, frame included.
• OVER (name ORDER BY ...) or OVER (name ROWS ...) completes it: the reused window must not have a frame (error 4013), the new one cannot redefine PARTITION BY (4011), nor write an ORDER BY if the reused window has one (4012).
• A named window can likewise complete another one defined before it: WINDOW w1 AS (PARTITION BY ville), w2 AS (w1 ORDER BY solde DESC).
• Unknown name: error 4009; name defined twice: 4010. Names are case-insensitive.
• A view keeps the text as written (SHOW CREATE VIEW shows the WINDOW clause).

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

QUALIFY clause#

QUALIFY condition (MIRAJ extension, as in Teradata, Snowflake, BigQuery and DuckDB) filters the rows after the window functions are computed, and before DISTINCT, ORDER BY and LIMIT. Evaluation order of a block: FROM, WHERE, GROUP BY, HAVING, window functions, QUALIFY, select list and DISTINCT, ORDER BY, LIMIT.

• The condition can contain window functions (QUALIFY RANK() OVER (PARTITION BY ville ORDER BY solde DESC) <= 2), columns, aggregates when the block aggregates, subqueries, and aliases of the select list (SELECT ..., ROW_NUMBER() OVER (...) AS rn ... QUALIFY rn <= 3).
• Resolution of an unqualified name: as in HAVING (§9.5). Without an aggregate, the select-list alias takes precedence over a column of the same name; when the block aggregates, the column takes precedence.
• The block must compute at least one window function (select list, ORDER BY or QUALIFY): otherwise error 1221 (Incorrect usage of QUALIFY and a query without window function).
• A window function nested in another one is still rejected (1111); unknown name: 1054 (in 'qualify clause').
• In a union, QUALIFY applies to the member that writes it. A view can contain QUALIFY (SHOW CREATE VIEW returns the text as written); it is then not updatable.

-- The two largest balances of each city, ties included
SELECT nom, ville, solde FROM clients
QUALIFY RANK() OVER (PARTITION BY ville ORDER BY solde DESC) <= 2
ORDER BY ville, solde DESC;

-- Cities with the two largest headcounts (window over an aggregate)
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 |
+-------------+---+

The two largest distinct counts are 2 and 1: DENSE_RANK therefore keeps all the cities. The first query returns all six clients (no city has more than two).

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

Top ranks of each partition (top-N)#

When a query keeps only the first ranks of a window, MIRAJ passes to the window only the useful rows: the others are discarded as they are read, with no full sort or copy. The cost becomes close to that of a GROUP BY (on 10 million rows, about 0.2 s instead of 16 s in the Enterprise edition), and the computation is spread across threads in the Enterprise edition. The result is identical to that of the full computation.

The optimization applies:

• to a bound on ROW_NUMBER(), RANK() or DENSE_RANK(): rn <= k, rn < k, rn = k, rn <=> k, k >= rn, rn BETWEEN a AND k, rn IN (k1, k2, ...) (the largest value counts), k being an integer constant, a ? parameter or a small + - * expression on them; with several bounds: the tightest;
• written in the WHERE of a query that reads a derived table, a CTE or an expanded view that computes the rank, or in the QUALIFY of the block that computes it;
• when all the window functions of the block share the same window and only look at the preceding rows: ROW_NUMBER, RANK, DENSE_RANK, LAG, FIRST_VALUE, ROWS aggregates that end at CURRENT ROW at the latest (the default frame with ORDER BY is allowed only with a RANK or DENSE_RANK bound, which keep ties);
• with no GROUP BY, aggregate or HAVING in the window's block; with a bound from the enclosing query, no LIMIT, DISTINCT or union either in the derived table (with QUALIFY, they are allowed: they apply after the filter). The derived table must not be on the optional side of an outer join.

RANK() <= k also keeps the ties of the k-th row, DENSE_RANK() <= k all the rows of the first k distinct values: memory stays bounded, except when a value has a very large number of ties. EXPLAIN shows Using window top-N (k per partition) (and Using parallel window top-N when the computation is spread out); the optimization is disabled by SET optimizer_switch = 'window_top_n=off'.

Sliding window (ROWS 1 PRECEDING: previous row and current row):

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 Read locks and optimizer hints#

Locking reads#

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]

The clause is written last in the block (after LIMIT; only INTO can follow it) and does not accept OF with LOCK IN SHARE MODE. It applies to the tables of the block, or to those in OF. The rows read are held until the end of the transaction (FOR UPDATE: no other transaction can write or hold them; FOR SHARE / LOCK IN SHARE MODE: other shared reads remain allowed). A row already held by another transaction gives, depending on the clause:

A wait that would close a cycle (deadlock) is refused at once with error 1213. Outside a transaction (autocommit), the statement waits in the same way, but returns the row as soon as it is read: the lock is not kept. See chapter 14 ("Transactions and concurrency", locking reads) for details.

-- session 2, while a session 1 holds row 1 through FOR UPDATE
SELECT nom FROM clients WHERE id <= 2 FOR UPDATE NOWAIT;
-- ERROR 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é: row 1 is skipped

The usual "read then write" pattern:

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

Optimizer hints /*+ ... */#

A hint comment right after SELECT is read. Only MAX_EXECUTION_TIME(n) has an effect: maximum execution time in milliseconds (1 to 2,147,483,647); beyond that, the statement fails with error 1969. The other recognized hints are read and then ignored; a syntax error in the comment causes it to be ignored entirely with a warning, without the query failing.

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 million pairs
-- ERROR 1969 (70100): Query was interrupted: execution time limit 0.001 sec exceeded

Without a hint, the duration is bounded by the max_statement_time variable (300 s by default, 0: no limit), with the same error 1969.

The index hints USE | FORCE | IGNORE {INDEX | KEY} [FOR {JOIN | ORDER BY | GROUP BY}] (…) are written after a table name or its alias, possibly several in a row. They choose the index of a join by index probe (§9.13) on that table: USE and FORCE limit the probe to the named indexes, IGNORE rules out the named indexes (with no usable index, the join is done by hash). The read of a table by equality with a constant does not depend on them. The query always returns the same result with or without a hint. Each name must designate an index of the table (PRIMARY for the primary key) that is not IGNORED: otherwise 1176 ("Key 'i' doesn't exist in table 't'", the table alias if there is one). No join hint changes the join order.

-- lignes: 2,000 rows, index idx_lc on 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)   (without the hint: type ref, key idx_lc)
SELECT nom FROM clients c FORCE INDEX (idx_x) WHERE id = 2;
-- ERROR 1176 (42000): Key 'idx_x' doesn't exist in table 'c'

LIMIT n ROWS EXAMINED m is another execution bound: the statement stops after examining m rows and returns the partial result with a warning ("The query exceeded LIMIT ROWS EXAMINED m. The query result may be incomplete"), without an error.

9.20 Reading a plan with EXPLAIN#

EXPLAIN applies to SELECT, UPDATE, DELETE and INSERT ... SELECT (the statement is planned, never executed); EXPLAIN INSERT ... VALUES, EXPLAIN REPLACE ... VALUES and EXPLAIN LOAD DATA respond with error 1235. EXPLAIN table_name is equivalent to DESCRIBE. The output is tabular by default (FORMAT=TRADITIONAL too); FORMAT=JSON returns a JSON document (below), FORMAT=TREE the operator tree (see 9.21), and EXPLAIN ANALYZE / ANALYZE execute the query (9.21). EXPLAIN EXTENDED adds the filtered column (always 100.00: MIRAJ does not estimate the selectivity of a condition) and leaves the statement text as note 1003 (SHOW WARNINGS); EXPLAIN PARTITIONS adds the partitions column (partitions read of a partitioned table, NULL otherwise). Neither EXTENDED nor PARTITIONS can be combined with FORMAT (1064).

Output columns: id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra (partitions after table with EXPLAIN PARTITIONS, filtered after rows with EXPLAIN EXTENDED). key_len is NULL (indexes are in memory, with no serialized key length).

Examples (with an index idx_ville on clients(ville) and idx_cli on 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) |
+----+-------------+-------+------+-------------------+------+---------+------+------+-------------------------------+

Reading: table c is scanned in full, table o is loaded into a join buffer and matched by hashing. A join without equality between columns (ON a.solde > b.solde) gives two ALL rows with no Using join buffer: nested loop. Using filesort indicates a sort (ORDER BY), Using temporary a grouping (GROUP BY) or a deduplicated union.

When the joined table is large (here lignes, 2,000 rows, index idx_lc on client_id), it is read by index probe (§9.13); a condition on a constant also serves the driving table:

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

A correlated subquery in the select list, a derived table and a 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 |
+----+-------------+-----------+------+-------------------+------+---------+------+------+-----------------+

Limits of this version's EXPLAIN, to be aware of before drawing conclusions:

• It describes table reads, not each operator: an IN (SELECT ...) subquery does not add its own row (its condition appears as Using where); a derived table appears as a <derivedN> row of the enclosing block, followed by the reads of its content (DERIVED, block N).
• Range reads (id > 2, BETWEEN, IN (1, 2)) and ORDER BY id LIMIT n are reported as ALL (with Using filesort for the sort): only equality uses an index. Exception: a key in which a VECTOR column follows equated columns is also read through the index for IN, IS NULL, <, >, BETWEEN (range or ref, chapter 11).
• The key_len column is populated only for a read through a key that carries a VECTOR column and for a join by index probe on a vector (4 bytes per dimension + 2, size of an integer, + 1 per column that admits NULL); filtered (EXTENDED, ANALYZE) is always 100.

FORMAT=JSON. One row, one EXPLAIN column, a formatted document: {"query_block": {"select_id": 1, "nested_loop": [{"table": {...}}, ...]}}. Each table carries table_name, partitions (partitioned table), access_type, possible_keys, key, used_key_parts, ref, rows, filtered and, when a condition applies to the table, attached_condition (text of the condition checked on its rows, whether pushed into the read or evaluated above: t.a = 10 and t.b > 5, clients.nom like '%e' or clients.solde > 1). A table read by a hash join is wrapped in block-nl-join (join_type: BNLH); a single-table sort in read_sorted_file / filesort, otherwise filesort and temporary_table above the loop; a derived table carries materialized (its block), a union union_result / query_specifications (subsequent members: operation: UNION), a correlated subquery subqueries. With no 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 of another connection. SHOW EXPLAIN [FORMAT=JSON] FOR id, EXPLAIN [FORMAT=JSON] FOR CONNECTION id and SHOW ANALYZE [FORMAT=JSON] FOR id describe the statement currently running on connection id (the Id column of SHOW PROCESSLIST). MIRAJ does not keep another session's plan: the text of its statement is re-read and planned in its current database, without executing anything (same plan as the EXPLAIN of that text); SHOW ANALYZE returns the columns r_rows, filtered, r_filtered, actual values set to NULL. The statement text is left as note 1003. Errors: an identifier that is not a constant (CONNECTION_ID(), 1+1): 1204; unknown connection: 1094; idle connection, own connection, or statement with no plan (other than SELECT, UPDATE, DELETE, INSERT ... SELECT): 1933. Without the PROCESS privilege, only the connections of the same user are visible (1227). JSON column: SHOW EXPLAIN / SHOW ANALYZE.

Profiling. SHOW PROFILES and SHOW PROFILE [type, ...] [FOR QUERY n] [LIMIT n [OFFSET m]] return their columns, with no rows: profiling is never recorded (@@profiling stays 0, @@profiling_history_size is 15). information_schema.PROFILING and information_schema.OPTIMIZER_TRACE exist with their columns, empty (@@optimizer_trace is enabled=off).

What to do for a slow query: read type (ALL on a large table?), add a UNIQUE or secondary index on the column compared to a constant, check type equality between the two columns of a join, aggregate before joining, and set MAX_EXECUTION_TIME or max_join_size to bound exploratory queries.

9.21 EXPLAIN ANALYZE and ANALYZE: actual times and rows#

EXPLAIN ANALYZE <statement> and ANALYZE [FORMAT=JSON] <statement> execute the statement and return its plan with, for each operator, the number of rows actually produced and the time spent. The query rows are never sent to the client: only the plan is returned. An execution error (1242, max_statement_time exceeded, KILL) is returned as is.

Tree example (times in milliseconds: first result, then total, both inclusive of what is under the node):

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 on a join by index probe (§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 (table):

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) is the number of rows of the table (plan estimate); loops=0 is written (never executed) (source of a LIMIT 0, for example). The operators are: reads (Table scan, Index lookup, Single-row index lookup, Vector index scan, with with pushed-down filter if a predicate is pushed into the read), Filter, Project, joins (Inner|Left|Right hash join, Nested loop ... join), Group aggregate / Aggregate, Sort, Limit, Remove duplicates, Derived table, Append (union), Window aggregate, Dependent subqueries.

Parallelism. A read spread over several threads becomes a Parallel gather|aggregate|sort node (timed) whose children are the read ([parallel: degree N, M morsels]) and each stage (filter, projection, join): their rows are the sum over all threads, but without their own time (the sum of the thread times is not elapsed time). The rows are the same as in serial. Partitioned tables (Cluster edition): the partitions read appear on the read.

Writes. ANALYZE UPDATE and ANALYZE DELETE (or EXPLAIN ANALYZE UPDATE|DELETE) really execute the statement: rows are modified or deleted, with the privileges, locks, triggers and transaction of an ordinary statement (a ROLLBACK undoes them). The plan describes the read of the rows to modify; r_rows is the number of rows it examined, r_filtered the share retained. A DELETE without a condition empties the table without reading it: a single Delete all rows from t node (Extra: Deleting all rows). To modify nothing, use EXPLAIN alone. ANALYZE INSERT ..., ANALYZE REPLACE ...: error 1235. In a routine body, only ANALYZE SELECT is allowed. An MCP session needs the write level for ANALYZE UPDATE|DELETE.

The plan carries no cost estimate; expression subqueries (IN (SELECT ...), EXISTS, scalar) are not detailed, their time is included in that of the operator that evaluates them; loops is always 0 or 1. ANALYZE TABLE t [, ...] returns one analyze / status / OK row per table (Error then Operation failed for an unknown table), preceded with PERSISTENT FOR ... by the status / Engine-independent statistics collected row: MIRAJ keeps its row counts up to date continuously, nothing is recomputed. Overhead: an ordinary query is not instrumented; under ANALYZE, two clock reads per batch of 4,096 rows and per operator, and a count of the rows examined by reads with a pushed-down predicate.

9.22 Full-text search: 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');
-- ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list

The columns of MATCH must be exactly those of a FULLTEXT index of the table, in any order (chapter 7, "Full-text indexes"); otherwise 1191. The AGAINST query is a constant (literal, parameter, variable, constant expression; a column: 1210). Three modes: natural (default), IN BOOLEAN MODE (operators + - ~ < > * " ( ) @n) and WITH QUERY EXPANSION; plus fuzzy search, a MIRAJ extension (suffix word~1 / word~2 in boolean mode, IN FUZZY MODE, below). Words are compared without regard to case or accents (according to the columns' collation), from 3 to 84 characters, stop words excluded. A combining mark (an accent written separately, such as U+0301) does not split the word: e + U+0301

• b forms a three-character word, found by eb under utf8mb4_unicode_ci and by the likewise decomposed éb under utf8mb4_general_ci.

Reading through the index. A WHERE MATCH … AGAINST (…) condition (or > c, >= c) reads only the rows that carry the searched words: EXPLAIN shows type = fulltext and the index name. A GROUP BY above it (facets: SELECT famille, COUNT(*) … WHERE MATCH … GROUP BY famille) benefits from the same read. ORDER BY MATCH … LIMIT without a WHERE MATCH condition reads the whole 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 returns a DOUBLE, the relevance of the row. The session variable miraj_ft_ranking chooses its formula, taken at planning time of each statement:

miraj_ft_bm25_k1 (0 to 3, default 1.2) sets frequency saturation and miraj_ft_bm25_b (0 to 1, default 0.75) length normalization (0: none). SET, SET STATEMENT … FOR, SHOW VARIABLES and @@miraj_ft_ranking read and set them; an out-of-range value: 1231. A prepared statement takes the session's formula at each EXECUTE. In both modes, the operators >, < and ~ add or subtract 1, and the weights declared by the index (FULLTEXT ft (designation WEIGHT 3, notes), chapter 7) replace the frequency tf by Σ weight(column) × tf(column).

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

Row 5, being longer, falls behind; with the default formula, the three rows have the same score (0.0492168664932251), each citing toux once:

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

Fuzzy search (MIRAJ extension). A typo no longer empties the result:

-- boolean mode: allowed edit distance attached to the word (0 exact, 1 or 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);
-- ERROR 9052 (HY000): Fuzzy distance 3 is out of range (0 to 2)
-- fuzzy mode: natural-mode lexicon, each word widened according to its length
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 |
+----+--------------------+

• The distance counts inserted, removed and replaced characters, and the swap of two adjacent characters (praacetamol is at 1 from paracetamol), over the folded words of the index: without case or accents in a case-insensitive collation, exact in _bin.
• word~n: ~ followed by digits attached to the word and followed by a blank, an operator or the end. ~0: exact word; above 2: 9052. sirop ~1 (blank before) and sirop~1toux keep the usual reading (~ operator). The suffix applies neither to a phrase ("a b"~1), nor to a truncation (a*~1, a~1*): 1064. The operators + - > < ~ ( ) apply to the fuzzy word as to an exact word. In natural mode, ~ is part of the word.
• IN FUZZY MODE: words of 4 to 5 characters get distance 1, those of 6 and more get distance 2; quoted phrases stay exact; no WITH QUERY EXPANSION (1064).
• A word shorter than miraj_ft_fuzzy_min_length characters (default 4, from 3 to 84) is searched exact. At most miraj_ft_fuzzy_max_expansions index words (default 50, from 1 to 1,000) are kept in addition to the exact word, the closest first, then the most frequent.
• Score: the fuzzy word counts as a word whose frequency is the sum of those of the variants present in the row, multiplied by (length − d) / length (d: distance of the closest variant present); at equal frequency, the exact word ranks ahead of a distance-1 variant, itself ahead of a distance-2 variant, in both score formulas. EXPLAIN is unchanged (fulltext).

Notes: a transaction sees its own writes in MATCH, before its COMMIT; N and df count the current state of the index, uncommitted writes of other sessions included (approximate relevance during a concurrent write, rows always correct); MATCH on the columns of a view or a derived table: 1191. On a partitioned table (Cluster edition), the score is that of the whole table (N, df and lengths summed over the segments), but the read is not served by the index.

9.23 SELECT … INTO: variables and files#

SELECT list INTO @variable [, @variable ...] FROM ...
SELECT list INTO OUTFILE 'file'
    [CHARACTER SET set]
    [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'c'] [ESCAPED BY 'c']]
    [LINES [STARTING BY 'string'] [TERMINATED BY 'string']]
    FROM ...
SELECT list INTO DUMPFILE 'file' FROM ...

The INTO clause is written after the select list, after LIMIT or after the lock clause (… WHERE id = 1 INTO @n, … FOR UPDATE INTO @n), only once, in the first SELECT of the statement; it then receives the result of the entire statement (union included). It is rejected in a subquery or a table expression (1064). The statement returns no result set, but a row count.

Variables#

Each column of the result goes into the variable of the same rank; in a routine, a local variable or a parameter can replace the @ variable (and the row of a FOR loop, chapter 7).

SELECT nom, solde INTO @n, @s FROM clients WHERE id = 1;
-- OK, 1 row(s) affected
SELECT @n, @s;

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

SET @n = 'x';
SELECT nom INTO @n FROM clients WHERE id = 99;   -- warning, @n is still 'x'
SELECT nom INTO @n FROM clients;
-- ERROR 1172 (42000): Result consisted of more than one row

Files: INTO OUTFILE and INTO DUMPFILE#

The file is written by the server, in the folder of the secure_file_priv variable (with no folder set: error 1290), with the FILE privilege; an existing file is never replaced (error 1086, File '...' already exists). The number of affected rows is the number of rows written.

• INTO OUTFILE writes one line of text per row of the result. By default, fields separated by a tab, lines terminated by \n, NULL written \N, special characters escaped by \; the FIELDS and LINES options are those of LOAD DATA (chapter 8, §8.8), so that a file written with certain options reloads with the same ones.
• INTO DUMPFILE writes the value of a single row, with no separator or escaping (for a binary value, an image for example); more than one row: error 1172.

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

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 row(s) affected      (1,"Alice",100.50 then 2,"Bob",-20.00 ...)
SELECT id, nom FROM clients INTO OUTFILE 'clients.csv';
-- ERROR 1086 (HY000): File 'clients.csv' already exists
SELECT nom FROM clients WHERE id = 2 INTO DUMPFILE 'bob.bin';   -- the file contains Bob