Recipe 3.2. Specifying Which Rows to Select

Problem

You want to see only those rows that match certain criteria.

Solution

To specify which rows to return, add a WHERE clause to identify the rows that you want to see, such as customers that live in a particular city or tasks that have a status of "finished."

Discussion

Unless you qualify or restrict a SELECT query in some way, it retrieves every row in your table, which in many cases is a lot more information than you really want to see. To be more precise about which rows to select, provide a WHERE clause that specifies one or more conditions that rows must match.

Conditions can perform tests for equality, inequality, or relative ordering. For some types of data, such as strings, you can use pattern matches. The following statements select columns from rows from the mail table containing srchost values that are exactly equal to the string 'venus' or that begin with the letter 's':

mysql> SELECT t, srcuser, srchost  FROM mail WHERE srchost = 'venus'; +---------------------+---------+---------+ | t                   | srcuser | srchost | +---------------------+---------+---------+ | 2006-05-14 09:31:37 | gene    | venus   | | 2006-05-14 14:42:21 | barb    | venus   | | 2006-05-15 08:50:57 | phil    | venus   | | 2006-05-16 09:00:28 | gene    | venus   | | 2006-05-16 23:04:19 | phil    | venus   | +---------------------+---------+---------+ mysql> SELECT t, srcuser, srchost FROM mail WHERE srchost LIKE 's%'; +---------------------+---------+---------+ | t                   | srcuser | srchost | +---------------------+---------+---------+ | 2006-05-11 10:15:08 | barb    | saturn  | | 2006-05-13 13:59:18 | barb    | saturn  | | 2006-05-14 17:03:01 | tricia  | saturn  | | 2006-05-15 17:35:31 | gene    | saturn  | | 2006-05-19 22:21:51 | gene    | saturn  | +---------------------+---------+---------+ 

The LIKE operator in the previous query performs a pattern match, where % acts as a wildcard that matches any string. Section 5.10 discusses pattern matching further.

A WHERE clause can test multiple conditions and different conditions can test different columns. The following statement finds messages sent by barb to tricia:

mysql> SELECT * FROM mail WHERE srcuser = 'barb' AND dstuser = 'tricia'; +---------------------+---------+---------+---------+---------+-------+ | t                   | srcuser | srchost | dstuser | dsthost | size  | +---------------------+---------+---------+---------+---------+-------+ | 2006-05-11 10:15:08 | barb    | saturn  | tricia  | mars    | 58274 | | 2006-05-13 13:59:18 | barb    | saturn  | tricia  | venus   |   271 | +---------------------+---------+---------+---------+---------+-------+