13.2 Comparison Operators

The general form of the WHERE clause prescribes a matching of a field, a comparison operator, and a value. You can use any field on the table specified in the FROM clause as part of a WHERE clause, whether the field appears in the SELECT query or not. For example, you could write a query that lists the first name of all members whose last name is "Smith". The syntax of this query is below. The results are depicted on Figure 13.2. Notice that the query successfully returns the only member whose last name is "Smith" and shows only the first name of that member.

SELECT first_name

FROM Member

WHERE last_name = 'Smith'

You may also notice something else about the syntax of the WHERE clause in our example. Notice the single quotes (') around the value of 'Smith'. When we use a text field in our criterion, we place single quotes around the value part of the clause.

There are times when the value part of the WHERE clause has a single quote (') in it. For example, one of the events in the student organizations database is "Women's Soccer". In this situation, we can’t use single quotes (') in the WHERE clause around the text (in this case Women’s Soccer) in the criteria. The WHERE clause (event_name = 'Women's Soccer') will result in an error from the query processing engine. We can, however, substitute the single quotes around the value part of the WHERE clause with double quotes ("). The syntax (event_name = "Women's Soccer") is also correct and will not result in a syntax error from the query processing engine.

If the desired value in the comparison is a number, we do not use quotes. We also do not use any formatting of the number—even if the number is generally formatted (such as money with a dollar sign). For example, if we were interested in displaying only those expenses that cost $122.06, our WHERE clause would be WHERE cost = 122.06 (see Figure 13.3).

With practice, you will remember these rules governing how to use text and numbers as values in a WHERE clause. Also, with practice, you will feel comfortable using different comparison operators in WHERE clauses. The comparison operators that can be used in a WHERE clause are shown in Table 13.1.

Up to this point, we have used the "=" operator. This operator is used when we want to ensure that the rows in the query result contain exactly the specified value for the specified field. The syntax "WHERE cost = 122.06" will return only those expenses where the cost is exactly $122.06.

We can also use the inequality operators "<" or ">" to create WHERE clause conditions that restrict rows based on ranges of values. For example, we could restrict expenses to those items that cost more than $100. The syntax for this restriction would be WHERE cost > 100 (see Figure 13.4)

We could also restrict expenses to those that are less than $50. The syntax for this restriction would be WHERE cost < 50 (see Figure 13.5).

Finally, we can use operators that combine equals (=) with one of the comparison operators (< or >) to create operators that include endpoints. For example, we may want to see all of the line items on the income table that are at least $200. The syntax for the WHERE clause in this example would be WHERE amount >= 200. Figure 13.6 shows the results of this SQL statement. Notice that the rows included in this query result include those in which the amount is greater than $200. It also includes the row in which the amount is equal to $200.