Precedence represents the order in which operators from the same expression are being evaluated. When several operators are used together, the operators with higher precedence are evaluated before those with the lower precedence. In general, the operators' precedence follows the same rules as in the high school math, which might be somewhat counterintuitive. The order of the precedence is indicated in Table 11-4.

Operator |
Precedence |
---|---|

Unary operators, bitwise NOT (MS SQL Server only) |
1 |

Multiplication and division |
2 |

Addition, subtraction, and concatenation |
3 |

SQL conditions |
4 |

The evaluation precedence could dramatically affect results of the query. Consider the following Oracle query, which supposedly calculates value as TOTAL_PRICE + 4 * 0.085:

SELECT total_price, total_price + 4 * 0.085 tax FROM v_customer_totals total_price tax ----------- ------- 7538.20 7538.54 8420.10 8420.44 6630.40 6630.74

Depending on how you are inclined to count, it might mean that you want to increase all your prices by four dollars and then calculate 8.5 percent of the result; or — if operators' precedence rules are taken into consideration — it means that you would like to increase the price by 0.34 cents. Of course, RDBMS would follow the rules of precedence and would first multiply 4 by 0.085 and then add the result to whatever value there is in the TOTAL_PRICE column.

You may be wondering how the minus (–) and
plus (+) operators are of the first and third
precedence at the same time. This is just another example of an operator's
overloading: in addition to performing subtraction and addition operations,
they also signify the unary operators *negation* and
*sign identity *(like –5 or +5). For example, the following
expression will evaluate to a negative number rather than a positive one:

SELECT -2 * 3 + 5 result FROM dual; result ------ -1

Instead of -11, as you might have expected, this expression evaluates to –1 because the sign of the multiplier 2 is taken into consideration before the multiplication. If we employ brackets to apply the minus sign last, the result is different:

SELECT –(2 * 3 + 5) result FROM dual; result ------ -11

The unary operator + does not affect its operand, while – changes it to negative, as illustrated in the following example (applies to all three RDBMS):

SELECT –(+total_price) minus_first, +(-total_price) plus_first FROM v_customer_totals minus_first plus_first ----------- ---------- -7538.20 -7538.20 -8420.10 -8420.10 . . . . . . -6630.40 -6630.40 . . . . . . -12138.60 -12138.60

As you can see, the order of unary operators did not affect the result it turned out negative in both cases.

Tip |
You can change the precedence of operations (not operators!) by using parentheses. |

The previous expression would evaluate to the different values if parentheses were used:

SELECT total_price, (total_price + 4) * 0.085 price_increase1, total_price + 4 * 0.085 price_increase2 FROM v_customer_totals; total_price value1 value2 ----------- --------- ------- 7538.20 641.08700 7538.54 8420.10 716.04850 8420.44 6630.40 563.92400 6630.74