SQL min() function with null values

In SQL, the MIN function is used to find the minimum value in a column. The behaviour of the MIN function in SQL can vary depending on the database management system (DBMS) that's being used, and this is particularly true when NULL values are involved.

In some database management systems, such as Oracle and SQL Server, the MIN value will be NULL if the column contains at least one NULL value, regardless of the number of non-null values.

In other database management systems (such as PostgreSQL), when a column contains at least one non-null value, MIN returns the minimum value among those values, regardless of NULLs. MIN returns NULL if there are no non-null values in the column.

Example 1: Using MIN with NULL values in a single column

Suppose we have a table student_score with columns student_id and score_achieved, where score_achieved may contain NULL values.

Here is the table:

To get the minimum score_achieved from the table, we can simply use the MIN function as follows:

Code:

SELECT MIN(score_achieved) FROM student_score;

Output:

min|
---+
150|

The result of this query is 150, which is the minimum score_achieved in the table. Note that the NULL values are ignored by the MIN function.

If there are no non-null values in the column score_achieved, then MIN will return NULL.

Here is the table:

table: student_score

Code:

SELECT MIN(score_achieved) FROM student_score;

Output:

min|
---+
   |

The result of this query is NULL, because schore_achieved column contain NULL.

To include the NULL values in the calculation of MIN, we can use the COALESCE function to replace the NULL values with a default value (such as -1) before applying MIN.

In the above table the column score_achieved contain NULL values. To find the minimum score_achieved, we can use the following query:

Code:

SELECT MIN(COALESCE(score_achieved,-1)) FROM student_score;

Output:

min|
---+
 -1|

This query returns -1 because all score_achived are NULL, or the minimum score otherwise.

Example 2: Using MIN with NULL values in multiple columns:

Here is the table:

Suppose we want to get the lowest score_achieved for each student, including those with NULL score_achieved. We can use the following query:

Code:

SELECT student_id,MIN(COALESCE(score_achieved,-1)) as minscore
FROM student_score
group by student_id
order by minscore

Output:

student_id|minscore|
----------+--------+
         3|      -1|
         5|      -1|
         1|     150|
         6|     172|
         2|     174|
         4|     185|

The NULL values in the score_achieved column are also considered while grouping the results by student_id. MIN returns NULL when all values in the group are NULL, which means that even if there is only one NULL value in a group, the MIN function will return NULL for the entire group if all the values are NULL. In this case, student_id 3 and 5 have non-NULL values in their respective groups, so the MIN function returns the minimum non-NULL value for those records. This orders the results by the minscore column in ascending order, so that the students with the lowest minimum score achieved are listed first.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous:Min Functions