MySQL Cookbook

3.7 Specifying Which Rows to Select

3.7.1 Problem

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

3.7.2 Solution

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

3.7.3 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 15:02:49 | phil    | mars    |
| 2001-05-14 11:52:17 | phil    | mars    |
| 2001-05-15 07:17:48 | gene    | mars    |
| 2001-05-15 10:25:52 | gene    | mars    |
| 2001-05-17 12:49:23 | phil    | mars    |
+---------------------+---------+---------+
mysql> SELECT t, srcuser, srchost FROM mail WHERE srchost LIKE 's%';
+---------------------+---------+---------+
| t                   | srcuser | srchost |
+---------------------+---------+---------+
| 2001-05-11 10:15:08 | barb    | saturn  |
| 2001-05-13 13:59:18 | barb    | saturn  |
| 2001-05-14 17:03:01 | tricia  | saturn  |
| 2001-05-15 17:35:31 | gene    | saturn  |
| 2001-05-19 22:21:51 | gene    | saturn  |
+---------------------+---------+---------+

WHERE clauses can test multiple conditions. The following statement looks for rows where the srcuser column has any of three different values. (It asks the question, "When did gene, barb, or phil send mail?"):

mysql> SELECT t, srcuser, dstuser FROM mail
    -> WHERE srcuser = 'gene' OR srcuser = 'barb' OR srcuser = 'phil';
+---------------------+---------+---------+
| t                   | srcuser | dstuser |
+---------------------+---------+---------+
| 2001-05-11 10:15:08 | barb    | tricia  |
| 2001-05-12 15:02:49 | phil    | phil    |
| 2001-05-13 13:59:18 | barb    | tricia  |
| 2001-05-14 09:31:37 | gene    | barb    |
...

Queries such as the preceding one that test a given column to see if it has any of several different values often can be written more easily by using the IN( ) operator. IN( ) is true if the column is equal to any value in its argument list:

mysql> SELECT t, srcuser, dstuser FROM mail
    -> WHERE srcuser IN ('gene','barb','phil');
+---------------------+---------+---------+
| t                   | srcuser | dstuser |
+---------------------+---------+---------+
| 2001-05-11 10:15:08 | barb    | tricia  |
| 2001-05-12 15:02:49 | phil    | phil    |
| 2001-05-13 13:59:18 | barb    | tricia  |
| 2001-05-14 09:31:37 | gene    | barb    |
...

Different conditions can test different columns. This query finds messages sent by barb to tricia:

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

Comparisons need only be legal syntactically; they need not make any sense semantically. The comparison in the following query doesn't have a particularly obvious meaning, but MySQL will happily execute it:^[1]

^[1] If you try issuing the query to see what it returns, how do you account for the result?

SELECT * FROM mail WHERE srcuser + dsthost < size

Are Queries That Return No Rows Failed Queries? If you issue a SELECT statement and get no rows back, has the query failed? It depends. If the lack of a result set is due to a problem such as that the statement is syntactically invalid or refers to nonexisting tables or columns, the query did indeed fail, because it could not even be executed. In this case, some sort of error condition should occur and you should investigate why your program is attempting to issue a malformed statement. If the query executes without error but returns nothing, it simply means that the query's WHERE clause matched no rows: mysql> SELECT * FROM mail WHERE srcuser = 'no-such-user'; Empty set (0.01 sec) This is not a failed query. It ran successfully and produced a result; the result just happens to be empty because no rows have a srcuser value of no-such-user.

Columns need not be compared to literal values. You can test a column against other columns. Suppose you have a cd table lying around that contains year, artist, and title columns:^[2]

^[2] It's not unlikely you'll have such a table if you've been reading other database books. Many of these have you go through the exercise of creating a database to keep track of your CD collection, a scenario that seems to rank second in popularity only to parts-and-suppliers examples.

mysql> SELECT year, artist, title FROM cd;
+------+-----------------+-----------------------+
| year | artist          | title                 |
+------+-----------------+-----------------------+
| 1990 | Iona            | Iona                  |
| 1992 | Charlie Peacock | Lie Down in the Grass |
| 1993 | Iona            | Beyond These Shores   |
| 1987 | The 77s         | The 77s               |
| 1990 | Michael Gettel  | Return                |
| 1989 | Richard Souther | Cross Currents        |
| 1996 | Charlie Peacock | strangelanguage       |
| 1982 | Undercover      | Undercover            |
...

If so, you can find all your eponymous CDs (those with artist and title the same) by performing a comparison of one column within the table to another:

mysql> SELECT year, artist, title FROM cd WHERE artist = title;
+------+------------+------------+
| year | artist     | title      |
+------+------------+------------+
| 1990 | Iona       | Iona       |
| 1987 | The 77s    | The 77s    |
| 1982 | Undercover | Undercover |
+------+------------+------------+

A special case of within-table column comparison occurs when you want to compare a column to itself rather than to a different column. Suppose you collect stamps and list your collection in a stamp table that contains columns for each stamp's ID number and the year it was issued. If you know that a particular stamp has an ID number 42 and want to use the value in its year column to find the other stamps in your collection that were issued in the same year, you'd do so by using year-to-year comparison—in effect, comparing the year column to itself:

mysql> SELECT stamp.* FROM stamp, stamp AS stamp2
    -> WHERE stamp.year = stamp2.year AND stamp2.id = 42 AND stamp.id != 42;
+-----+------+-------------------------+
| id  | year | description             |
+-----+------+-------------------------+
|  97 | 1987 | 1-cent transition stamp |
| 161 | 1987 | aviation stamp          |
+-----+------+-------------------------+

This kind of query involves a self-join, table aliases, and column references that are qualified using the table name. But that's more than I want to go into here. Those topics are covered in Chapter 12.