Home / Funtions and Operators / Question No: 230

Comparison functions enable you to test relative values or membership of one value within a set of values. LEAST() and GREATEST() take a set of values as arguments and return the one that is smallest or largest, respectively:

mysql> SELECT LEAST(4,3,8,-1,5), LEAST('cdef','ab','ghi');
+-------------------+--------------------------+
| LEAST(4,3,8,-1,5) | LEAST('cdef','ab','ghi') |
+-------------------+--------------------------+
|                -1 | ab                       |
+-------------------+--------------------------+

mysql> SELECT GREATEST(4,3,8,-1,5), GREATEST('cdef','ab','ghi');
+----------------------+-----------------------------+
| GREATEST(4,3,8,-1,5) | GREATEST('cdef','ab','ghi') |
+----------------------+-----------------------------+
|                    8 | ghi                         |
+----------------------+-----------------------------+

INTERVAL Function

INTERVAL() takes a comparison value as its first argument. The remaining arguments should be a set of values in sorted order. INTERVAL() compares the first argument to the others and returns a value to indicate how many of them are less than or equal to it.

mysql> SELECT INTERVAL(2,1,2,3,4);
+---------------------+
| INTERVAL(2,1,2,3,4) |
+---------------------+
|                   2 |
+---------------------+

mysql> SELECT INTERVAL(0,1,2,3,4);
+---------------------+
| INTERVAL(0,1,2,3,4) |
+---------------------+
|                   0 |
+---------------------+

mysql> SELECT INTERVAL(6.3,2,4,6,8,10);
+--------------------------+
| INTERVAL(6.3,2,4,6,8,10) |
+--------------------------+
|                        3 |
+--------------------------+

IN Function

It's sometimes necessary to determine whether a value is equal to any of several specific values. One way to accomplish this is to combine several equality tests into a single expression with the OR logical operator:

... WHERE id = 13 OR id = 45 OR id = 97 OR id = 142
... WHERE name = 'Tom' OR name = 'Dick' OR name = 'Harry'

However, MySQL provides an IN() operator that performs the same kind of comparison and that is more concise and easier to read. To use it, provide the comparison values as a comma-separated list of arguments to IN():

... WHERE id IN(13,45,97,142)
... WHERE name IN('Tom','Dick','Harry')

Using IN() is equivalent to writing a list of comparisons with OR, but IN() is much more efficient. Arguments to IN() may be of any type (numeric, string, or temporal), although generally all values given within a list should all have the same type. Elements in a list may be given as expressions that are evaluated to produce a value. If the expression references a column name, the column is evaluated for each row.

IN() always returns NULL when used to test NULL. That is, NULL IN(list) is NULL for any list of values, even if NULL is included in the list. This occurs because NULL IN(NULL) is equivalent to NULL = NULL, which evaluates to NULL.

BETWEEN Operator

IN() tests membership within a set of individual values. If you're searching for a range of values, a range test might be more suitable. The BETWEEN operator takes the two endpoint values of the range and returns true if a comparison value lies between them:

... WHERE id BETWEEN 5 AND 10

The comparison is inclusive, so the preceding expression is equivalent to this one:

... WHERE id >= 5 AND id <= 10