Comparison operators

OceanBase Database supports general comparison operators.

Overview

The following table describes the comparison operators supported by OceanBase Database.

Description

The result of a comparison is 1 (TRUE), 0 (FALSE), or NULL. An operand can be a number or a string. Strings and numbers are automatically converted to each other as needed. By default, string comparisons are case-insensitive and use the current character set.

The following sections describe the syntax of some operators and the operation results if a NULL input is used.

<=>

In an operation that contains the <=> operator, if both operands are NULL, 1 is returned. If only one operand is NULL, 0 is returned. Here is an 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 operation is as follows:

expr IN (value,...)

The NOT IN syntax is equivalent to NOT (expr IN (value,...)) or expr NOT IN (value,...).

In an operation that contains the IN operator, if expr equals any value in the value,... list, 1 (TRUE) is returned. Otherwise, 0 (FALSE) is returned. When expr is NULL, NULL is returned. When expr is not NULL and the set contains NULL, if any non-NULL value in the set equals expr, TRUE is returned. Otherwise, NULL is returned.

We recommend that you do not mix quoted and unquoted values in the value,... list. This returns inconsistent results because the comparison rules for quoted values (such as strings) and unquoted values (such as numbers) are different.

Here is an 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 operation is as follows:

expr BETWEEN min AND max

NOT BETWEEN AND is equivalent to NOT (expr BETWEEN min AND max) or expr NOT BETWEEN min AND max.

In a BETWEEN AND operation, if expr is greater than or equals min and is less than or equals max, 1 is returned. Otherwise, 0 is returned. If all arguments are of the same data type, the BETWEEN AND operation is equivalent to the following expression: (min <= expr AND expr <= max).

When expr is NULL, or min and max are both NULL, the result is NULL. When expr is not NULL, and only min or max is NULL, if the operation result can be determined only by using expr and min or max, this operation result is used. Otherwise, NULL is returned.

Here is an 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 operation is as follows:

expr LIKE pat [ESCAPE 'escape_char']

The NOT LIKE syntax is equivalent to NOT (expr LIKE pat [ESCAPE 'escape_char']) or expr NOT LIKE pat [ESCAPE 'escape_char'].

You can use LIKE to match a string that contains a wildcard. The return value is 1 (TRUE) or 0 (FALSE). If expr or pat is NULL, the result is NULL. You can use the ESCAPE parameter to define escape characters. If pat contains escape_char, the characters after escape_char are processed as ordinary characters during the match.

Two types of wildcards are supported:

• %: matches a string of any length.

• _: matches a single character.

Here is an example:

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