Logical operators

OceanBase Database supports common logical operators.

Overview

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

Considerations

In SQL, the result of all logical operators is TRUE, FALSE, or NULL (UNKNOWN). OceanBase Database implements them as 1 (TRUE), 0 (FALSE), and NULL, and evaluates any non-zero, non-NULL value as TRUE.

Limitations

Only the array data type supports the ANY operator. For more information about its usage and examples, see Array decision functions.

NOT/!

If the operand is 0, the result is 1. If the operand is non-zero, the result is 0, and NOT NULL returns NULL.

Here is an example:

obclient> SELECT NOT NULL;
+----------+
| NOT NULL |
+----------+
|     NULL |
+----------+
1 row in set

obclient> SELECT ! (1+1);
+---------+
| ! (1+1) |
+---------+
|       0 |
+---------+
1 row in set

AND/&&

If all operands are non-zero and not NULL, the result is 1. If one or more operands are 0, the result is 0. Otherwise, NULL is returned.

Here is an example:

obclient> SELECT 1 AND NULL;
+------------+
| 1 AND NULL |
+------------+
|       NULL |
+------------+
1 row in set

obclient> SELECT 0 AND NULL;
+------------+
| 0 AND NULL |
+------------+
|          0 |
+------------+
1 row in set

OR/||

If both operands are not NULL, the result is 1 if any operand is non-zero, otherwise 0. For NULL operands, if the other operand is non-zero, the result is 1, otherwise NULL. If both operands are NULL, the result is NULL.

Here is an example:

obclient> SELECT 0 OR NULL;
+-----------+
| 0 OR NULL |
+-----------+
|      NULL |
+-----------+
1 row in set

obclient> SELECT 1 OR NULL;
+-----------+
| 1 OR NULL |
+-----------+
|         1 |
+-----------+
1 row in set

XOR

If any operand is NULL, NULL is returned. For non-NULL operands, if an odd number of operands are non-zero, the result is 1; otherwise, 0 is returned.

Here is an example:

obclient> SELECT 1 XOR NULL;
+------------+
| 1 XOR NULL |
+------------+
|       NULL |
+------------+
1 row in set

obclient> SELECT 1 XOR 1 XOR 1;
+---------------+
| 1 XOR 1 XOR 1 |
+---------------+
|             1 |
+---------------+
1 row in set