MENU


Retrieving Data SELECT and Joins

Section 3.2 read rows from one table. Real questions cross tables: which customers never ordered, what each order is worth, which category sits under which. This section answers them with the clauses of SELECT, the join types and the set operators, on a freshly loaded bookshop database. Sorting and paging are in Section 3.2.7.

3.5.1 The Logical Order in Which a SELECT Executes

You write SELECT first, but logically the server evaluates it sixth: rows come from FROM and its joins, WHERE filters them, groups form and HAVING filters them, window functions run, and only then the select list, DISTINCT, ORDER BY and LIMIT. Each step sees only what earlier steps made.Figure 3.4: The logical processing order of a SELECT statementSo a column alias, born in step 6, is invisible to WHERE in step 2:

SELECT customer_id, COUNT(*) AS n FROM orders WHERE n > 1 GROUP BY customer_id;

SELECT customer_id, COUNT(*) AS n FROM orders WHERE status <> 'cancelled'

GROUP BY customer_id HAVING n > 1 ORDER BY n DESC, customer_id;

Listing 3.39: An alias is invisible to WHERE but visible to HAVING and ORDER BY

ERROR 1054 (42S22): Unknown column 'n' in 'where clause'

+-------------+---+

| customer_id | n |

+-------------+---+

| 1 | 2 |

| 2 | 2 |

| 3 | 2 |

+-------------+---+

3 rows in set (0.000 sec)

Output of Listing 3.39 Standard SQL allows select-list aliases only in ORDER BY. MySQL also resolves them in GROUP BY and HAVING, which is convenient and not portable. In WHERE, repeat the expression instead.The order is logical, not physical: the optimizer may skip a sort by reading an index in order, stop at the LIMIT, or reorder joins, as long as the result is the same (Section 3.12).

3.5.2 Projections, Aliases, and DISTINCT

The select list is the projection: the columns and expressions the result carries. AS names an output column (backticks allow spaces); a table alias such as products AS p shortens references and is compulsory when a table appears twice (Section 3.5.8). SELECT sku title, price * 0.9 AS `member price` FROM products WHERE category_id = 3; Listing 3.40: A missing comma and a computed alias

+-----------+--------------+

| title | member price |

+-----------+--------------+

| BK-SQL-01 | 40.050 |

| BK-SQL-02 | 26.100 |

+-----------+--------------+

2 rows in set (0.000 sec)

Output of Listing 3.40 The "title" column holds SKUs: AS is optional, so the missing comma made sku title an alias without any error. Always write AS. The third decimal comes from DECIMAL(8,2) times 0.9; round money with ROUND(..., 2).DISTINCT removes duplicate rows across the whole projection and treats NULLs as equal: SELECT DISTINCT parent_id FROM categories returns two rows, NULL and 1. COUNT(DISTINCT country) counts 7 countries for 8 customers. A DISTINCT that hides join duplicates usually masks a fan-out (Section 3.5.8). Note: SQL_CALC_FOUND_ROWS and FOUND_ROWS() are deprecated (warning 1287); run a separate SELECT COUNT(*). SQL_NO_CACHE draws warning 1681, since the query cache went in MySQL 8.0.

3.5.3 Filtering with WHERE

WHERE keeps each row for which its condition is true, combining comparisons, IN, BETWEEN, LIKE and IS NULL (Section 3.7) with AND, OR and NOT. Both queries below look right:

SELECT sku FROM products WHERE category_id = 2 OR category_id = 3 AND price > 40;

SELECT id FROM reviews WHERE product_id IN (1, 6) AND NOT (body LIKE '%chapter%');

Listing 3.41: Operator precedence, and NOT applied to a NULL

+-----------+

| sku |

+-----------+

| BK-PHP-01 |

| BK-LAR-01 |

| BK-LNX-01 |

| BK-SQL-01 |

+-----------+

4 rows in set (0.000 sec)

+----+

| id |

+----+

| 1 |

+----+

1 row in set (0.001 sec)

