All Products
Search
Document Center

Comparison functions

Last Updated: Jun 18, 2021

GREATEST

Declaration

GREATEST(value1, ...)

Description

This function returns the maximum parameter value and is opposite to the LEAST() function.

At least two parameters are required. If one parameter is NULL, the return value is NULL.

If a parameter contains both a numeric value and a character, the character is implicitly converted into a numeric value. An error is returned if the conversion fails.

Example

obclient> select greatest(2,1), greatest('2',1,0), greatest('a','b','c'), greatest('a', NULL, 'c'), greatest('2014-05-15','2014-06-01')\G
*************************** 1. row ***************************
                      greatest(2,1): 2
                  greatest('2',1,0): 2
              greatest('a',' B ','c'): c
           greatest('a', NULL, 'c'): NULL
greatest('2014-05-15','2014-06-01'): 2014-06-01
1 row in set (0.01 sec)

obclient> select greatest(2);
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'greatest'

LEAST

Declaration

LEAST(value1, ...)

Description

This function returns the minimum parameter value and is opposite to the GREATEST() function.

At least two parameters are required. If any parameter is NULL, the return value is NULL.

If a parameter contains both a numeric value and a character, the character is implicitly converted into a numeric value. An error is returned if the conversion fails.

Example

obclient> select least(2, null), least('2',4,9), least('a','b','c'), least('a',NULL,'c'), least('2014-05-15','2014-06-01')\G;
*************************** 1. row ***************************
                  least(2, null): NULL
                  least('2',4,9): 2
              least('a',' B ','c'): a
             least('a',NULL,'c'): NULL
least('2014-05-15','2014-06-01'): 2014-05-15
1 row in set (0.01 sec)

obclient> select least(2);
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'least'

ISNULL

Declaration

ISNULL(expr)

Description

If the expr parameter is NULL, the return value of the ISNULL() function is 1. Otherwise, the return value is 0.

The ISNULL() function can be used to replace the equivalent (=) comparison for NULL, because NULL is neither equal to a value nor unequal to it.Some features of the ISNULL() function are the same as those of the IS NULL comparison operator.

Example

obclient> SELECT ISNULL(null), ISNULL('test'), ISNULL(123.456), ISNULL('10:00');
+--------------+----------------+-----------------+-----------------+
| ISNULL(null) | ISNULL('test') | ISNULL(123.456) | ISNULL('10:00') |
+--------------+----------------+-----------------+-----------------+
|            1 |              0 |               0 |               0 |
+--------------+----------------+-----------------+-----------------+
1 row in set (0.01 sec)

obclient> SELECT ISNULL(null+1);
+----------------+
| ISNULL(null+1) |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)