Databases Reference
In-Depth Information
Emp ID Last Name Salary
---------- ------------------------- ----------
101 Kochhar 17000
102 De Haan 17000
2 rows selected.
The rules of precedence tell us that AND is very low on the list, and therefore
the AND operation is performed last in the WHERE clause. However, for clarity, it
doesn't hurt to add parentheses to make the conditional expressions more obvious:
where (salary + 10000 > 24000)
and (employee_id != 100);
There are other ways to remove King from the query. We'll discuss some of
these methods in Chapter 6, “Advanced SQL Queries.”
Now King decides that he wants to include anyone who works in the IT
department, in addition to those whose salaries are close to his. Janice recognizes
that this is a job for the OR operator. She modifies the query to include those
employees who are in the IT department, using the JOB_ID column:
select employee_id "Emp ID", last_name "Last Name",
salary "Salary" from employees
where (salary + 10000 > 24000)
and (employee_id != 100)
or job_id = 'IT_PROG';
Emp ID Last Name Salary
---------- ------------------------- ----------
101 Kochhar 17000
102 De Haan 17000
103 Hunold 9000
104 Ernst 6000
105 Austin 4800
106 Pataballa 4800
107 Lorentz 4200
7 rows selected.
Since the AND has a higher priority than the OR, the salary and employee ID
comparisons are evaluated to see if they are both true; if so, the row is returned.
If either one or the other is not true, the row might still be returned if the
