Today, we will introduce the usage of comparison functions and operators in the GBase 8a database. Let's start with the operator precedence, listed from highest to lowest. Operators on the same line have the same precedence:

BINARY, COLLATE
!
-(unary minus), ~(unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
&&, AND
OR, XOR
:=

Comparison Functions and Operators

The result of a comparison operation can be 1 (TRUE), 0 (FALSE), or NULL. These operations can be used with both numbers and strings. Strings are automatically converted to numbers as needed, and numbers can also be automatically converted to strings.

Description:

Some functions in this article (e.g., GREATEST() and LEAST()) do not return 1 (TRUE), 0 (FALSE), or NULL, but still perform comparisons based on the rules below. GBase 8a MPP Cluster uses the following rules for comparisons:

If one or both arguments are NULL, the comparison result is NULL, except for the <=> operator (when parameters include NULL, the result is not NULL).
If both arguments in a comparison operation are strings, they are compared as strings (case-insensitive by default).
If both arguments are numbers, they are compared numerically.
If one argument is a string and the other is a number, the string is converted to a number, and the comparison is numeric. For strings starting with numbers, the numeric portion is used for comparison. For strings that do not start with numbers, the result is 0.
If a hexadecimal value is not compared with a number, it is treated as a binary string.
If one argument is of DATETIME type and the other is a constant, the constant is converted to a timestamp before comparison.
In other cases, arguments are compared as floating-point (REAL) numbers.

Note:

For safety, it's recommended to use complete DATETIME/DATE/TIME strings in comparisons. To perform comparisons, you can use the CAST() function to convert values to other types.

1.1 `=` (Equals)

Syntax: a = b

Explanation: Returns 1 if both operands are equal.

Examples:

Example 1: Both operands are numbers.

  gbase> SELECT 1 = 0 FROM dual;
  +-------+
  | 1 = 0 |
  +-------+
  |     0 |
  +-------+
  1 row in set

Example 2: Comparing a number with a string.

  gbase> SELECT '0' = 0 FROM dual;
  +---------+
  | '0' = 0 |
  +---------+
  |       1 |
  +---------+
  1 row in set

Example 3: Comparing a floating-point number with an integer.

  gbase> SELECT 0.0 = 0 FROM dual;
  +---------+
  | 0.0 = 0 |
  +---------+
  |       1 |
  +---------+
  1 row in set

Example 4: Both operands are NULL.

  gbase> SELECT NULL = NULL FROM dual;
  +--------------+
  | NULL = NULL  |
  +--------------+
  |         NULL |
  +--------------+
  1 row in set

1.2 `<=>` (NULL-Safe Equals)

Syntax: a <=> b

Explanation: This operator performs an equality comparison like the = operator but differs in the following cases:
1) If all operands are NULL, it returns 1 instead of NULL.
2) If only one operand is NULL, it returns 0 instead of NULL.

Examples:

Example 1: All or some operands are NULL.

  gbase> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL FROM dual;
  +---------+---------------+-------------+
  | 1 <=> 1 | NULL <=> NULL | 1 <=> NULL  |
  +---------+---------------+-------------+
  |       1 |             1 |           0 |
  +---------+---------------+-------------+
  1 row in set

Example 2: Results using the = operator.

  gbase> SELECT 1 = 1, NULL = NULL, 1 = NULL FROM dual;
  +-------+-------------+-----------+
  | 1 = 1 | NULL = NULL | 1 = NULL  |
  +-------+-------------+-----------+
  |     1 |        NULL |      NULL |
  +-------+-------------+-----------+
  1 row in set

1.3 `<>`, `!=` (Not Equals)

Syntax: a <> b or a != b

Explanation: Returns 1 if both operands are not equal. Returns NULL if one operand is NULL.

Examples:

Example 1: Both operands are strings.

  gbase> SELECT '01' <> '1' FROM dual;
  +-----------+
  | '01' <> '1' |
  +-----------+
  |         1 |
  +-----------+
  1 row in set

Example 2: One operand is a string.

  gbase> SELECT 01 <> '1' FROM dual;
  +---------+
  | 01 <> '1' |
  +---------+
  |       0 |
  +---------+
  1 row in set

Example 3: Both operands are strings.

  gbase> SELECT 'zapp' <> 'zappp' FROM dual;
  +-------------------+
  | 'zapp' <> 'zappp' |
  +-------------------+
  |                 1 |
  +-------------------+
  1 row in set

1.4 `<=` (Less Than or Equal To)

Syntax: a <= b

Explanation: Returns 1 if a is less than or equal to b. Returns NULL if one operand is NULL.

