Databases Reference
In-Depth Information
This time, our result is:
CUSTNUM
CUSTNAME
HQCIT
0121
Main St. Hardware
New York
1525
Fred's Tool Stores
Atlanta
1700
XYZ Stores
Washington
1826
City Hardware
New York
2198
Western Hardware
New York
2267
Central Stores
New York
Notice that the OR operator really means one or the other or both. Customer
0121 is included because it is headquartered in New York. Customers 1525 and
1700 are included because they have customer numbers higher than 1500. Cus-
tomers 1826, 2198, and 2267 satisfy both conditions.
If both AND and OR are required, AND has a higher precedence than OR.
Operator precedence
determines the order in which operators are processed, so
all ANDs are considered before any ORs are considered. The following query,
which has to be worded very carefully, illustrates this point:
List the customer numbers, customer names, and headquarter cities of the
customers that are headquartered in New York or that satisfy the two con-
ditions of having a customer number higher than 1500 and being head-
quartered in Atlanta.
The query for this is:
SELECT CUSTNUM, CUSTNAME, HQCITY
FROM CUSTOMER WHERE HQCITY='New York' OR
(CUSTNUM>1500 AND HQCITY='Atlanta')
The result of this query is:
CUSTNUM
CUSTNAME
HQCIT
0121
Main St. Hardware
New York
1525
Fred's Tool Stores
Atlanta
1826
City Hardware
New York
2198
Western Hardware
New York
2267
Central Stores
New York
Notice that since the AND is considered first, one way for a row to qualify
being in the result is if its customer number is greater than 1500 and its head-
quarter city is Atlanta. With the AND taken first, it's that combination or the
headquarters city has to be New York. If the OR operator was considered first,
it would change the whole complexion of the statement. The best way to deal
with this, especially if there are several ANDs and ORs in a WHERE clause, is
to use parentheses. The rule is that anything in parentheses is done first. If the
