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.
|