Output of Listing 3.41 AND binds tighter than OR, so the 39.90 PHP book got in; with (category_id = 2 OR category_id = 3) parenthesized, three rows return.NOT of unknown is unknown. Review 5's body is NULL, so the LIKE and its NOT are NULL, and WHERE drops it. Write body IS NULL OR body NOT LIKE '%chapter%' to get both reviews.BETWEEN is inclusive, and a bare date means midnight, so BETWEEN '2026-05-01' AND '2026-05-18' misses order 5, placed at 22:10 on the 18th. The half-open ordered_at >= '2026-05-01' AND ordered_at < '2026-05-19' finds orders 4 and 5, and, unlike YEAR(ordered_at) = 2026, keeps the column bare for an index (Section 3.12.9).

3.5.4 GROUP BY, HAVING, and ROLLUP

GROUP BY collapses rows sharing its expressions into one row per group for the aggregates (Section 3.8), and HAVING filters the groups. Under the default ONLY_FULL_GROUP_BY mode every select-list column must be grouped, aggregated, or functionally dependent on the grouped columns:

SELECT customer_id, status, COUNT(*) FROM orders GROUP BY customer_id;

SELECT IF(GROUPING(category_id), 'all', category_id) AS category,

COUNT(*) AS titles, SUM(stock) AS units

FROM products WHERE category_id IN (2, 3)

GROUP BY ROLLUP (category_id);

Listing 3.42: A column that is not grouped, then subtotals with ROLLUP

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains

nonaggregated column 'shop.orders.status' which is not functionally dependent on columns in

GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

+----------+--------+-------+

| category | titles | units |

+----------+--------+-------+

| 2 | 3 | 52 |

| 3 | 2 | 12 |

| all | 5 | 64 |

+----------+--------+-------+

3 rows in set (0.000 sec)

Output of Listing 3.42 A customer's group has several statuses, and MySQL refuses to pick one at random as versions before 5.7.5 did: group by it, aggregate it, or write ANY_VALUE(status). Grouping by a primary key makes the rest of the row dependent, so GROUP BY c.id may select c.name.ROLLUP adds super-aggregate rows: a grand total here, plus per-a subtotals with ROLLUP (a, b). GROUPING() returns 1 on those rows, whose grouped column is NULL. The GROUP BY ROLLUP (...) spelling arrived in MySQL 9.1; WITH ROLLUP also runs on 8.4 LTS. And since 8.0, GROUP BY no longer sorts, so add ORDER BY.

3.5.5 Cross Joins and the Cartesian Product

Logically, every join starts from the Cartesian product: each left row paired with each right row. A cross join returns it unfiltered, so 8 customers and 8 products make 64 rows. That suits grids of every combination: recommendations, report skeletons, test data.

SELECT cu.name, p.sku FROM customers AS cu CROSS JOIN products AS p

WHERE cu.country = 'MY' AND p.category_id = 3;

SELECT c.name, o.id

FROM customers AS c, orders AS o JOIN order_items AS i

ON i.order_id = o.id AND o.customer_id = c.id;

Listing 3.43: A deliberate product, and the comma-precedence trap

+--------------+-----------+

| name | sku |

+--------------+-----------+

| Chen Wei | BK-SQL-01 |

| Chen Wei | BK-SQL-02 |

| Farah Rahman | BK-SQL-01 |

| Farah Rahman | BK-SQL-02 |

+--------------+-----------+

4 rows in set (0.000 sec)

ERROR 1054 (42S22): Unknown column 'c.id' in 'on clause'

Output of Listing 3.43 Two tables of a million rows each cross-join into a trillion rows, so a forgotten join condition can stall a server. In MySQL, JOIN, CROSS JOIN and INNER JOIN are syntactic equivalents, a product without ON and an inner join with it; standard SQL keeps them apart, so write CROSS JOIN only for the product. Don't mix commas with JOIN: JOIN binds tighter, MySQL reads c, (o JOIN i ON ...), and that ON cannot see c.

3.5.6 INNER JOIN and the ON Clause

An inner join keeps only the pairs that satisfy ON, which usually follows a foreign key. This query prices each paid order and names its customer:

