MySQL – LEAST and GREATEST Comparison Operators

The biggest challenge which we often face as a database person is to compare the value across multiple columns. Trust me, if there were no comparison operators, we would have never got this task done. MySQL supports many comparison operators like BETWEEN, IN, etc. LEAST and GREATEST is very handy if you want to compare values across many columns.

These operators accept multiple parameters and find the least and greatest value respectively

Let us create this dataset

CREATE table testing(num1 int, num2 int, num3 int);
INSERT INTO testing(num1,num2,num3)
SELECT 456,90,774 UNION ALL
SELECT 70,111,4 UNION ALL
SELECT 190,87,391;

Now that we have created the dataset. We will execute the code displayed below.

SELECT LEAST(num1,num2,num3) as least_value,
GREATEST(num1,num2,num3) as greatest_value
FROM testing;

Once you execute the code above, it will display following result set. You can clearly see that the resulset now contains two different values – least value and greatest value.

If these operators are not available, you will end up with creating lots of CASE expressions which become complex if number of columns are more than 3 or you may need to use user defined function. But these operators are very powerful and easy to use.

Let me know what you think about this blog post in the comment section below.

Reference: Pinal Dave (https://blog.sqlauthority.com)