Greatest(), least()

1. ⚡Non-conforming null handling
2. Alternative: scalar min and max functions
3. Non-conforming handling of null elements in row values

The SQL functions greatest and least take one or more arguments✓✗ and return the largest or smallest value if none of the arguments is null.✓✗

SELECT GREATEST(1, 2, 3)
  FROM …

Greatest and least are scalar functions, not aggregate functions. Unlike min and max they operate on one row at a time.

Null Treatment

The SQL standard requires greatest and least to return null in case one argument is null. This is different from aggregate functions, in particular min and max. While min and max ignore null values and return the smallest or largest value from the remaining set, greatest and least respect null values in so far as the presence of a single null value means that the highest or lowest value cannot be determined without doubt.

As you can see in the chart, the standard SQL null treatment is not always applied. This is no surprise because greatest and least were often supported decades before they were added to the SQL standard in 2023.

Personally, I think that standardizing greatest and least with strict a respect nulls semantic was not the best decision. The SQL standard is anyway not followed very strictly. Demanding new behavior that contradicts decades-old implementations0 makes it even worse because existing implementations will probably not adopt to the new standard. As both ways to treat null have been in the wild for a very long time, neither way should have become standard. It’s just another implementation-defined behavior.1

Going forward I hope that users will have the choice. For example, respect nulls and ignore nulls specifications could be added similar to those for some window functions.

LEAST(…) [ RESPECT NULLS | IGNORE NULLS ]

The filter clause, known from aggregates, would be more generic but it doesn’t solve the problem of long-standing implementations that ignore null values.

Row Values

In standard SQL, a value that compares to any other value as unknown cannot be the result of greatest or least.

Applying this to the following examples means that ⓵ and ⓶ return the first value because there is no other value that could be even smaller.

⓵ LEAST( ROW(1   ,    1),  ROW(1,    2) )
⓶ LEAST( ROW(1   , null),  ROW(2, null) )
⓷ LEAST( ROW(1   , null),  ROW(1, null) )
⓸ LEAST( ROW(null,    1),  ROW(9,    2) )

If you’re wondering about ⓶, keep in mind that range comparisons on row values are fully determined as soon as a prefix gives a definite answer. This is not the case for example ⓷; even though both values are not distinct from each other, they are still not equal as per the normal comparison rules of SQL. A less-then-or-equals (<=) comparison of these two values yields unknown. The existence of an even smaller value cannot be ruled out for either of the values so that least returns the null value. The same applies to example ⓸. As the first element of one value is null, all comparisons to that value return unknown. Consequently, it is impossible that there is any value that is undoubtedly smaller-or-equal to all other values. Least returns null. These are just consequences of the general behavior of null in SQL: null represents uncertainty, uncertainty propagates.

Again, it is no surprise that old systems don’t conform to the new standard. Those few tested systems that do support row values in greatest and least implement a sort-based logic—i.e., they treat null elements as largest or smallest possible values. Whether it is a very large or very small value varies among systems, very much like the default null ordering in the order by clause (ID133). Luckily, they do it the same way for greatest/least as they do it for order by.

Limits

The SQL standard requires greatest and least to have one or more arguments. In practice, some systems do not support the (nearly useless) one-argument case. The upper bound is typically beyond my tests.

Alternatives

Some SQL dialects have scalar min and max functions that take multiple arguments and return the lowest or highest value.

1. Respects null values: one null value makes the result null
2. Respects null values: one null value makes the result null • Non-conforming handling of collations

Related

• NULL: Indicating the Absence of Data

• filter: Selective Aggregates

Normative References

The greatest and least functions form the optional feature T054, “GREATEST and LEAST”.