MySQL LEAST() function

Last update on March 03 2026 12:42:30 (UTC/GMT +8 hours)

LEAST() function

MySQL LEAST() function returns the smallest argument from two or more arguments.

This function is useful in -

LEAST() is useful in conditional logic, allowing you to determine the smallest value among a set of options, which is particularly useful in decision-making processes.
It provides a convenient way to find the smallest value among a set of expressions or column values.
LEAST() returns NULL if any of the input values are NULL.
Using LEAST() makes queries more readable and understandable, as it clearly states the intention to find the minimum value.
It can be used in mathematical calculations where you need to find the minimum value among a set of numeric expressions.
This is especially useful when dealing with complex queries involving multiple conditions.

Syntax:

LEAST(value1,value2,...)

The arguments are compared using the following rules:

If any argument is NULL, the result is NULL. No comparison is needed.
If the return value is used in an INTEGER context or all arguments are integer-valued, they are compared as integers.
If the return value is used in a REAL context or all arguments are real-valued, they are compared as reals.
If the arguments comprise a mix of numbers and strings, they are compared as numbers.
If any argument is a nonbinary (character) string, the arguments are compared as nonbinary strings.
In all other cases, the arguments are compared as binary strings.

MySQL Version: 8.0

Example: MySQL LEAST() function

The following MySQL statement will find the smallest out of the list of arguments.

Code:


-- This query uses the LEAST() function to find the smallest value among the given arguments.
SELECT LEAST(15, 10, 25);
-- The LEAST() function compares the provided values and returns the smallest one.

Explanation:

The purpose of this SQL query is to determine the smallest value among a set of provided values using the LEAST function.

SELECT LEAST(15, 10, 25): This part of the query uses the LEAST function to compare the values 15, 10, and 25.

The LEAST function evaluates the given arguments and returns the smallest value among them.

In this case, it compares the three values: 15, 10, and 25.

Among these values, 10 is the smallest.

Output:

Example: MySQL LEAST() function using string

The following MySQL statement will find the smallest out of the list of arguments. It returns M, since S and Z come after M.

Code:


-- This query uses the LEAST() function to find the smallest value among the given string arguments.
SELECT LEAST("Z", "M", "S");
-- The LEAST() function compares the provided string values lexicographically and returns the smallest one.

Explanation:

The purpose of this SQL query is to determine the smallest string value among a set of provided string values using the LEAST function.

SELECT LEAST("Z", "M", "S"): This part of the query uses the LEAST function to compare the string values "Z", "M", and "S".

The LEAST function evaluates the given string arguments and returns the smallest value based on lexicographical (dictionary) order.

In this case, it compares the three string values: "Z", "M", and "S".

In lexicographical order, "M" comes before "S" and "Z".

Output:

Example: MySQL LEAST() function with where clause

The following MySQL statement will fetch those books from book_mast table which have less number of pages than lowest argument returning from the LEAST() function.

Sample table: book_mast

+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| book_id | book_name                           | isbn_no     | cate_id | aut_id | pub_id | dt_of_pub  | pub_lang | no_page | book_price |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| BK001   | Introduction to Electrodynamics     | 0000979001  | CA001   | AUT001 | P003   | 2001-05-08 | English  |     201 |      85.00 |
| BK002   | Understanding of Steel Construction | 0000979002  | CA002   | AUT002 | P001   | 2003-07-15 | English  |     300 |     105.50 |
| BK003   | Guide to Networking                 | 0000979003  | CA003   | AUT003 | P002   | 2002-09-10 | Hindi    |     510 |     200.00 |
| BK004   | Transfer  of Heat and Mass          | 0000979004  | CA002   | AUT004 | P004   | 2004-02-16 | English  |     600 |     250.00 |
| BK005   | Conceptual Physics                  | 0000979005  | CA001   | AUT005 | P006   | 2003-07-16 | NULL     |     345 |     145.00 |
| BK006   | Fundamentals of Heat                | 0000979006  | CA001   | AUT006 | P005   | 2003-08-10 | German   |     247 |     112.00 |
| BK007   | Advanced 3d Graphics                | 0000979007  | CA003   | AUT007 | P002   | 2004-02-16 | Hindi    |     165 |      56.00 |
| BK008   | Human Anatomy                       | 0000979008  | CA005   | AUT008 | P006   | 2001-05-17 | German   |      88 |      50.50 |
| BK009   | Mental Health Nursing               | 0000979009  | CA005   | AUT009 | P007   | 2004-02-10 | English  |     350 |     145.00 |
| BK010   | Fundamentals of Thermodynamics      | 0000979010  | CA002   | AUT010 | P007   | 2002-10-14 | English  |     400 |     225.00 |
... ... ...
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+

View the table

Code:


-- Selects the columns book_name, dt_of_pub, and no_page from the book_mast table
SELECT book_name, dt_of_pub, no_page
-- Specifies the table from which to retrieve the data
FROM book_mast
-- Filters the rows to include only those where the number of pages (no_page) is less than the smallest value among 500, 300, and 395
WHERE no_page < LEAST(500, 300, 395);

Explanation:

SELECT book_name, dt_of_pub, no_page:

This part of the query specifies the columns to be retrieved from the book_mast table.

book_name: The name of the book.

dt_of_pub: The date of publication of the book.

no_page: The number of pages in the book.

FROM book_mast:

This specifies the table from which to retrieve the data. In this case, it is the book_mast table.

WHERE no_page < LEAST(500, 300, 395):

This clause filters the rows to include only those where the number of pages (no_page) is less than the smallest value among the numbers 500, 300, and 395.

LEAST(500, 300, 395): This function compares the values 500, 300, and 395, and returns the smallest value, which is 300 in this case.

So, the WHERE clause effectively becomes WHERE no_page < 300, filtering the rows to include only those books with fewer than 300 pages.

Output:

Slideshow of MySQL Comparison Function and Operators

PREV : ISNULL()
NEXT : LESS THAN OR EQUAL OPERATOR(<=)