Spec-Zone .ru
спецификации, руководства, описания, API

13.2.9. SELECT Syntax

13.2.9.1. SELECT ... INTOSyntax
13.2.9.2. JOIN Syntax
13.2.9.3. Index Hint Syntax
13.2.9.4. 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 [, select_expr ...]    [FROM table_references      [PARTITION partition_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)]    [INTO OUTFILE 'file_name'        [CHARACTER SET charset_name]        export_options      | INTO DUMPFILE 'file_name'      | INTO var_name [, var_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 Section 13.2.9.4, "UNION Syntax", and Section 13.2.10, "Subquery Syntax".

The most commonly used clauses of SELECT statements are these:

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:

The following list provides additional information about other SELECT clauses:

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.

Prior to MySQL 5.6.6, a SELECT from a partitioned table using a storage engine such as MyISAM that employs table-level locks locked all partitions of the table. This was true even for SELECT ... PARTITION queries. (This did not and does not occur with storage engines such as InnoDB that employ row-level locking.) In MySQL 5.6.6 and later, MySQL uses partition lock pruning, so that only partitions containing rows matching the SELECT statement's WHERE clause are actually locked. For more information, see Section 18.6.4, "Partitioning and Locking".