MySQL MIN() and MAX() Functions

In MySQL, the MIN() and MAX() functions are used to retrieve the smallest and largest values from a specified column in a table. These aggregate functions are often used in queries to find the minimum or maximum value in a dataset, such as the lowest or highest price, the oldest or newest date, etc.

MIN() Function

The MIN() function returns the smallest value from a selected column. It can be used with numeric, date, or string columns.

Syntax:

SELECT MIN(column_name)
FROM table_name;

• column_name: The column from which to retrieve the minimum value.
• table_name: The table containing the column.

Example: Finding the Minimum Value

If you have a Products table and want to find the lowest price of a product, you would use the MIN() function:

SELECT MIN(Price) AS LowestPrice
FROM Products;

This will return the lowest price value from the Price column in the Products table.

MAX() Function

The MAX() function returns the largest value from a selected column. It can also be used with numeric, date, or string columns.

Syntax:

SELECT MAX(column_name)
FROM table_name;

• column_name: The column from which to retrieve the maximum value.
• table_name: The table containing the column.

Example: Finding the Maximum Value

If you want to find the highest salary in an Employees table, you would use the MAX() function:

SELECT MAX(Salary) AS HighestSalary
FROM Employees;

This will return the largest salary value from the Salary column in the Employees table.

Example Table: Products

Example 1: Finding the Minimum Price

SELECT MIN(Price) AS LowestPrice
FROM Products;

Result:

Example 2: Finding the Maximum Price

SELECT MAX(Price) AS HighestPrice
FROM Products;

Result:

Using MIN() and MAX() with Other Columns

You can also use these functions along with other columns. For example, if you want to find the product with the lowest price and display its ProductName, you can use a query like this:

Example 3: Finding the Product with the Minimum Price

SELECT ProductName, MIN(Price) AS LowestPrice
FROM Products;

However, if you want to find the product with the exact lowest price, you might need a WHERE clause:

SELECT ProductName, Price
FROM Products
WHERE Price = (SELECT MIN(Price) FROM Products);

This query will return the name and price of the product with the lowest price.

Notes:

• NULL Values: The MIN() and MAX() functions ignore NULL values in the column, so they only consider rows with actual data.
• Aggregate Functions: These functions are often used alongside other aggregate functions like AVG(), COUNT(), or SUM() to provide summary data.
• Performance Considerations: Both functions are typically very efficient, but performance can be impacted if you're applying them to large tables without proper indexing.

The MIN() and MAX() functions are essential tools for quickly retrieving the smallest and largest values from a dataset, enabling users to analyze data efficiently.