Chapter 5
Filtering Retrieved Records
Introduction
In this chapter, you will learn how to create conditionals via the WHERE clause. You will also learn how to use the comparison and logical operators to further refine the filtering of data within the recordsets.
Definitions
Comparison operators — Used to perform comparisons among expressions.
Expression — Any data type that returns a value.
Logical operators — Used to test for the truth of some condition.
WHERE clause — Used to filter retrieved records.
Wildcard characters — Special characters used to match parts of a value.
The WHERE Clause
Recall that a clause is a segment of an SQL statement that assists in the selection and manipulation of data. The WHERE clause is yet another clause commonly used in the SELECT statement. It is used to filter retrieved records.
Look at the following syntax for the WHERE clause:
WHERE [Search Condition];
The preceding syntax uses the WHERE keyword to specify a specific search condition. Field names and operators are used in the WHERE clause to create search conditions. The WHERE clause is an extension of one of the most basic Access query elements — the filter. To see an example of this, create a query in Design mode and set one of the fields in the query grid. Next set a criterion. Refer to Figure 5-1.
Figure 5-1. Query Design view
Now change the layout to SQL view to see the SQL representation of the query grid, as shown in Figure 5-2.
Figure 5-2. SQL view
As soon as the criterion was added, Access inserted the WHERE clause. One thing you will note about the SQL builder in Access is its extensive use of parentheses. They are optional and can be ignored when building SQL queries. In fact, if you were to delete all the parentheses in this query and rerun it, you will get exactly the same result as if you ran it directly from the query grid.
Note: Access can be a bit frustrating at times. If you delete the parentheses, convert the query back to Design view, then reconvert it to SQL view, the parentheses reappear.
There are several different operators that can be used in the WHERE clause. In this chapter, we will discuss two categories of operators commonly used in the WHERE clause: comparison and logical.
Comparison Operators
The comparison operators are used to perform comparisons among expressions. An expression is any data type that returns a value. Table 5-1 shows the comparison operator symbols used in Microsoft Access.
Table 5-1. Comparison operator symbols
Name | Symbol |
Greater Than | > |
Greater Than or Equal To | > = |
Equal | = |
Less Than | < |
Less Than or Equal To | < = |
Not Equal | < > |
Table 5-2 shows additional comparison operators that can be used in the WHERE clause.
Table 5-2. Additional comparison operators
Operator | Description |
BETWEEN | Used to determine whether a value of an expression falls within a specified range of values. |
IN | Used to match conditions in a list of expressions. |
LIKE | Used to match patterns in data. |
IS NULL | Used to determine if a field contains data. |
IS NOT NULL | Used to determine if a field does not contain data. |
Logical Operators
Logical operators are used to test for the truth of some condition. Table 5-3 describes each of the logical operators.
Table 5-3. Logical operators
Operator | Description |
AND | Requires both expressions on either side of the AND operator to be true in order for data to be returned. |
OR | Requires at least one expression on either side of the OR operator to be true in order for data to be returned. |
NOT | Used to match any condition opposite of the one defined. |
Operator Precedence
When multiple operators are used in the WHERE clause, operator precedence determines the order in which operations are performed. The following list shows the order of evaluation among operators from the highest level of binding (the operators that are performed first) to the lowest level of binding (those that are performed last).
=, >, <, >=, <=, <>
AND, OR
NOT
BETWEEN, IN, LIKE, IS NULL
If two operators in an expression have the same operator precedence level, they will be evaluated from left to right. Since parentheses have a higher precedence level than all operators, parentheses can be used to override defined precedence. Simply enclose specific expressions in parentheses and everything within the parentheses is evaluated first. Take a look at Example 1.
The AND, OR, =, and < Operators
Example 1
Figure 5-3. Computers table
Suppose you want to query the Computers table in Figure 5-3. You want to display the SerialNum, Brand, and Department columns for computers located in office numbers less than 130 and with a brand name of either Dell or Gateway.
Using the query design grid in Figure 5-4, you would bring the SerialNum, Brand, and Department fields into the query grid, then select the filter operations in the Criteria row.
Figure 5-4. Query Design view
This produces the following script:
SELECT SerialNum, Brand, Department
FROM Computers
WHERE (Brand = 'Dell' OR Brand = 'Gateway')
AND OfficeNumber < 130;
The preceding script uses the equal (=) and less than (<) operators to perform comparisons among expressions. The AND and OR operators are used to separate two conditions. The AND operator requires that both expressions on either side of the AND operator be true in order for data to be returned. The OR operator requires that at least one expression on either side of the OR operator be true in order for data to be returned. Since the AND operator has precedence over the OR operator based on it occurring first, the conditions containing the OR operator are enclosed in parentheses to cause them to be evaluated before the AND condition. The query displays the SerialNum, Brand, and Department columns for all Gateway or Dell computers located in offices with an office number less than 130. Look at the results in Figure 5-5.
Figure 5-5. Results (output)
As a side note, whenever you use both the AND and the OR operators, always use parentheses to ensure that you retrieve the expected results.
The observant reader will note that we cheated a bit with this query grid. It would have been more correct to enter the criteria on two lines instead of combining the two values with the OR statement. Look at Figure 5-6.
Figure 5-6. Query Design view
This produces a bit more involved SQL script, as shown in Figure 5-7.
Figure 5-7. SQL view
If you look a bit closer at the script, you will see that terms can be combined in the WHERE clause, producing the results shown earlier. This is one of the real strengths of SQL script. When you have a really complex WHERE conditional, it is often far easier to see what is really happening in the SQL text rather than in the query grid. It is also often easier to build the query in SQL, rather than the query grid. Take a look at Figures 5-8 and 5-9.
Figure 5-8. SQL view
Figure 5-9. Query Design view
The LIKE Operator
The LIKE operator uses wildcard characters to match patterns in data. These are special characters used to match parts of a value. Table 5-4 shows the wildcard characters used with the LIKE operator.
Table 5-4. Wildcard characters used with the LIKE operator
Character | Description |
? | Any single character. |
* | Zero or more characters. |
# | Any single digit (0-9). |
[characters] | Any single character in a group of one or more characters. |
[!characters] | Any single character not in a group of one or more characters. |
Note: If your Access database is set to SQL Server compatible syntax (ANSI-92), you must use the percent sign (%) instead of the asterisk (*) and an exclamation mark (!) instead of the question mark (?) in queries that contain the LIKE operator. Additionally, you must use the percent sign (%) instead of the pound sign (#).
Example 2
Figure 5-10. Tools table
Suppose you want to query the Tools table in Figure 5-10 to retrieve tools made by manufacturers that begin with the letter D and are located in warehouse sections A through C.
SELECT *
FROM Tools
WHERE Manufacturer LIKE 'D*' AND Location LIKE '[A-C]';
The preceding script uses the asterisk (*) wildcard character and the brackets ([ ]) with the LIKE operator in the WHERE clause. The letter D is placed in front of the asterisk to instruct the DBMS to retrieve manufacturers that begin with the letter D. The brackets ([ ]) are used to instruct the database to retrieve locations from A to C. Look at the results in Figure 5-11.
Figure 5-11. Results (output)
The following examples show implementations of other LIKE operator search patterns. Remember to use the percent sign (%) instead of the asterisk (*) if your Access database is set to SQL Server compatible syntax (ANSI-92).
Example 3
To retrieve manufacturers that end with the letter H, type the following:
SELECT *
FROM Tools
WHERE Manufacturer LIKE '*H';
Example 4
To retrieve any occurrence of the word Dewalt within the name of the manufacturer, type the following:
SELECT *
FROM Tools
WHERE Manufacturer LIKE '*Dewalt*';
Example 5
To retrieve data that matches a single character in the Manufacturer column, type the following:
SELECT *
FROM Tools
WHERE Manufacturer LIKE 'Bos?h';
The question mark (?) is used as a character placeholder.
Note: If your Access database is set to SQL Server compatible syntax (ANSI-92), use an exclamation mark (!) instead of the question mark (?) in this type of query.
Example 6
To retrieve data that matches a single digit in the ToolID column, type the following:
SELECT *
FROM Tools
WHERE ToolID LIKE '1#';
The pound sign (#) is used as a digit placeholder.
Note: If your Access database is set to SQL Server compatible syntax (ANSI-92), use the percent sign (%) in place of the pound sign (#).
Example 7
To retrieve warehouse locations that are not A to C, type the following:
SELECT *
FROM Tools
WHERE Location LIKE '[!A-C]';
The ! symbol means NOT.
Example 8
To retrieve characters that are not digits from 1 to 5, type the following:
SELECT *
FROM Tools
WHERE ToolID LIKE '[!1-5]';
Example 9
To retrieve a combination of characters and digits, type the following:
SELECT *
FROM Computers
WHERE SerialNum LIKE 'm*[1-9]';
This script retrieves serial numbers from the Computers table that begin with the letter m and end with numbers 1 through 9.
Note: If your Access database is set to SQL Server compatible syntax (ANSI-92), use the percent sign (%) in place of the asterisk (*).
The BETWEEN Operator
The BETWEEN operator is used to determine whether a value of an expression falls within a specified range of values. Take a look at Example 10.
Example 10
Figure 5-12. Tools table
Suppose you want to query the Tools table in Figure 5-12 to retrieve tool IDs equal to or between 3 and 10. Look at the following script:
SELECT *
FROM Tools
WHERE ToolID BETWEEN 3 AND 10;
This script uses the BETWEEN operator in the WHERE clause to retrieve tool IDs equal to or between 3 and 10. The AND operator is used to specify values 3 and 10. Note that the BETWEEN operator always includes the expressions specified on either side of the AND operator. Look at the results in Figure 5-13.
Figure 5-13. Results (output)
This query is equivalent to the preceding query:
SELECT *
FROM Tools
WHERE ToolID >= 3 AND ToolID <=10;
What is the difference between the LIKE and the BETWEEN operators? At first glance one might expect that the expressions LIKE '[A-C]'* and BETWEEN 'A' and 'C' would produce the same results. While that would be true in some specific cases, generally the results will be different. Let’s say you have the list A, Apple, B, Bear, C, Cat, D, and Dog. The LIKE '[A-C]*' example will collect the values A, Apple, B, Bear, C, and Cat. The BETWEEN 'A' and 'C' example will collect the values A, Apple, B, Bear, and C but not Cat because Cat comes after C, which is the maximum value of the sequence.
The IN and NOT Operators
The IN operator is used to match conditions in a list of expressions.
The NOT operator is used to match any condition opposite of the one defined. Take a look at Example 11.
Example 11
Say you want to query the Tools table to retrieve information on every tool except the ones manufactured by Bosch, Porter, or Makita. Look at the following script:
SELECT *
FROM Tools
WHERE Manufacturer NOT IN ('Bosch', 'Porter', 'Makita'),
The preceding script uses the IN operator to specify three text values (Bosch, Porter, and Makita). The values are enclosed in parentheses and each individual value is enclosed in quotes. Remember that when you retrieve values from fields defined as a character data type, you must enclose the values in quotes. The NOT operator instructs the DBMS to match any condition opposite of the ones defined by the IN operator. Look at the results in Figure 5-14.
Figure 5-14. Results (output)
The following query specifies the exact opposite of the preceding query. It retrieves records that contain the values (Bosch, Porter, and Makita) specified by the IN operator.
SELECT *
FROM Tools
WHERE Manufacturer IN ('Bosch', 'Porter', 'Makita'),
Example 12
Suppose you want to retrieve tool IDs 2, 4, 6, and 8 from the Tools table. Look at the following script:
SELECT *
FROM Tools
WHERE ToolID IN (2, 4, 6, 8);
The preceding script uses the IN operator to specify four numbers (2, 4, 6, 8). The values are enclosed in parentheses. There are no quotes enclosing the numbers because the ToolID column in the Tools table contains a number data type as opposed to a text data type. Only values associated with a column containing text values must be enclosed in quotes. Look at the results in Figure 5-15.
Figure 5-15. Results (output)
The IS NULL and IS NOT NULL Operators
The IS NULL operator is used to determine if a field contains data. The IS NOT NULL operator is used to determine if a field does not contain data. Take a look at Example 13.
Figure 5-16. Friends table
Example 13
Suppose you want to retrieve individuals who do not have an e-mail address but do have a phone number listed in the Friends table in Figure 5-16. Look at the following script:
SELECT Firstname, Lastname, Areacode, PhoneNumber, Email
FROM Friends
WHERE Email IS NULL AND PhoneNumber IS NOT NULL;
The preceding script implements the IS NULL and IS NOT NULL keywords in the WHERE clause. The IS NULL keywords are used to locate NULL values in the Email column. The IS NOT NULL keywords are used to locate values in the PhoneNumber column. Look at the results in Figure 5-17.
Figure 5-17. Results (output)
Summary
In this chapter, you learned how to create a WHERE clause. You also learned how to use the comparison and logical operators in the WHERE clause.
Quiz 5
1. True or False. An expression is a special character used to match parts of a value.
2. True or False. The following queries are equivalent:
Query 1:
SELECT *
FROM Tools
WHERE ToolID > 3 AND ToolID < 10;
Query 2:
SELECT *
FROM Tools
WHERE ToolID BETWEEN 3 AND 10;
3. Using the Friends table in Figure 5-16, what will the following query return?
SELECT FriendsID
FROM Friends
WHERE Lastname = 'Jones' AND Email IS NULL;
4. True or False. The exclamation mark (!) in the following WHERE clause means NOT:
WHERE Location LIKE '[!A-C]';
5. True or False. The OR operator is processed before the AND operator in the order of evaluation.
Project 5
Use the Friends table in Figure 5-16 to write a query that returns records for individuals who live in Florida (FL).