SQL MIN

Summary: this tutorial helps you master the SQL MIN function through practical examples. After the tutorial, you will know how to apply the MIN function effectively to find the minimum value in a set of values.

Introduction to SQL MIN function

The SQL MIN function returns the minimum value in a set of values. The following demonstrates the syntax of the MIN function.

MIN(expression)Code language: SQL (Structured Query Language) (sql)

Like the MAX function, the MIN function also ignores NULL values and the DISTINCT option is not applicable to the MIN function.

SQL MIN function examples

We will use the employees table to demonstrate the functionality of the MIN function.

To find the lowest (minimum) salary of employees, you apply the MIN function to the salary column of the employees table.

SELECT 
    MIN(salary)
FROM
    employees;Code language: SQL (Structured Query Language) (sql)

Try It

To get the information of the employee who has the lowest salary, you use the following subquery:

SELECT
	employee_id,
	first_name,
	last_name,
	salary
FROM
	employees
WHERE
	salary = (
		SELECT
			MIN(salary)
		FROM
			employees
	);
Code language: SQL (Structured Query Language) (sql)

Try It

First, the subquery returns the minimum salary. Then, the outer query retrieves the employee whose salary is equal to the lowest salary returned by the subquery.

SQL MIN with GROUP BY example

We often use the MIN function together with the GROUP BY clause to find the minimum value in each group.

For instance, the following query returns the employees who have the lowest salaries in each department.

SELECT
	department_id,
	MIN(salary)
FROM
	employees
GROUP BY
	department_id;Code language: SQL (Structured Query Language) (sql)

Try It

The GROUP BY clause groups the employees by department. For each group, the query returns the row with the lowest salary.

The result set is not informative as long as we see the names of the department.

To combine the department’s name in the result set, we need to join the employees table with the departments using the inner join clause as follows:

SELECT
	d.department_id,
	department_name,
	MIN(salary)
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
	d.department_id;Code language: SQL (Structured Query Language) (sql)

Try It

SQL MIN with ORDER BY example

To sort the groups by the result of the MIN function, you need to put the MIN function in the ORDER BY clause.

The following query first retrieves the employee who has the lowest salary in each department, then sorts these departments by the salary in ascending order.

SELECT
	d.department_id,
	department_name,
	MIN(salary)
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
	d.department_id
ORDER BY
	MIN(salary);Code language: SQL (Structured Query Language) (sql)

Try It

SQL MIN with HAVING example

You use the HAVING clause to specify the filter condition for groups. To filter the groups based on the result of the MIN function, you place the MIN function in the HAVING clause.

For example, the following query retrieves the employees who have the lowest salary in each department. Then, it includes only departments whose salary is less than 3000.

SELECT
	d.department_id,
	department_name,
	MIN(salary)
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
	d.department_id
HAVING
	MIN(salary) < 3000;Code language: SQL (Structured Query Language) (sql)

Try It

So now you should know how to use the SQL MIN function to find the minimum value in a set of values.