Operator Precedence

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.

Table 11-4: Operators Precedence

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

Why Operator Precedence Is Important

The issue of operator precedence is not as trivial as it may seem. Take, for example, a ubiquitous computer program such as Microsoft Calculator (or any modern calculator, for that matter), which is bundled with every copy of the Windows operating system. It seems as if this program differentiates between Standard and Scientific types of calculations. When Standard mode is selected from the View menu, the expression 5+4*3, for example, evaluates to 27, and if a Scientific mode is selected from the menu, exactly the same expression suddenly gives 17 as an answer; a regular calculator would insist on 27 as a correct answer, no matter what.

For a historical reason — namely, dearth of memory, the first calculators had only two registers (i.e., storage for input numbers) so only two numbers could be placed there and then an operation had to be performed to free up space for the next number. In our example it would look like the following sequence of instructions: put 5 into one register, then put 4 into the second register, perform operation (+), save the result into one of the registers, put number 3 into the second register, perform operation (*), and so on.

The result is calculated after each operator button is pressed. Gradually, as hardware became cheaper, it became possible to use more than two registers — and implement precedence rules that had been established in mathematics for centuries.

By then, there was already a huge amount of calculators out there doing math the "simple" way, and rather than risk consumer revolt, vendors decided on having two types of calculators — one for "normal" people, and one for the "scientific" ones. This, of course, was transferred into software calculators.

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