Comparison operators

OceanBase Database supports commonly used comparison operators.

Overview

The following table describes the comparison operators supported in the current version of OceanBase Database.

Syntax

The result of a comparison operator is 1 (TRUE), 0 (FALSE), or NULL. The operands can be numbers or strings. If needed, strings are automatically converted to numbers, and numbers are automatically converted to strings. By default, string comparisons are case-insensitive and use the current character set.

The following sections describe the syntax and behavior of some operators when NULL is involved.

<=>

For the safe equal to operator <=>, if both operands are NULL, the result is 1; if one operand is NULL, the result is 0. Example:

obclient> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
+---------+---------------+------------+
| 1 <=> 1 | NULL <=> NULL | 1 <=> NULL |
+---------+---------------+------------+
|       1 |             1 |          0 |
+---------+---------------+------------+
1 row in set

[NOT] IN

The syntax of the IN operator is as follows:

expr IN (value,...)

The syntax of the NOT IN operator is equivalent to NOT (expr IN (value,...)), which is the same as expr NOT IN (value,...).

For the IN operator, if expr is equal to any value in the value,... list, the result is 1 (TRUE); otherwise, the result is 0 (FALSE). If expr is NULL, the result is NULL. If expr is not NULL and the set contains NULL, the result is TRUE if there is a non-NULL value in the set that is equal to expr; otherwise, the result is NULL.

We recommend that you do not mix quoted and unquoted values in the value,... list. Quoted values, such as strings, and unquoted values, such as numbers, have different comparison rules. Mixed types may result in inconsistent results.

Example:

obclient> SELECT 1 IN (1, NULL), 1 IN (2, NULL);
+----------------+----------------+
| 1 IN (1, NULL) | 1 IN (2, NULL) |
+----------------+----------------+
|              1 |           NULL |
+----------------+----------------+
1 row in set

[NOT] BETWEEN AND

The syntax of the BETWEEN AND operator is as follows:

expr BETWEEN min AND max

The syntax of the NOT BETWEEN AND operator is equivalent to NOT (expr BETWEEN min AND max), which is the same as expr NOT BETWEEN min AND max.

For the BETWEEN AND operator, if expr is greater than or equal to min and less than or equal to max, the result is 1; otherwise, the result is 0. If all parameters are of the same type, the result is equivalent to the expression (min <= expr AND expr <= max).

If expr is NULL, or both min and max are NULL, the result is NULL. If expr is not NULL and only min or max is NULL, the result is determined based on expr and min/max. If the result can be determined, it is returned; otherwise, the result is NULL.

Example:

obclient> 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

[NOT] LIKE

The syntax of the LIKE operator is as follows:

expr LIKE pat [ESCAPE 'escape_char']

The syntax of the NOT LIKE operator is equivalent to NOT (expr LIKE pat [ESCAPE 'escape_char']), which is the same as expr NOT LIKE pat [ESCAPE 'escape_char'].

The LIKE operator is used for string wildcard matching. The result is 1 (TRUE) or 0 (FALSE). If expr or pat is NULL, the result is NULL. The ESCAPE option is used to define an escape character. If pat contains escape_char, the character following escape_char is treated as a normal character during matching.

The following are the supported wildcards:

• %: Matches any string of any length.

• _: Matches a single character.

Example:

obclient> SELECT 'ab%' LIKE 'abc%' ESCAPE 'c';
+------------------------------+
| 'ab%' LIKE 'abc%' ESCAPE 'c' |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set