MySQL SELECT Patterns

SELECT can read rows, combine sources, filter, aggregate, order, and limit a result. A useful outline of its syntax is:

SELECT [DISTINCT] select_expr [, ...]
FROM table_references [PARTITION (partition_names)]
WHERE condition
GROUP BY group_expr [, ...]
HAVING condition
WINDOW window_name AS (window_spec) [, ...]
ORDER BY order_expr [ASC | DESC] [, ...]
LIMIT {row_count | offset, row_count | row_count OFFSET offset};

Logical query processing provides a model for reasoning about a result:

1. FROM, including joins, constructs candidate rows.
2. WHERE filters individual rows.
3. GROUP BY forms groups and aggregate functions compute group values.
4. HAVING filters groups; named windows are then available for window functions.
5. The select list produces output columns, DISTINCT removes duplicate output rows, ORDER BY orders them, and LIMIT restricts the returned rows.

The optimizer is free to transform and reorder physical operations when the result preserves these semantics. For example, it can push a predicate into an index access or choose a different join order. Logical processing is therefore not an execution plan; use EXPLAIN or EXPLAIN ANALYZE to inspect the plan.

Displaying Wide Results #

The MySQL command-line client can print one row vertically, which is useful when a result has many columns:

SELECT * FROM user\G

\G is a client statement terminator, not SQL syntax. It changes only the display format.

Pattern Matching #

LIKE and NOT LIKE #

Use LIKE or NOT LIKE to compare a string against a pattern:

SELECT ...
FROM ...
WHERE column_name [NOT] LIKE pattern;

LIKE has two pattern metacharacters:

• % matches zero or more characters.
• _ matches exactly one character.

The expression’s collation determines comparisons such as case and accent sensitivity. An indexed column can support a constant prefix pattern such as 'abc%'; a leading %, as in '%abc', cannot use an ordinary B-tree index for that prefix search.

Regular Expressions #

MySQL 8.0 uses ICU regular expressions through the REGEXP (or RLIKE) operator and functions such as REGEXP_LIKE(). For example:

SELECT *
FROM pet
WHERE owner REGEXP '^B';

Whether ^B matches Benny, benny, or both depends on the collation and the regex options. Make a case-sensitive requirement explicit rather than relying on a server default:

SELECT * FROM pet
WHERE REGEXP_LIKE(owner, '^b', 'c');

SELECT * FROM pet
WHERE REGEXP_LIKE(owner, '^b' COLLATE utf8mb4_0900_as_cs);

The 'c' match-type flag requests case-sensitive matching. The collation example is specific to MySQL 8.0 collations; it is not portable to MariaDB. MariaDB’s REGEXP implementation and supported collations differ by version, although REGEXP BINARY '^B' is a common way to request bytewise, case-sensitive matching there. Consult the manual for the deployed server version before sharing regex SQL between MySQL and MariaDB.

Aggregate Functions and Grouping #

Without GROUP BY, an aggregate query has one implicit group containing all rows that survive WHERE. With GROUP BY, aggregate functions operate once per group. MySQL documents the available functions, including COUNT, SUM, AVG, MIN, and MAX.

SELECT a, b, MAX(c) AS max_c, MIN(c) AS min_c
FROM t
GROUP BY a, b;

For example, rows with the same (a, b) pair form one group, and the query returns its highest and lowest c values. MAX(c) does not identify the complete source row that contained that value; retrieve such a row with a well-defined join, window function, or subquery when needed.

GROUP BY Rules #

Enable ONLY_FULL_GROUP_BY, which is the default in current MySQL releases. It rejects a nonaggregate select expression unless the expression is listed in GROUP BY or MySQL can prove that it is functionally dependent on the grouped columns. A primary key supplies a common functional dependency:

-- Code is the primary key of country, so it determines Name.
SELECT co.Name, COUNT(*) AS language_count
FROM countrylanguage AS cl
JOIN country AS co ON co.Code = cl.CountryCode
GROUP BY co.Code;

Grouping by co.Code forms one group for each country, so co.Name has one value per group. A query accepted only because ONLY_FULL_GROUP_BY is disabled can select an arbitrary value from each group. Do not disable the mode to preserve such ambiguity. Use an aggregate that expresses the desired choice, add the column to GROUP BY, or use ANY_VALUE() only when every value is genuinely interchangeable.

