SQL - Operators Precedence

SQL Operators Precedence

Operator precedence (order of operations) is a collection of rules that reflect conventions about which procedures to perform first in order to evaluate a given expression.

For example, multiplication has higher precedence than addition. Thus, the expression 1 + 2 × 3 is interpreted to have the value 1 + (2 × 3) = 7, and not (1 + 2) × 3 = 9. When exponent is used in the expression, it has precedence over both addition and multiplication. Thus 3 + 5² = 28 and 3 × 5² = 75.

Example:

Consider the following expressions:

--evaluates 5 * 2 first
Result1 = 15 - 5 * 2;

--above expression is equivalent to
Result2 = 15 - (5 * 2);

--forcing compiler to evaluate 15 - 5 first
Result3 = (15 - 5) * 2;

Now, Consider a database table called Sample with the following records:

The above discussed can be applied on the table Sample. Consider the example below:

SELECT *, 
  Var1 - Var2 * 2 AS Result1,
  Var1 - (Var2 * 2) AS Result2,
  (Var1 - Var2) * 2 AS Result3 
FROM Sample;

The above query will produce following result:

SQL Operators Precedence Table

The following table lists the precedence of SQL operators. Operators are listed top to bottom, in descending precedence. Operators with higher precedence are evaluated before operators with relatively lower precedence.