Logical operators

OceanBase Database supports general logical operators.

Overview

The following table describes the logical operators supported by OceanBase Database.

Considerations

All SQL logical operators evaluate to TRUE, FALSE, or NULL (UNKNOWN). OceanBase Database implements TRUE as 1, FALSE as 0, and NULL as null, and evaluates any non-zero, non-NULL value to TRUE.

Limitations

Only ARRAY data supports the ANY operator. For more information, see Array judgment functions.

NOT/!

If the operand is 0, the result is 1. If the operand is not 0, the result is 0. If the operand is NOT NULL, the result is NULL.

Here are some examples:

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 not 0 and not NULL, the result is 0. If one or more operands are 0, the result is 0. Otherwise, NULL is returned.

Here are some examples:

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, and any operand is not 0, the result is 1. Otherwise, 0 is returned. If one operand is NULL and the other operand is not 0, the result is 1. Otherwise, NULL is returned. If both operands are NULL, the result is NULL.

Here are some examples:

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 result is NULL. If operands are not NULL and an odd number of operands is not 0, the result is 1. Otherwise, 0 is returned.

Here are some examples:

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