SQL Bible [Electronic resources] نسخه متنی

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.

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

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.

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