Mastering SQL MIN Function Through Practical Examples

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.

Here’s 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 does not have any effect when using the DISTINCT option.

SQL MIN function examples #

We’ll 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) min_salary
FROM
  employees;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 min_salary
------------
    2500.00Code language: CSS (css)

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

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

Try it

Output:

 first_name | last_name  | salary
------------+------------+---------
 Karen      | Colmenares | 2500.00

How the query works:

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 function 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

Output:

 department_id |   min
---------------+----------
            11 |  8300.00
             9 | 17000.00
             3 |  2500.00
             5 |  2700.00
             4 |  6500.00
            10 |  6900.00
             6 |  4200.00
             2 |  6000.00
             7 | 10000.00
             1 |  4400.00
             8 |  6200.00

In this example, 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 department names. To include department names in the result set, you need to join the employees table with the departments using an inner join clause as follows:

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

Try it

Output:

 department_name  | min_salary
------------------+------------
 Purchasing       |    2500.00
 Shipping         |    2700.00
 IT               |    4200.00
 Administration   |    4400.00
 Marketing        |    6000.00
 Sales            |    6200.00
 Human Resources  |    6500.00
 Finance          |    6900.00
 Accounting       |    8300.00
 Public Relations |   10000.00
 Executive        |   17000.00Code language: PHP (php)

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
  department_name,
  MIN(salary) min_salary
FROM
  employees e
  INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
  department_name
HAVING
  MIN(salary) < 3000
ORDER BY
  min_salary;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 department_name | min_salary
-----------------+------------
 Purchasing      |    2500.00
 Shipping        |    2700.00

Summary #

Use the MIN function to find the minimum value in a set of values.
Use the MIN function with the GROUP BY clause to retrieve the minium value for each group.

Databases #

Was this tutorial helpful ?