This chapter is from the book
This chapter is from the book
This chapter is from the book
Multiple Sets of Parentheses
Let’s say we want to select two different sets of rows from the Purchases table: first, rows for customers in New York, and second, rows for customers in Illinois who have made a purchase with a quantity between 3 and 10. The following SELECT accomplishes this requirement:
SELECT CustomerName, State, QuantityPurchased FROM Purchases WHERE State = 'NY' OR (State = 'IL' AND (QuantityPurchased >= 3 AND QuantityPurchased <= 10))
The result is:
CustomerName |
State |
QuantityPurchased |
Kim Chiang |
IL |
4 |
James Turban |
NY |
5 |
Notice that there are two sets of parentheses in this statement, with one set inside the other. This use of parentheses is analogous to the parentheses used in the composite functions seen in Chapter 4. When there is more than one set of parentheses in functions, the innermost set is always evaluated first. The same is true of parentheses used in Boolean expressions. In this example, the innermost set of parentheses contains:
(QuantityPurchased >= 3 AND QuantityPurchased <= 10)
After this condition is evaluated for each row, the logic proceeds outward to the second set of parentheses:
(State = 'IL' AND (QuantityPurchased >= 3 AND QuantityPurchased <= 10))
Finally, the logic adds in the final line of the WHERE clause (not enclosed in parentheses) regarding the state of New York:
WHERE State = 'NY' OR (State = 'IL' AND (QuantityPurchased >= 3 AND QuantityPurchased <= 10))
In essence, SQL’s logic first evaluated expressions in the innermost set of parentheses, then the outer set of parentheses, and then all remaining expressions.