There are number of operators which can be used in any SQL statement. And also if there are more than one operators used in same SQL statement, they are executed based on operator precedence. Operator Precedence is the definition of priority of one operator over another.  Every programmer should have good knowledge of operator precedence to make it best use in SQL statement.

A statement having multiple operators is evaluated based on the priority of operators. If all the operators in SQL statement have equal precedence, then the operators are evaluated from left to right. Operators with different precedence are evaluated from highest to lowest. Following list shows the operators with  their precedence from high to low as you go from top to bottom. Operators having same precedence are placed in the same row.

SQL Operators:

• Parenthesis ( )
• DIVISION (/), MULTIPLICATION(*)
• ADDITION (+), SUBSTRACTION(-)
• NOT
• AND
• ALL, ANY, BETWEEN, IN, LIKE, OR

These operators will be discussed in details under their respective articles.

Examine the following statement, and see how the operator precedence affect its execution:

 SELECT ename, DOB

FROM emp

WHERE ename = ‘SCOTT’ OR ename = ‘CHRIS’

AND DOB >= ‘1998-08-01’;

 As AND has higher precedence than OR, this statement will check for either rows with values: ename CHRIS AND DOB greater than ‘1998-08-01’ OR ename SCOTT. The result of this query is shown below:

IMAGE 9

Now lets put brackets in same query and see how it effect the result. The new query is as follows:

SELECT ename, DOB

FROM emp

WHERE (ename = ‘SCOTT’ OR ename = ‘CHRIS’)

AND DOB >= ‘1998-08-01’;

We changed the precedence of operator by use of parenthesis. The new result would be like this:

IMAGE 10

 

Brackets also make the SQL easier to read as you don’t have to remember operator precedence and they make it clear which conditions are evaluated first.