Arithmetic Operators

Arithmetic Operators

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.

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

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