MySQL SELECT: Syntax, Filters, Sorting, and Limits

MySQL SELECT retrieves columns or calculated expressions from one or more tables. Use WHERE to filter rows, ORDER BY to request a specific order, and LIMIT to restrict the number of returned rows. This is a simplified form of the clauses used most often:

To inspect how MySQL plans a query and compare estimates with actual execution, see MySQL EXPLAIN and EXPLAIN ANALYZE.

SELECT [DISTINCT] select_expr [, select_expr ...]
[FROM table_references]
[WHERE row_condition]
[GROUP BY group_expression [, ...]]
[HAVING group_condition]
[ORDER BY sort_expression [, ...]]
[LIMIT row_count [OFFSET offset]];

The FROM clause is optional when selecting a calculated expression such as SELECT 1 + 2;. When clauses are present, place them in the order shown. See the full MySQL 8.4 SELECT reference.

Select specific columns

These examples use the actor table from the Sakila sample database:

SELECT actor_id, first_name, last_name
FROM sakila.actor;

List only the columns needed by the query. SELECT * is convenient for exploring a table, but it returns every visible column and makes application code depend on the table’s full shape.

Filter rows with WHERE

WHERE keeps rows that match a condition. It runs before grouping, so use it for row-level conditions:

SELECT actor_id, first_name, last_name
FROM sakila.actor
WHERE last_name = 'Davis';

For operators, NULL, and combined conditions, see MySQL WHERE.

Return distinct values

Use DISTINCT when the result should contain each value combination only once:

SELECT DISTINCT last_name
FROM sakila.actor
ORDER BY last_name;

DISTINCT applies to the selected column combination. See the MySQL SELECT reference.

Sort and limit the result

Without ORDER BY, MySQL does not guarantee a particular row order. Add a unique tie-breaker if equal sort values need a stable order:

SELECT actor_id, first_name, last_name
FROM sakila.actor
ORDER BY last_name, actor_id
LIMIT 10;

Use ASC for ascending order or DESC for descending order. LIMIT 10 OFFSET 20 skips the first 20 rows and returns the next 10; the requested order should include a unique key for repeatable pagination. For sorting and row limits, see MySQL ORDER BY and MySQL LIMIT.

Select a calculated expression

You can give a result expression an alias and use that alias in ORDER BY:

SELECT
  actor_id,
  CONCAT(first_name, ' ', last_name) AS full_name
FROM sakila.actor
ORDER BY full_name, actor_id;

An alias is not available in the same query’s WHERE clause; filter using the underlying columns or a subquery. See MySQL’s column alias rules.

Aggregate rows

Use aggregate functions with GROUP BY to return summaries, then use HAVING to filter the grouped results. See MySQL GROUP BY and MySQL HAVING. In MySQL’s default ONLY_FULL_GROUP_BY mode, selected nonaggregate columns must be grouped or functionally dependent on the grouping columns; see MySQL Error 1055.

Select without a table

FROM can be omitted when the result is computed from expressions:

SELECT 1 + 2 AS total, NOW() AS server_time;

MySQL also accepts SELECT 1 + 2 FROM DUAL for compatibility, but DUAL is not required. See the MySQL SELECT reference.

For reusable query steps, see MySQL CTEs. To run the same statement with different parameter values, use MySQL prepared statements.