SQL max() function with null values

In SQL, the MAX function is used to find the maximum value in a column. The behaviour of the MAX function in SQL can differ depending on the database management system (DBMS) being used, particularly when dealing with NULL values.

For some database management systems, such as Oracle and SQL Server, the MAX value will be NULL if the column contained in the query has at least one NULL value, even if there are non-null values in the column.

When a column contains at least one non-null value in other database management systems (such as PostgreSQL), MAX returns the maximum value among those values, ignoring NULLs. MAX returns NULL if there are no non-null values in the column.

Example 1: Using MAX 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:

table: student_score

To get the maximum score_achieved from the table, we can simply use the MAX function as follows:

Code:

SELECT MAX(score_achieved) FROM student_score;

Output:

max|
---+
185|

The result of this query is 185, which is the maximum score_achieved in the table. Note that the NULL values are ignored by the MAX function.

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

Here is the table:

table: student_score

Code:

SELECT MAX(score_achieved) FROM student_score;

Output:

max|
---+
   |

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

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

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

Code:

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

Output:

max|
---+
 -1|

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

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

Here is the table:

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

Code:

SELECT student_id,MAX(COALESCE(score_achieved,-1)) as maxscore
FROM student_score
group by student_id

Output:

student_id|maxscore|
----------+---------+
         3|      175|
         5|      179|
         4|      185|
         6|      172|
         2|      174|
         1|      150|

The NULL values in the score_achieved column are also considered while grouping the results by student_id. The MAX function returns NULL when all values in the group are NULL, so even if there is only one NULL value in a group, the MAX function will return NULL for that group. In this case, student_id 3 and 5 have non-NULL values in their respective groups, so the MAX function returns the maximum non-NULL value for those records.

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

Previous:Max Functions
Next: Min Function