DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info) select

Info Catalog (mysql.info) replace (mysql.info) data-manipulation (mysql.info) subqueries
 
 13.2.7 `SELECT' Syntax
 ----------------------
 

Menu

 
* join                         `JOIN' Syntax
* union                        `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, ...
          [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}]
          [PROCEDURE PROCEDURE_NAME(ARGUMENT_LIST)]
          [INTO OUTFILE 'FILE_NAME' EXPORT_OPTIONS
            | INTO DUMPFILE 'FILE_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  union, and
  subqueries.
 
 The most commonly used clauses of `SELECT' statements are these:
 
    * Each SELECT_EXPR indicates a column that you want to retrieve.
      There must be at least one SELECT_EXPR.
 
    * TABLE_REFERENCES indicates the table or tables from which to
      retrieve rows. Its syntax is described in  join.
 
    * 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 MySQL supports, except for aggregate (summary)
      functions. See  functions.
 
 `SELECT' can also be used to retrieve rows computed without reference
 to any table.
 
 For example:
 
      mysql> SELECT 1 + 1;
              -> 2
 
 You are allowed 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 compatibility with some other database servers
 that require a `FROM' clause. MySQL does not require the clause 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 preceding the `FROM' clause.
 
    * 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. 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.
 
    * It is not allowable to use a column alias in a `WHERE' clause,
      because the column value might not yet be determined when the
      `WHERE' clause is executed. See  problems-with-alias.
 
    * 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 
      join.  For each table specified, you can optionally specify an
      alias.
 
           TBL_NAME [[AS] ALIAS]
               [{USE|IGNORE|FORCE} INDEX (KEY_LIST)]
 
      The use of `USE INDEX', `IGNORE INDEX', `FORCE INDEX' to give the
      optimizer hints about how to choose indexes is described in 
      join.
 
      You can use `SET max_seeks_for_key=VALUE' as an alternative way to
      force MySQL to prefer key scans instead of table scans. See 
      server-system-variables.
 
    * 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  legal-names, 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:
 
           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;
 
    * 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.
 
      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;
 
    * MySQL extends the `GROUP BY' clause so that you can also specify
      `ASC' and `DESC' after columns named in the clause:
 
           SELECT a, COUNT(b) FROM test_table GROUP BY a DESC;
 
    * MySQL extends the use of `GROUP BY' to allow selecting fields that
      are not mentioned in the `GROUP BY' clause. If you are not getting
      the results that you expect from your query, please read the
      description of `GROUP BY' found in 
      group-by-functions-and-modifiers.
 
    * `GROUP BY' allows a `WITH ROLLUP' modifier. See 
      group-by-modifiers.
 
    * The `HAVING' clause is applied nearly last, just before items are
      sent to the client, with no optimization. (`LIMIT' is applied after
      `HAVING'.)
 
      Before MySQL 5.0.2, a `HAVING' clause can refer to any column or
      alias named in a SELECT_EXPR in the `SELECT' list or in outer
      subqueries, and to aggregate functions. However, the SQL standard
      requires that `HAVING' must reference only columns in the `GROUP
      BY' clause or columns used in aggregate functions. To accommodate
      both standard SQL and the MySQL-specific behavior of being able to
      refer columns in the `SELECT' list, MySQL 5.0.2 and up allows
      `HAVING' to refer to columns in the `SELECT' list, columns in the
      `GROUP BY' clause, columns in outer subqueries, and to aggregate
      functions.
 
      For example, the following statement works in MySQL 5.0.2 but
      produces an error for earlier versions:
 
           mysql> SELECT COUNT(*) FROM t GROUP BY col1 HAVING col1 = 2;
 
      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.)
 
    * 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 non-negative integer
      constants (except when using prepared statements).
 
      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 (supported as of
      MySQL version 5.0.7). The following statements will return one row
      from the `tbl' table:
 
           SET @a=1;
           PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
           EXECUTE STMT USING @a;
 
      The following statements will return the second to sixth row 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 the `LIMIT
      ROW_COUNT OFFSET OFFSET' syntax.
 
    * The `SELECT ... INTO OUTFILE 'FILE_NAME'' form of `SELECT' writes
      the selected rows to a file. The file is created on the server
      host, so you must have the `FILE' privilege to use this syntax.
      FILE_NAME cannot be an existing file, which among other things
      prevents files such as `/etc/passwd' and database tables from
      being destroyed. As of MySQL 5.0.19, the
      `character_set_filesystem' system variable controls the
      interpretation of the filename.
 
      The `SELECT ... INTO OUTFILE' statement is intended primarily to
      let you very quickly dump a table to a text file on the server
      machine. If you want to create the resulting file on some client
      host other than the server host, you cannot use `SELECT ... INTO
      OUTFILE'. In that case, you should instead use a command such as
      `mysql -e "SELECT ..." > FILE_NAME' to generate the file on the
      client host.
 
      `SELECT ... INTO OUTFILE' is the complement of `LOAD DATA INFILE';
      the syntax for the EXPORT_OPTIONS part of the statement consists
      of the same `FIELDS' and `LINES' clauses that are used with the
      `LOAD DATA INFILE' statement. See  load-data.
 
      `FIELDS ESCAPED BY' controls how to write special characters. If
      the `FIELDS ESCAPED BY' character is not empty, it is used as a
      prefix that precedes following characters on output:
 
         * The `FIELDS ESCAPED BY' character
 
         * The `FIELDS [OPTIONALLY] ENCLOSED BY' character
 
         * The first character of the `FIELDS TERMINATED BY' and `LINES
           TERMINATED BY' values
 
         * ASCII `NUL' (the zero-valued byte; what is actually written
           following the escape character is ASCII ‘`0'’, not a
           zero-valued byte)
 
      The `FIELDS TERMINATED BY', `ENCLOSED BY', `ESCAPED BY', or `LINES
      TERMINATED BY' characters _must_ be escaped so that you can read
      the file back in reliably. ASCII `NUL' is escaped to make it
      easier to view with some pagers.
 
      The resulting file does not have to conform to SQL syntax, so
      nothing else need be escaped.
 
      If the `FIELDS ESCAPED BY' character is empty, no characters are
      escaped and `NULL' is output as `NULL', not `\N'. It is probably
      not a good idea to specify an empty escape character, particularly
      if field values in your data contain any of the characters in the
      list just given.
 
      Here is an example that produces a file in the comma-separated
      values (CSV) format used by many programs:
 
           SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
             FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
             LINES TERMINATED BY '\n'
             FROM test_table;
 
    * If you use `INTO DUMPFILE' instead of `INTO OUTFILE', MySQL writes
      only one row into the file, without any column or line termination
      and without performing any escape processing. This is useful if
      you want to store a `BLOB' value in a file.
 
    * * Any file created by `INTO OUTFILE' or `INTO DUMPFILE' is
      writable by all users on the server host. The reason for this is
      that the MySQL server cannot create a file that is owned by anyone
      other than the user under whose account it is running. (You should
      _never_ run `mysqld' as `root' for this and other reasons.) The
      file thus must be world-writable so that you can manipulate its
      contents.
 
    * The `SELECT' syntax description at the beginning this section
      shows the `INTO' clause near the end of the statement. It is also
      possible to use `INTO OUTFILE' or `INTO DUMPFILE' immediately
      preceding the `FROM' clause.
 
    * A `PROCEDURE' clause names a procedure that should process the
      data in the result set. For an example, see 
      procedure-analyse.
 
    * If you use `FOR UPDATE' with a storage engine that uses page or
      row locks, rows examined by the query are write-locked until the
      end of the current transaction. Using `LOCK IN SHARE MODE' sets a
      shared lock that allows other transactions to read the examined
      rows but not to update or delete them. See 
      innodb-locking-reads.
 
 Following the `SELECT' keyword, you can use a number of options that
 affect the operation of the statement.
 
 The `ALL', `DISTINCT', and `DISTINCTROW' options specify whether
 duplicate rows should be returned. If none of these options are given,
 the default is `ALL' (all matching rows are returned). `DISTINCT' and
 `DISTINCTROW' are synonyms and specify removal of duplicate rows from
 the result set.
 
 `HIGH_PRIORITY', `STRAIGHT_JOIN', and options beginning with `SQL_' are
 MySQL extensions to standard SQL.
 
    * `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.
 
      `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
      non-optimal order. See  explain.  `STRAIGHT_JOIN' also can
      be used in the TABLE_REFERENCES list. See  join.
 
    * `SQL_BIG_RESULT' can be used with `GROUP BY' or `DISTINCT' to tell
      the optimizer that the result set has many rows. In this case,
      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.
 
    * `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.
 
    * `SQL_SMALL_RESULT' can be used with `GROUP BY' or `DISTINCT' to
      tell the optimizer that the result set is small. In this case,
      MySQL uses fast temporary tables to store the resulting table
      instead of using sorting. This should not normally be needed.
 
    * `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  information-functions.
 
    * `SQL_CACHE' tells MySQL to store the query result in the query
      cache if you are using a `query_cache_type' value of `2' or
      `DEMAND'. For a query that uses `UNION' or subqueries, this option
      effects any `SELECT' in the query.  See  query-cache.
 
    * `SQL_NO_CACHE' tells MySQL not to store the query result in the
      query cache. See  query-cache. For a query that uses
      `UNION' or subqueries, this option effects any `SELECT' in the
      query.
 
Info Catalog (mysql.info) replace (mysql.info) data-manipulation (mysql.info) subqueries
automatically generated byinfo2html