How AND/OR Logical Operators Are Used Together

You can use any number of AND and OR operators together in a single WHERE clause to create complex filtering. But, putting these two together in a WHERE clause may also put you in trouble. Let's see an example of this. You are asked to provide a list of all employees working either in department number 10 or 20 and are earning more than or equal to 6000. In response, you created a query like this:

Select   first_name||' '||last_name Employee, department_id, salary

from      employees

where   department_id=10 or department_id=20 and salary >= 6000

    Output : 

Look at the record of Jennifer Whalen. This should have been filtered out (because of the AND condition which says that the salary must be greater than or equal to 6000). Why this record appeared? The answer is the order of evaluation. As mentioned earlier, AND operator has higher precedence over OR, therefore the WHERE clause in the above statement was executed in the following order:  

1.       department_id = 20 and salary >= 6000 - returned second and third records.

2.       department_id = 10 - returned record number 1 of Jennifer Whalen.

This happened because of the precedence rule that joined wrong operators together. Again, the solution to the problem lies in the use of parentheses to explicitly group related operators. Modify the statement by enclosing the first two filters within parentheses, as shown below.

Select   first_name||' '||last_name Employee, department_id, salary

from     employees

where   (department_id=10 or department_id=20) and salary >= 6000 

    Output 

Since parentheses have a higher order of evaluation than either AND or OR operators, the OR condition (within the parentheses) was evaluated first like this:

1. (department_id=10 or department_id=20) 

2. salary >= 6000

Now, the statement fetches records of employees working under department number 10 or department number 20 earning 6000 or more. Although Jennifer is working in department number 10, she doesn’t meet the second criterion and is thus filtered out.