The HAVING Clause #

WHERE filters rows before grouping, whereas HAVING filters groups after aggregation:

SELECT CountryCode, SUM(Population) AS population
FROM city
GROUP BY CountryCode
HAVING SUM(Population) > 70000000;

MySQL also permits a select-list alias in HAVING:

SELECT CountryCode, SUM(Population) AS population
FROM city
GROUP BY CountryCode
HAVING population > 70000000;

Aggregate expressions may appear in HAVING; they do not have to appear in the select list. For portability and clarity, repeat the aggregate expression or use a derived table when an alias is unavailable in another database. Aggregates cannot appear in WHERE, because grouping has not occurred at that logical stage.

MySQL permits HAVING without GROUP BY. Without aggregates, it can act much like a late row filter:

SELECT Name, Population
FROM city
HAVING Population > 9600000;

Use WHERE for that condition because it states the intended stage and lets the optimizer consider row filtering directly. More importantly, do not try to compare each row to an aggregate in HAVING without grouping:

-- Not a per-row maximum comparison.
SELECT *
FROM city
HAVING Population = MAX(Population);

The query has one implicit group, and nonaggregated columns are ambiguous or rejected under ONLY_FULL_GROUP_BY. Express the per-row comparison with a scalar subquery:

SELECT *
FROM city
WHERE Population = (SELECT MAX(Population) FROM city);

SELECT *
FROM city
WHERE Population > (SELECT AVG(Population) FROM city);

Joins #

JOIN ... ON supplies a Boolean match condition. JOIN ... USING (column) names one or more columns present in both inputs and is equivalent to equality conditions for those names, with one coalesced join column in SELECT *:

SELECT *
FROM t0
JOIN t1 ON t0.b = t1.b;

SELECT *
FROM t0
JOIN t1 USING (b);

Join forms have distinct row-preservation rules:

• INNER JOIN returns matching row pairs. CROSS JOIN returns the Cartesian product; in MySQL, JOIN without ON or USING also has that effect.
• LEFT [OUTER] JOIN retains every left row. A left row with no right match produces one result with NULL values for right-side columns.
• RIGHT [OUTER] JOIN is the analogous operation for the right input. Reverse the inputs and use LEFT JOIN when a consistent left-to-right style helps.
• NATURAL [LEFT|RIGHT|INNER] JOIN implicitly uses every identically named column. Avoid it because adding an unrelated same-named column changes the query’s meaning.

Put a right-side condition in ON when unmatched left rows must remain:

SELECT c.id, o.id
FROM customer AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
 AND o.status = 'active';

Moving o.status = 'active' into WHERE discards null-extended rows and therefore changes the LEFT JOIN into an effective inner join for that condition.

MySQL does not support FULL OUTER JOIN. A full join returns matched pairs, unmatched left rows, and unmatched right rows. When that result is necessary, combine a left join with right-only rows using UNION ALL, while excluding already matched rows from the second branch:

SELECT l.*, r.*
FROM left_table AS l
LEFT JOIN right_table AS r ON r.key_col = l.key_col
UNION ALL
SELECT l.*, r.*
FROM right_table AS r
LEFT JOIN left_table AS l ON l.key_col = r.key_col
WHERE l.key_col IS NULL;

Choose a nonnullable key for the final anti-match test, and account for the desired handling of nullable join keys.

JOIN Versus Comma Syntax #

For an inner join, these forms have the same relational meaning:

SELECT ...
FROM t0, t1
WHERE t0.key_col = t1.key_col;

SELECT ...
FROM t0
INNER JOIN t1 ON t0.key_col = t1.key_col;

The optimizer can produce the same plan for both. Prefer explicit JOIN syntax because it separates table relationships from row filters and avoids mistakes when outer joins are added.

LIMIT and Pagination #

Pagination Syntax #

LIMIT supports three forms:

SELECT ... FROM ... LIMIT n;
SELECT ... FROM ... LIMIT offset, n;
SELECT ... FROM ... LIMIT n OFFSET offset;

