Arithmetic Operator Precedence

When there is more than one arithmetic operator in an expression; multiplication, division, and modulo are calculated first, followed by subtraction and addition.

If all arithmetic operators in an expression have the same level of precedence, the order of execution is left to right. Expressions in parentheses take precedence over all other operations.

For example, the following select statement multiplies the total sales of a book by its price to calculate a total dollar amount, then subtracts from that one half of the author’s advance.

select title_id, total_sales * price - advance / 2 
from titles 

The product of total_sales and price is calculated first, because the operator is multiplication. Next, the advance is divided by 2, and the result is subtracted from total_sales * price.

To avoid misunderstandings, use parentheses. The following query has the same meaning and gives the same results as the previous one, but it is easier to understand:

select title_id,(total_sales * price) - (advance /2) 
from titles 
title_id 
--------     ----------
 BU1032       79,359.05 
 BU1111       43,818.20 
 BU2075       50,916.28 
 BU7832       79,359.05 
 MC2222       40,619.68 
 MC3021       59,015.54 
 MC3026            NULL 
 PC1035      198,001.00 
 PC8888       77,900.00 
 PC9999            NULL 
 PS1372        4,596.25 
 PS2091       21,255.25 
 PS2106       -2,223.00 
 PS3333       80,399.28 
 PS7777       24,654.64 
 TC3218        4,356.25 
 TC4203      178,397.20 
 TC7777       57,384.05 
 
(18 rows affected)

Use parentheses to change the order of execution; calculations inside parentheses are handled first. If parentheses are nested, the most deeply nested calculation has precedence. For example, the result and meaning of the preceding example is changed if you use parentheses to force evaluation of the subtraction before the division:

select title_id, (total_sales * price - advance) /2 
from titles 
title_id                           
--------   -----------------------  
BU1032                  38,429.53  
BU1111                  20,659.10  
BU2075                  22,926.89  
BU7832                  38,429.53  
MC2222                  20,309.84  
MC3021                  25,757.77  
MC3026                       NULL 
PC1035                  97,250.50  
PC8888                  36,950.00  
PC9999                       NULL  
PS1372                     548.13  
PS2091                  10,058.88  
PS2106                  -2,611.50  
PS3333                  39,699.64  
PS7777                  11,327.32  
TC3218                     428.13  
TC4203                  88,198.60  
TC7777                  26,692.03  
 
(18 rows affected)