Example:

Example 1: Both operands are numbers.

  gbase> SELECT 0.1 <= 2 FROM dual;
  +----------+
  | 0.1 <= 2 |
  +----------+
  |        1 |
  +----------+
  1 row in set

1.5 `<` (Less Than)

Syntax: a < b

Explanation: Returns 1 if a is less than b. Returns NULL if one operand is NULL.

Example:

Example 1: Both operands are numbers.

  gbase> SELECT 2 < 2 FROM dual;
  +-------+
  | 2 < 2 |
  +-------+
  |     0 |
  +-------+
  1 row in set

1.6 `>=` (Greater Than or Equal To)

Syntax: a >= b

Explanation: Returns 1 if a is greater than or equal to b. Returns NULL if one operand is NULL.

Example:

Example 1: Both operands are numbers.

  gbase> SELECT 2 >= 2 FROM dual;
  +--------+
  | 2 >= 2 |
  +--------+
  |      1 |
  +--------+
  1 row in set

1.7 `>` (Greater Than)

Syntax: a > b

Explanation: Returns 1 if a is greater than b. Returns NULL if one operand is NULL.

Example:

Example 1: Both operands are numbers.

  gbase> SELECT 2 > 2 FROM dual;
  +-------+
  | 2 > 2 |
  +-------+
  |     0 |
  +-------+
  1 row in set

1.8 `IS [NOT]`

Syntax: IS [NOT] <NULL|TRUE|FALSE|UNKNOWN>

Explanation: Checks a value against a Boolean (NULL, TRUE, FALSE, UNKNOWN).

Examples:

Example 1: Checking 1, 0, and NULL with IS.

  gbase> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN FROM dual;
  +-----------+------------+------------------+
  | 1 IS TRUE | 0 IS FALSE | NULL IS UNKNOWN  |
  +-----------+------------+------------------+
  |         1 |          1 |                1 |
  +-----------+------------+------------------+
  1 row in set

Example 2: Use the IS NOT statement to check 1, 0, and NULL.

  gbase> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN FROM dual;
  +------------------+------------------+----------------------+
  | 1 IS NOT UNKNOWN | 0 IS NOT UNKNOWN | NULL IS NOT UNKNOWN  |
  +------------------+------------------+----------------------+
  |                1 |                1 |                    0 |
  +------------------+------------------+----------------------+
  1 row in set

Example 3: Use the IS statement to check if a value is NULL.

  gbase> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL FROM dual;
  +-----------+-----------+---------------+
  | 1 IS NULL | 0 IS NULL | NULL IS NULL  |
  +-----------+-----------+---------------+
  |         0 |         0 |             1 |
  +-----------+-----------+---------------+
  1 row in set

Example 4: Use the IS NOT statement to check if a value is not NULL.

  gbase> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL FROM dual;
  +---------------+---------------+-------------------+
  | 1 IS NOT NULL | 0 IS NOT NULL | NULL IS NOT NULL  |
  +---------------+---------------+-------------------+
  |             1 |             1 |                 0 |
  +---------------+---------------+-------------------+
  1 row in set

1.9 expr BETWEEN min AND max

Syntax: expr BETWEEN min AND max

Function Description: Returns 1 if the value of expr is between min and max (inclusive), otherwise returns 0. If all parameters are of the same type, this expression is equivalent to (min <= expr AND expr <= max). Other type conversions follow the rules specified at the beginning of this chapter.

Examples:

Example 1: All parameters are of the same type, expr is not between min and max.

  gbase> SELECT 1 BETWEEN 2 AND 3 FROM dual;
  +-------------------+
  | 1 BETWEEN 2 AND 3 |
  +-------------------+
  |                 0 |
  +-------------------+
  1 row in set

Example 2: All parameters are of the same type, expr is between min and max.

  gbase> SELECT 'b' BETWEEN 'a' AND 'c' FROM dual;
  +-------------------------+
  | 'b' BETWEEN 'a' AND 'c' |
  +-------------------------+
  |                       1 |
  +-------------------------+
  1 row in set

Example 3: Parameters include both numbers and strings.

  gbase> SELECT 2 BETWEEN 2 AND '3' FROM dual;
  +---------------------+
  | 2 BETWEEN 2 AND '3' |
  +---------------------+
  |                   1 |
  +---------------------+
  1 row in set

1.10 expr NOT BETWEEN min AND max

Syntax: expr NOT BETWEEN min AND max

Equivalent to NOT(expr BETWEEN min AND max).

1.11 COALESCE(value,...)

Syntax: COALESCE(value,...)

