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.

Using a Compound Condition

```
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'
```
The image below shows the result:

The 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 example above, 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.
Use parentheses to override natural precedence. Modify the previous query with additional parentheses, so that the 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'
```
The image below shows the expected result:

Note: Order of precedence is not an issue just for AND and OR. When you use complex search conditions, always add parentheses to ensure that the conditions evaluate in order that you intend them to.

Advance To:

Using Subqueries