Until now, we have used only simple WHERE clause tests. In the following sections, we will demonstrate how to perform more complex WHERE clause testing.
Complex WHERE clause tests are done by connecting simple
tests using the words AND and OR. For illustration,
new people have been inserted into the friend table, as shown
in Figure ![[*]](cross_ref_motif.gif)
.
test=> DELETE FROM friend;
DELETE 6
test=> INSERT INTO friend
test-> VALUES ('Dean', 'Yeager', 'Plymouth', 'MA', 24);
INSERT 19744 1
test=> INSERT INTO friend
test-> VALUES ('Dick', 'Gleason', 'Ocean City', 'NJ', 19);
INSERT 19745 1
test=> INSERT INTO friend
test-> VALUES ('Ned', 'Millstone', 'Cedar Creek', 'MD', 27);
INSERT 19746 1
test=> INSERT INTO friend
test-> VALUES ('Sandy', 'Gleason', 'Ocean City', 'NJ', 25);
INSERT 19747 1
test=> INSERT INTO friend
test-> VALUES ('Sandy', 'Weber', 'Boston', 'MA', 33);
INSERT 19748 1
test=> INSERT INTO friend
test-> VALUES ('Victor', 'Tabor', 'Williamsport', 'PA', 22);
INSERT 19749 1
test=> SELECT * FROM friend ORDER BY firstname;
firstname | lastname | city | state | age
-----------------+----------------------+-----------------+-------+-----
Dean | Yeager | Plymouth | MA | 24
Dick | Gleason | Ocean City | NJ | 19
Ned | Millstone | Cedar Creek | MD | 27
Sandy | Gleason | Ocean City | NJ | 25
Sandy | Weber | Boston | MA | 33
Victor | Tabor | Williamsport | PA | 22
(6 rows)
Selecting certain rows from the table will require more complex WHERE
conditions. For example, if we wanted to select Sandy Gleason
by name, it would be impossible using 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
approach is to use AND to test both firstname and
lastname. This query is shown in Figure .
test=> SELECT * FROM friend
test-> WHERE firstname = 'Sandy' AND lastname = 'Gleason';
firstname | lastname | city | state | age
-----------------+----------------------+-----------------+-------+-----
Sandy | Gleason | Ocean City | NJ | 25
(1 row)
The AND combines the two needed comparisons.
A similar comparison could be used to select friends living in Cedar Creek, Maryland. Other friends could live 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 live 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 .
test=> SELECT * FROM friend
test-> WHERE state = 'NJ' OR state = 'PA'
test-> ORDER BY firstname;
firstname | lastname | city | state | age
-----------------+----------------------+-----------------+-------+-----
Dick | Gleason | Ocean City | NJ | 19
Sandy | Gleason | Ocean City | NJ | 25
Victor | Tabor | Williamsport | PA | 22
(3 rows)
An unlimited number of AND and OR clauses can be
linked together to perform complex comparisons. When ANDs
are linked with other ANDs, there is no possibility
for confusion. The same is true of ORs. On the other
hand, when ANDs and ORs are both used in the same
query, the results can be confusing. Figure
shows such a case.
test=> SELECT * FROM friend
test-> WHERE firstname = 'Victor' AND state = 'PA' OR state = 'NJ'
test-> ORDER BY firstname;
firstname | lastname | city | state | age
-----------------+----------------------+-----------------+-------+-----
Dick | Gleason | Ocean City | NJ | 19
Sandy | Gleason | Ocean City | NJ | 25
Victor | Tabor | Williamsport | PA | 22
(3 rows)
You might suspect that it would return rows with firstname
equal to Victor and state equal to PA or
NJ. In fact, the query returns rows with firstname
equal to Victor and state equal to PA, or
state equal to NJ. In this case, the AND
is evaluated first, then the 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.
test=> SELECT * FROM friend
test-> WHERE firstname = 'Victor' AND (state = 'PA' OR state = 'NJ')
test-> ORDER BY firstname;
firstname | lastname | city | state | age
-----------------+----------------------+-----------------+-------+-----
Victor | Tabor | Williamsport | PA | 22
(1 row)
Without parentheses, it is very difficult to understand a query with
mixed ANDs and ORs.
|