SELECT o.id, c.name, SUM(i.quantity * i.unit_price) AS total

FROM orders AS o

INNER JOIN customers AS c ON c.id = o.customer_id

INNER JOIN order_items AS i ON i.order_id = o.id

WHERE o.status = 'paid'

GROUP BY o.id

ORDER BY total DESC;

Listing 3.44: Two inner joins along foreign keys, then aggregation

+----+--------------+-------+

| id | name | total |

+----+--------------+-------+

| 9 | Ben Carter | 53.99 |

| 7 | Farah Rahman | 50.00 |

| 3 | Chen Wei | 49.00 |

+----+--------------+-------+

3 rows in set (0.002 sec)

Output of Listing 3.44 c.name needs no grouping: o.id fixes o.customer_id, and the ON equality fixes c.id. An order without items would vanish, which is what makes the join inner.Keep join logic in ON and filters in WHERE. ON takes any expression, such as ON p.price BETWEEN b.low AND b.high. USING (col) abbreviates ON a.col = b.col. Avoid NATURAL JOIN, which joins on every shared column name and changes silently when a column is added. Under the hood: MySQL runs joins as nested loops that probe the inner table through an index, so index every foreign key (InnoDB adds one if missing). Without a usable index it runs a hash join, as it has since MySQL 8.0.18 (Section 3.12.6).

3.5.7 LEFT and RIGHT OUTER JOIN

A left outer join keeps every row of the left table; where no right row matches, the right-side columns are NULL. RIGHT JOIN is the mirror image, and MySQL rewrites it internally as a left join, so prefer LEFT JOIN and put the table you must keep first. OUTER is optional.Figure 3.5: Which rows each join keeps, for A(a) = 1-4 and B(b) = 3-6The classic outer-join bug is a filter in the wrong clause. Both queries look for the Malaysian customers' shipped orders:

SELECT c.name, o.id FROM customers AS c

LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'shipped'

WHERE c.country = 'MY';

SELECT c.name, o.id FROM customers AS c

LEFT JOIN orders AS o ON o.customer_id = c.id

WHERE c.country = 'MY' AND o.status = 'shipped';

Listing 3.45: A right-table filter in ON versus in WHERE

+--------------+------+

| name | id |

+--------------+------+

| Chen Wei | NULL |

| Farah Rahman | NULL |

+--------------+------+

2 rows in set (0.001 sec)

Empty set (0.000 sec)

Output of Listing 3.45 In ON, the condition decides what matches, and unmatched customers stay. In WHERE, it runs after the padding, rejects the NULL status, and quietly turns the outer join into an inner one.Counting orders per customer over such a join, COUNT(*) gives Gus and Hana, who never ordered, 1 each for the padded row; COUNT(o.id) gives 0, and SUM needs COALESCE(SUM(...), 0).The padding also yields the anti-join: WHERE o.id IS NULL after the LEFT JOIN returns exactly Gus and Hana, as does NOT EXISTS (Section 3.6.3), and EXPLAIN shows a "Nested loop antijoin" for both. Test a NOT NULL column such as the key.

3.5.8 Self-Joins and Joining More Than Two Tables

A self-join joins a table to itself under two aliases. categories.parent_id points at another categories row, so a product's department takes two joins, one hop per foreign key; the second is a LEFT JOIN because top-level categories have no parent:

SELECT p.sku, cat.name AS category, top.name AS department

FROM products AS p

JOIN categories AS cat ON cat.id = p.category_id

LEFT JOIN categories AS top ON top.id = cat.parent_id

WHERE p.id IN (2, 8);

SELECT name FROM categories WHERE id NOT IN (SELECT parent_id FROM categories);

Listing 3.46: A three-way join with a self-join, and a NOT IN that returns nothing

+-----------+-------------+------------+

| sku | category | department |

+-----------+-------------+------------+

| BK-SQL-01 | Databases | Books |

| AC-STK-01 | Accessories | NULL |

+-----------+-------------+------------+

2 rows in set (0.000 sec)

Empty set (0.001 sec)

