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.

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]

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 recognized by the parser but rejected at execution (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.8):

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

7.4 WHERE#

WHERE 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, age 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.).