SELECT

The SELECT syntax is as follows:

SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT]    [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE]    [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY] [DISTINCT |    DISTINCTROW | ALL] expression, ... [INTO {OUTFILE |    DUMPFILE} 'file_name' export_options] [FROM table_names [WHERE where_clause] [GROUP BY {unsigned_integer |    field_name | formula} [ASC | DESC], ... [HAVING    where_definition] [ORDER BY {unsigned_integer |    field_name | formula} [ASC | DESC], ...] [LIMIT    [offset,] rows] [PROCEDURE procedure_name] [FOR UPDATE  | LOCK IN SHARE MODE]]

SELECT statements return data from tables. The expression is usually a list of fields (with a function if required), but it can also be a computation or function that has nothing to do with the table fields. For example:

SELECT VERSION();

or as follows:

SELECT 42/10;

Fields can be specified as field_name, table_name.field_name, or database_name.table_name.field_name. The longer forms are required if there's any ambiguity.

The expression can also be given an alias with the keyword AS. For example:

SELECT 22/7 AS about_pi

The expression can be used elsewhere in the statement (but not in the WHERE clause, which is usually determined first).

The table_names clause is a comma-separated list of tables used in the query. You can also use an alias for a table name. For example:

SELECT watts FROM wind_water_solar_power AS n;

You can also control MySQL's index usage if you're unhappy with MySQL's choice (which you can view by using EXPLAIN) with the USE INDEX and IGNORE INDEX clauses after the table name. The syntax is as follows:

table_name [[AS] alias] [USE INDEX (indexlist)] [IGNORE INDEX (indexlist)]

The ORDER BY clause orders the returned results in ascending (default, or using the ASC keyword) or descending (DESC) order. It does not have to use items explicitly returned in the expression. For example:

SELECT team_name FROM results ORDER BY points DESC

The WHERE clause consists of conditions (which can contain functions) that a row needs to adhere to in order to be returned:

SELECT team_name FROM results WHERE points > 10

GROUP BY groups output rows, which are useful when you use an aggregate function. Two non-ANSI MySQL extensions that you can use are ASC or DESC with GROUP BY, and you can also use fields in the expression that are not mentioned in the GROUP BY clauses. For example:

SELECT team_name, team_address, SUM(points) FROM teams GROUP BY team_name DESC

The HAVING clause is also a condition, but it is applied last so it can apply to items you group by. For example:

SELECT team_name, SUM(points) FROM teams GROUP BY team_name HAVING SUM(points) > 20

Do not use it to replace the WHERE clause, as it is will slow down queries.

DISTINCT and its synonym, DISTINCTROW, indicate that the returned row should be unique. ALL (the default) returns all rows, unique or not.

HIGH_PRIORITY (non-ANSI MySQL extension) gives the SELECT a higher priority than any updates.

SQL_BIG_RESULT and SQL_SMALL_RESULT (non-ANSI MySQL extensions) assist the MySQL optimizer by letting it know whether the results returned will be large or small before it begins processing. Both are used with GROUP BY and DISTINCT clauses and usually result in MySQL using a temporary table for greater speed.

SQL_BUFFER_RESULT (non-ANSI MySQL extension) causes MySQL to place the result in a temporary table.

LIMIT takes one or two arguments to limit the number of rows returned. If one argument, it's the maximum number of rows to return; if two, the first is the offset and the second the maximum number of rows to return. If the second argument is –1, MySQL will return all rows from the specified offset until the end. For example, to return from row 2 to the end, use this:

SELECT f1 FROM t1 LIMIT 1,-1

SQL_CALC_FOUND_ROWS causes MySQL to calculate the number of rows that would have been returned if no LIMIT clause existed. This figure can be returned with the SELECT FOUND_ ROWS() function.

SQL_CACHE gets MySQL to store the result in the query cache, and SQL_NO_CACHE causes the result not to be cached. Both are non-ANSI MySQL extensions.

STRAIGHT_JOIN (non-ANSI MySQL extension) causes the optimizer to join the tables in the order in they are listed in the FROM clause, which can speed up queries if tables are joined non-optimally (use EXPLAIN to check this).

SELECT...INTO OUTFILE 'file_name' writes the results into a new file (readable by everyone) on the server. You need to FILE permission to use this. It is the complement of LOAD DATA INFILE, and it uses the same options.

Using INTO DUMPFILE causes MySQL to write one row into the file, without any column or line terminations and without any escaping.

With InnoDB and BDB tables, the FOR UPDATE clause write locks the rows.