# Operator Precedence

## 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

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```  BackCover  SQL Bible  Preface  Part I: SQL Basic Concepts and Principles  Part II: Creating and Modifying Database Objects  Part III: Data Manipulation and Transaction Control  Part IV: Retrieving and Transforming Data  Chapter 8: Understanding SELECT Statement  Chapter 9: Multitable Queries  Chapter 10: SQL Functions  Chapter 11: SQL Operators  Arithmetic Operators  Logical Operators  Operator Precedence  Assignment Operator  Comparison Operators  Bitwise Operators  User-defined Operators  Summary  Part V: Implementing Security Using System Catalogs  Part VI: Beyond SQL--Procedural Programming and Database Access Mechanisms  Part VII: Appendix  List of Figures  List of Tables  List of Code Examples  List of Sidebars  CD Content