Comparison operators

seekdb supports the common comparison operators.

Overview of comparison operators

The following table describes the comparison operators supported by the current version of seekdb.

Operator	Operand	Meaning	Result when NULL is involved
=	2	Equal to	NULL
<=>	2	Safe equal to	See the following description
<> / !=	2	Not equal to	NULL
>	2	Greater than	NULL
>=	2	Greater than or equal to	NULL
<	2	Less than	NULL
<=	2	Less than or equal to	NULL
[NOT] IN	2	Whether the value is in the set	See the following description
[NOT] BETWEEN AND	3	Whether the value is in the range	See the following description
[NOT] LIKE	3	String wildcard matching	See the following description
IS [NOT] TRUE	1	Whether the value is equal to TRUE	TRUE or FALSE
IS [NOT] FALSE	1	Whether the value is equal to FALSE	TRUE or FALSE
IS [NOT] NULL	1	Whether the value is equal to NULL	TRUE or FALSE

Description

The result of a comparison operation is 1 (TRUE), 0 (FALSE), or NULL. The operands can be numbers or strings. When comparing strings and numbers, 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 of some operators and the results 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:

SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
+---------+---------------+------------+
| 1 <=> 1 | NULL <=> NULL | 1 <=> NULL |
+---------+---------------+------------+
|       1 |             1 |          0 |
+---------+---------------+------------+
1 row in set (0.001 sec)

[NOT] IN

The IN operator is used to check whether a value is in a set. The syntax is as follows:

expr IN (value,...)

The NOT IN operator is equivalent to NOT (expr IN (value,...)) or 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 a non-NULL value in the set is equal to expr, otherwise NULL.

It is not recommended to mix quoted and unquoted values in the value,... list. The comparison rules for quoted values (such as strings) and unquoted values (such as numbers) are different, which may lead to inconsistent results.

Example:

SELECT 1 IN (1, NULL), 1 IN (2, NULL);
+----------------+----------------+
| 1 IN (1, NULL) | 1 IN (2, NULL) |
+----------------+----------------+
|              1 |           NULL |
+----------------+----------------+
1 row in set (0.001 sec)

[NOT] BETWEEN AND

The BETWEEN AND operator is used to check whether a value is in a range. The syntax is as follows:

expr BETWEEN min AND max

The NOT BETWEEN AND operator is equivalent to NOT (expr BETWEEN min AND max) or 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 by expr and min/max. If the result can be determined, it is used; otherwise, the result is NULL.

Example:

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.001 sec)

[NOT] LIKE

The LIKE operator is used for string wildcard matching. The syntax is as follows:

expr LIKE pat [ESCAPE 'escape_char']

The NOT LIKE operator is equivalent to NOT (expr LIKE pat [ESCAPE 'escape_char']) or 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 clause 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 wildcards are supported:

• %: Matches any length of string.

• _: Matches a single character.

Example:

SELECT 'ab%' LIKE 'abc%' ESCAPE 'c';
+------------------------------+
| 'ab%' LIKE 'abc%' ESCAPE 'c' |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.001 sec)