Operators
Case Insensitivity
Operator names are case-insensitive.
Logical Operators
Logical operators in precedence order:
NOT
,
AND
,
OR
.
Comparison Operators
Comparison operators:
=
,
>
,
>=
,
<
,
<=
,
<>
(not equal).
These operators can compare only values of comparable types. (Exact numeric values and approximate numerical values are comparable types.) Attempting to compare incomparable types yields
false
. If either value in a comparison evaluates to
NULL
, then the result is unknown (in SQL 3-valued logic).
Comparison of string values is restricted to
=
and
<>
. Two strings are equal if and only if they contain the same sequence of characters.
Comparison of boolean values is restricted to
=
and
<>
.
Arithmetic Operators
Arithmetic operators in precedence order:
+
,-
(unary)*
,/
(multiplication and division)+
,-
(addition and subtraction)
Arithmetic operations obey numeric promotion rules of the Java programming language.
Between Operator
arithmetic-expr1 [NOT] BETWEEN
arithmetic-expr2 AND
arithmetic-expr3
The
BETWEEN
comparison operator includes its endpoints. For example:
age BETWEEN 5 AND 9
is equivalent toage >= 5 AND age <= 9
age NOT BETWEEN 5 AND 9
is equivalent toage < 5 OR age > 9
String Set Membership
identifier [NOT] IN (
string-literal1,
string-literal2,
...)
The
identifier must evaluate to either a string or
NULL
. If it is
NULL
, then the value of this expression is unknown. You can use a maximum of 32,767 string-literals in the string set.
Pattern Matching
identifier [NOT] LIKE
pattern-value [ESCAPE
escape-character]
The identifier must evaluate to a string.
The pattern-value is a string literal, in which some characters bear special meaning:
_
(underscore) can match any single character.%
(percent) can match any sequence of zero or more characters.- escape-character preceding either of the special characters changes them into ordinary characters (which match only themselves).
Null Header or Property
identifier IS NULL
This comparison operator tests whether a message header is null, or a message property is absent.
identifier IS NOT NULL
This comparison operator tests whether a message header or message property is non-null.