13.6 Compound WHERE Clauses

It is also possible to create a WHERE clause that includes more than one criterion. For example, we may wish to see all income items where the amount is between $100 and $1,000. In this case, we would need two WHERE clause criteria: amount >=100 and amount <=1000 (see Figure 13.11). Notice that only the rows that meet both criteria are included in the query results. We combine criteria in a WHERE clause using the operators AND and OR.

Figure 13.11: Query with a Compound WHERE Clause.

The AND Operator

The AND operator is used to combine criteria when both conditions must be met for a row to be included in the query result. Notice that when you use the AND operator to combine criteria in a WHERE clause, the keyword AND appears between the criteria being combined. Consider another example. Assume we wish to see all items on the expense table that cost more than $20.20 and less than $50.13. The WHERE clause for this query would be WHERE cost >=20.2 AND cost <=50.13 (see Figure 13.12).

Figure 13.12: WHERE Clause with the AND Operator.

There is a shorthand way to write a query that selects values within a range for the same field. This method involves using the keyword BETWEEN. The conditions "WHERE cost >=20.2 AND cost <=50.13" can be rewritten as "WHERE cost BETWEEN 20.2 AND 50.13" (see Figure 13.13). BETWEEN implies that the endpoints are included in the criteria (>=20.2 and <=50.13). The result will be the same for either approach.

Figure 13.13: Criterion Using the BETWEEN Operator.

We can also combine criteria that are applied to different fields. For example, we may wish to see all expenses for Pizza (expense_description = 'Pizza') that cost more than $100. The WHERE clause that would produce this result is WHERE cost > 100 AND expense_description = 'Pizza' (see Figure 13.14).

Figure 13.14: AND Criteria with Different Fields.

The OR Operator

The OR operator is used to combine criteria when we want our results to match any of the criteria. The syntax for using the OR operator is the same as the syntax for using the AND operator. The keyword OR is included between the criteria being combined. If we wish to write a query that displays the names of members with a small or medium shirt size, the syntax of the WHERE clause for this query would be WHERE t_shirt_size = 'Small' OR t_shirt_size = 'Medium' (see Figure 13.15).

Figure 13.15: WHERE Clause with the OR Operator.

Note that the AND operator would not return the rows that we want for this query. There are no members with both a small and medium shirt size. Instead, we want to return members for which either of these conditions is true. The OR operator accomplishes that.

The query in Figure 13.16 can be rewritten in a simpler form using the IN operator. The syntax for the IN operator is WHERE fieldname IN (list of values separated by commas). The WHERE clause "WHERE t_shirt_size = 'Small' OR t_shirt_size = 'Medium'" could be rewritten as WHERE t_shirt_size IN ('Small', 'Medium'). Figure 13.16 displays the results of this query. Notice that the results are the same as in the previous example.

Figure 13.16: WHERE Clause Using the IN Operator.

We can combine criteria based on different fields using the OR operator. For example, we may wish to see all members with the first name Amy or the last name Sanders. The WHERE clause for this SQL statement would be WHERE first_name = 'Amy' OR last_name = 'Sanders' (see Figure 13.17).

Figure 13.17: OR Criteria with Different Fields.

More information and examples about using the AND and OR operators can be found on data.world at Basic Concepts: AND, OR, and NOT.

Combining AND and OR

A single WHERE clause can contain a combination of AND and OR operators to create a sophisticated set of conditions. There is a standard order in which combined AND and OR operators will be processed, much like the order of operations you learned in elementary school. AND operators will be processed before OR operators. If you would like an OR operator to execute before an AND operator in the same WHERE clause, you can use parentheses around the operators you would like to execute first, just like using parentheses in the order of operations for math. Consider a few examples:

  • WHERE color = 'blue' AND size = 'Large' OR size = 'Medium'
    This syntax will return those rows that contain large blue items and medium items of any color.
  • WHERE color = 'blue' AND (size = 'Large' OR size = 'Medium')
    This syntax will return those rows that contain large and medium items, all of which will be blue.

The NOT Operator

The NOT operator can also be helpful in constructing a WHERE clause. As you recall, we discussed NOT in our conversation about NULL in the previous section. The NOT operator can be used to create a condition that reverses the logic of any condition. The syntax for the NOT operator is NOT(condition we wish to reverse).

Often it is simpler to define a condition based on what it does not contain rather than what it does contain. For example, we may wish to return all of the members with a t_shirt_size other than small. The following WHERE clause would accomplish this objective: WHERE t_shirt_size IN ('Medium', 'Large', 'X-Large'). We can accomplish the same thing using the NOT operator (see Figure 13.18). An equivalent WHERE clause using the NOT operator would be WHERE NOT(t_shirt_size = 'Small').

Figure 13.18: The NOT Operator.
Previous Next