Using WHERE to Filter MySQL Data

In previous chapters we have performed various data retrievals using the SELECT statement and also looked at how to sort the results of a retrieval. One of the most important requirements when retrieving data is to be able to filter that data so that the only rows returned are those which match specified search criteria. This is achieved in MySQL using the WHERE clause in conjunction with the SELECT statement.

The Basics of the WHERE Clause

The syntax of the WHERE clause is as follows:

SELECT column(s) FROM table WHERE column = value

The first part of the statement looks very much like any regular SELECT statement. The WHERE clause changes things, however. After the WHERE we need to specify the couln on which the search criteria are to based, followed by an operator which specifies the type of comparison to perform (in this case we are looking for equality) and finally the value to which the column must match.

Assuming we have a database table containing prod_code, prod_name, prod_desc and prod_price columns, a standard SELECT statement might generate following output:

mysql> SELECT * FROM products;
+-----------+----------------------------+-----------------------------+------------+
| prod_code | prod_name                  | prod_desc                   | prod_price |
+-----------+----------------------------+-----------------------------+------------+
|         1 | WildTech 250Gb 1700        | SATA Disk Drive             |        120 |
|         2 | Moto Razr                  | Mobile Phone                |        200 |
|         3 | Microsoft 10-20 Keyboard   | Ergonmoc Keyboard           |         49 |
|         4 | EasyTech Mouse 7632        | Cordless Mouse              |         49 |
|         5 | Dell XPS 400               | Desktop PC                  |        999 |
|         6 | Buffalo AirStation Turbo G | Wireless Ethernet Bridge    |         60 |
|         7 | Apple iPod Touch           | Portable Music/Movie Player |        199 |
|         8 | Apple iPhone 8Gb           | Smart Phone                 |        399 |
+-----------+----------------------------+-----------------------------+------------+
8 rows in set (0.00 sec)

This is fine if we actually want a list of every row in our table. It is more likely, however, that we really want a subset of the data in the table. For example, if we wanted to retrieve only the rows where the price matches a certain value we might construct a SQL statement as follows:

SELECT * FROM products WHERE prod_price = 49;

Such a statement would result in the following output when executed on our sample database:

+-----------+--------------------------+-------------------+------------+
| prod_code | prod_name                | prod_desc         | prod_price |
+-----------+--------------------------+-------------------+------------+
|         3 | Microsoft 10-20 Keyboard | Ergonmoc Keyboard |         49 |
|         4 | EasyTech Mouse 7632      | Cordless Mouse    |         49 |
+-----------+--------------------------+-------------------+------------+
2 rows in set (0.00 sec)

Now that we have looked at the equality operator (=) we can now explore the other WHERE clause operators.

Comparison Operators

So far we have only reviewed the WHERE clause equality operator (=). This is just one of a number of operators that can be used to refine a data retrieval:

Operator	Description
=	Equal to
!=	Not equal to
<>	Not equal to
>	Greater than
<	Less than
<=	Less than or equal to
>=	Greater than or equal to
BETWEEN	Falls between two values