SQL Tutorial SQL Advanced SQL Database SQL References

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 + 52 = 28 and 3 × 52 = 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:

DataVar1Var2
Data1101
Data2152
Data3203
Data4254
Data5305
Data6356

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:

DataVar1Var2Result1Result2Result3
Data11018818
Data2152111126
Data3203141434
Data4254171742
Data5305202050
Data6356232358

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.

PrecedenceOperatorsDescription
9+,  -Unary plus, Unary minus
8*,  /,  %Multiplication, Division, Modulo
7+,  -,  ||Addition, Subtraction, Concatenation
6=,  !=,  >,  <,  >=,  <=,  <>Comparison
5IS [NOT] NULL
LIKE
[NOT] BETWEEN
[NOT] IN
EXISTS
IS OF
Comparison
4NOTLogical negation
3ANDConjunction
2ORDisjunction
1=Assignment

❮ SQL - Operators