| Order of Precedence in SQL | | | | way that even the computer can understand. The |
| The importance of order of precedence is most | | | | way we achieve this is by surrounding the OR |
| evident when you deal with the grouping of conditions. | | | | conditions with parenthesis ( ). |
| This is best illustrated in select statements like this: | | | | Parenthesis are higher on the order of precedence |
| SELECT empno, payrate, dept_code | | | | chart than AND or OR. Anything inside of parenthesis |
| FROM emp_work | | | | will be evaluated first. When you use parenthesis to |
| WHERE dept_code = 'SALES' | | | | group together statements you want evaluated |
| AND payrate = 18 | | | | together (like the OR conditions from this example) it is |
| OR payrate = 20; | | | | called Grouping. |
| The user is trying to query for information on all the | | | | For instance, if we add parenthesis around the OR |
| employees in the SALES department who have | | | | conditions of our previous example: |
| payrates of either 18 or 20. However the actual results | | | | SELECT empno, payrate, dept_code |
| from this query are going to be ALL of the employees | | | | FROM emp_work |
| in the SALES department with a payrate of 18 and | | | | WHERE dept_code = 'SALES' |
| ANY employee with a payrate of 20. | | | | AND (payrate = 18 |
| This is because Oracle evaluates the 'AND' first | | | | OR payrate = 20); |
| because of its higher precedence. | | | | The payrate conditions are grouped together and |
| When Oracle looks at this query it sees: | | | | evaluated first because of the parenthesis around |
| WHERE dept_code = 'SALES ' | | | | them. Then Oracle looks at the AND statement. |
| AND payrate = 18together and evaluates those | | | | What we end up with is a select that asks for a listing |
| conditions first. | | | | of all the employees who have a payrate of either 18 |
| Oracle looks for employee records where the | | | | OR 20, and then checks to see which of those is in |
| employee is in the SALES department and has a | | | | the SALES department, which will give us the results |
| payrateof 18. | | | | we were looking for. |
| THEN Oracle looks at the OR operator, since the first | | | | Grouping is not often needed, especially in simple |
| two conditions have been grouped together by the | | | | selects. |
| AND operator, all Oracle has left is: | | | | However, if you ever find that you're getting more or |
| OR payrate =20;to evaluate, so Oracle checks for all | | | | different information than you expected from a query |
| employees with a payrate of 20. | | | | with multiple conditions, it's a good guess that grouping |
| NOTE: Employees with a 20 dollar an hour payrate | | | | will help give you the results you were originally looking |
| aren't all in the SALES department. | | | | for. |
| Since what the user wanted to see was employees in | | | | Operator Precedence |
| the SALES department and with either a payrate of | | | | Precedence defines the order that Oracle uses when |
| 18 or 20, we want Oracle to look at the OR operator | | | | evaluating different operators in the same expression. |
| before looking at the AND. | | | | Every operator has a predefined precedence. Oracle |
| You can put the OR above the AND if you want, but it | | | | evaluates operators with a higher precedence before |
| won't help. Oracle still looks at the AND operator first | | | | it evaluates operators with a lower precedence. |
| because it's higher on the order of precedence. What | | | | Operators with equal precedence will be evaluated |
| we need to do is somehow raise OR above AND in a | | | | from left to right. |