Function Description: Returns the first non-NULL value in the list. If all values are NULL, it returns NULL.

Examples:

Example 1: One of the parameter values is NULL.

  gbase> SELECT COALESCE(NULL,1) FROM dual;
  +------------------+
  | COALESCE(NULL,1) |
  +------------------+
  |                1 |
  +------------------+
  1 row in set

Example 2: Every parameter value is NULL.

  gbase> SELECT COALESCE(NULL,NULL,NULL) FROM dual;
  +--------------------------+
  | COALESCE(NULL,NULL,NULL) |
  +--------------------------+
  |                     NULL |
  +--------------------------+
  1 row in set

1.12 GREATEST(value1,value2,...)

Syntax: GREATEST(value1,value2,...)

Function Description:

Returns the largest value among the given parameters when there are two or more parameters. If any parameter is NULL, it returns NULL. If all parameters are strings, the comparison is case-insensitive by default. To perform case-sensitive comparison, prepend the sensitive string parameter with BINARY.

Comparison rules are as follows:

If the return value is in an INTEGER context or all parameters are integers, they use integer comparison.
If the return value is in a REAL context or all parameters are real numbers, they use real number comparison.
If all parameters are case-sensitive strings, the comparison is case-sensitive; otherwise, it is case-insensitive.

Examples:

Example 1: Parameter values are integers.

  gbase> SELECT GREATEST(2,0) FROM dual;
  +---------------+
  | GREATEST(2,0) |
  +---------------+
  |             2 |
  +---------------+
  1 row in set

Example 2: Parameter values are floating-point numbers.

  gbase> SELECT GREATEST(34.0,3.0,5.0,767.0) FROM dual;
  +------------------------------+
  | GREATEST(34.0,3.0,5.0,767.0) |
  +------------------------------+
  |                        767.0 |
  +------------------------------+
  1 row in set

Example 3: Parameter values are strings, comparison is case-insensitive.

  gbase> SELECT GREATEST('B','a','C') FROM dual;
  +-----------------------+
  | GREATEST('B','a','C') |
  +-----------------------+
  | C                     |
  +-----------------------+
  1 row in set

Example 4: Parameter values are strings, using BINARY for case-sensitive comparison.

  gbase> SELECT GREATEST('B',BINARY 'a','C') FROM dual;
  +------------------------------+
  | GREATEST('B',BINARY 'a','C') |
  +------------------------------+
  | a                            |
  +------------------------------+
  1 row in set

Example 5: Parameter values include NULL, result is NULL.

  gbase> SELECT GREATEST('B',NULL,'C') FROM dual;
  +------------------------+
  | GREATEST('B',NULL,'C') |
  +------------------------+
  | NULL                   |
  +------------------------+
  1 row in set

1.13 expr IN (value,...)

Syntax: expr IN (value,...)

Function Description:

Returns 1 if expr is any of the values in the IN list; otherwise, returns 0. If all values in the IN list are constants, they are evaluated and sorted based on the type of expr. If the left-hand expression is NULL, or no matching value is found in the list and one of the list expressions is NULL, IN returns NULL. The IN() syntax can also be used for subqueries.

Examples:

Example 1: expr is not any of the values in the IN list.

  gbase> SELECT 2 IN (0,3,5,'8') FROM dual;
  +------------------+
  | 2 IN (0,3,5,'8') |
  +------------------+
  |                0 |
  +------------------+
  1 row in set

Example 2: expr is a value in the IN list.

gbase> SELECT '1' IN (0, 3, 5, '1') AS v_1, '1' IN (0, 3, 5, NULL) AS v_null FROM dual;
+-----+--------+
| v_1 | v_null |
+-----+--------+
|   1 |   NULL |
+-----+--------+
1 row in set

Example 3: The value of expr is NULL.

gbase> SELECT NULL IN (0, 3, 5, 'wefwf') FROM dual;
+-------------------------+
| NULL IN (0, 3, 5, 'wefwf') |
+-------------------------+
|                    NULL |
+-------------------------+
1 row in set

Example 4: Using IN() function in a subquery. Tables and data used in the example:

CREATE TABLE sc (sno VARCHAR(4), grade INT);
INSERT INTO sc VALUES ('101', 82), ('102', 59), ('103', 90), ('104', 88), ('106', 82);

Query the student numbers of students who passed all courses.

gbase> SELECT sno FROM sc WHERE grade IN (SELECT grade FROM sc WHERE grade > 60) GROUP BY sno;
+------+
| sno  |
+------+
| 103  |
| 101  |
| 104  |
| 106  |
+------+
4 rows in set

