The MySQL LEAST function is part of comparison functions and is used to find the smallest or minimum value among a list of expressions. It is particularly useful when you have multiple values and you want to retrieve the smallest one. The LEAST function takes two or more arguments, and it returns the smallest value among them.
Syntax
The basic syntax of the LEAST function is as follows:
LEAST(expr1, expr2, ...)
expr1, expr2, …: These are the expressions or values that you want to compare. You can have two or more expressions as arguments.
Examples
Find the smallest integer from a list:
SELECT LEAST(10, 20, 30); Output: 10
Find the smallest date from a list:
SELECT LEAST('2023-10-04', '2023-10-05', '2023-10-06');
Output: 2023-10-04
Find the smallest string from a list:
SELECT LEAST('apple', 'banana', 'orange');
Output: apple
Combining with NULL values
When using the LEAST function with NULL values, it’s important to consider how you want to handle NULL values. If you want the smallest value that includes NULL, you can use the IFNULL function to replace NULL values with a default value.
For instance, to find the smallest integer that includes NULL:
SELECT LEAST(10, 20, IFNULL(NULL, -100)); Output: -100
This ensures that the NULL value is treated as the smallest value in the list.
The LEAST function serves as a crucial function in MySQL for identifying the smallest value within a set of arguments. Its versatility across data types and its ability to handle NULL values make it a valuable asset for data analysts and developers alike. Whether selecting the cheapest product, determining the order with the lowest ID, or comparing multiple criteria, the LEAST function seamlessly navigates through various scenarios, providing accurate and insightful results.