OceanBase AI Database supports common comparison operators.
Overview of comparison operators
The following table describes the comparison operators supported by the current version of OceanBase AI Database.
operator |
operand |
Meaning |
Result of operations involving NULL values |
|---|---|---|---|
= |
binary | equal to | NULL |
<=> |
binary | Security equals | For more information, see the following topics. |
<> / != |
binary | Not equal to | NULL |
> |
binary | greater than | NULL |
>= |
binary | greater than or equal to | NULL |
< |
binary | less than | NULL |
<= |
binary | less than or equal to | NULL |
[NOT] IN |
binary | Whether in the set | For more information, see the following topics. |
[NOT] BETWEEN AND |
Ternary | Whether it is within the interval | For more information, see the following topics. |
[NOT] LIKE |
Ternary | String wildcard matching | For more information, see the following topics. |
IS [NOT] TRUE |
unitary | Equal to TRUE |
TRUE or FALSE |
IS [NOT] FALSE |
unitary | Equal to FALSE |
TRUE or FALSE |
IS [NOT] NULL |
unitary | Equal to NULL |
TRUE or FALSE |
Description
The result of a comparison operation is 1 (TRUE), 0 (FALSE), or NULL. The operands of the operation can be numbers and strings. As needed, strings are automatically converted to numbers, and numbers are automatically converted to strings. By default, string comparison is case-insensitive and uses the current character set.
The following table describes the syntax of some operators and the results when NULL values are involved in the operations.
<=>
For the security equal to <=>, when NULL participates in the operation, if both operands are NULL, then 1 is returned; if one operand is NULL, then 0 is returned. 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 for IN is as follows:
expr IN (value,...)
The NOT IN syntax is equivalent to NOT (expr IN (value,...)), that is, expr NOT IN (value,...).
For the IN operator, if expr equals any value in the value,... list, it returns 1 (TRUE); otherwise, it returns 0 (FALSE). When 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 equals expr; otherwise, the result is NULL.
It is not recommended to mix quoted and unquoted values in the value,... list. This is because the comparison rules for quoted values (such as strings) and unquoted values (such as numbers) are different, and mixing these types may lead to inconsistent results.
For 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 for BETWEEN AND is as follows:
expr BETWEEN min AND max
The NOT BETWEEN AND syntax is equivalent to NOT (expr BETWEEN min AND max), that is, expr NOT BETWEEN min AND max.
For the BETWEEN AND operation, if expr is greater than or equal to min and less than or equal to max, BETWEEN returns 1; otherwise, it returns 0. If all parameters are of the same type, this 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 by expr and min/max. If the result can be determined solely from expr and min/max, that result is used; otherwise, NULL is returned.
An example is as follows:
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 LIKE syntax is as follows:
expr LIKE pat [ESCAPE 'escape_char']
The NOT LIKE syntax is equivalent to NOT (expr LIKE pat [ESCAPE 'escape_char']), that is, expr NOT LIKE pat [ESCAPE 'escape_char'].
LIKE is used for string wildcard matching. The result is 1 (TRUE) or 0 (FALSE). If expr or pat is NULL, the result is NULL. ESCAPE is used to define an escape character. If pat contains escape_char, the characters following escape_char are treated as ordinary characters during matching.
There are two types of wildcards:
%: Matches any length of string._: 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