7. 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 7.12 to 7.20 complete this foundation: full grammar, joins in practice, common table expressions, window functions, locks, optimizer hints and reading EXPLAIN. Every statement in this chapter was verified in the parser, in the executor and by execution; what does not exist is marked not supported.

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

7.1 Synopsis#

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

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

7.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;
SELECT * FROM clients;                -- all columns
SELECT clients.* , 1 AS actif FROM clients;   -- all columns of a named table, plus an expression

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;
SELECT DISTINCT ville, solde FROM clients WHERE ville IS NOT NULL ORDER BY ville, solde;

COUNT(DISTINCT expr) counts distinct values rather than rows:

SELECT COUNT(DISTINCT ville) FROM clients;

7.3 FROM and joins#

Supported join types#

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)

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

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;

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;

Not supported#

• NATURAL JOIN (in any form) is rejected by the parser (error 1235): join explicitly with ON or USING instead.
• There is no FULL [OUTER] JOIN; an equivalent can be built with a LEFT JOIN and a RIGHT JOIN combined by UNION (see §7.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;

7.4 WHERE#

WHERE filters the rows produced by FROM before any grouping. Available operators:

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

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 9 (§9.5, "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).

7.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;
SELECT ville, COUNT(*) c FROM clients GROUP BY ville HAVING c > 1 ORDER BY ville;

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;

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.

7.6 GROUP_CONCAT#

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

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.

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

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

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

7.7 ORDER BY, LIMIT 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;

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;
SELECT nom FROM clients ORDER BY solde DESC LIMIT 1 OFFSET 4;
SELECT nom FROM clients ORDER BY solde DESC LIMIT 4, 1;   -- equivalent: LIMIT 1 OFFSET 4

7.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 §7.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
SELECT nom, solde FROM clients c WHERE solde = (
    SELECT MAX(solde) FROM clients v WHERE v.ville = c.ville
);

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

-- IN with subquery
DELETE FROM commandes WHERE client_id NOT IN (SELECT id FROM clients);

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

7.9 UNION#

requête_select UNION [ALL] requête_select [UNION [ALL] requête_select ...]
[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;

SELECT client_id FROM commandes
UNION ALL
SELECT id FROM clients WHERE id > 5
ORDER BY 1 DESC LIMIT 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 can reference a column of a member qualified by its original table name (ORDER BY clients.id) as well as by its position or its alias. 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);

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

7.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 5, "DDL Language".

7.11 Features not available in this version#

Based on a check of the parser and executor code (the project's readme.txt file is out of date on several of these points):

This table reflects the state of the code at the time of writing; refer to chapter 16 ("Known limitations") for the up-to-date list of features outside DML/SELECT (transactions, privileges, networking, etc.).

7.12 Full grammar#

SELECT grammar as accepted by the parser ([ ]: optional, |: alternative, { }: at least one element):

requête ::=
    [ WITH nom [(col, ...)] AS ( select | VALUES (expr, ...), ... ) [, ...] ]
    membre [ UNION [ALL | DISTINCT] membre ... ]
    [ ORDER BY expr [ASC|DESC] [, ...] ]          -- after the last member: applies to the union
    [ LIMIT ... ]                                  -- same

membre ::=  select  |  ( select )

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 'file' ... | INTO DUMPFILE 'file' ]
    [ FROM DUAL | source [ jointure ... ] ]
    [ WHERE condition ]
    [ GROUP BY expr [ASC|DESC] [, ...] ]
    [ HAVING condition ]
    [ ORDER BY expr [ASC|DESC] [, ...] ]
    [ LIMIT { n | décalage, n | n OFFSET décalage } [ROWS EXAMINED m] ]
    [ FOR UPDATE | FOR SHARE [OF table, ...] [NOWAIT | WAIT s | SKIP LOCKED]
    | LOCK IN SHARE MODE [NOWAIT | WAIT s | SKIP LOCKED] ]

item    ::=  * | table.* | base.table.* | expr [[AS] alias]
source  ::=  table [PARTITION (p, ...)] [[AS] alias] | ( select ) [AS] alias | ( source jointure ... )
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, ...) }

Details verified in the parser:

• 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_CALC_FOUND_ROWS, SQL_NO_CACHE, SQL_CACHE and HIGH_PRIORITY are accepted and have no effect on the result; 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.
• INTO (variables, file) is allowed only in the first SELECT of the statement, never in a table expression or a subquery.

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

7.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 §7.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. Note: SELECT * returns the column once per table (two ville columns below).

SELECT * FROM clients c1 JOIN clients c2 USING (ville) LIMIT 1;

+----+-------+-------+--------+----+-------+-------+-------+
| id | nom   | ville | solde  | id | nom   | ville | solde |
+----+-------+-------+--------+----+-------+-------+-------+
|  1 | Alice | Oran  | 100.50 |  3 | Chloé | Oran  |  0.00 |
+----+-------+-------+--------+----+-------+-------+-------+

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 §7.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 §7.20). In the tests carried out, EXPLAIN of a join shows an ALL access for each table, the matching being done by the hash join: an index on the join column does not replace this mechanism. A read through a non-unique index on a constant applies only to equality; ranges (id > 2) scan the table.
• 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 (§7.15).

7.14 Set operations: UNION, INTERSECT, EXCEPT#

