null
handlingmin
and max
functionsnull
elements in row valuesThe 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
TreatmentThe 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
I offer SQL training, tuning and consulting. Buying my book “SQL Performance Explained” (from €9.95) also supports my work on this website.
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.
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
.
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.
Some SQL dialects have scalar min
and max
functions that take multiple arguments and return the lowest or highest value.
null
values: one null
value makes the result null
null
values: one null
value makes the result null
• Non-conforming handling of collationsThe greatest
and least
functions form the optional feature T054, “GREATEST and LEAST”.
You can’t catch up on 20 years of SQL evolution in one day. Subscribe the newsletter via E-Mail, Twitter or RSS to gradually catch up and to keep modern-sql.com on your radar.
The essence of SQL tuning in 200 pages
Buy now!
(paperback and/or PDF)
Paperback also available at Amazon.com.
Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »
The oldest non-conforming implementation I found is PostgreSQL 8.1 from 2005.
Starting with SQL:2023, implementation-defined elements have an ID and a name similar to features. Part 2 has 360 of them.