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 1,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)
