Funciones GREATEST() y LEAST() y NULL

Hoy nos metemos en un tema aún más específico pero súper importante: las funciones GREATEST() y LEAST(). Vas a aprender cómo encontrar los valores máximos y mínimos de varias columnas y, lo más importante, cómo NULL afecta su funcionamiento.

Si alguna vez has buscado lo más importante en tu vida (el amor, el trabajo de tus sueños o la mejor receta de pizza), enseguida vas a pillar para qué sirven las funciones GREATEST() y LEAST(). Estas funciones te ayudan a encontrar el valor más grande o más pequeño en una lista de cosas. Solo que en vez de pizza, aquí trabajas con números, fechas, cadenas y otros datos en PostgreSQL.

GREATEST()

GREATEST() devuelve el valor más grande del conjunto que le pasas.

Sintaxis:

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

LEAST()

LEAST() hace lo contrario: busca el valor más pequeño.

Sintaxis:

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

Ejemplo:

Supón que tenemos una tabla students_scores donde se guardan las notas de los estudiantes en tres exámenes:

Uso de GREATEST() y 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;

Resultado:

¿Cómo afecta NULL a GREATEST() y LEAST()?

Ahora llegamos a lo más interesante. Junto con los valores en la tabla puede haber NULL. Y como ya sabes, NULL es esa cosa misteriosa que significa que no hay datos o que el valor es desconocido. Vamos a ver qué pasa si NULL cae en las funciones GREATEST() y LEAST() en PostgreSQL.

Comportamiento de NULL:

En PostgreSQL, las funciones GREATEST() y LEAST() tienen un comportamiento especial: ignoran los valores NULL al buscar el valor más grande o más pequeño entre sus argumentos. Importante: El único caso en que estas funciones devuelven NULL es si todos sus argumentos son NULL.

Ejemplo:

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

Resultado:

Como ves, NULL fue ignorado y las funciones devolvieron los valores más grande y más pequeño de los presentes (10, 20, 5).

Y aquí tienes un ejemplo donde todos los argumentos son NULL:

Ejemplo:

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

Resultado:

¿Cómo evitar problemas con NULL?

Aunque PostgreSQL ignora NULL por defecto, a veces puede que necesites otro comportamiento. Por ejemplo, si quieres que NULL se trate como un valor concreto (como 0 u otro valor por defecto) al buscar el máximo/mínimo. En estos casos puedes usar la función COALESCE().

La función COALESCE(arg1, arg2, ...) devuelve el primer argumento que no sea NULL de su lista. Esto te permite sustituir previamente NULL por un valor con sentido antes de pasarlo a GREATEST() o LEAST().

Ejemplo 1: Sustituir NULL por 0

Supón que quieres considerar que la ausencia de nota (NULL) es igual a 0. Podemos usar COALESCE() para poner el valor por defecto.

Aquí está nuestra tabla original:

Consulta:

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;

Resultado:

Ejemplo 2: Sustituir NULL por el valor de otra columna

A veces, en vez de un valor fijo (como 0), necesitas poner el valor de otra columna. Por ejemplo, si falta exam_3, queremos usar el valor de exam_1.

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

Supón que tenemos esta tabla:

Resultado de la consulta:

Casos prácticos

Caso 1: Buscar el descuento máximo

Trabajas con la tabla orders, donde cada pedido puede tener tres tipos de descuento diferentes. Tienes que encontrar el mayor de todos los descuentos para cada pedido.

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

Resultado:

Caso 2: Buscar el precio mínimo del producto

En la tabla products se guardan los precios de productos en tres monedas (USD, EUR, GBP). Tu tarea es encontrar el precio mínimo para cada producto.

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

Si todos los precios son NULL, el resultado también es NULL

Errores típicos al usar GREATEST() y LEAST()

Error 1: Resultado inesperado por culpa de NULL.

Antes en la lección ya vimos en detalle cómo NULL afecta a GREATEST() y LEAST() en PostgreSQL. El error principal es que la gente que está acostumbrada al comportamiento de NULL en otros SGBD (donde un solo NULL "envenena" todo el resultado), espera lo mismo en PostgreSQL.

Cómo se manifiesta el error: Puedes pensar por error que si hay un NULL en la lista de argumentos, la función siempre va a devolver NULL. Como resultado, puedes usar COALESCE() en todos los argumentos sin necesidad, lo que puede complicar la consulta y hacerla más lenta, si en tu caso NULL debería ser simplemente ignorado.

Error 2: Usar GREATEST() y LEAST() con tipos incompatibles.

Las funciones GREATEST() y LEAST() están pensadas para comparar valores del mismo tipo de dato o de tipos que se pueden convertir entre sí de forma implícita. Si intentas comparar valores de tipos totalmente diferentes e incompatibles, te va a dar error.

Cómo se manifiesta el error: Vas a recibir un mensaje de error que te dice que los tipos de datos no son compatibles.