This chapter is from the book
This chapter is from the book
This chapter is from the book
Using Parentheses
Suppose we are interested only in orders from customers from either the state of Illinois or the state of California. Additionally, we want to see orders only where the quantity purchased is greater than 8. To attempt to satisfy this request, we might put together this SELECT statement:
SELECT CustomerName, State, QuantityPurchased FROM Purchases WHERE State = 'IL' OR State = 'CA' AND QuantityPurchased > 8
We would expect this statement to return only one row of data, for Sandy Harris. Although we have two rows for customers in Illinois or California (Chiang and Harris), only one of those (Harris) has a quantity purchased greater than 8. However, when this statement is executed, we get the following:
CustomerName |
State |
QuantityPurchased |
Kim Chiang |
IL |
4 |
Sandy Harris |
CA |
10 |
We see two rows instead of the expected one row. What went wrong? The answer lies in how SQL interprets the WHERE clause, which happens to contain both AND and OR operators. Like other computer languages, SQL has a predetermined order of evaluation that specifies the order in which various operators are interpreted. Unless told otherwise, SQL always processes the AND operator before the OR operator. So in the previous statement, it first looks at the AND and evaluates the condition:
State = 'CA' AND QuantityPurchased > 8
The row that satisfies that condition is for Sandy Harris. SQL then evaluates the OR operator, which allows for rows where the State equals IL. That adds the row for Kim Chiang. The result is that SQL determines that both the Kim Chiang and the Sandy Harris rows meet the condition.
Obviously, this isn’t what was intended. This type of problem often comes up when AND and OR operators are combined in a single WHERE clause. The way to resolve the ambiguity is to use parentheses to specify the desired order of evaluation. Anything in parentheses is always evaluated first.
Here’s how parentheses can be added to the previous SELECT to correct the situation:
SELECT CustomerName, State, QuantityPurchased FROM Purchases WHERE (State = 'IL' OR State = 'CA') AND QuantityPurchased > 8
When this is executed, we see this data:
CustomerName |
State |
QuantityPurchased |
Sandy Harris |
CA |
10 |
The parentheses in the SELECT statement force the OR expression (State = 'IL' OR State = 'CA') to be evaluated first. This produces the intended result.