InterBase Quick Start: Part IV - Controlling the Order of Evaluation

From InterBase
Jump to: navigation, search

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.

Image 025.jpgUsing a Compound Condition

  1. 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:

    TutorialWhere18.png

    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.

  2. 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:

    TutorialWhere19.png
    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: