OceanBase AI Database supports common logical operators.
Overview of logical operators
The following table describes the logical operators supported by the current version of OceanBase AI Database.
operator |
operand |
Meaning |
|---|---|---|
NOT/! |
unitary | Logical NOT |
AND/&& |
binary | Logical AND |
OR/\|\| |
binary | Logical OR |
XOR |
Multiple | Logical XOR |
Description
In SQL, the results of all logical operations are TRUE, FALSE, or NULL (UNKNOWN). OceanBase AI Database implements them as 1 (TRUE), 0 (FALSE), and NULL, evaluating any non-zero, non-NULL value as TRUE.
Limitations
Only the array data type supports the ANY operator. For usage and examples, see Array comparison 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.
An example is as follows:
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.
An example is as follows:
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/||
When both operands are not NULL, the result is 1 if either operand is non-zero, and 0 otherwise. For a NULL operand, if the other operand is non-zero, the result is 1; otherwise, the result is 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, the function returns NULL. For non-NULL operands, if an odd number of operands are non-zero, the result is 1; otherwise, the result is 0.
For 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
Note
a XOR b is mathematically equal to (a AND (NOT b)) OR ((NOT a) and b).