SELECT

Syntax

SELECT
    [/*+ hints */]
    [/*+ JOIN_PREFIX(argument_list) */]
    [/*+ JOIN_ORDER(argument_list) */]
    [/*+ JOIN_FIXED_ORDER(argument_list) */]
    [/*+ JOIN_SUFFIX(argument_list) */]
    [/*+ MAX_EXECUTION_TIME(milliseconds) */]
    [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
      [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  
      [ROWS EXAMINED rows_limit] } |
        [OFFSET start { ROW | ROWS }]
        [FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }] ]
      procedure|[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_option | LOCK IN SHARE MODE lock_option]
export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
lock_option:
    [WAIT n | NOWAIT | SKIP LOCKED]

Description

SELECT is used to retrieve rows selected from one or more tables, and can include statements and .

• Each select_expr expression indicates a column or data that you want to retrieve. You must have at least one select expression. See below.

• The FROM clause indicates the table or tables from which to retrieve rows. Use either a single table name or a JOIN expression. See for details. If no table is involved, can be specified.

SELECT can also be used to retrieve rows computed without reference to any table.

Select Expressions

A SELECT statement must contain one or more select expressions, separated by commas. Each select expression can be one of the following:

• The name of a column.

• Any expression using .

• * to select all columns from all tables in the FROM clause.

When specifying a column, you can either use just the column name or qualify the column name with the name of the table using tbl_name.col_name. The qualified form is useful if you are joining multiple tables in the FROM clause. If you do not qualify the column names when selecting from multiple tables, MariaDB will try to find the column in each table. It is an error if that column name exists in multiple tables.

You can quote column names using backticks. If you are qualifying column names with table names, quote each part separately as tbl_name`.`col_name.

If you use any in any of the select expressions, all rows in your results will be implicitly grouped, as if you had used GROUP BY NULL. GROUP BY NULL being an expression behaves specially such that the entire result set is treated as a group.

DISTINCT

A query may produce some identical rows. By default, all rows are retrieved, even when their values are the same. To explicitly specify that you want to retrieve identical rows, use the ALL option. If you want duplicates to be removed from the result set, use the DISTINCT option. DISTINCTROW is a synonym for DISTINCT. See also and .

INTO

The INTO clause is used to specify that the query results should be written to a file or variable.

• - formatting and writing the result to an external file.

• - binary-safe writing of the unformatted results to an external file.

• - selecting and setting variables.

The reverse of SELECT INTO OUTFILE is .

LIMIT

Restricts the number of returned rows. See and for details.

LOCK IN SHARE MODE/FOR UPDATE

See and for details on the respective locking clauses.

OFFSET ... FETCH

ORDER BY

Order a result set. See for details.

PARTITION

Specifies to the optimizer which partitions are relevant for the query. Other partitions will not be read. See for details.

PROCEDURE

Passes the whole result set to a C Procedure. See and (the only built-in procedure not requiring the server to be recompiled).

SKIP LOCKED

Optimizer Hints

These include , , , , , and .

See for details.

max_statement_time clause

By using in conjunction with , it is possible to limit the execution time of individual queries. For example:

WAIT/NOWAIT

Set the lock wait timeout. See .

Examples

See (Beginner tutorial), or the various sub-articles, for more examples.

See Also

• (Beginner tutorial)

_{This page is licensed: GPLv2, originally from}