Полезная информация

Next: LIKE Comparison Up: Customizing Queries Previous: Comments

# AND/OR usage

Up to this point, there have been only simple WHERE clause tests. In the next few sections, we will show how to do more complex WHERE clause testing.

Multiple WHERE clause tests are done by connecting simple tests using the words AND and OR. For illustration, I have loaded the friends table with new people in figure .

Selecting certain rows from the table will require more complex WHERE conditions. For example, If we wanted to select Sandy Gleason, it would be difficult with only one comparison in the WHERE clause. If we tested for firstname = 'Sandy', we would select both Sandy Gleason and Sandy Weber. If we tested for lastname = 'Gleason', we would get both Sandy Gleason and her brother Dick Gleason. The proper test is to use AND to joins tests of both firstname and lastname. The proper query is shown in figure .

The AND joins the two comparisons we needed.

A similar comparison could be done to select friends living in Cedar Creek, Maryland. There could be other friends living in Cedar Creek, Ohio, so the comparison city = 'Cedar Creek' is not enough. The proper test is city = 'Cedar Creek' AND state = 'MD'.

Another complex test would be to select people who are in the state of New Jersey (NJ) or Pennsylvania (PA). Such a comparison requires the use of OR. The test state = 'NJ' OR state = 'PA' would return the desired rows, as shown in figure .

An unlimited number of ANDs and ORs can be linked together to perform complex comparison tests. When ANDs are linked with other ANDs, there is no possibility for confusion. The same is true of ORs. However, when ANDs and ORs are both used in the same query, the results can be confusing. Figure  shows such a case.

You may suspect that it would return rows with firstname equal to Victor and state equals PA or NJ. In fact, the query returns rows with firstname equal to Victor and state equals PA, or state equals NJ. In this case, AND is evaluated first, then OR. When mixing ANDs and ORs, it is best to collect the ANDs and ORs into common groups using parentheses. Figure  shows the proper way to enter this query.

Without parentheses, it is very difficult to understand a query with mixed ANDs and ORs.

Table: Comparisons
 Comparison Operator less than < less than or equal <= equal = greater than or equal >= greater than > not equal <> or !=

Next: LIKE Comparison Up: Customizing Queries Previous: Comments
Bruce Momjian
1999-11-21