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)
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;
To get the information about 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
);
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;
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;
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);
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;
So now you should know how to use the SQL MIN function to find the minimum value in a set of values.
0 Comments
CAN FEEDBACK
Emoji