SELECT Statement Keywords

This MySQL statement, the SELECT is used to retrieve and display data from tables within a MySQL database. This page of our MySQL documentation lists the keywords that may be used with SELECT.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

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]
{*|column|expression}[, ...]
FROM table[, ...]
[WHERE condition] [other clauses] [options]

Explanation

The syntax above for this MySQL statement shows the keywords that may be used with SELECT. Within the text below are descriptions of each.

Between the initial SELECT keyword and list of columns in a MySQL table and expressions, several keywords may be given. They are shown in the preceding syntax, with the other components of the statement abbreviated.

When a WHERE clause is used with the SELECT statement, rows in the results may contain duplicate data. If you want all rows that meet the selection conditions to be displayed, you may include the ALL keyword. This is the default, so it's not necessary to give this keyword. If you want to display only the first occurrence of a row, include the DISTINCT keyword or its synonym DISTINCTROW.

Examples

SELECT DISTINCT dept 
FROM employees;

This statement will list the names of all departments for which we have employees listed in the employees table. Even though there are several employees in the same department, it will list only one row for each department.

By deafult, any UPDATE statements that are issued have priority over SELECT statements submitted by other client sessions at the same time; the updates are run first. To give a particular SELECT statement higher priority than any UPDATE statements, use the HIGH_PRIORITY keyword.

Multiple tables may be selected with the SELECT statement. The column on which they should be joined is given with the WHERE clause or the JOIN clause. The JOIN clause is described separately. For the purposes of this section, you just need to know that in order to optimize retrieval, MySQL might not join tables in the order that they are listed in the SQL statement. To insist on joining in the order given, you must use the STRAIGHT_JOIN keyword.

When you know that the results of a SELECT statement using the DISTINCT keyword or the GROUP BY clause (discussed later) will be small, you can use the SQL_SMALL_RESULT keyword. This will cause MySQL to use temporary tables, with a key based on the GROUP BY clause elements, to sort the results and possibly make for faster data retrieval. If you expect the results to be large, you can use the SQL_BIG_RESULT keyword. This will cause MySQL to use temporary tables on the filesystem. Regardless of whether you use DISTINCT or GROUP BY, the SQL_BUFFER_RESULT keyword may be given for any SELECT statement to have MySQL use a temporary table to buffer the results. You can use only one of the SQL_*_RESULT keywords in each statement.

If the MySQL server is not using the query cache by default, you can force its use by including the SQL_CACHE keyword. If the server does use the query cache by default, you can use the SQL_NO_CACHE to instruct MySQL not to use the cache for this particular SELECT statement. To determine whether the server uses query cache by default, enter SHOW VARIABLES LIKE 'query_cache_type';. A value of ON indicates that it is in use.

The last keyword available is SQL_CALC_FOUND_ROWS, which counts the number of rows that meet the conditions of the statement. This is not affected by a LIMIT clause. The results of this count must be retrieved in a separate SELECT statement with the FOUND_ROWS( ) function.

SELECT SQL_CALC_FOUND_ROWS 
name_first, name_last, telephone_home,
DATEDIFF(now( ), last_review)
AS 'Days Since Last Review'
FROM employees 
WHERE dept = 'sales'
ORDER BY last_review DESC
LIMIT 10;

SELECT FOUND_ROWS();

The first MySQL statement retrieves a list of sales people to review, limited to the ten to have gone the longest without a performance review. The second gets a count of how many employees there are to review in the sales department.