Output of Listing 3.46 Make the last join inner and the sticker pack disappears. The second query wants the leaf categories and finds none, although four exist. The subquery returns NULL, 1, 1, 1, NULL, and x NOT IN (..., NULL) is never true. Never use NOT IN on a nullable column; NOT EXISTS (SELECT 1 FROM categories AS k WHERE k.parent_id = c.id) returns all four.Beware fan-out when joining two one-to-many children of one parent: product 1's two order lines and two reviews join into four rows, and SUM(quantity) says 6 instead of 3. Aggregate each child in a derived table first (Section 3.6.5). For trees of any depth, use a recursive CTE (Section 3.6.8).

3.5.9 Emulating a FULL OUTER JOIN

A full outer join keeps unmatched rows from both sides, which is what reconciling two lists needs. MySQL 9.7 has none. Here a supplier's cost feed lacks one Databases title and lists one the shop does not stock:

CREATE TABLE supplier_feed (sku VARCHAR(20) PRIMARY KEY, cost DECIMAL(8,2) NOT NULL);

INSERT INTO supplier_feed VALUES ('BK-SQL-01', 26.00), ('BK-PG-01', 21.50);

SELECT * FROM products FULL JOIN supplier_feed f ON products.sku = f.sku;

SELECT p.sku AS ours, f.sku AS theirs, p.price, f.cost

FROM products AS p LEFT JOIN supplier_feed AS f ON f.sku = p.sku

WHERE p.category_id = 3

UNION ALL

SELECT p.sku, f.sku, p.price, f.cost

FROM products AS p RIGHT JOIN supplier_feed AS f ON f.sku = p.sku

WHERE p.id IS NULL;

Listing 3.47: A failed FULL JOIN, then LEFT JOIN plus an anti-RIGHT JOIN

Query OK, 0 rows affected (0.040 sec)

Query OK, 2 rows affected (0.012 sec)

Records: 2 Duplicates: 0 Warnings: 0

ERROR 1054 (42S22): Unknown column 'products.sku' in 'on clause'

Warning (Code 4119): Using FULL as unquoted identifier is deprecated, please use quotes or

rename the identifier.

Error (Code 1054): Unknown column 'products.sku' in 'on clause'

+-----------+-----------+-------+-------+

| ours | theirs | price | cost |

+-----------+-----------+-------+-------+

| BK-SQL-01 | BK-SQL-01 | 44.50 | 26.00 |

| BK-SQL-02 | NULL | 29.00 | NULL |

| NULL | BK-PG-01 | NULL | 21.50 |

+-----------+-----------+-------+-------+

3 rows in set (0.001 sec)

Output of Listing 3.47 FULL is not reserved, so MySQL read products FULL as a table alias and JOIN as an inner join, then lost products.sku under the new name; FULL OUTER JOIN is a plain syntax error. The emulation takes every left row with its match or NULLs, then appends only the right rows with no match (WHERE p.id IS NULL). Prefer it to the common LEFT JOIN ... UNION ... RIGHT JOIN: plain UNION must deduplicate everything, and it merges rows that are genuinely identical.

3.5.10 UNION, INTERSECT, and EXCEPT

Set operators combine whole results: UNION returns rows from either query, INTERSECT rows in both, EXCEPT rows of the first absent from the second. INTERSECT and EXCEPT arrived in MySQL 8.0.31. Operands may be SELECT, TABLE or VALUES statements with equal column counts.

SELECT product_id FROM order_items EXCEPT SELECT product_id FROM reviews;

SELECT 'order' AS kind, id, ordered_at AS happened FROM orders WHERE customer_id = 1

UNION ALL

SELECT 'review', id, created_at FROM reviews WHERE customer_id = 1

ORDER BY happened;

Listing 3.48: Products sold but never reviewed, and one customer's activity feed

+------------+

| product_id |

+------------+

| 5 |

| 8 |

+------------+

2 rows in set (0.000 sec)

+--------+----+---------------------+

| kind | id | happened |

+--------+----+---------------------+

| order | 1 | 2026-03-02 10:15:00 |

| review | 1 | 2026-03-20 18:00:00 |

