All Products
Search
Document Center

Comparison operators

Last Updated: Dec 31, 2019

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 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.
<=> Binary NULL-safe equal. The result is either TRUE or FALSE.

The following is the special treatments of the NULL value by some operators:

  • 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.

Example:

  1. OceanBase (root@oceanbase)> SELECT 1 IN (1, NULL), 1 IN (2, NULL);
  2. +----------------+----------------+
  3. | 1 IN (1, NULL) | 1 IN (2, NULL) |
  4. +----------------+----------------+
  5. | 1 | NULL |
  6. +----------------+----------------+
  7. 1 row in set (0.01 sec)
  8. OceanBase (root@oceanbase)> SELECT 1 BETWEEN 0 AND NULL, 1 BETWEEN 2 AND NULL;
  9. +----------------------+----------------------+
  10. | 1 BETWEEN 0 AND NULL | 1 BETWEEN 2 AND NULL |
  11. +----------------------+----------------------+
  12. | NULL | 0 |
  13. +----------------------+----------------------+
  14. 1 row in set (0.01 sec)