Mastering SQL MIN Function Through Practical Examples (original) (raw)

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.

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

employees_table

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.00`Code 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:

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 ?