Comparison Functions and Comparison Operators

Comparison operators are used to compare the sizes of two pieces of data and return a value of the Boolean type.

Comparison operators are binary operators. For details, see Table 1. For details about the comparison operators provided in M-compatible databases, see Table 1.

• The calculation priority of the inequality sign is higher than that of the equality sign.
• In M-compatible databases, only B-tree and UB-tree indexes are supported.
• <> and <=> do not support index scan.
• <=> does not support hash join or merge join.
• <=> does not support row comparison.
• <, <=, =, >=, >, and <> operators support comparison between row expressions and NULL values.
• The <, >, <=, >=, =, !=, <>, <=>, and BETWEEN AND operations can be performed on operands (string type, binary type, and bit string type) that contain 0 characters.
• Row expressions can be used as parameters for the IS NULL and ISNULL operations.

m_db=# SELECT (1,2) <=> row(2,3);
ERROR:  could not determine interpretation of row comparison operator <=>
LINE 1: select (1,2) <=> row(2,3);
                     ^
HINT:  unsupported operator.
m_db=# SELECT (1,2) < NULL;
 ?column? 
----------

(1 row)

m_db=# SELECT (1,2) <> NULL;
 ?column? 
----------

(1 row)
m_db=# SELECT (1, 2)  IS NULL;
 ?column? 
----------
 f
(1 row)

m_db=# SELECT ISNULL((1, 2));
 ?column? 
----------
 f
(1 row)

• >

  Description: "greater than" operator.

  Examples:

  1 2 3 4 5

  m_db=# SELECT 2 > 2; ?column? ---------- f (1 row)

• <=

  Description: "less than or equal to" operator.

  Examples:

  1 2 3 4 5

  m_db=# SELECT 0.1 <= 0.2; ?column? ---------- t (1 row)

• >=

  Description: "greater than or equal to" operator.

  Examples:

  1 2 3 4 5

  m_db=# SELECT 2 >= 2; ?column? ---------- t (1 row)

• =

  Description: "equal to" operator.

  Examples:

  1 2 3 4 5

  m_db=# SELECT 2 = 2; ?column? ---------- t (1 row)

• <> or !=

  Description: "not equal to" operator.

  Examples:

  1 2 3 4 5

  m_db=# SELECT 1 <> 2; ?column? ---------- t (1 row)

• <=>

  Description: NULL-safe equal operator. It performs an equality comparison like the = operator, but returns t rather than f if both operands are NULL, and f rather than t if one operand is NULL.

  Examples:

  1 2 3 4 5

  m_db=# SELECT 1 <=> 1, 1 <=> NULL, NULL <=> NULL; ?column? | ?column? | ?column? ----------+----------+---------- t | f | t (1 row)

• BETWEEN AND operator

  Description: Checks whether operand 1 is greater than or equal to operand 2 and less than or equal to operand 3.

  Examples:

  1 2 3 4 5

  m_db=# select 2 between 1 and 3; ?column? ---------- t (1 row)

• IS

  expr IS boolean_value

  Description: Checks whether expr is equivalent to boolean_value. If they are equivalent, true t is returned. Otherwise, false f is returned. boolean_value can be TRUE, FALSE, NULL, or UNKNOWN.

  Examples:

  m_db=# SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
   is true | is false | ?column? 
  ---------+----------+----------
   t       | t        | t
  (1 row)

• IS NOT

  expr IS NOT boolean_value

  Description: Checks whether expr is not equivalent to boolean_value. If expr is not equivalent to boolean_value, TRUE (t) is returned. Otherwise, FALSE (f) is returned. boolean_value can be TRUE, FALSE, NULL, or UNKNOWN.

  Examples:

  m_db=# SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
   ?column? | ?column? | ?column? 
  ----------+----------+----------
   t        | t        | f
  (1 row)

• IS NOT NULL

  expr IS NOT NULL

  Description: Checks whether expr is not NULL. If it is not NULL, true t is returned. Otherwise, false f is returned.

  Examples:

  m_db=# SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
   ?column? | ?column? | ?column? 
  ----------+----------+----------
   t        | t        | f
  (1 row)

• IS NULL

  expr IS NULL

  Description: Checks whether expr is NULL. If so, true t is returned. If not, false f is returned.

  Examples:

  m_db=# SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
   ?column? | ?column? | ?column? 
  ----------+----------+----------
   f        | f        | t
  (1 row)

• GREATEST(value1, value2, ...)

  Description: Returns the maximum value in the parameter list.

  Parameter: At least two parameters are required.

  Examples:

  m_db=# SELECT GREATEST(34.0, 3.0, 5.0, 767.0);
   greatest 
  ----------
      767.0
  (1 row)
  
  m_db=# SELECT GREATEST('B', 'A', 'C');
   greatest 
  ----------
   C
  (1 row)

• INTERVAL(N, N1, N2, N3, ...)

  Description: Returns the result of comparison between N and a list (N1, N2, N3, ...) consisting of other parameters.

  Parameter: At least two parameters are required. All input parameters are considered as numeric types.

  Return value type: SMALLINT

  • If N is NULL, -1 is returned.
  • If the value of N is less than N1, 0 is returned.
  • If there is a positive integer j, and for any positive integer i≤j, there is N_­­i≤N≤N_j+1, and j is returned.

  Examples:

  m_db=# SELECT INTERVAL(24, 2, 5, 14, 26, 28, 46);
   interval 
  ----------
          3
  (1 row)
  
  m_db=# SELECT INTERVAL(24, 29, 5, 14);
   interval 
  ----------
          0
  (1 row)

• ISNULL(expr)

  Description: If parameter expr is NULL, t is returned. Otherwise, f is returned.

  Parameter: any input parameter.

  Return value type: Boolean

  Examples:

  m_db=# SELECT ISNULL('2023-01-01');
   isnull 
  --------
   f
  (1 row)
  
  m_db=# SELECT ISNULL(NULL);
   isnull 
  --------
   t
  (1 row)

• LEAST(value1, value2, ...)

  Description: Returns the minimum value in the parameter list.

  Parameter: At least two parameters are required.

  Examples:

  m_db=# SELECT LEAST(34.0, 3.0, 5.0, 767.0);
   least 
  -------
     3.0
  (1 row)
  
  m_db=# SELECT LEAST('B', 'A', 'C');
   least 
  -------
   A
  (1 row)