1.14 `expr NOT IN (value,...)`

Syntax: expr NOT IN (value,...)

Equivalent to: NOT(expr IN (value,...))

1.15 `ISNULL(expr)`

Syntax: ISNULL(expr)

Function Description:

If expr is NULL, ISNULL() returns 1; otherwise, it returns 0.

Examples:

Example 1: The value of expr is not NULL.

gbase> SELECT ISNULL(1+1) FROM dual;
+-------------+
| ISNULL(1+1) |
+-------------+
|           0 |
+-------------+
1 row in set

Example 2: The result of 1/0 is NULL, so ISNULL() returns 1.

gbase> SELECT ISNULL(1/0) FROM dual;
+-------------+
| ISNULL(1/0) |
+-------------+
|           1 |
+-------------+
1 row in set

Example 3: Using = to compare a NULL value, ISNULL() returns 1.

gbase> SELECT ISNULL(NULL = NULL) FROM dual;
+-------------------+
| ISNULL(NULL = NULL) |
+-------------------+
|                 1 |
+-------------------+
1 row in set

The ISNULL() function shares some characteristics with the IS NULL comparison operator. For usage of IS NULL, refer to Example 3 in section "1.8 IS [NOT]".

1.16 `LEAST(value1, value2,...)`

Syntax: LEAST(value1, value2,...)

Function Description:

With two or more arguments, returns the smallest argument value. If any argument is NULL, the return value of LEAST() is NULL.

The comparison rules for LEAST() are the same as those for GREATEST().

Examples:

Example 1: Argument values are integers, returning the smallest value.

gbase> SELECT LEAST(2, 0) FROM dual;
+------------+
| LEAST(2, 0) |
+------------+
|          0 |
+------------+
1 row in set

Example 2: Argument values are floating-point numbers, returning the smallest value.

gbase> SELECT LEAST(34.0, 3.0, 5.0, 767.0) FROM dual;
+---------------------------+
| LEAST(34.0, 3.0, 5.0, 767.0) |
+---------------------------+
|                       3.0 |
+---------------------------+
1 row in set

Example 3: Argument values are strings, case-insensitive comparison.

gbase> SELECT LEAST('B', 'A', 'C') FROM dual;
+--------------------+
| LEAST('B', 'A', 'C') |
+--------------------+
| A                  |
+--------------------+
1 row in set

gbase> SELECT LEAST('B', 'a', 'C') FROM dual;
+---------------------+
| LEAST('B', 'a', 'C') |
+---------------------+
| a                   |
+---------------------+
1 row in set

Example 4: Argument values are strings, using BINARY for case-sensitive comparison.

gbase> SELECT LEAST(BINARY 'B', BINARY 'a', 'C') FROM dual;
+-----------------------------------+
| LEAST(BINARY 'B', BINARY 'a', 'C') |
+-----------------------------------+
| B                                 |
+-----------------------------------+
1 row in set

Example 5: If an argument value includes NULL, the result is NULL.

gbase> SELECT LEAST('C', NULL, 'B') FROM dual;
+---------------------+
| LEAST('C', NULL, 'B') |
+---------------------+
| NULL                |
+---------------------+
1 row in set

Introduction to Comparison Functions and Operators in GBase 8a MPP Cluster

Comparison Functions and Operators

1.1 = (Equals)

Examples:

1.2 <=> (NULL-Safe Equals)

Examples:

1.3 <>, != (Not Equals)

Examples:

1.4 <= (Less Than or Equal To)

Example:

1.5 < (Less Than)

Example:

1.6 >= (Greater Than or Equal To)

Example:

1.7 > (Greater Than)

Example:

1.8 IS [NOT]

Examples:

1.9 expr BETWEEN min AND max

Examples:

1.10 expr NOT BETWEEN min AND max

1.11 COALESCE(value,...)

Examples:

1.12 GREATEST(value1,value2,...)

Examples:

1.13 expr IN (value,...)

Examples:

1.14 expr NOT IN (value,...)

1.15 ISNULL(expr)

Examples:

1.16 LEAST(value1, value2,...)

Examples:

1.1 `=` (Equals)

1.2 `<=>` (NULL-Safe Equals)

1.3 `<>`, `!=` (Not Equals)

1.4 `<=` (Less Than or Equal To)

1.5 `<` (Less Than)

1.6 `>=` (Greater Than or Equal To)

1.7 `>` (Greater Than)

1.8 `IS [NOT]`

1.14 `expr NOT IN (value,...)`

1.15 `ISNULL(expr)`

1.16 `LEAST(value1, value2,...)`