If you have performed a SELECT or UPDATE query recently, you have likely used one or more of MySQL's comparison operators to limit the output of the query.Comparisons are an integral part of most SELECT queries, and MySQL provides many functions for this function; at last count, it had over 20 such operators and functions, ranging from the well-known=and LIKE to the more obscure ones NOT IN and STRCMP().

This article focuses on some of the less common MySQL data comparison functions and operators, and discusses how they can be used in applications to compare strings, numbers, and date/time or user-supplied values ​​in table fields.

BETWEEN

The

BETWEEN operator is a useful way to test for the existence of a numeric or date value within a range.This operator takes two arguments-a maximum value and a minimum value, and tests whether the supplied value is within the range of those two values.If within this range, the operator returns a boolean value-true; otherwise, it returns a false value.Here is an example:

mysql> SELECT 2350 BETWEEN 100 AND 10000;

+----------------------------+

| 2350 BETWEEN 100 AND 10000 |

+----------------------------+

| 1 |

+----------------------------+

1 row in set (0.18 sec)

The following two examples use date values:

mysql> SELECT 20060405 BETWEEN 20060101 AND 20070101;

+------------------------------------------------------+

| 20060405 BETWEEN 20060101 AND 20070101 |

+------------------------------------------------------+

| 1 |

+------------------------------------------------------+

1 row in set (0.00 sec)

mysql> SELECT 20060405 BETWEEN 20060101 AND 20060401;

+------------------------------------------------------+

| 20060405 BETWEEN 20060101 AND 20060401 |

+------------------------------------------------------+

| 0 |

+------------------------------------------------------+

1 row in set (0.00 sec)

GREATEST and LEAST

The

GREATEST and LEAST operators provide a convenient way to determine the maximum and minimum values ​​in a set of numeric or time values.You can tell by the name what these two operators do-here's an example of using the GREATEST operator on a set of dates:

mysql> SELECT GREATEST(20000601, 20000529, 20000604);

+------------------------------------------------------+

| GREATEST(20000601, 20000529, 20000604) |

+------------------------------------------------------+

| 20000604 |

+------------------------------------------------------+

1 row in set (0.00 sec)

And here is an example of using the LEAST operator on a set of numbers:

mysql> SELECT LEAST(100, 200, 50,-6,-73, 1000);

+------------------------------------------------+

| LEAST(100, 200, 50,-6,-73, 1000) |

+------------------------------------------------+

|-73 |

+------------------------------------------------+

1 row in set (0.03 sec)