Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

13.2.7. 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, ...
    [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 Section 13.2.7.2, “UNION Syntax”, and Section 13.2.8, “Subquery Syntax”.

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 Section 13.2.7.1, “JOIN Syntax”.

  • 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 Chapter 12, Functions and Operators.

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 Section A.5.4, “Problems with Column Aliases”.

  • 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 Section 13.2.7.1, “JOIN Syntax”. 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 Section 13.2.7.1, “JOIN Syntax”.

    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 Section 5.2.2, “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 Section 9.2, “Database, Table, Index, Column, and Alias 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 Section 12.11, “Functions and Modifiers for Use with GROUP BY Clauses”.

  • GROUP BY allows a WITH ROLLUP modifier. See Section 12.11.2, “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.)

    The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and allows HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.

    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. 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.1.6, 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 Section 13.2.5, “LOAD DATA INFILE Syntax”.

    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.

  • Note: 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 Section 27.4.1, “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 Section 14.2.10.5, “SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE 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 Section 7.2.1, “Optimizing Queries with EXPLAIN. STRAIGHT_JOIN also can be used in the table_references list. See Section 13.2.7.1, “JOIN Syntax”.

  • 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 Section 12.10.3, “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 Section 5.13, “The MySQL Query Cache”.

  • SQL_NO_CACHE tells MySQL not to store the query result in the query cache. See Section 5.13, “The MySQL Query Cache”. For a query that uses UNION or subqueries, this option effects any SELECT in the query.


 
 
  Published under the terms of the GNU General Public License Design by Interspire