SELECT

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)]
      [{USE|FORCE|IGNORE} INDEX [FOR {JOIN|ORDER BY|GROUP BY}] ([index_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] [WAIT n | NOWAIT] ] ]

export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

Description

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

• Each select_expr expression indicates a column or data that you want to retrieve. You must have at least one select expression. See Select Expressions 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 JOIN for details. If no table is involved, FROM DUAL can be specified.

• Each table can also be specified as db_name.tabl_name. Each column can also be specified as tbl_name.col_name or even db_name.tbl_name.col_name. This allows to write queries which involve multiple databases. See Identifier Qualifiers for syntax details.

• 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 MariaDB supports, except for aggregate (summary) functions. See Functions and Operators and Functions and Modifiers for use with GROUP BY (aggregate).

• Use the ORDER BY clause to order the results.

• Use the LIMIT clause allows you to restrict the results to only a certain number of rows, optionally with an offset.

• Use the GROUP BY and HAVING clauses to group rows together when they have columns or computed values in common.

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

Optimizer hints

Some options are available to affect the execution plan.

HIGH PRIORITY

HIGH_PRIORITY gives the statement a higher priority. If the table is locked, high priority SELECTs will be executed as soon as the lock is released, even if other statements are queued. HIGH_PRIORITY applies only if the storage engine only supports table-level locking (MyISAM, MEMORY, MERGE). See HIGH_PRIORITY and LOW_PRIORITY clauses for details.

SQL_CACHE / SQL_NO_CACHE

If the query_cache_type system variable is set to 2 or DEMAND, and the current statement is cacheable, SQL_CACHE causes the query to be cached and SQL_NO_CACHE causes the query not to be cached. For UNIONs, SQL_CACHE or SQL_NO_CACHE should be specified for the first query. See also The Query Cache for more detail and a list of the types of statements that aren't cacheable.

SQL_BUFFER_RESULT

SQL_BUFFER_RESULT forces the optimizer to use a temporary table to process the result. This is useful to free locks as soon as possible.

SQL_SMALL_RESULT / SQL_BIG_RESULT

SQL_SMALL_RESULT and SQL_BIG_RESULT tell the optimizer whether the result is very big or not. Usually, GROUP BY and DISTINCT operations are performed using a temporary table. Only if the result is very big, using a temporary table is not convenient. The optimizer automatically knows if the result is too big, but you can force the optimizer to use a temporary table with SQL_SMALL_RESULT, or avoid the temporary table using SQL_BIG_RESULT.

STRAIGHT_JOIN

STRAIGHT_JOIN applies to the JOIN queries, and tells the optimizer that the tables must be read in the order they appear in the SELECT. For const and system table this options is sometimes ignored.

SQL_CALC_FOUND_ROWS

SQL_CALC_FOUND_ROWS is only applied when using the LIMIT clause. If this option is used, MariaDB will count how many rows would match the query, without the LIMIT clause. That number can be retrieved in the next query, using FOUND_ROWS().

USE/FORCE/IGNORE INDEX

USE INDEX, FORCE INDEX and IGNORE INDEX constrain the query planning to a specific index.

For further information about some of these options, see How to force query plans.

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 functions and operators.
• * to select all columns from all tables in the FROM clause.
• tbl_name.* to select all columns from just the table tbl_name.

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 grouping functions in any of the select expressions, all rows in your results will be implicitly grouped, as if you had used GROUP BY NULL.

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 resultset, use the DISTINCT option. DISTINCTROW is a synonym for DISTINCT. See also COUNT DISTINCT.

INTO

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

• SELECT INTO OUTFILE - formatting and writing the result to an external file.
• SELECT INTO DUMPFILE - binary-safe writing of the unformatted results to an external file.
• SELECT INTO Variable - selecting and setting variables.

The reverse of SELECT INTO OUTFILE is LOAD DATA.

PROCEDURE clause

The PROCEDURE clause passes the whole resultset to a Procedure which will process it. Those Procedures are not Stored Procedures. They can only be written in C language, so it is necessary to recompile the server.

Currently, the only available procedure is ANALYSE, which examines the resultset and suggests the optimal datatypes for each column. It is defined in the sql/sql_analyse.cc file, and can be used as an example to create more Procedures.

This clause cannot be used in a view's definition.

LOCK IN SHARE MODE and FOR UPDATE clauses

InnoDB/XtraDB supports row-level locking. SELECTed rows can be locked using LOCK IN SHARE MODE or FOR UPDATE. In both cases, a lock is acquired on the rows read by the query, and it will be released when the current transaction is committed.

When LOCK IN SHARE MODE is specified, the statement will wait until all transactions that have modified the rows are committed. Then, a write lock is acquired. All transactions can read the rows, but if they want to modify them, they have to wait until your transaction is committed.

FOR UPDATE only applies when autocommit is set to 0 or the SELECT is enclosed in a transaction. A lock is acquired on the rows, and other transactions are prevented from writing the rows, acquire locks, and from reading them (unless their isolation level is READ UNCOMMITTED).

If autocommit is set to 1, LOCK IN SHARE MODE and FOR UPDATE clauses have no effect.

If the isolation level is set to SERIALIZABLE, all plain SELECT statements are converted to SELECT ... LOCK IN SHARE MODE.

See also

• SELECT INTO OUTFILE
• WITH (common table expressions) - Allows you to refer to a subquery as a temporary table.

SET STATEMENT max_statement_time=100 FOR 
  SELECT field1 FROM table_name ORDER BY field1;