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)