Logical operators

OceanBase Database supports general logical operators.

Overview

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

Notes

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.

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.

Example:

obclient> SELECT NOT NULL;
+----------+
 NOT NULL 
+----------+
     NULL 
+----------+
1 row in set (0.00 sec)

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

AND/&&

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

Example:

obclient> SELECT 1 AND NULL;
+------------+
 1 AND NULL 
+------------+
       NULL 
+------------+
1 row in set (0.00 sec)

obclient> SELECT 0 AND NULL;
+------------+
 0 AND NULL 
+------------+
          0 
+------------+
1 row in set (0.00 sec)

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.

Example:

obclient> SELECT 0 OR NULL;
+-----------+
 0 OR NULL 
+-----------+
      NULL 
+-----------+
1 row in set (0.00 sec)

obclient> SELECT 1 OR NULL;
+-----------+
 1 OR NULL 
+-----------+
         1 
+-----------+
1 row in set (0.00 sec)

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.

Example:

obclient> SELECT 1 XOR NULL;
+------------+
 1 XOR NULL 
+------------+
       NULL 
+------------+
1 row in set (0.00 sec)

obclient> SELECT 1 XOR 1 XOR 1;
+---------------+
 1 XOR 1 XOR 1 
+---------------+
             1 
+---------------+
1 row in set (0.00 sec)

Note

Mathematically, a XOR b equals (a AND (NOT b)) OR ((NOT a) and b).