The following table lists the frequently used comparison operators.

Table 1. Comparison operators
Operator Description
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
= Equal
<> Not equal
! = Not equal

You can use comparison operators for all valid data types. All comparison operators are binary operators that return boolean values. Expressions like 1 < 2 < 3 are invalid (because no < operator is available to compare a Boolean value with 3).

In addition to the comparison operators, you can also use the BETWEEN construct.

  • a BETWEEN x AND y

    is equivalent to

    a >= x AND a <= y 

    is equivalent to

    a < x OR a > y

No difference exists between the two expression forms except that the CPU cycles require that you internally rewrite the first one into the second one.

To check whether a value is null, you can use the following constructs:

expression IS NULL
expression IS NOT NULL

Do not use expression = NULL because NULL is not equal to the null value. (The null value represents an unknown value, and it cannot be determined whether two unknown values are equal). This behavior complies with the SQL standard.

If expression evaluates to the null value, some applications may expect that expression = NULL returns true. We recommend that you modify these applications to comply with the SQL standard.