(mysql.info) select
Info Catalog
(mysql.info) replace
(mysql.info) data-manipulation
(mysql.info) subqueries
13.2.7 `SELECT' Syntax
----------------------
Menu
* join `JOIN' Syntax
* union `UNION' 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, ...
[FROM TABLE_REFERENCES
[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)]
[INTO OUTFILE 'FILE_NAME' EXPORT_OPTIONS
| INTO DUMPFILE 'FILE_NAME']
[FOR UPDATE | LOCK IN SHARE MODE]]
`SELECT' is used to retrieve rows selected from one or more tables, and
can include `UNION' statements and subqueries. See union, and
subqueries.
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 one SELECT_EXPR.
* TABLE_REFERENCES indicates the table or tables from which to
retrieve rows. Its syntax is described in join.
* 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 MySQL supports, except for aggregate (summary)
functions. See functions.
`SELECT' can also be used to retrieve rows computed without reference
to any table.
For example:
mysql> SELECT 1 + 1;
-> 2
You are allowed 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 compatibility with some other database servers
that require a `FROM' clause. MySQL does not require the clause 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 exception
is that the `INTO' clause can appear either as shown in the syntax
description or immediately preceding the `FROM' clause.
* A SELECT_EXPR can be given an alias using `AS ALIAS_NAME'. The
alias is used as the expression's column name and can be used in
`GROUP BY', `ORDER BY', or `HAVING' 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 a SELECT_EXPR. 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 two SELECT_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 allowable to use a column alias in a `WHERE' clause,
because the column value might not yet be determined when the
`WHERE' clause is executed. See problems-with-alias.
* The `FROM TABLE_REFERENCES' 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
join. For each table specified, you can optionally specify an
alias.
TBL_NAME [[AS] ALIAS]
[{USE|IGNORE|FORCE} INDEX (KEY_LIST)]
The use of `USE INDEX', `IGNORE INDEX', `FORCE INDEX' to give the
optimizer hints about how to choose indexes is described in
join.
You can use `SET max_seeks_for_key=VALUE' as an alternative way to
force MySQL to prefer key scans instead of table scans. See
server-system-variables.
* You can refer to a table within the default database as TBL_NAME,
or as DB_NAME.TBL_NAME to specify a database explicitly. You can
refer to a column as COL_NAME, TBL_NAME.COL_NAME, or
DB_NAME.TBL_NAME.COL_NAME. You need not specify a TBL_NAME or
DB_NAME.TBL_NAME prefix for a column reference unless the
reference would be ambiguous. See legal-names, for examples
of ambiguity that require the more explicit column reference forms.
* A table reference can be aliased using `TBL_NAME AS ALIAS_NAME' or
TBL_NAME ALIAS_NAME:
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' and
`GROUP 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 the `ORDER BY' clause that you are
sorting by. The default is ascending order; this can be specified
explicitly using the `ASC' keyword.
Use of column positions is deprecated because the syntax has been
removed from the SQL standard.
* If you use `GROUP BY', output rows are sorted according to the
`GROUP BY' columns as if you had an `ORDER BY' for the same
columns. To avoid the overhead of sorting that `GROUP BY'
produces, add `ORDER BY NULL':
SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;
* MySQL extends the `GROUP BY' clause so that you can also specify
`ASC' and `DESC' after columns named in the clause:
SELECT a, COUNT(b) FROM test_table GROUP BY a DESC;
* MySQL extends the use of `GROUP BY' to allow selecting fields that
are not mentioned in the `GROUP BY' clause. If you are not getting
the results that you expect from your query, please read the
description of `GROUP BY' found in
group-by-functions-and-modifiers.
* `GROUP BY' allows a `WITH ROLLUP' modifier. See
group-by-modifiers.
* The `HAVING' clause is applied nearly last, just before items are
sent to the client, with no optimization. (`LIMIT' is applied after
`HAVING'.)
Before MySQL 5.0.2, a `HAVING' clause can refer to any column or
alias named in a SELECT_EXPR in the `SELECT' list or in outer
subqueries, and to aggregate functions. However, the SQL standard
requires that `HAVING' must reference only columns in the `GROUP
BY' clause or columns used in aggregate functions. To accommodate
both standard SQL and the MySQL-specific behavior of being able to
refer columns in the `SELECT' list, MySQL 5.0.2 and up allows
`HAVING' to refer to columns in the `SELECT' list, columns in the
`GROUP BY' clause, columns in outer subqueries, and to aggregate
functions.
For example, the following statement works in MySQL 5.0.2 but
produces an error for earlier versions:
mysql> SELECT COUNT(*) FROM t GROUP BY col1 HAVING col1 = 2;
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 in `GROUP BY' and as an aliased column in
the output column list, preference is given to the column in the
`GROUP BY' column.
* Do not use `HAVING' for items that should be in the `WHERE'
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 the
`WHERE' 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.)
* The `LIMIT' clause can be used to constrain the number of rows
returned by the `SELECT' statement. `LIMIT' takes one or two
numeric arguments, which must both be non-negative integer
constants (except when using prepared statements).
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 ROW_COUNT' is equivalent to `LIMIT 0,
ROW_COUNT'.
For prepared statements, you can use placeholders (supported as of
MySQL version 5.0.7). The following statements will return one row
from the `tbl' table:
SET @a=1;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
EXECUTE STMT USING @a;
The following statements will return the second to sixth row 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
ROW_COUNT OFFSET OFFSET' syntax.
* The `SELECT ... INTO OUTFILE 'FILE_NAME'' form of `SELECT' writes
the selected rows to a file. The file is created on the server
host, so you must have the `FILE' privilege to use this syntax.
FILE_NAME cannot be an existing file, which among other things
prevents files such as `/etc/passwd' and database tables from
being destroyed. As of MySQL 5.0.19, the
`character_set_filesystem' system variable controls the
interpretation of the filename.
The `SELECT ... INTO OUTFILE' statement is intended primarily to
let you very quickly dump a table to a text file on the server
machine. If you want to create the resulting file on some client
host other than the server host, you cannot use `SELECT ... INTO
OUTFILE'. In that case, you should instead use a command such as
`mysql -e "SELECT ..." > FILE_NAME' to generate the file on the
client host.
`SELECT ... INTO OUTFILE' is the complement of `LOAD DATA INFILE';
the syntax for the EXPORT_OPTIONS part of the statement consists
of the same `FIELDS' and `LINES' clauses that are used with the
`LOAD DATA INFILE' statement. See load-data.
`FIELDS ESCAPED BY' controls how to write special characters. If
the `FIELDS ESCAPED BY' character is not empty, it is used as a
prefix that precedes following characters on output:
* The `FIELDS ESCAPED BY' character
* The `FIELDS [OPTIONALLY] ENCLOSED BY' character
* The first character of the `FIELDS TERMINATED BY' and `LINES
TERMINATED BY' values
* ASCII `NUL' (the zero-valued byte; what is actually written
following the escape character is ASCII ‘`0'’, not a
zero-valued byte)
The `FIELDS TERMINATED BY', `ENCLOSED BY', `ESCAPED BY', or `LINES
TERMINATED BY' characters _must_ be escaped so that you can read
the file back in reliably. ASCII `NUL' is escaped to make it
easier to view with some pagers.
The resulting file does not have to conform to SQL syntax, so
nothing else need be escaped.
If the `FIELDS ESCAPED BY' character is empty, no characters are
escaped and `NULL' is output as `NULL', not `\N'. It is probably
not a good idea to specify an empty escape character, particularly
if field values in your data contain any of the characters in the
list just given.
Here is an example that produces a file in the comma-separated
values (CSV) format used by many programs:
SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
* If you use `INTO DUMPFILE' instead of `INTO OUTFILE', MySQL writes
only one row into the file, without any column or line termination
and without performing any escape processing. This is useful if
you want to store a `BLOB' value in a file.
* * Any file created by `INTO OUTFILE' or `INTO DUMPFILE' is
writable by all users on the server host. The reason for this is
that the MySQL server cannot create a file that is owned by anyone
other than the user under whose account it is running. (You should
_never_ run `mysqld' as `root' for this and other reasons.) The
file thus must be world-writable so that you can manipulate its
contents.
* The `SELECT' syntax description at the beginning this section
shows the `INTO' clause near the end of the statement. It is also
possible to use `INTO OUTFILE' or `INTO DUMPFILE' immediately
preceding the `FROM' clause.
* A `PROCEDURE' clause names a procedure that should process the
data in the result set. For an example, see
procedure-analyse.
* 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. Using `LOCK IN SHARE MODE' sets a
shared lock that allows other transactions to read the examined
rows but not to update or delete them. See
innodb-locking-reads.
Following the `SELECT' keyword, you can use a number of options that
affect the operation of the statement.
The `ALL', `DISTINCT', and `DISTINCTROW' options specify whether
duplicate rows should be returned. If none of these options are given,
the default is `ALL' (all matching rows are returned). `DISTINCT' and
`DISTINCTROW' are synonyms and specify removal of duplicate rows from
the result set.
`HIGH_PRIORITY', `STRAIGHT_JOIN', and options beginning with `SQL_' are
MySQL extensions to standard SQL.
* `HIGH_PRIORITY' gives the `SELECT' 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. A `SELECT 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.
`HIGH_PRIORITY' cannot be used with `SELECT' statements that are
part of a `UNION'.
* `STRAIGHT_JOIN' forces the optimizer to join the tables in the
order in which they are listed in the `FROM' clause. You can use
this to speed up a query if the optimizer joins the tables in
non-optimal order. See explain. `STRAIGHT_JOIN' also can
be used in the TABLE_REFERENCES list. See join.
* `SQL_BIG_RESULT' can be used with `GROUP BY' or `DISTINCT' to tell
the optimizer that the result set has many rows. In this case,
MySQL directly uses disk-based temporary tables if needed, and
prefers sorting to using a temporary table with a key on the
`GROUP BY' elements.
* `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.
* `SQL_SMALL_RESULT' can be used with `GROUP BY' or `DISTINCT' to
tell the optimizer that the result set is small. In this case,
MySQL uses fast temporary tables to store the resulting table
instead of using sorting. This should not normally be needed.
* `SQL_CALC_FOUND_ROWS' tells MySQL to calculate how many rows there
would be in the result set, disregarding any `LIMIT' clause. The
number of rows can then be retrieved with `SELECT FOUND_ROWS()'.
See information-functions.
* `SQL_CACHE' tells MySQL to store the query result in the query
cache if you are using a `query_cache_type' value of `2' or
`DEMAND'. For a query that uses `UNION' or subqueries, this option
effects any `SELECT' in the query. See query-cache.
* `SQL_NO_CACHE' tells MySQL not to store the query result in the
query cache. See query-cache. For a query that uses
`UNION' or subqueries, this option effects any `SELECT' in the
query.
Info Catalog
(mysql.info) replace
(mysql.info) data-manipulation
(mysql.info) subqueries
automatically generated byinfo2html