RELATIONAL DATA RETRIEVAL: SQL - Fundamentals of Database Management Systems - page 77

complexion of the statement. The best way to deal with this, especially if there are
several ANDs and ORs in a WHERE clause, is by using parentheses. The rule is that
anything in parentheses is done first . If the parentheses are nested, then whatever
is in the innermost parentheses is done first and then the system works from there
towards the outermost parentheses. Thus, a ''safer'' way to write the last SQL
statement would be:
SELECT CUSTNUM, CUSTNAME, HQCITY
FROM CUSTOMER
WHERE HQCITY='New York'
OR (CUSTNUM > 1500
AND HQCITY='Atlanta');
If you really wanted the OR to be considered first, you could force it by writing
the query as:
SELECT CUSTNUM, CUSTNAME, HQCITY
FROM CUSTOMER
WHERE (HQCITY='New York'
OR CUSTNUM > 1500)
AND HQCITY='Atlanta';
This would mean that, with the AND outside of the parentheses, both of two
conditions have to be met for a row to qualify for the results. One condition is that
the headquarters city is New York or the customer number is greater than 1500.
The other condition is that the headquarters city is Atlanta. Since for a given row,
the headquarters city can't be both Atlanta and New York, the situation looks grim.
But, in fact, customer number 1525 qualifies. Its customer number is greater than
1500, which satisfies the OR of the first of the two conditions, and its headquarters
city is Atlanta, which satisfies the second condition. Thus, both conditions are met
for this and only this row.
BETWEEN, IN, and LIKE BETWEEN , IN ,and LIKE are three useful operators.
BETWEEN allows you to specify a range of numeric values in a search. IN allows
you to specify a list of character strings to be included in a search. LIKE allows you
to specify partial character strings in a ''wildcard'' sense.
BETWEEN Suppose that you want to find the customer records for those customers
whose customer numbers are between 1000 and 1700 inclusive (meaning that both
1000 and 1700, as well as all numbers in between them, are included). Using the
AND operator, you could specify this as:
SELECT *
FROM CUSTOMER
WHERE (CUSTNUM > =1000
AND CUSTNUM > =1700);
Or, you could use the BETWEEN operator and specify it as:
SELECT *
FROM CUSTOMER
WHERE CUSTNUM BETWEEN 1000 AND 1700;