MIN() and MAX() in MySQL

The MIN() and MAX() functions in MySQL are aggregate functions used to find the smallest (minimum) and largest (maximum) values in a column.

1. Syntax

SELECT MIN(column_name) FROM table_name;SELECT MAX(column_name) FROM table_name;

✅ MIN() returns the smallest value.
✅ MAX() returns the largest value.

2. Example Table: products

3. Using MIN()

Find the cheapest product

SELECT MIN(price) AS min_price FROM products;

✅ Output:

+-----------+| min_price |+-----------+| 150       |+-----------+

🚀 MIN(price) returns 150 (Headphones).

4. Using MAX()

Find the most expensive product

SELECT MAX(price) AS max_price FROM products;

✅ Output:

+-----------+| max_price |+-----------+| 800       |+-----------+

🚀 MAX(price) returns 800 (Laptop).

5. MIN() and MAX() with GROUP BY

You can use GROUP BY to get minimum and maximum values per category.

Example Table: employees

Find the lowest and highest salary per department

SELECT department, MIN(salary) AS min_salary, MAX(salary) AS max_salaryFROM employeesGROUP BY department;

✅ Output:

+------------+-----------+-----------+| department | min_salary | max_salary |+------------+-----------+-----------+| HR         | 5000      | 5500      || IT         | 6000      | 7000      |+------------+-----------+-----------+

🚀 IT Department: Salary range = 6000 - 7000
🚀 HR Department: Salary range = 5000 - 5500

6. MIN() and MAX() with WHERE

Find the most expensive product below $600

SELECT MAX(price) FROM products WHERE price < 600;

✅ Gets the highest price below $600.

7. MIN() and MAX() with HAVING

Find departments where the highest salary is more than $6000

SELECT department, MAX(salary) AS max_salaryFROM employeesGROUP BY departmentHAVING max_salary > 6000;

✅ Output:

+------------+-----------+| department | max_salary |+------------+-----------+| IT         | 7000      |+------------+-----------+

🚀 Only IT department qualifies.

8. Performance Tips

⚡ Use Indexing – Index columns used in MIN() and MAX() for faster queries.
⚡ Use LIMIT Instead – Instead of MIN() or MAX(), use:

SELECT price FROM products ORDER BY price ASC LIMIT 1; -- For MINSELECT price FROM products ORDER BY price DESC LIMIT 1; -- For MAX

⚡ Avoid NULL Values – MIN() and MAX() ignore NULL values.

9. Key Takeaways

✅ MIN() returns the smallest value in a column.
✅ MAX() returns the largest value in a column.
✅ Use GROUP BY to get min/max per category.
✅ Use HAVING to filter grouped results.