MySQL Cookbook

Specifying Which Rows to Select

Problem

You don’t want to see all the rows from a table, just some of them.

Solution

Add a WHERE clause to the query that indicates to the server which rows to return.

Discussion

Unless you qualify or restrict a SELECT query in some way, it retrieves every row in your table, which may be a lot more information than you really want to see. To be more precise about the 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 column types such as strings, you can use pattern matches. The following queries select columns from rows containing srchost values that are exactly equal to the string 'venus', that are lexically less than the string 'pluto', or that begin with the letter 's':

mysql> SELECT t, srcuser, srchost  FROM mail WHERE srchost = 'venus';
+---------------------+---------+---------+
| t                   | srcuser | srchost |
+---------------------+---------+---------+
| 2001-05-14 09:31:37 | gene    | venus   |
| 2001-05-14 14:42:21 | barb    | venus   |
| 2001-05-15 08:50:57 | phil    | venus   |
| 2001-05-16 09:00:28 | gene    | venus   |
| 2001-05-16 23:04:19 | phil    | venus   |
+---------------------+---------+---------+
mysql> SELECT t, srcuser, srchost FROM mail WHERE srchost < 'pluto'; +---------------------+---------+---------+ | t | srcuser | srchost | +---------------------+---------+---------+ | 2001-05-12 12:48:13 | tricia | mars | | 2001-05-12 ...