SELECT
Syntax
SELECT [ALL | DISTINCT | DISTINCTROW] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [ INTO OUTFILE 'file_name' [export_options] | INTO DUMPFILE 'file_name' | INTO var_name [, var_name] ] [ FROM table_references [{USE|FORCE|IGNORE} INDEX [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list]) ] [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [FOR UPDATE | LOCK IN SHARE MODE] ]
Contents
Description
SELECT
is used to retrieve rows selected from one or more
tables, and can include UNION statements and subqueries.
- Each select_expr expression indicates a column or data that you want to retrieve. You must have at least one select expression. See Select Expressions below.
- The
FROM
clause indicates the table or tables from which to retrieve rows. Use either a single table name or aJOIN
expression. SeeJOIN
for details. If no table is involved, FROM DUAL can be specified.
- Each table can also be specified as
db_name
.tabl_name
. Each column can also be specified astbl_name
.col_name
or evendb_name
.tbl_name
.col_name
. This allows to write queries which involve multiple databases. See Identifier Qualifiers for syntax details.
- The
WHERE
clause, if given, indicates the condition or conditions that rows must satisfy to be selected.where_condition
is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause.- In the
WHERE
clause, you can use any of the functions and operators that MariaDB supports, except for aggregate (summary) functions. See Functions and Operators and Functions and Modifiers for use with GROUP BY (aggregate).
- In the
- Use the
ORDER BY
clause to order the results.
- Use the
LIMIT
clause allows you to restrict the results to only a certain number of rows, optionally with an offset.
- Use the
GROUP BY
andHAVING
clauses to group rows together when they have columns or computed values in common.
SELECT can also be used to retrieve rows computed without reference to any table.
Optimizer hints
Some options are available to affect the execution plan.
HIGH_PRIORITY
gives the statement an higher priority. If the table is locked, high priority SELECT
s will be executed as soon as the lock is released, even if other statements are queued. HIGH_PRIORITY
applies only if the storage engine only supports table-level locking (MyISAM
, MEMORY
, MERGE
). See HIGH_PRIORITY and LOW_PRIORITY clauses for details.
If the query_cache_type system variable is set to 2 or DEMAND
, and the current statement is cacheable, SQL_CACHE
causes the query to be cached and SQL_NO_CACHE
causes the query not to be cached. For UNION
s, SQL_CACHE
or SQL_NO_CACHE
should be specified for the first query. See also The Query Cache.
SQL_BUFFER_RESULT
forces the optimizer to use a temporary table to process the result. This is useful to free locks as soon as possible.
SQL_SMALL_RESULT
and SQL_BIG_RESULT
tell the optimizer whether the result is very big or not. Usually, GROUP BY
and DISTINCT
operations are performed using a temporary table. Only if the result is very big, using a temporary table is not convenient. The optimizer automatically knows if the result is too big, but you can force the optimizer to use a temporary table with SQL_SMALL_RESULT
, or avoid the temporary table using SQL_BIG_RESULT
.
STRAIGHT_JOIN
applies to the JOIN queries, and tells the optimizer that the tables must be read in the order they appear in the SELECT
. For const
and system
table this options is sometimes ignored.
SQL_CALC_FOUND_ROWS
is only applied when using the LIMIT
clause. If this option is used, MariaDB will count how many rows would match the query, without the LIMIT
clause. That number can be retrieved in the next query, using FOUND_ROWS().
USE INDEX
, FORCE INDEX
and IGNORE INDEX
constrain the query planning to a specific index.
For further information about some of these options, see How to force query plans.
Select Expressions
A SELECT
statement must contain one or more select expressions, separated
by commas. Each select expression can be one of the following:
- The name of a column.
- Any expression using functions and operators.
*
to select all columns from all tables in theFROM
clause.tbl_name.*
to select all columns from just the table tbl_name.
When specifying a column, you can either use just the column name or qualify the column
name with the name of the table using tbl_name.col_name
. The qualified form is
useful if you are joining multiple tables in the FROM
clause. If you do not qualify the
column names when selecting from multiple tables, MariaDB will try to find the column in
each table. It is an error if that column name exists in multiple tables.
You can quote column names using backticks. If you are qualifying column names
with table names, quote each part separately as `tbl_name`.`col_name`
.
If you use any grouping functions
in any of the select expressions, all rows in your results will be implicitly grouped, as if
you had used GROUP BY NULL
.
DISTINCT
A query may produce some identical rows. By default, all rows are retrieved, even when their values are the same. To explicitly specify that you want to retrieve identical rows, use the ALL
option. If you want duplicates to be removed from the resultset, use the DISTINCT
option. DISTINCTROW
is a synonym for DISTINCT
. See also COUNT DISTINCT.
INTO
See also:
The reverse of SELECT INTO OUTFILE
is LOAD DATA.
ORDER BY
Use the ORDER BY
clause to order the results that are returned from a SELECT
statement. You can specify just a column or use any expression with functions. If you are
using the GROUP BY
clause, you can use grouping functions in ORDER BY
.
Ordering is done after grouping.
You can use multiple ordering expressions, separated by commas. Rows will be sorted by the first expression, then by the second expression if they have the same value for the first, and so on.
You can use the keywords ASC
and DESC
after each ordering expression to
force that ordering to be ascending or descending, respectively. Ordering is ascending
by default.
You can also use a single integer as the ordering expression. If you use an integer n, the results will be ordered by the nth column in the select expression.
When string values are compared, they are compared as if by the STRCMP
function. STRCMP
ignores trailing whitespace and may normalize
characters and ignore case, depending on the collation in use.
MariaDB starting with 5.5.35
Starting from MariaDB 5.5.35 duplicated entries in the ORDER BY
clause are removed. MySQL 5.6 also removes duplicated fields.
See also:
LIMIT
Use the LIMIT
clause to restrict the number of returned rows. When you use a single
integer n with LIMIT
, the first n rows will be returned. Use the ORDER BY
clause to control which rows come first. You can also select a number of rows after an offset
using either of the following:
LIMIT offset, row_count LIMIT row_count OFFSET offset
When you provide an offset m with a limit n, the first m rows will be ignored, and the following n rows will be returned.
UPDATEs using the LIMIT
clause are not safe for replication.
MariaDB starting with 10.0.11
Since MariaDB 10.0.11, LIMIT 0
has been an exception to this rule (see MDEV-6170).
MariaDB starting with 5.5.21
Beginning in MariaDB 5.5.21, there is a LIMIT ROWS EXAMINED
optimization which provides the
means to terminate the execution of SELECT
statements which examine too
many rows, and thus use too many resources. See the LIMIT ROWS EXAMINED
page for details.
GROUP BY
Use the GROUP BY
clause to group rows together that have the same value in
one or more column, or the same computed value using expressions with any
functions and operators except
grouping functions. When you
use a GROUP BY
clause, you will get a single result row for each group of rows
that have the same value for the expression given in GROUP BY
.
When grouping rows, grouping values are compared as if by the =
operator.
For string values, the =
operator ignores trailing whitespace and may normalize
characters and ignore case, depending on the collation in use.
You can use any of the grouping functions in your select expression. Their values will
be calculated based on all the rows that have been grouped together for each result
row. If you select a non-grouped column or a value computed from a non-grouped
column, it is undefined which row the returned value is taken from. This is not permitted if the ONLY_FULL_GROUP_BY
SQL_MODE is used.
You can use multiple expressions in the GROUP BY
clause, separated by commas.
Rows are grouped together if they match on each of the expressions.
You can also use a single integer as the grouping expression. If you use an integer n, the results will be grouped by the nth column in the select expression.
The WHERE
clause is applied before the GROUP BY
clause. It filters non-aggregated
rows before the rows are grouped together. To filter grouped rows based on aggregate values,
use the HAVING
clause. The HAVING
clause takes any expression and evaluates it as
a boolean, just like the WHERE
clause. You can use grouping functions in the HAVING
clause. As with the select expression, if you reference non-grouped columns in the HAVING
clause, the behavior is undefined.
By default, if a GROUP BY
clause is present, the rows in the output will be sorted by the expressions used in the GROUP BY
. You can also specify ASC
or DESC
(ascending, descending) after those expressions, like in ORDER BY
. The default is ASC
.
If you want the rows to be sorted by another field, you can add an explicit ORDER BY
. If you don't want the result to be ordered, you can add ORDER BY NULL
.
GROUP BY
Examples
Consider the following table that records how many times each user has played and won a game:
CREATE TABLE plays (name VARCHAR(16), plays INT, wins INT); INSERT INTO plays VALUES ("John", 20, 5), ("Robert", 22, 8), ("Wanda", 32, 8), ("Susan", 17, 3);
Get a list of win counts along with a count:
MariaDB> SELECT wins, COUNT(*) FROM plays GROUP BY wins; +------+----------+ | wins | COUNT(*) | +------+----------+ | 3 | 1 | | 5 | 1 | | 8 | 2 | +------+----------+ 3 rows in set (0.00 sec)
The GROUP BY
expression can be a computed value, and can refer back to an identifer
specified with AS
. Get a list of win averages along with a count:
MariaDB> SELECT (wins / plays) AS winavg, COUNT(*) FROM plays GROUP BY winavg; +--------+----------+ | winavg | COUNT(*) | +--------+----------+ | 0.1765 | 1 | | 0.2500 | 2 | | 0.3636 | 1 | +--------+----------+ 3 rows in set (0.00 sec)
You can use any grouping function in the select expression. For each win average as above, get a list of the average play count taken to get that average:
MariaDB> SELECT (wins / plays) AS winavg, AVG(plays) FROM plays GROUP BY winavg; +--------+------------+ | winavg | AVG(plays) | +--------+------------+ | 0.1765 | 17.0000 | | 0.2500 | 26.0000 | | 0.3636 | 22.0000 | +--------+------------+ 3 rows in set (0.00 sec)
You can filter on aggregate information using the HAVING
clause. The HAVING
clause is applied after GROUP BY
and allows you to filter on aggregate data that is
not available to the WHERE
clause. Restrict the above example to results that involve
an average number of plays over 20:
MariaDB> SELECT (wins / plays) AS winavg, AVG(plays) FROM plays GROUP BY winavg HAVING AVG(plays) > 20; +--------+------------+ | winavg | AVG(plays) | +--------+------------+ | 0.2500 | 26.0000 | | 0.3636 | 22.0000 | +--------+------------+ 2 rows in set (0.00 sec)
PROCEDURE
clause
The PROCEDURE
clause passes the whole resultset to a Procedure which will process it. Those Procedures are not Stored Procedures. They can only be written in C language, so it is necessary to recompile the server.
Currently, the only available procedure is ANALYSE, which examines the resultset and suggests the optimal datatypes for each column. It is defined in the sql/sql_analyse.cc
file, and can be used as an example to create more Procedures.
This clause cannot be used in a view's definition.
LOCK IN SHARE MODE
and FOR UPDATE
clauses
InnoDB/XtraDB supports row-level locking. SELECT
ed rows can be locked using LOCK IN SHARE MODE
or FOR UPDATE
. In both cases, a lock is acquired on the rows read by the query, and it will be released when the current transaction is committed.
When LOCK IN SHARE MODE
is specified, the statement will wait until all transactions that have modified the rows are committed. Then, a write lock is acquired. All transactions can read the rows, but if they want to modify them, they have to wait until your transaction is committed.
FOR UPDATE
only applies when autocommit
is set to 0 or the SELECT
is enclosed in a transaction. A lock is acquired on the rows, and other transactions are prevented from writing the rows, acquire locks, and from reading them (unless their isolation level is READ UNCOMMITTED
).
If autocommit
is set to 1, LOCK IN SHARE MODE
and FOR UPDATE
clauses have no effect.
If the isolation level is set to SERIALIZABLE
, all plain SELECT
statements are converted to SELECT ... LOCK IN SHARE MODE
.
MariaDB starting with 10.1.2
MAX_STATEMENT_TIME
clause
MariaDB 10.1.1 introduced the MAX_STATEMENT_TIME
clause, which prevents a query from executing for longer than specified, in seconds. See Aborting statements that take longer than a certain time to execute.