InterBase Quick Start: Part IV - Controlling the Order of Evaluation
Go Up to InterBase Quick Start: Part IV - Using the WHERE Clause
When entering compound search conditions, you must be aware of the order of evaluation of the conditions. Suppose you want to retrieve employees in department 623 or department 600 who have a hire date later than January 1, 1992.
Trying a Compound Condition
Try executing this query:
SELECT last_name, first_name, hire_date, dept_no
FROM Employee
WHERE dept_no = 623 OR dept_no = 600 AND hire_date > '01-JAN-1992'
As you can see, the results include employees hired earlier than you want:
This query produces unexpected results because AND has higher precedence than OR. This means that the expressions on either side of AND are tested before those associated with OR. In the example as written, the search conditions are interpreted as follows:
( WHERE dept_no = 623 )
OR
( WHERE dept_no = 600 AND hire_date > '01-JAN-1992' )
The restriction on the hire date applies only to the second department. Employees in department 623 are listed regardless of hire date.
Using a Compound Condition Successfully
Use parentheses to override normal precedence. In the exercise below, place parentheses around the two departments so they are tested against the AND operator as a unit. Redisplay your last query and add parentheses, so that your query is interpreted correctly:
SELECT last_name, first_name, hire_date, dept_no
FROM Employee
WHERE (dept_no = 623 OR dept_no = 600)
AND hire_date > '01-JAN-1992'
This displays the results you want:
Order of precedence is not just an issue for AND and OR. All operators are defined with a precedence level that determines their order of interpretation. You can study precedence levels in detail by reading any number of books about SQL, but in general, the following rule of thumb is all you need to remember.
- Tip: Always use parentheses to group operations in complex search conditions.