SQL MIN Function – Syntax and Examples

SQL MIN Function

The SQL MIN function is used to find the minimum value in a column. It’s commonly used with numeric, date, or text data types and returns the smallest value from a specified column.

The MIN function is often used in conjunction with the GROUP BY clause to get minimum values for each group in the dataset.

In this tutorial, we will go through SQL MIN Function, its syntax, and how to use this function in SQL statements, with the help of well detailed examples.

---

Syntax of SQL MIN Function

The basic syntax of the SQL MIN function is as follows:

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

Each part of this syntax has a specific purpose:

• SELECT: Specifies the column to retrieve the minimum value from.
• MIN(column_name): The MIN function, which returns the smallest value in the specified column.
• AS min_value: An alias for the minimum value column in the results.
• FROM: Specifies the table from which to retrieve data.
• WHERE (optional): Filters the data based on specified conditions before applying the MIN function.

---

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the MIN function in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality along with a grades table that records students’ scores.

Setup for Examples: Creating the Database and Tables

1. Open MySQL Workbench and create a new database:

CREATE DATABASE school;

2. Select the school database:

USE school;

3. Create a students table:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Create a grades table:

CREATE TABLE grades (
    student_id INT,
    subject VARCHAR(50),
    score INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

5. Insert sample data into the students and grades tables:

INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

INSERT INTO grades (student_id, subject, score)
VALUES
(1, 'Math', 85),
(2, 'Math', 78),
(3, 'Science', 90),
(4, 'Math', 88),
(5, 'Science', 70);

---

Examples: Using MIN Function in Queries

Now, let’s explore different scenarios of using the MIN function with these tables.

---

1. Finding the Minimum Score Across All Subjects

To find the lowest score in the grades table:

SELECT MIN(score) AS min_score
FROM grades;

This query returns the lowest score across all subjects in the grades table, displaying the minimum score.

---

2. Finding the Minimum Age in the Students Table

To find the youngest student in the students table:

SELECT MIN(age) AS min_age
FROM students;

This query returns the smallest age value in the students table, displaying the age of the youngest student.

---

3. Using MIN with a WHERE Clause

To find the minimum score for the subject “Math” only:

SELECT MIN(score) AS min_math_score
FROM grades
WHERE subject = 'Math';

This query finds the minimum score for “Math” in the grades table, filtering scores only for that subject.

---

4. Using MIN with GROUP BY to Find Minimum Scores per Subject

To find the minimum score for each subject:

SELECT subject, MIN(score) AS min_score
FROM grades
GROUP BY subject;

This query returns the minimum score for each subject in the grades table, displaying the lowest score per subject.

Reference: SQL GROUP BY

---

FAQs for SQL MIN

1. What does the SQL MIN function do?

The MIN function finds the minimum value in a specified column, returning the smallest value from a dataset.

2. Can I use MIN with non-numeric data?

Yes, MIN can be used with text and date data. For text, it returns the lexicographically smallest value, and for dates, it returns the earliest date.

3. How do I find the minimum value for each group?

Use the GROUP BY clause with MIN to find the minimum value for each group in the dataset.

4. Can MIN be used with WHERE conditions?

Yes, you can filter results before applying MIN by using a WHERE clause to specify conditions.

5. Does MIN consider NULL values?

No, MIN ignores NULL values in the column and only considers non-NULL values when calculating the minimum.