This chapter describes the following elements of SQL statements:
This chapter identifies the differences in syntax between embedded and interactive SQL (where applicable). If the embedded syntax is dependent on a host language, see the Embedded SQL Companion Guide.
There are three types of SQL operators:
Arithmetic operators are used to combine numeric expressions arithmetically to form other numeric expressions.
The following are the valid arithmetic operators (in descending order of precedence):
Arithmetic Operator |
Description |
+ and – |
plus, minus (unary) |
** |
exponentiation (binary) |
* and / |
multiplication, division (binary) |
+ and – |
addition, subtraction (binary) |
Unary operators group from right to left, while binary operators group from left to right. Use the unary minus (-) to reverse the algebraic sign of a value.
To force a desired order of evaluation, use parentheses. For example:
(job.lowsal + 1000) * 12
is an expression in which the parentheses force the addition operator (+) to take precedence over the multiplication operator (*).
Comparison operators allow you to compare two expressions. SQL includes the following comparison operators:
Comparison Operator |
Description |
= |
equal to |
<> |
not equal to |
> |
greater than |
>= |
greater than or equal to |
< |
less than |
<= |
less than or equal to |
In addition, you can specify the comparison operator "not equal to" (<>) using "!=" or "^=".
All comparison operators are of equal precedence.
The equal sign (=) also serves as the assignment operator in assignment operations. For a discussion of assignment operations, see Assignment Operations.
There are three logical operators in SQL:
The not operator has the highest precedence, followed by the and operator, with the or operator having the least precedence. Use parentheses to change this behavior. For example, the following expression:
exprA or exprB and exprC
is evaluated as:
exprA or (exprB and exprC)
To change the order of evaluation, use parentheses:
(exprA or exprB) and exprC
When parentheses are used as shown, the DBMS Server evaluates (exprA or exprB) first, then uses the and operator for the result with exprC.
Parentheses can also be used to change the default evaluation order of a series of expressions combined with the same logical operator. For example, the following expression:
exprA and exprB and exprC
is evaluated as:
(exprA and exprB) and exprC
To change this default left-to-right grouping, use parentheses as follows:
exprA and (exprB and exprC)
The parentheses direct the DBMS Server to use the and operator for exprB and exprC, then use the and operator for that result with exprA.