SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr] ...
[into_option]
[FROM table_references
[PARTITION partition_list]]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
[HAVING where_condition]
[WINDOW window_name AS (window_spec)
[, window_name AS (window_spec)] ...]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[into_option]
[FOR {UPDATE | SHARE}
[OF tbl_name [, tbl_name] ...]
[NOWAIT | SKIP LOCKED]
| LOCK IN SHARE MODE]
[into_option]
into_option: {
INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name] ...
}
export_options:
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
SELECT
is used to retrieve rows
selected from one or more tables, and can include
UNION
operations and subqueries.
INTERSECT
and
EXCEPT
operations are also
supported. The UNION
,
INTERSECT
, and EXCEPT
operators are described in more detail later in this section. See
also Section 15.2.15, “Subqueries”.
A SELECT
statement can start with a
WITH
clause to define common table
expressions accessible within the
SELECT
. See Section 15.2.20, “WITH (Common Table Expressions)”.
The most commonly used clauses of
SELECT
statements are these:
Each
select_expr
indicates a column that you want to retrieve. There must be at least oneselect_expr
.table_references
indicates the table or tables from which to retrieve rows. Its syntax is described in Section 15.2.13.2, “JOIN Clause”.SELECT
supports explicit partition selection using thePARTITION
clause with a list of partitions or subpartitions (or both) following the name of the table in atable_reference
(see Section 15.2.13.2, “JOIN Clause”). In this case, rows are selected only from the partitions listed, and any other partitions of the table are ignored. For more information and examples, see Section 26.5, “Partition Selection”.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 noWHERE
clause.In the
WHERE
expression, you can use any of the functions and operators that MySQL supports, except for aggregate (group) functions. See Section 11.5, “Expressions”, and Chapter 14, Functions and Operators.
SELECT
can also be used to retrieve
rows computed without reference to any table.
For example:
mysql> SELECT 1 + 1;
-> 2
You are permitted to specify DUAL
as a dummy
table name in situations where no tables are referenced:
mysql> SELECT 1 + 1 FROM DUAL;
-> 2
DUAL
is purely for the convenience of people
who require that all SELECT
statements should have FROM
and possibly other
clauses. MySQL may ignore the clauses. MySQL does not require
FROM DUAL
if no tables are referenced.
In general, clauses used must be given in exactly the order shown
in the syntax description. For example, a
HAVING
clause must come after any
GROUP BY
clause and before any ORDER
BY
clause. The INTO
clause, if
present, can appear in any position indicated by the syntax
description, but within a given statement can appear only once,
not in multiple positions. For more information about
INTO
, see Section 15.2.13.1, “SELECT ... INTO Statement”.
The list of select_expr
terms comprises
the select list that indicates which columns to retrieve. Terms
specify a column or expression or can use
*
-shorthand:
A select list consisting only of a single unqualified
*
can be used as shorthand to select all columns from all tables:SELECT * FROM t1 INNER JOIN t2 ...
can be used as a qualified shorthand to select all columns from the named table:tbl_name
.*SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...
If a table has invisible columns,
*
and
do not include them. To be included, invisible columns must be referenced explicitly.tbl_name
.*Use of an unqualified
*
with other items in the select list may produce a parse error. For example:SELECT id, * FROM t1
To avoid this problem, use a qualified
reference:tbl_name
.*SELECT id, t1.* FROM t1
Use qualified
references for each table in the select list:tbl_name
.*SELECT AVG(score), t1.* FROM t1 ...
The following list provides additional information about other
SELECT
clauses:
A
select_expr
can be given an alias usingAS
. The alias is used as the expression's column name and can be used inalias_name
GROUP BY
,ORDER BY
, orHAVING
clauses. For example:SELECT CONCAT(last_name,', ',first_name) AS full_name FROM mytable ORDER BY full_name;
The
AS
keyword is optional when aliasing aselect_expr
with an identifier. The preceding example could have been written like this:SELECT CONCAT(last_name,', ',first_name) full_name FROM mytable ORDER BY full_name;
However, because the
AS
is optional, a subtle problem can occur if you forget the comma between twoselect_expr
expressions: MySQL interprets the second as an alias name. For example, in the following statement,columnb
is treated as an alias name:SELECT columna columnb FROM mytable;
For this reason, it is good practice to be in the habit of using
AS
explicitly when specifying column aliases.It is not permissible to refer to a column alias in a
WHERE
clause, because the column value might not yet be determined when theWHERE
clause is executed. See Section B.3.4.4, “Problems with Column Aliases”.The
FROM
clause indicates the table or tables from which to retrieve rows. If you name more than one table, you are performing a join. For information on join syntax, see Section 15.2.13.2, “JOIN Clause”. For each table specified, you can optionally specify an alias.table_references
tbl_name [[AS] alias] [index_hint]
The use of index hints provides the optimizer with information about how to choose indexes during query processing. For a description of the syntax for specifying these hints, see Section 10.9.4, “Index Hints”.
You can use
SET max_seeks_for_key=
as an alternative way to force MySQL to prefer key scans instead of table scans. See Section 7.1.8, “Server System Variables”.value
You can refer to a table within the default database as
tbl_name
, or asdb_name
.tbl_name
to specify a database explicitly. You can refer to a column ascol_name
,tbl_name
.col_name
, ordb_name
.tbl_name
.col_name
. You need not specify atbl_name
ordb_name
.tbl_name
prefix for a column reference unless the reference would be ambiguous. See Section 11.2.2, “Identifier Qualifiers”, for examples of ambiguity that require the more explicit column reference forms.A table reference can be aliased using
ortbl_name
ASalias_name
tbl_name alias_name
. These statements are equivalent:SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 WHERE t1.name = t2.name; SELECT t1.name, t2.salary FROM employee t1, info t2 WHERE t1.name = t2.name;
Columns selected for output can be referred to in
ORDER BY
andGROUP BY
clauses using column names, column aliases, or column positions. Column positions are integers and begin with 1:SELECT college, region, seed FROM tournament ORDER BY region, seed; SELECT college, region AS r, seed AS s FROM tournament ORDER BY r, s; SELECT college, region, seed FROM tournament ORDER BY 2, 3;
To sort in reverse order, add the
DESC
(descending) keyword to the name of the column in theORDER BY
clause that you are sorting by. The default is ascending order; this can be specified explicitly using theASC
keyword.If
ORDER BY
occurs within a parenthesized query expression and also is applied in the outer query, the results are undefined and may change in a future version of MySQL.Use of column positions is deprecated because the syntax has been removed from the SQL standard.
When you use
ORDER BY
orGROUP BY
to sort a column in aSELECT
, the server sorts values using only the initial number of bytes indicated by themax_sort_length
system variable.MySQL extends the use of
GROUP BY
to permit selecting fields that are not mentioned in theGROUP BY
clause. If you are not getting the results that you expect from your query, please read the description ofGROUP BY
found in Section 14.19, “Aggregate Functions”.The
HAVING
clause, like theWHERE
clause, specifies selection conditions. TheWHERE
clause specifies conditions on columns in the select list, but cannot refer to aggregate functions. TheHAVING
clause specifies conditions on groups, typically formed by theGROUP BY
clause. The query result includes only groups satisfying theHAVING
conditions. (If noGROUP BY
is present, all rows implicitly form a single aggregate group.)The
HAVING
clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT
is applied afterHAVING
.)The SQL standard requires that
HAVING
must reference only columns in theGROUP BY
clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permitsHAVING
to refer to columns in theSELECT
list and columns in outer subqueries as well.If the
HAVING
clause refers to a column that is ambiguous, a warning occurs. In the following statement,col2
is ambiguous because it is used as both an alias and a column name:SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;
Preference is given to standard SQL behavior, so if a
HAVING
column name is used both inGROUP BY
and as an aliased column in the select column list, preference is given to the column in theGROUP BY
column.Do not use
HAVING
for items that should be in theWHERE
clause. For example, do not write the following:SELECT col_name FROM tbl_name HAVING col_name > 0;
Write this instead:
SELECT col_name FROM tbl_name WHERE col_name > 0;
The
HAVING
clause can refer to aggregate functions, which theWHERE
clause cannot:SELECT user, MAX(salary) FROM users GROUP BY user HAVING MAX(salary) > 10;
(This did not work in some older versions of MySQL.)
MySQL permits duplicate column names. That is, there can be more than one
select_expr
with the same name. This is an extension to standard SQL. Because MySQL also permitsGROUP BY
andHAVING
to refer toselect_expr
values, this can result in an ambiguity:SELECT 12 AS a, a FROM t GROUP BY a;
In that statement, both columns have the name
a
. To ensure that the correct column is used for grouping, use different names for eachselect_expr
.The
WINDOW
clause, if present, defines named windows that can be referred to by window functions. For details, see Section 14.20.4, “Named Windows”.MySQL resolves unqualified column or alias references in
ORDER BY
clauses by searching in theselect_expr
values, then in the columns of the tables in theFROM
clause. ForGROUP BY
orHAVING
clauses, it searches theFROM
clause before searching in theselect_expr
values. (ForGROUP BY
andHAVING
, this differs from the pre-MySQL 5.0 behavior that used the same rules as forORDER BY
.)The
LIMIT
clause can be used to constrain the number of rows returned by theSELECT
statement.LIMIT
takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:Within prepared statements,
LIMIT
parameters can be specified using?
placeholder markers.Within stored programs,
LIMIT
parameters can be specified using integer-valued routine parameters or local variables.
With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):
SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:
SELECT * FROM tbl LIMIT 95,18446744073709551615;
With one argument, the value specifies the number of rows to return from the beginning of the result set:
SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows
In other words,
LIMIT
is equivalent torow_count
LIMIT 0,
.row_count
For prepared statements, you can use placeholders. The following statements return one row from the
tbl
table:SET @a=1; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?'; EXECUTE STMT USING @a;
The following statements return the second to sixth rows from the
tbl
table:SET @skip=1; SET @numrows=5; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?'; EXECUTE STMT USING @skip, @numrows;
For compatibility with PostgreSQL, MySQL also supports the
LIMIT
syntax.row_count
OFFSEToffset
If
LIMIT
occurs within a parenthesized query expression and also is applied in the outer query, the results are undefined and may change in a future version of MySQL.The
SELECT ... INTO
form ofSELECT
enables the query result to be written to a file or stored in variables. For more information, see Section 15.2.13.1, “SELECT ... INTO Statement”.If you use
FOR UPDATE
with a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction.You cannot use
FOR UPDATE
as part of theSELECT
in a statement such asCREATE TABLE
. (If you attempt to do so, the statement is rejected with the error Can't update table 'new_table
SELECT ... FROMold_table
...old_table
' while 'new_table
' is being created.)FOR SHARE
andLOCK IN SHARE MODE
set shared locks that permit other transactions to read the examined rows but not to update or delete them.FOR SHARE
andLOCK IN SHARE MODE
are equivalent. However,FOR SHARE
, likeFOR UPDATE
, supportsNOWAIT
,SKIP LOCKED
, andOF
options.tbl_name
FOR SHARE
is a replacement forLOCK IN SHARE MODE
, butLOCK IN SHARE MODE
remains available for backward compatibility.NOWAIT
causes aFOR UPDATE
orFOR SHARE
query to execute immediately, returning an error if a row lock cannot be obtained due to a lock held by another transaction.SKIP LOCKED
causes aFOR UPDATE
orFOR SHARE
query to execute immediately, excluding rows from the result set that are locked by another transaction.NOWAIT
andSKIP LOCKED
options are unsafe for statement-based replication.NoteQueries that skip locked rows return an inconsistent view of the data.
SKIP LOCKED
is therefore not suitable for general transactional work. However, it may be used to avoid lock contention when multiple sessions access the same queue-like table.OF
appliestbl_name
FOR UPDATE
andFOR SHARE
queries to named tables. For example:SELECT * FROM t1, t2 FOR SHARE OF t1 FOR UPDATE OF t2;
All tables referenced by the query block are locked when
OF
is omitted. Consequently, using a locking clause withouttbl_name
OF
in combination with another locking clause returns an error. Specifying the same table in multiple locking clauses returns an error. If an alias is specified as the table name in thetbl_name
SELECT
statement, a locking clause may only use the alias. If theSELECT
statement does not specify an alias explicitly, the locking clause may only specify the actual table name.For more information about
FOR UPDATE
andFOR SHARE
, see Section 17.7.2.4, “Locking Reads”. For additional information aboutNOWAIT
andSKIP LOCKED
options, see Locking Read Concurrency with NOWAIT and SKIP LOCKED.
Following the SELECT
keyword, you
can use a number of modifiers that affect the operation of the
statement. HIGH_PRIORITY
,
STRAIGHT_JOIN
, and modifiers beginning with
SQL_
are MySQL extensions to standard SQL.
The
ALL
andDISTINCT
modifiers specify whether duplicate rows should be returned.ALL
(the default) specifies that all matching rows should be returned, including duplicates.DISTINCT
specifies removal of duplicate rows from the result set. It is an error to specify both modifiers.DISTINCTROW
is a synonym forDISTINCT
.DISTINCT
can be used with a query that also usesWITH ROLLUP
.HIGH_PRIORITY
gives theSELECT
higher priority than a statement that updates a table. You should use this only for queries that are very fast and must be done at once. ASELECT HIGH_PRIORITY
query that is issued while the table is locked for reading runs even if there is an update statement waiting for the table to be free. This affects only storage engines that use only table-level locking (such asMyISAM
,MEMORY
, andMERGE
).HIGH_PRIORITY
cannot be used withSELECT
statements that are part of aUNION
.STRAIGHT_JOIN
forces the optimizer to join the tables in the order in which they are listed in theFROM
clause. You can use this to speed up a query if the optimizer joins the tables in nonoptimal order.STRAIGHT_JOIN
also can be used in thetable_references
list. See Section 15.2.13.2, “JOIN Clause”.STRAIGHT_JOIN
does not apply to any table that the optimizer treats as aconst
orsystem
table. Such a table produces a single row, is read during the optimization phase of query execution, and references to its columns are replaced with the appropriate column values before query execution proceeds. These tables appear first in the query plan displayed byEXPLAIN
. See Section 10.8.1, “Optimizing Queries with EXPLAIN”. This exception may not apply toconst
orsystem
tables that are used on theNULL
-complemented side of an outer join (that is, the right-side table of aLEFT JOIN
or the left-side table of aRIGHT JOIN
.SQL_BIG_RESULT
orSQL_SMALL_RESULT
can be used withGROUP BY
orDISTINCT
to tell the optimizer that the result set has many rows or is small, respectively. ForSQL_BIG_RESULT
, MySQL directly uses disk-based temporary tables if they are created, and prefers sorting to using a temporary table with a key on theGROUP BY
elements. ForSQL_SMALL_RESULT
, MySQL uses in-memory temporary tables to store the resulting table instead of using sorting. This should not normally be needed.SQL_BUFFER_RESULT
forces the result to be put into a temporary table. This helps MySQL free the table locks early and helps in cases where it takes a long time to send the result set to the client. This modifier can be used only for top-levelSELECT
statements, not for subqueries or followingUNION
.SQL_CALC_FOUND_ROWS
tells MySQL to calculate how many rows there would be in the result set, disregarding anyLIMIT
clause. The number of rows can then be retrieved withSELECT FOUND_ROWS()
. See Section 14.15, “Information Functions”.NoteThe
SQL_CALC_FOUND_ROWS
query modifier and accompanyingFOUND_ROWS()
function are deprecated; expect them to be removed in a future version of MySQL. See the description ofFOUND_ROWS()
for information about an alternative strategy.The
SQL_CACHE
andSQL_NO_CACHE
modifiers were used with the query cache prior to MySQL 8.4. The query cache was removed in MySQL 8.4. TheSQL_CACHE
modifier was removed as well.SQL_NO_CACHE
is deprecated, and has no effect; expect it to be removed in a future MySQL release.