All Products
Search
Document Center

Comparison operators

Last Updated: Jun 18, 2021

The following table lists all comparison operators.

Operator

Operand

Description

Effect of NULL on the operators

=

Binary

The equal operator.

The result is NULL.

<>/! =

Binary

The not equal operator.

The result is NULL.

>

Binary

The greater than operator.

The result is NULL.

>=

Binary

The greater than or equal operator.

The result is NULL.

<

Binary

The less than operator.

The result is NULL.

<=

Binary

The less than or equal operator.

The result is NULL.

[NOT] IN

Binary

Specifies whether the value is in the set.

For more information, see the descriptions.

[NOT] BETWEEN AND

Ternary

Specifies whether the value is within the range.

For more information, see the descriptions.

IS [NOT] TRUE

Unary

Specifies whether the value is equal to TRUE.

The result is TRUE or FALSE.

IS [NOT] FALSE

Unary

Specifies whether the value is equal to FALSE.

The result is TRUE or FALSE.

IS [NOT] NULL

Unary

Specifies whether the value is equal to NULL.

The result is TRUE or FALSE.

<=>

Binary

The NULL-safe equal operator.

The result is TRUE or FALSE.

The following list describes the special treatment of the NULL value by some operators.

  • value [NOT] IN ():
    • If the value is NULL, the result is NULL.
    • If the value is not NULL and the set contains NULL, the result is TRUE when a non-NULL value in the set is equal to the value. Otherwise, the result is NULL.
  • value [NOT] BETWEEN lower AND upper:
    • If the value is NULL or both the lower and upper values are NULL, the result is NULL.
    • Assume that the value is not NULL and the lower or upper value is NULL. In this case, the result that can be determined based on the value and the lower or upper value is returned.

Examples

OceanBase (root@oceanbase)> SELECT 1 IN (1, NULL), 1 IN (2, NULL);
+----------------+----------------+
| 1 IN (1, NULL) | 1 IN (2, NULL) |
+----------------+----------------+
|              1 |           NULL |
+----------------+----------------+
1 row in set (0.01 sec)

OceanBase (root@oceanbase)> SELECT 1 BETWEEN 0 AND NULL, 1 BETWEEN 2 AND NULL;
+----------------------+----------------------+
| 1 BETWEEN 0 AND NULL | 1 BETWEEN 2 AND NULL |
+----------------------+----------------------+
|                 NULL |                    0 |
+----------------------+----------------------+
1 row in set (0.01 sec)