MySQL :: MySQL 8.4 Reference Manual :: 15.2.13 SELECT Statement (original) (raw)

15.2.13 SELECT Statement

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 includeUNION operations and subqueries.INTERSECT andEXCEPT 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 aWITH clause to define common table expressions accessible within theSELECT. See Section 15.2.20, “WITH (Common Table Expressions)”.

The most commonly used clauses ofSELECT 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 requireFROM DUAL if no tables are referenced.

In general, clauses used must be given in exactly the order shown in the syntax description. For example, aHAVING clause must come after anyGROUP 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 aboutINTO, see Section 15.2.13.1, “SELECT ... INTO Statement”.

The list of selectexpr terms comprises the select list that indicates which columns to retrieve. Terms specify a column or expression or can use*-shorthand:

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

To avoid this problem, use a qualified_`tblname`_.* reference: 

SELECT id, t1.* FROM t1

Use qualified_`tblname`_.* references for each table in the select list: 

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

The following list provides additional information about otherSELECT clauses:

SELECT CONCAT(last_name,', ',first_name) AS full_name  
  FROM mytable ORDER BY full_name;

The AS keyword is optional when aliasing a_selectexpr_ 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_selectexpr_ 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 aWHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section B.3.4.4, “Problems with Column Aliases”.

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, seeSection 10.9.4, “Index Hints”.
You can use SET max_seeks_for_key=_`value`_ as an alternative way to force MySQL to prefer key scans instead of table scans. SeeSection 7.1.8, “Server System Variables”.

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;  
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 the ASC 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.

SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

Preference is given to standard SQL behavior, so if aHAVING 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.

SELECT col_name FROM tbl_name HAVING col_name > 0;

Write this instead: 

SELECT col_name FROM tbl_name WHERE col_name > 0;  
SELECT user, MAX(salary) FROM users  
  GROUP BY user HAVING MAX(salary) > 10;

(This did not work in some older versions of MySQL.)

SELECT 12 AS a, a FROM t GROUP BY a;

In that statement, both columns have the namea. To ensure that the correct column is used for grouping, use different names for each_selectexpr_.

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_`rowcount`_ is equivalent to LIMIT 0,_`rowcount`_.
For prepared statements, you can use placeholders. The following statements return one row from thetbl 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 theLIMIT _`rowcount`_ OFFSET_`offset`_ syntax.
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.

SELECT * FROM t1, t2 FOR SHARE OF t1 FOR UPDATE OF t2;

All tables referenced by the query block are locked whenOF _`tblname`_ is omitted. Consequently, using a locking clause withoutOF _`tblname`_ 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 theSELECT statement, a locking clause may only use the alias. If the SELECT 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, seeSection 17.7.2.4, “Locking Reads”. For additional information about NOWAIT and SKIP LOCKED options, seeLocking 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 withSQL_ are MySQL extensions to standard SQL.