The first returns at most n rows. The latter two skip offset result rows and return at most n; the OFFSET spelling makes the two arguments clearer. LIMIT does not define an order, so pagination requires ORDER BY.

Tied ORDER BY Values #

Order by enough columns to form a total order. If several rows have the same sort value, MySQL may return those tied rows in any relative order, and the order can change when a LIMIT changes the chosen plan:

SELECT *
FROM ratings
ORDER BY category, id
LIMIT 5;

Adding the unique id tiebreaker makes page boundaries deterministic.

LIMIT ... OFFSET Cost #

Large offsets cost work because the server must find and discard the preceding result rows; an index can reduce scanning but cannot make OFFSET seek to an arbitrary result position. Prefer keyset pagination for sequential access:

SELECT id, created_at
FROM event
WHERE tenant_id = 7
  AND (created_at, id) < ('2026-08-01 00:00:00', 9001)
ORDER BY created_at DESC, id DESC
LIMIT 100;

An index compatible with the equality filter and ordering, such as (tenant_id, created_at, id), lets MySQL continue from the last observed key. The suitable index and comparison direction depend on the complete query.

Keyset pagination is stable only relative to the rows visible to each query. At READ COMMITTED, inserts and updates between page requests can move rows across the cursor boundary. Use an immutable ordering key, carry the full unique cursor tuple, and select an explicit snapshot or product-level consistency contract when a multi-page export must represent one database state.

Considerations for ORDER BY #

ORDER BY accepts multiple expressions; earlier expressions have higher priority. A compatible index can provide the required order, whereas an incompatible order can require an explicit sort, reported by EXPLAIN as Using filesort. filesort is MySQL terminology for a sorting operation; it does not imply that a disk file is always used.

Temporary Tables #

A temporary table stores an intermediate result for the current connection:

CREATE TEMPORARY TABLE active_customer AS
SELECT customer_id, SUM(total) AS total_spent
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

It is distinct from a user-defined variable, which holds a scalar value. MySQL drops a temporary table when its session ends, or earlier with DROP TEMPORARY TABLE. A view declared with ALGORITHM = TEMPTABLE can cause materialization during a query, but it does not create a session-visible temporary table.

Name and drop temporary tables within the owning workflow when pooled connections are used, because connection-local objects survive transaction commit and can remain present for the next borrower of that physical session.

Locking Reads #

In an explicit transaction, locking reads coordinate a subsequent update with concurrent transactions:

SELECT ...
FROM ...
WHERE ...
FOR SHARE;

SELECT ...
FROM ...
WHERE ...
FOR UPDATE;

For InnoDB, FOR SHARE acquires shared locks and FOR UPDATE acquires exclusive locks on rows that InnoDB examines and locks, subject to the access path, isolation level, and predicate. InnoDB can also use gap or next-key locks, so locks are not limited to returned records. These clauses release their locks when the transaction commits or rolls back; with autocommit enabled and no explicit transaction, they generally provide no interval for a later statement to rely on the lock. Locking behavior, including NOWAIT and SKIP LOCKED support, is version-specific; consult the InnoDB locking manual for the deployed MySQL release.

Subqueries #

A subquery is a SELECT within another statement. Its context determines whether it must return one scalar value, one row, one column, or a table. The maximum-population query above is a scalar subquery. A subquery in FROM is a derived table, whereas IN (subquery) consumes a single-column result.

Correlated subqueries refer to a row from their outer query. They are logically evaluated in that row context, although the optimizer can transform them into joins, semijoins, or other equivalent plans.

WITH Clauses #

A common table expression (CTE) names a subquery for one following statement:

WITH foo AS (
  SELECT name
  FROM x
  WHERE type = 13
)
SELECT bar.*
FROM bar
JOIN foo ON foo.name = bar.name;

Defining foo does not make its columns implicitly visible to bar; the outer query must reference the CTE in FROM or JOIN. MySQL 8.0 supports nonrecursive and recursive CTEs. Depending on the query and MySQL version, the optimizer can merge a CTE into the outer query or materialize it, so a CTE is a structuring feature rather than a guarantee of a particular execution method.

References #

• SELECT Statement
• Aggregate Functions
• InnoDB Locking
• EXPLAIN ANALYZE