The following table lists all comparison operators.
| Operator | Operand | Description | Given a NULL input |
|---|---|---|---|
= |
Binary | The equal to operator | The result is NULL. |
<> / != |
Binary | The not equal to operator | The result is NULL. |
> |
Binary | The greater than operator | The result is NULL. |
>= |
Binary | The greater than or equal to operator | The result is NULL. |
< |
Binary | The less than operator | The result is NULL. |
<= |
Binary | The less than or equal to operator | The result is NULL. |
[NOT] IN |
Binary | Whether a value is not within a specified set of values | For more information, see the following description. |
[NOT] BETWEEN AND |
Ternary | Whether a value is not within a specified range of values | For more information, see the following description. |
IS [NOT] TRUE |
Unary | Whether a value is not TRUE | The result is TRUE or FALSE. |
IS [NOT] FALSE |
Unary | Whether a value is not FALSE | The result is TRUE or FALSE. |
IS [NOT] NULL |
Unary | Whether the value is not NULL | The result is TRUE or FALSE. |
<=> |
Binary | The NULL-safe equal to operator | The result is TRUE or FALSE. |
Rules for NULL-value treatment in operations of some operators:
value [NOT] IN ():
value [NOT] BETWEEN lower AND upper:
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 (0.01 sec)
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 (0.01 sec)