5.2 Restricting a Selection Using WHERE

If you don't specify any criteria for selecting records from a table, a SELECT statement will retrieve every record in the table. This is often more information than you need, particularly for large tables. To be more specific about the rows that are of interest, include a WHERE clause that describes the characteristics of those rows.

A WHERE clause can be as simple or complex as necessary to identify the rows that are relevant for your purposes. For example, to return only those rows from a table personnel that have id values less than 100, it's sufficient to use a WHERE clause that specifies a single condition:

SELECT * FROM personnel WHERE id < 100;

More complex WHERE clauses specify multiple conditions, which may be combined using logical operators such as AND and OR. The following statement returns rows with id values in the range from 10 to 20:

SELECT * FROM t WHERE id >= 10 AND id <= 20;

For testing values in a range, you can also use the BETWEEN operator:

SELECT * FROM t WHERE id BETWEEN 10 AND 20;

Some operators have higher precedence than others. For example, AND has a higher precedence than OR. To control the order of evaluation of terms within a complex expression (or simply to make the evaluation order explicit), you can use parentheses to group expression terms. Consider the following WHERE clause:

WHERE id < 3 AND name = 'Wendell' OR name = 'Yosef'

Because AND has a higher precedence than OR, the preceding expression is equivalent to the following one:

WHERE (id < 3 AND name = 'Wendell') OR name = 'Yosef'

This expression finds all records with an id value less than 3 that also have a name value of 'Wendell', as well as all records with a name value of 'Yosef' (regardless of their id value).

A different placement of parentheses results in a very different meaning:

WHERE id < 3 AND (name = 'Wendell' OR name = 'Yosef')

This expression finds records that have an id value less than 3 and a name value of either 'Wendell' or 'Yosef'.

More information on writing expressions can be found in section 6.1, "Using SQL Expressions and Functions." Detailed descriptions of the operators and functions that you can use in expressions are provided in the MySQL Reference Manual.

It's possible to prevent SELECT statements that might generate a great deal of output from returning more than 1,000 rows. The mysql client supports this feature if you invoke it with the --safe-updates option. For more information, see section 3.2.8, "Using the --safe-updates Option."