• UNION [ALL | DISTINCT]: supported (§7.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 and EXCEPT (as well as MINUS, and their ALL variants): not supported, the parser responds with syntax error 1064. The equivalents are written with IN / NOT IN or EXISTS / NOT EXISTS:

  -- INTERSECT: identifiers present in clients AND in commandes.client_id
  SELECT DISTINCT id FROM clients WHERE id IN (SELECT client_id FROM commandes) ORDER BY id;
  -- EXCEPT: identifiers of clients without orders (NOT EXISTS, safe with NULL)
  SELECT id FROM clients c WHERE NOT EXISTS (SELECT 1 FROM commandes o WHERE o.client_id = c.id) ORDER BY id;

  Result of the first (1, 2, 3) and of the second (4, 5, 6). Unlike the standard INTERSECT and EXCEPT, these rewrites compare with equality, and therefore do not match two NULL values.

7.15 Common table expressions (WITH)#

WITH nom [(colonne, ...)] AS ( SELECT ... | VALUES (expr, ...), ... ) [, nom2 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; without a column list, the column names are those of the first row (1, 'a'): provide a column list;
• the WITH is written at the start of a query or of a parenthesized member, and applies to the whole union;
• WITH RECURSIVE is not supported (error 1235): no recursive queries (tree traversal, number series); hierarchy traversals are done with fixed-depth self-joins or on the application side.

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

7.16 Subqueries: additional notes#

Supported forms (§7.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 > ANY (subquery), x = SOME (...), x > ALL (...) are not supported (syntax error 1064). Equivalents: x = ANY (S) is x IN (S); x > ANY (S) is EXISTS (SELECT 1 FROM ... WHERE x > column) or x > (SELECT MIN(col) ...); x > ALL (S) is x > (SELECT MAX(col) ...) (beware of NULLs: filter with col IS NOT NULL).
• Row constructor: (a, b) IN ((1, 'x'), ...) and (a, b) = (SELECT ...) are not supported (1064). Write a = 1 AND b = 'x' or a correlated EXISTS.

• 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 (§7.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.

7.17 GROUP BY, HAVING and groupings: additional notes#

• GROUP BY accepts expressions, positions (GROUP BY 1) and aliases (resolution rules in §7.5). NULL values form a single group.
• Aggregate functions supported in SELECT ... GROUP BY: COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT (§7.6), STD / STDDEV / STDDEV_POP, VARIANCE (details in chapter 8). COUNT(DISTINCT expr) and SUM(DISTINCT expr) filter on distinct values.
• 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 (§7.5); a condition on a non-aggregated column belongs rather in WHERE, which is more efficient because it filters before grouping.

• WITH ROLLUP: not supported (error 1235). ROLLUP(...) and CUBE(...) are understood as function calls and fail (error 1305); GROUPING SETS is a syntax error (1064). Produce subtotals with a UNION ALL query of one GROUP BY per level:

  SELECT ville, COUNT(*) AS n FROM clients WHERE ville IS NOT NULL GROUP BY ville
  UNION ALL
  SELECT NULL, COUNT(*) FROM clients WHERE ville IS NOT NULL
  ORDER BY ville IS NULL, ville;

  (one row per city, then a total row whose ville is NULL).

• DISTINCT applies to the entire row; SELECT DISTINCT ville, solde distinguishes pairs (§7.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 |
  +----+---------+

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

Supported functions:

Not supported (error 1235 unless stated otherwise): COUNT(DISTINCT ...), SUM(DISTINCT ...) and GROUP_CONCAT with OVER; RANGE with a numeric offset (RANGE BETWEEN 1 PRECEDING ...; RANGE UNBOUNDED ... and RANGE CURRENT ROW remain allowed, as does ROWS with an offset); any other function used with OVER; the named WINDOW w AS (...) clause and OVER w (syntax error 1064): write the full specification at each 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 or in the ORDER BY; in WHERE it is rejected (error 1111). To filter on its result, 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;

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

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

7.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) and does not accept OF with LOCK IN SHARE MODE. It applies to the tables of the block, or to those in OF. In the default model (multi-version optimistic concurrency, MV-OCC), nobody waits for a lock. A row held by another transaction (or modified since the transaction's snapshot) gives, depending on the clause:

Outside a transaction (autocommit), only the check is performed: the lock is not kept after the statement. The lock intents of an explicit transaction last until COMMIT or ROLLBACK. See chapter 9 ("Transactions and concurrency") for the locking models.

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

The index hints USE INDEX, FORCE INDEX and IGNORE INDEX (after a table name) are not supported (syntax error 1064): the engine chooses the index on its own. No join hint changes the join algorithm or order.

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.

7.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 format is limited to the tabular output: no FORMAT=JSON, no EXPLAIN ANALYZE.

Output columns: id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, Extra. key_len is always 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   | partitions | type   | possible_keys | key     | key_len | ref   | rows | Extra |
|  1 | SIMPLE      | clients | NULL       | eq_ref | PRIMARY       | PRIMARY |    NULL | const |    1 |       |

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

| id | select_type | table   | partitions | type | possible_keys     | key       | key_len | ref   | rows | Extra |
|  1 | SIMPLE      | clients | NULL       | 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 | partitions | type | possible_keys   | key  | key_len | ref  | rows | Extra                         |
|  1 | SIMPLE      | c     | NULL       | ALL  | PRIMARY,idx_ville | NULL |    NULL | NULL |    6 |                               |
|  1 | SIMPLE      | o     | NULL       | 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.

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; a correlated subquery in the select list appears as DEPENDENT SUBQUERY (one row per table read, Using where); a derived table appears through the reads of its content (DERIVED).
• Range reads (id > 2, BETWEEN) and ORDER BY id LIMIT n are reported as ALL (with Using filesort for the sort): only equality with a constant uses an index.
• With a join, a WHERE c.id = 1 condition on one of the tables does not switch its read to eq_ref in the output (ALL read, condition in Using where).
• The key_len and filtered columns are not populated (filtered does not exist).

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.