These operators, just as the name implies, are used for arithmetic computations. The use of the arithmetic operators is very intuitive (assuming that one did not flunk elementary school), and they can be used in virtually every clause of the SQL statement. The full list of arithmetic operators is given in Table 11-1.

Operator |
Description |
---|---|

+ |
Addition; adds two numbers or — in the case of MS SQL Server — also concatenates strings. With this exception, the usage is identical across all three databases. Only MS SQL Server overloads the operator, using it both for concatenation and addition. |

- |
Subtraction; subtracts one numeric value from another. The usage is identical across all three databases. It is also used as a sign identity or unary negation operator. |

* |
Multiplication; multiplies one number by another. The usage is identical across all three databases. |

/ |
Division; divides one number by another. The usage is identical across all three databases. |

|| |
Concatenation operator; concatenates character strings; valid for Oracle and IBM DB2 UDB only. |

% |
Modulo; calculates integer remainder of a division. This is an MS SQL Server-only operator. The functionality of this operator is represented by the MOD function in both Oracle and IBM DB2 UDB. |

While doing arithmetic in SQL is relatively easy, one must pay attention to the data type used in the operations; for numeric values that would mean the precision and scale of the result; for datetime, the range of the resulting values and so on.

Some databases (like Oracle) would perform implicit conversion (whenever possible) if data types are not compatible with operator (e.g., string value used with addition operator); the others (DB2 UDB and SQL Server) would require explicit conversion into a compatible data type to perform an operation.

Here are several examples of arithmetic operator usage. To add two values in Oracle, the following query could be used:

SELECT 5 + 5 total_value FROM dual; total_value ----------- 10

The resulting TOTAL_VALUE is of a numeric data type; if, instead of the addition operator, the concatenation operator is used, the result would be quite different:

SELECT 5 || 5 total_value FROM dual; total_value ----------- 55

Here, Oracle implicitly converted numbers into characters and the TOTAL_VALUE is a result of this concatenation of the character data type. DB2 UDB also recognizes the concatenation operator, though it does not perform implicit conversion; this example executed in UDB would generate an error, requiring explicit data type conversion of the numbers into strings.

MS SQL Server works differently — it overloads the addition operator, for example, the addition operator is also used for concatenating strings. The decision to add operands or concatenate them is made based upon the operand's data types: SQL Server will add numbers and concatenate strings. The following examples demonstrate this functionality. This query has two integers as operands, and SQL Server calculates the sum of these (also an integer):

SELECT 5 + 5 total_value total_value ----------- 10

The following query uses two characters (signified by single quotes for literal values), and the result is concatenation (character data):

SELECT '5' + '5' total_value total_value ----------- 55

This operator also is used in date arithmetic. While numerous functions could be employed to add and subtract dates, the same functionality can be achieved with arithmetic operators. Here is an example of adding 10 days to a date in MS SQL Server 2000 (date is given as a literal and is converted to a datetime data type to ensure proper handling):

SELECT CAST('09/10/2003 12:00 AM' AS DATETIME) + 10 AS result_date result_date ------------------------- 2003-09-20 00:00:00.000

The date arithmetic could be very confusing. Oracle allows for extensive use of arithmetic for date manipulation, as does IBM DB2 UDB — whereas Microsoft SQL Server clearly steers users to use date- and time-related functions.

The following are examples of date arithmetic in Oracle. The first query adds a specified number of days to the specified date; the date might come from the table or be requested from the RDBMS:

SELECT SYSDATE, (SYSDATE) + 10 result_date FROM dual; SYSDATE result_date ---------- ------------ 9/17/2003 9/27/2003

For instance, to add two hours, the following operation could be used:

SELECT SYSDATE, (SYSDATE) + 2/24 result_date FROM dual; SYSDATE result_date --------------------- --------------------- 9/22/2003 11:04:05 AM 9/22/2003 1:04:05 PM

By adding 24/24 (evaluating to 1), you are essentially adding 1 day; henceforth 2/24 constitute 2 hours.

The same goes for the minutes and seconds:

SELECT SYSDATE , (SYSDATE) + 1/(24*60) result_date FROM dual; SYSDATE result_date --------------------- --------------------- 9/22/2003 11:08:26 AM 9/22/2003 11:09:26 AM

Note |
The parentheses around the 24*60 are significant. As you will learn in this chapter, the results are dependent on the precedence of operators used in the expression; these brackets make sure that 1 is divided by the product of 24 multiplied by 60 (number of minutes). |

Of course, the same manipulations would apply to other operators — as long as the operands are of compatible data types. You cannot multiply or divide dates, for example; only addition and subtraction is allowed. For the regular numeric data types, any arithmetic operator is valid.

For example, if you would like to calculate amount of sales tax (say, 8.5%) imposed on each of your transactions as recorded in the ACME database, view V_CUSTOMER_TOTALS:

SELECT order_number, total_price, total_price * 0.085 tax FROM v_customer_totals order_number total_price tax ---------------- ----------- --------- 523720 7511.00 638.43500 523721 8390.00 713.15000 523722 6608.00 561.68000

The modulo operator (%) calculates the integer remainder of a division. This is MS SQL Server-specific operator, as both Oracle and IBM use the MOD function instead. The following query calculates modulo of the integer 5 divided by 3 in MS SQL Server:

SELECT 5%3 remainder remainder --------- 2

Which is absolutely identical to the Oracle and IBM DB2 UDB function MOD (where SYSDUMMY1 table is Oracle's equivalent of DUAL):

SELECT MOD(5,3) remainder FROM sysibm.sysdummy1 remainder --------- 2

Caution |
SQL Server and Oracle would allow NULLs to be used with arithmetic operators (e.g., SELECT SYSDATE + NULL FROM DUAL; the result is NULL). It is important to understand, that for any operator given a NULL operand, the result always will be NULL — no matter what the other operand may be. Oracle excepts the concatenation operator from this rule, whereas IBM DB2 UDB does not allow NULLs in any operator's context. |