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
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 .
WHERE test for Sandy Gleason
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 .
Friends in New Jersey and Pennsylvania
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.
Mixing ANDs and ORs
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.
Properly mixing ANDs and
Without parentheses, it is very difficult to understand a query with mixed ANDs