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.

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 exception is that the
INTO clause can appear either as shown in the
syntax description or immediately following the
select_expr list. For more information
about INTO, see Section 13.2.9.1, “SELECT ... INTO Syntax”.

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 ...

tbl_name.* can
be used as a qualified shorthand to select all columns from
the named table:

SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...

Use of an unqualified * with other items in
the select list may produce a parse error. To avoid this
problem, use a qualified
tbl_name.*
reference

SELECT AVG(score), t1.* FROM t1 ...

The following list provides additional information about other
SELECT clauses:

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 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 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.

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 Section 13.2.9.2, “JOIN Syntax”. For
each table specified, you can optionally specify an alias.

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 13.2.9.3, “Index Hint Syntax”.

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 Section 9.2.1, “Identifier Qualifiers”, 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:

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.

If ORDER BY occurs within a subquery and
also is applied in the outer query, the outermost
ORDER BY takes precedence. For example,
results for the following statement are sorted in descending
order, not ascending order:

(SELECT ... ORDER BY a) ORDER BY a DESC;

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;

Relying on implicit GROUP BY sorting in
MySQL 5.5 is deprecated. To achieve a specific
sort order of grouped results, it is preferable to use an
explicit ORDER BY clause. GROUP
BY sorting is a MySQL extension that may change in a
future release; for example, to make it possible for the
optimizer to order groupings in whatever manner it deems most
efficient and to avoid the sorting overhead.

MySQL extends the GROUP BY clause so that
you can also specify ASC and
DESC after columns named in the clause:

The HAVING clause is applied nearly last,
just before items are sent to the client, with no
optimization. (LIMIT is applied after
HAVING.)

The SQL standard requires that HAVING must
reference only columns in the GROUP BY
clause or columns used in aggregate functions. However, MySQL
supports an extension to this behavior, and permits
HAVING to refer to columns in the
SELECT 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 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.)

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 permits GROUP BY and
HAVING to refer to
select_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 each
select_expr.

MySQL resolves unqualified column or alias references in
ORDER BY clauses by searching in the
select_expr values, then in the
columns of the tables in the FROM clause.
For GROUP BY or HAVING
clauses, it searches the FROM clause before
searching in the select_expr
values. (For GROUP BY and
HAVING, this differs from the pre-MySQL 5.0
behavior that used the same rules as for ORDER
BY.)

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 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 as of MySQL 5.5.6.

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. The
following statements will return one row from the
tbl table:

For compatibility with PostgreSQL, MySQL also supports the
LIMIT row_count OFFSET
offset syntax.

If LIMIT occurs within a subquery and also
is applied in the outer query, the outermost
LIMIT takes precedence. For example, the
following statement produces two rows, not one:

(SELECT ... LIMIT 1) LIMIT 2;

A PROCEDURE clause names a procedure that
should process the data in the result set. For an example, see
Section 8.4.2.4, “Using PROCEDURE ANALYSE”, which describes
ANALYSE, a procedure that can be used to
obtain suggestions for optimal column data types that may help
reduce table sizes.

Following the SELECT keyword, you
can use a number of options that affect the operation of the
statement. HIGH_PRIORITY,
STRAIGHT_JOIN, and options beginning with
SQL_ are MySQL extensions to standard SQL.

The ALL and DISTINCT
options 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
options. DISTINCTROW is a synonym for
DISTINCT.

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. This
affects only storage engines that use only table-level locking
(such as MyISAM, MEMORY,
and MERGE).

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 nonoptimal order.
STRAIGHT_JOIN also can be used in the
table_references list. See
Section 13.2.9.2, “JOIN Syntax”.

STRAIGHT_JOIN does not apply to any table
that the optimizer treats as a
const or
system 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 will appear first in the query plan
displayed by EXPLAIN. See
Section 8.8.1, “Optimizing Queries with EXPLAIN”. This exception may not apply
to const or
system tables that are used
on the NULL-complemented side of an outer
join (that is, the right-side table of a LEFT
JOIN or the left-side table of a RIGHT
JOIN.

SQL_BIG_RESULT or
SQL_SMALL_RESULT can be used with
GROUP BY or DISTINCT to
tell the optimizer that the result set has many rows or is
small, respectively. For SQL_BIG_RESULT,
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. For
SQL_SMALL_RESULT, MySQL uses fast 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 option can be used
only for top-level SELECT
statements, not for subqueries or following
UNION.

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
Section 12.14, “Information Functions”.

The SQL_CACHE and
SQL_NO_CACHE options affect caching of
query results in the query cache (see
Section 8.9.3, “The MySQL Query Cache”). SQL_CACHE
tells MySQL to store the result in the query cache if it is
cacheable and the value of the
query_cache_type system
variable is 2 or DEMAND.
With SQL_NO_CACHE, the server does not use
the query cache. It neither checks the query cache to see
whether the result is already cached, nor does it cache the
query result. (Due to a limitation in the parser, a space
character must precede and follow the
SQL_NO_CACHE keyword; a nonspace such as a
newline causes the server to check the query cache to see
whether the result is already cached.)

For views, SQL_NO_CACHE applies if it
appears in any SELECT in the
query. For a cacheable query, SQL_CACHE
applies if it appears in the first
SELECT of a view referred to by
the query.

As of MySQL 5.5.3, these two options are mutually exclusive
and an error occurs if they are both specified. Also, these
options are not permitted in subqueries (including subqueries
in the FROM clause), and
SELECT statements in unions
other than the first SELECT.

Before MySQL 5.5.3, for a query that uses
UNION or subqueries, the
following rules apply:

For a cacheable query, SQL_CACHE
applies if it appears in the first
SELECT of the query.

A SELECT from a partitioned table using a
storage engine such as MyISAM that
employs table-level locks locks all partitions of the table. This
does not occur with tables using storage engines such as
InnoDB that employ row-level locking.
This issue is resolved in MySQL 5.6. See
Section 19.5.4, “Partitioning and Table-Level Locking”, for more
information.