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 conditions have been grouped together by the AND operator, all Oracle has left is:
OR payrate =20;
to evaluate, so Oracle checks for all employees with a payrate of 20.
NOTE: Employees with a 20 dollar an hour payrate aren't all in the SALES department.
Since what the user wanted to see was employees in the SALES department and with either a payrate of 18 or 20, we want Oracle to look at the OR operator before looking at the AND.
You can put the OR above the AND if you want, but it won't help. Oracle still looks at the AND operator first because it's higher on the order of precedence. What we need to do is somehow raise OR above AND in a way that even the computer can understand. The way we achieve this is by surrounding the OR conditions with parenthesis ( ).
Parenthesis are higher on the order of precedence chart than AND or OR. Anything inside of parenthesis will be evaluated first. When you use parenthesis to group together statements you want evaluated together (like the OR conditions from this example) it is called Grouping.
For instance, if we add parenthesis around the OR conditions of our previous example:
SELECT empno, payrate, dept_code
FROM emp_work
WHERE dept_code = 'SALES'
AND (payrate = 18
OR payrate = 20);
The payrate conditions are grouped together and evaluated first because of the parenthesis around them. Then Oracle looks at the AND statement.
What we end up with is a select that asks for a listing of all the employees who have a payrate of either 18 OR 20, and then checks to see which of those is in the SALES department, which will give us the results we were looking for.
Grouping is not often needed, especially in simple selects.
However, if you ever find that you're getting more or different information than you expected from a query with multiple conditions, it's a good guess that grouping will help give you the results you were originally looking for.
Operator Precedence
Precedence defines the order that Oracle uses when evaluating different operators in the same expression. Every operator has a predefined precedence. Oracle evaluates operators with a higher precedence before it evaluates operators with a lower precedence. Operators with equal precedence will be evaluated from left to right.
*Partial* Table of Operators In SQL Order of Precedence
& - Used as a Parameter Prefix
( ) - Overriding Normal Operator Precedence
NOT - Reversing the Result of an Expression
AND - Expressing True If Both Conditions are True
OR - Expressing True If Either Condition is True
There is a free Oracle Overview website at: http://oracletutorials.weebly.com/
Jennifer Blair is an independent Oracle trainer who specializes in training Oracle 11i / Oracle Apps (Oracle Manufacturing and Oracle Finance) and Oracle 10g (SQL, PL/SQL Programming, Discoverer, Forms, Reports)
She can be reached at: http://www.BlairTechnicalTraining.com
Tags: Oracle SQL Server, sql programming, sql tips
Comment on "Oracle SQL - The Importance of Order of Precedence"
| Related News: |
