FILTERING DATA

Database tables usually contain large amounts of data, and you seldom need to retrieve all the rows in a table. More often than not, you'll want to extract a subset of the table's data as needed for specific operations or reports. Retrieving just the data you want involves specifying search criteria, also known as a filter condition.

Within a SELECT statement, data is filtered by specifying search criteria in the WHERE clause. The WHERE clause is specified right after the table name.

Using the WHERE Clause

Database tables usually contain large amounts of data, and you seldom need to retrieve all the rows in a table. More often than not, you'll want to extract a subset of the t able's data as needed for specific operations or reports. Retrieving just the data you want involves specifying search criteria, also known as a filter condition.

Within a SELECT statement, data is filtered by specifying search criteria in the WHERE clause. The WHERE clause is specified right after the table name (the FROM clause) as follows:

• Input

SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;

• Analysis

This statement retrieves two columns from the products table, but instead of returning all rows, only rows with a prod_price value of 2.50 are returned, as follows:

• Output

+---------------+------------+
| prod_name     | prod_price |
+---------------+------------+
| Carrots       |       2.50 |
| TNT (1 stick) |       2.50 |
+---------------+------------+

This example uses a simple equality test: It checks to see if a column has a specified value, and it filters the data accordingly. But SQL enables you to do more than just test for equality.

Tip

SQL Versus Application Filtering Data can also be filtered at the application level. To do this, the SQL SELECT statement retrieves more data than is actually required for the client application, and the client code loops through the returned data to extract just the needed rows.

As a rule, this practice is strongly discouraged. Databases are optimized to perform filtering quickly and efficiently. Making the client application (or development language) do the database's job dramatically impacts application performance and creates applications that cannot scale properly. In addition, if data is filtered at the client, the server has to send unneeded data across the network connections, resulting in a waste of network bandwidth resources.
 

Caution

WHERE Clause Position When using both ORDER BY and WHERE clauses, make sure ORDER BY comes after the WHERE; otherwise an error will be generated. (See Tutorial 10, "Sorting Retrieved Data," for more information on using ORDER BY.)

The WHERE Clause Operators

The first WHERE clause we looked at tests for equality determining if a column contains a specific value. MySQL supports a whole range of conditional operators, some of which are listed in Table 6.1 bellow.

Table 6.1. WHERE Clause Operators

 Checking Against a Single Value

We have already seen an example of testing for equality. Here's one more:

• Input

SELECT prod_name, prod_price
FROM products
WHERE prod_name = 'fuses';

• Output

+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| Fuses     |       3.42 |
+-----------+------------+

• Analysis

Checking for WHERE prod_name = 'fuses' returned a single row with a value of Fuses. By default, MySQL is not case sensitive when performing matches, and so fuses and Fuses matched.

Now look at a few examples to demonstrate the use of other operators.

This first example lists all products that cost less than 10:

• Input

SELECT prod_name, prod_price
FROM products
WHERE prod_price < 10;

• Output

+---------------+------------+
| prod_name     | prod_price |
+---------------+------------+
| .5 ton anvil  |       5.99 |
| 1 ton anvil   |       9.99 |
| Carrots       |       2.50 |
| Fuses         |       3.42 |
| Oil can       |       8.99 |
| Sling         |       4.49 |
| TNT (1 stick) |       2.50 |
+---------------+------------+

This next statement retrieves all products costing 10 or less (resulting in two additional matches):

• Input

SELECT prod_name, prod_price
FROM products
WHERE prod_price <= 10;

• Output

+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| .5 ton anvil   |       5.99 |
| 1 ton anvil    |       9.99 |
| Bird seed      |      10.00 |
| Carrots        |       2.50 |
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Sling          |       4.49 |
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+

Checking for Nonmatches

This next example lists all products not made by vendor 1003:

• Input

SELECT vend_id, prod_name
FROM products
WHERE vend_id <> 1003;

• Output

+---------+--------------+
| vend_id | prod_name    |
+---------+--------------+
|    1001 | .5 ton anvil |
|    1001 | 1 ton anvil  |
|    1001 | 2 ton anvil  |
|    1002 | Fuses        |
|    1005 | JetPack 1000 |
|    1005 | JetPack 2000 |
|    1002 | Oil can      |
+---------+--------------+

Tip

When to Use Quotes If you look closely at the conditions used in the examples' WHERE clauses, you will notice that some values are enclosed within single quotes (such as 'fuses' used previously), and others are not. The single quotes are used to delimit strings. If you are comparing a value against a column that is a string data type, the delimiting quotes are required. Quotes are not used to delimit values used with numeric columns.

The following is the same example, except this one uses the != operator instead of <>:

• Input

SELECT vend_id, prod_name
FROM products
WHERE vend_id != 1003;

Checking for a Range of Values

To check for a range of values, you can use the BETWEEN operator. Its syntax is a little different from other WHERE clause operators because it requires two values: the beginning and end of the range. The BETWEEN operator can be used, for example, to check for all products that cost between 5 and 10 or for all dates that fall between specified start and end dates.

The following example demonstrates the use of the BETWEEN operator by retrieving all products with a price between 5 and 10:

• Input

SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;

• Output

+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| .5 ton anvil   |       5.99 |
| 1 ton anvil    |       9.99 |
| Bird seed      |      10.00 |
| Oil can        |       8.99 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+

• Analysis

As seen in this example, when BETWEEN is used, two values must be specified the low end and high end of the desired range. The two values must also be separated by the AND keyword. BETWEEN matches all the values in the range, including the specified range start and end values.

Checking for No Value

When a table is created, the table designer can specify whether individual columns can contain no value. When a column contains no value, it is said to contain a NULL value.

New Term

NULL No value, as opposed to a field containing 0, or an empty string, or just spaces.

The SELECT statement has a special WHERE clause that can be used to check for columns with NULL values the IS NULL clause. The syntax looks like this:

• Input

SELECT prod_name
FROM products
WHERE prod_price IS NULL;

This statement returns a list of all products that have no price (an empty prod_price field, not a price of 0), and because there are none, no data is returned. The customers table, however, does contain columns with NULL values the cust_email column contains NULL if a customer has no email address on file:

• Input

SELECT cust_id
FROM customers
WHERE cust_email IS NULL;

• Output

+---------+
| cust_id |
+---------+
|   10002 |
|   10005 |
+---------+

Caution

NULL and Nonmatches You might expect that when you filter to select all rows that do not have a particular value, rows with a NULL will be returned. But they will not. Because of the special meaning of unknown, the database does not know whether they match, and so they are not returned when filtering for matches or when filtering for nonmatches.

When filtering data, make sure to verify that the rows with a NULL in the filtered column are really present in the returned data.

 Summary

In this tutorial, you learned how to filter returned data using the SELECT statement's WHERE clause. You learned how to test for equality, non equality, greater than and less than, value ranges, and NULL values.

[Next]