| order | 4 | 2026-05-05 12:00:00 |

| review | 5 | 2026-05-20 07:00:00 |

+--------+----+---------------------+

4 rows in set (0.001 sec)

Output of Listing 3.48 All three remove duplicates unless you add ALL; the feed's halves cannot overlap, so UNION ALL skips that work. INTERSECT ALL keeps each row as often as its smaller count. Column names come from the first operand, and a trailing ORDER BY or LIMIT applies to the whole result (parenthesize an operand to limit it alone). INTERSECT binds tighter, so a EXCEPT b INTERSECT c means a EXCEPT (b INTERSECT c). NULLs compare as equal, as in DISTINCT. Oracle's MINUS is a syntax error.

3.5.11 Table Value Constructors with VALUES ROW()

Since MySQL 8.0.19, VALUES is a statement of its own, a table value constructor whose rows are written ROW(...). Alone, its columns are named column_0, column_1 and so on; as a derived table, you name them in a column list, giving you a lookup table without creating one:

SELECT s.status, s.label, COUNT(o.id) AS orders

FROM (VALUES ROW('paid', 'Paid'), ROW('shipped', 'On its way'),

ROW('refunded', 'Refunded')) AS s (status, label)

LEFT JOIN orders AS o ON o.status = s.status

GROUP BY s.status, s.label;

VALUES ROW(1, 2), ROW(3);

Listing 3.49: VALUES as a labeled lookup table driving a LEFT JOIN, and ragged rows

+----------+------------+--------+

| status | label | orders |

+----------+------------+--------+

| paid | Paid | 3 |

| shipped | On its way | 4 |

| refunded | Refunded | 0 |

+----------+------------+--------+

3 rows in set (0.001 sec)

ERROR 1136 (21S01): Column count doesn't match value count at row 2

Output of Listing 3.49 refunded shows a zero because the constructed table drives the join. Likewise, (VALUES ROW(1), ROW(5), ROW(42)) AS v (id) LEFT JOIN products with WHERE p.id IS NULL finds the pasted id 42 that has no product. Rows need equal lengths, and ROW is compulsory: bare VALUES (1, 2) is a syntax error. The sibling TABLE categories abbreviates SELECT * FROM categories and accepts ORDER BY and LIMIT.

3.5.12 CASE, IF(), and Pivoting Rows into Columns

CASE is SQL's conditional expression. The searched form, CASE WHEN price >= 40 THEN 'premium' WHEN price >= 20 THEN 'standard' ELSE 'budget' END, returns the first true branch, so order branches from the most restrictive. The simple form, CASE category_id WHEN 5 THEN 'merch' END, compares one value with a list. A miss without ELSE gives NULL, and IF(cond, a, b) is MySQL's two-way shorthand. A condition inside an aggregate pivots a column's values into columns:

SELECT c.name,

COUNT(CASE WHEN o.status = 'shipped' THEN 1 END) AS shipped,

SUM(o.status = 'paid') AS paid,

IF(COUNT(*) > 1, 'repeat', 'new') AS buyer

FROM customers AS c JOIN orders AS o ON o.customer_id = c.id

WHERE c.id BETWEEN 3 AND 5

GROUP BY c.id;

Listing 3.50: Order statuses pivoted into columns with CASE, a comparison, and IF()

+-------------+---------+------+--------+

| name | shipped | paid | buyer |

+-------------+---------+------+--------+

| Chen Wei | 0 | 1 | repeat |

| Dara Okafor | 0 | 0 | new |

| Elif Yilmaz | 1 | 0 | new |

+-------------+---------+------+--------+

3 rows in set (0.001 sec)

Output of Listing 3.50 COUNT(CASE WHEN ... THEN 1 END) is portable: non-matching rows become NULL, which COUNT skips. SUM(o.status = 'paid') is a MySQL shortcut, since a comparison yields 1 or 0. CASE x WHEN NULL never matches; write CASE WHEN x IS NULL. Pivot columns are fixed in the query text, so for data-driven columns build the SQL in PHP (Chapter 4) from whitelisted values.