Managing & Using MySQL, 2nd Edition

Name

SELECT

Syntax

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] 
column [[AS] alias][, ...]
[INTO {OUTFILE | DUMPFILE} 'filename' delimiters]
[FROM table [[AS] alias]
[USE INDEX (keys)] [IGNORE INDEX (keys)][, ...]
[constraints]]
[UNION [ALL] select substatement]

Description

Retrieves data from a database. The SELECT statement is the primary method of reading data from database tables.

If the DISTINCT keyword is present, only one row of data will be output for every group of rows that is identical. The ALL keyword is the opposite of DISTINCT and displays all returned data. The default behavior is ALL. DISTINCT and DISTINCTROWS are synonyms.

MySQL provides several extensions to the basic ANSI SQL syntax that help modify how your query runs:

HIGH_PRIORITY

Increases the priority with which the query is run, even to the extent of ignoring tables locked for update. You can cause the database to grind to a halt if you use this option with long-running queries.

STRAIGHT_JOIN

If you specify more than one table, MySQL will automatically join the tables so that you can compare values between them. In cases where MySQL does not perform the join in an efficient manner, you can specify STRAIGHT_JOIN to force MySQL to join the tables in the order you enter them in the query.

SQL_BUFFER_RESULT

Forces MySQL to store the result in a temporary table.

SQL_CALC_FOUND_ROWS ...