MySQL MIN() Function

Introduction

In this chapter, we will learn about the MIN() function in MySQL. The MIN() function is an aggregate function that returns the minimum value of a specified column. It is widely used to find the lowest value in a dataset, such as the minimum salary, minimum age, or lowest score. We will cover the syntax for the MIN() function, examples of its usage, and important considerations for using it in MySQL.

Syntax

The basic syntax for using the MIN() function in MySQL is:

SELECT MIN(column_name)
FROM table_name
[WHERE condition];

Example: Find the Minimum Salary

SELECT MIN(salary) AS minimum_salary
FROM employees;

Full Example

Let’s go through a complete example where we create a database and table, insert data, and demonstrate the usage of the MIN() function.

1. Create a Database and Table

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('Rahul', 'Sharma', 'Sales', 50000.00),
('Priya', 'Singh', 'Marketing', 60000.00),
('Amit', 'Kumar', 'Sales', 55000.00),
('Neha', 'Verma', 'IT', 58000.00),
('Sahil', 'Mehta', 'IT', 62000.00),
('Kiran', 'Patel', 'Sales', 53000.00);

2. Using MIN()

Example: Find the Minimum Salary

SELECT MIN(salary) AS minimum_salary
FROM employees;

Output

Example: Find the Minimum Salary by Department

You can use the MIN() function with the GROUP BY clause to find the minimum salary for each department.

SELECT department, MIN(salary) AS minimum_salary
FROM employees
GROUP BY department;

Output

Example: Find the Minimum Salary for a Specific Department

You can use the MIN() function with the WHERE clause to find the minimum salary for employees in a specific department.

SELECT MIN(salary) AS sales_minimum_salary
FROM employees
WHERE department = 'Sales';

Output

Using MIN() with Other Aggregate Functions

The MIN() function can be used in combination with other aggregate functions for more complex queries.

Example: Find the Minimum and Maximum Salary by Department

SELECT department,
       MIN(salary) AS minimum_salary,
       MAX(salary) AS maximum_salary
FROM employees
GROUP BY department;

Output

Important Considerations

• NULL Values: The MIN() function ignores NULL values. Ensure that the column being evaluated does not have NULL values if this is not the desired behavior.
• Data Types: The MIN() function works with numeric, string, and date data types. Ensure that the column being evaluated contains compatible values.
• Performance: The MIN() function can impact performance, especially on large datasets. Consider indexing columns used in the WHERE clause to improve performance.

Conclusion

The MIN() function in MySQL is used for finding the lowest value in a specified column. This chapter covered the syntax for using the MIN() function, provided examples of its usage, and discussed important considerations.