Oracle SQL - The Importance of Order of Precedence
Order of Precedence in SQL The importance of order of precedence is most evident when you deal with the grouping of conditions. This is best illustrated in select statements like this: SELECT empno, payrate, dept_code FROM emp_work WHERE dept_code = ‘SALES’ AND payrate = 18 OR payrate = 20; The user is trying to query for information on all the employees in the SALES department who have payrates of either 18 or 20. However the actual results from this query are going to be ALL of the employees in the SALES department with a payrate of 18 and ANY employee with a payrate of 20. This is because Oracle evaluates the ‘AND’ first because of its higher precedence. When Oracle looks at this query it sees: WHERE dept_code = ‘SALES ‘ AND payrate = 18 together and evaluates those conditions first. Oracle looks for employee records where the employee is in the SALES department and has a payrate of 18. THEN Oracle looks at the OR operator, since the first two ...