GREATEST() and LEAST() Functions and NULL

Today we're diving into a more specific but super important topic: the GREATEST() and LEAST() functions. You'll learn how to find the max and min values from several columns, and—most importantly—how NULL affects how they work.

If you've ever searched for the most important thing in your life (love, your dream job, or the best pizza recipe), you'll instantly get why we need GREATEST() and LEAST(). These functions help you find the biggest or smallest value in a list of things. Only instead of pizza, you're working with numbers, dates, strings, and other data in PostgreSQL.

GREATEST()

GREATEST() returns the largest value from the arguments you give it.

Syntax:

GREATEST(value1, value2, ..., valueN)

LEAST()

LEAST() does the opposite: it finds the smallest value.

Syntax:

LEAST(value1, value2, ..., valueN)

Example:

Let's say we have a table called students_scores where we store students' grades for three exams:

Using GREATEST() and LEAST():

SELECT
    student_id,
    GREATEST(exam_1, exam_2, exam_3) AS highest_score,
    LEAST(exam_1, exam_2, exam_3) AS lowest_score
FROM students_scores;

Result:

How does NULL affect GREATEST() and LEAST()?

Now we're getting to the fun part. Along with values in your table, you might have NULLs. And as we already know, NULL is that mysterious thing that means "no data" or "unknown value." Let's figure out what happens if NULL gets into GREATEST() and LEAST() in PostgreSQL.

NULL behavior:

In PostgreSQL, the GREATEST() and LEAST() functions have a special behavior: they ignore NULL values when searching for the biggest or smallest value among their arguments. Important: The only time these functions will return NULL is if all their arguments are NULL.

Example:

SELECT
    GREATEST(10, 20, NULL, 5) AS greatest_value,
    LEAST(10, 20, NULL, 5) AS least_value;

Result:

As you can see, NULL was ignored, and the functions returned the biggest and smallest values from the ones that were there (10, 20, 5).

And here's an example where all the arguments are NULL:

Example:

SELECT
    GREATEST(NULL, NULL) AS greatest_nulls,
    LEAST(NULL, NULL) AS least_nulls;

Result:

How do you avoid problems with NULL?

Even though PostgreSQL ignores NULL by default, sometimes you might want different behavior. For example, maybe you want NULL to be treated as a specific value (like 0 or some other default) when figuring out the max/min. In those cases, you can use the COALESCE() function.

The COALESCE(arg1, arg2, ...) function returns the first non-NULL argument from its list. This lets you swap out NULL for something meaningful before passing it to GREATEST() or LEAST().

Example 1: Replacing NULL with 0

Let's say we want to treat a missing grade (NULL) as 0. We can use COALESCE() to plug in a default value.

Here's our original table:

Query:

SELECT
    student_id,
    GREATEST(
        COALESCE(exam_1, 0), 
        COALESCE(exam_2, 0), 
        COALESCE(exam_3, 0)
    ) AS highest_score,
    LEAST(
        COALESCE(exam_1, 0), 
        COALESCE(exam_2, 0), 
        COALESCE(exam_3, 0)
    ) AS lowest_score
FROM students_scores;

Result:

Example 2: Replacing NULL with a value from another column

Sometimes instead of a fixed value (like 0), you want to use a value from another column. For example, if exam_3 is missing, you want to use the value from exam_1.

SELECT
    student_id,
    GREATEST(
        exam_1, 
        exam_2, 
        COALESCE(exam_3, exam_1)
    ) AS highest_score
FROM students_scores;

Let's say we have this table:

Query result:

Practical cases

Case 1: Finding the max discount

You're working with an orders table, where each order can have three different types of discounts. You need to find the biggest discount for each order.

SELECT
    order_id,
    GREATEST(discount_1, discount_2, discount_3) AS max_discount
FROM orders;

Result:

Case 2: Finding the lowest product price

In the products table, you store product prices in three currencies (USD, EUR, GBP). Your task is to find the lowest price for each product.

SELECT
    product_id,
    LEAST(price_usd, price_eur, price_gbp) AS lowest_price
FROM products;

If all prices are NULL, the result is also NULL

Common mistakes when using GREATEST() and LEAST()

Mistake 1: Unexpected result because of NULL.

Earlier in this lecture, we went over in detail how NULL affects GREATEST() and LEAST() in PostgreSQL. The main mistake is that users who are used to how NULL works in other DBMSs (where a single NULL "poisons" the whole result) expect the same from PostgreSQL.

How this mistake shows up: You might wrongly think that if there's a NULL in the argument list, the function will always return NULL. As a result, you might use COALESCE() on every argument for no reason, which can make your query more complicated and slower, especially if in your case NULL should just be ignored.

Mistake 2: Using GREATEST() and LEAST() with incompatible types.

The GREATEST() and LEAST() functions are meant to compare values of the same data type or types that can be implicitly cast to each other. If you try to compare totally different, incompatible types, you'll get an error.

How this mistake shows up: You'll get an error message telling you the data types are incompatible.