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 to   age >= 5 AND age <= 9
    • age NOT BETWEEN 5 AND 9   is equivalent to   age < 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.