Get more data compare features with special MySQL operators

If you have recently made a select or update query, you may have used one or more MySQL comparison operators to limit the query's output. Comparisons are an integral part of most select queries, and MySQL provides many functions for this function; According to the most recent statistics, it has more than 20 such operators and functions, from the famous = and like to the more difficult to understand not in and strcmp ().

This article focuses on some of the less common data comparison functions and operators, discussing how they can be used in the application to compare strings, numbers, and date/time or user-supplied values in a table field.

BETWEEN

The between operator is a useful way to test whether a numeric or date value exists within a range. This operator accepts two parameters-both the maximum and the minimum-and tests whether the supplied value is within the range of the two values. If within this range, the operator returns a Boolean value-true; otherwise, it returns a pseudo value. Here's an example:

Mysql> SELECT 2350 BETWEEN and 10000;

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

| 2350 BETWEEN 10000 |

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

| 1 |

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

1 row in Set (0.18 sec)

The following two examples use the date value:

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 for a set of numbers or time values. You'll know the effect of these two operators as soon as you see the name--here is an example of using the greatest operator for a set of dates:

Mysql> SELECT Greatest (20000601, 20000529, 20000604);

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

| Greatest (20000601, 20000529, 20000604) |

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

| 20000604 |

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

1 row in Set (0.00 sec)

The following is an example of using the least operator for a set of values:

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

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

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

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

| -73 |

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

1 row in Set (0.03 sec)