Using WHERE to Filter MySQL Data
| Previous | Table of Contents | Next |
| Sorting Data Retrieved from a MySQL Database | Advanced MySQL Data Filtering - AND, OR, NOT and IN |
| Purchase and download the full PDF and ePub versions of this MySQL eBook for only $9.99
|
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 column on which the search criteria are to be 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 | Ergonomic 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 | Ergonomic 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
| 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 x AND y | Falls between the two values x and y |
We have already used the equality (=) operator to check for numerical equality. We can also use this operator to evaluate other data types, for example we can check for equality of strings. Suppose we need to find the price and product description of an 8Gb iPhone:
SELECT prod_desc, prod_price FROM products WHERE prod_name = 'Apple iPhone 8Gb';
The above statement would generate the following output on execution:
+-------------+------------+ | prod_desc | prod_price | +-------------+------------+ | Smart Phone | 399 | +-------------+------------+ 1 row in set (0.00 sec)
Similarly, it is possible to list all products below a specified price threshold using the less than (<) operator:
mysql> SELECT * from products WHERE prod_price < 200; +-----------+----------------------------+-----------------------------+------------+ | prod_code | prod_name | prod_desc | prod_price | +-----------+----------------------------+-----------------------------+------------+ | 1 | WildTech 250Gb 1700 | SATA Disk Drive | 120 | | 3 | Microsoft 10-20 Keyboard | Ergonomic Keyboard | 49 | | 4 | EasyTech Mouse 7632 | Cordless Mouse | 49 | | 6 | Buffalo AirStation Turbo G | Wireless Ethernet Bridge | 60 | | 7 | Apple iPod Touch | Portable Music/Movie Player | 199 | +-----------+----------------------------+-----------------------------+------------+ 5 rows in set (0.00 sec)
We can also retrieve data rows where a value does not match certain criteria. The following example will retrieve all rows where the price is not equal to (!=) 49:
SELECT * from products WHERE prod_price != 49;
Checking for NULL Values
In addition to being able to check for the value of a column, it is also possible to check for columns that contain no value. Columns that have no value are said to contain NULL values. Not surprisingly, therefore, we can check for an empty column by looking for equality with NULL (although pay careful attention to the fact that we must now use IS in place of the = sign):
SELECT * FROM products WHERE prod_name IS NULL;
Any rows which do not contain a product name will be retrieved by the above SELECT statement.
Searching within Range Values
In the table above the BETWEEN...AND operator was listed. The purpose of this operator is to select rows which fall between specified upper and lower ranges. For example, we can list all products in our table with a price BETWEEN 100 AND 200:
mysql> SELECT * from products WHERE prod_price BETWEEN 100 AND 200; +-----------+---------------------+-----------------------------+------------+ | prod_code | prod_name | prod_desc | prod_price | +-----------+---------------------+-----------------------------+------------+ | 1 | WildTech 250Gb 1700 | SATA Disk Drive | 120 | | 2 | Moto Razr | Mobile Phone | 200 | | 7 | Apple iPod Touch | Portable Music/Movie Player | 199 | +-----------+---------------------+-----------------------------+------------+ 3 rows in set (0.02 sec)
Summary
In this chapter we have looked at the basics of filtering retrieved data from a database table. In the next chapter we will look at more advanced forms of data filtering.
| Purchase and download the full PDF and ePub versions of this MySQL eBook for only $9.99
|
| Previous | Table of Contents | Next |
| Sorting Data Retrieved from a MySQL Database | Advanced MySQL Data Filtering - AND, OR, NOT and IN |
