All Products
Search
Document Center

Comparison operators

Last Updated: Jan 07, 2020

Comparison operators can be used in the WHERE clauses of the following statements:

  • DELETE
  • SELECT
  • UPDATE

Comparison operators can be used in the following clauses of SELECT statements:

  • WHERE
  • START WITH
  • CONNECT BY
  • HAVING

The following table lists all comparison operators.

Operator Operand Description Effect of NULL on the operators
= Binary Equal to The result is NULL.
<>/! = Binary Not equal to The result is NULL.
> Binary Greater than The result is NULL.
>= Binary Greater than or equal to The result is NULL.
< Binary Less than The result is NULL.
<= Binary Less than or equal to The result is NULL.
[NOT] IN Binary Whether it is in the set. For more information, see detailed explanations.
[NOT] BETWEEN AND Ternary Whether it is within the interval. For more information, see detailed explanations.
IS [NOT] TRUE Unary Whether it is equal to TRUE. The result is either TRUE or FALSE.
IS [NOT] FALSE Unary Whether it is equal to FALSE. The result is either TRUE or FALSE.
IS [NOT] NULL Unary Whether it is equal to NULL. The result is either TRUE or FALSE.

The following describes the special treatments performed by some operators on the NULL value :

  • value [NOT] IN ()

    • When the value is NULL, the result is NULL.
    • When the value is not NULL and the set contains NULL, the result is TRUE if a non-NULL value in the set is equal to the value. Otherwise, the result is NULL.
  • value [NOT] BETWEEN lower AND upper

    • When the value is NULL or both lower and upper are NULL, the result is NULL.
    • When the value is not NULL and either lower or upper is NULL, if the result can be evaluated by using the value, lower, and upper, the result is returned. Otherwise, NULL is returned.