Funzioni GREATEST() e LEAST() e NULL

Oggi ci buttiamo su un argomento ancora più specifico ma super importante: le funzioni GREATEST() e LEAST(). Vedrai come trovare i valori massimi e minimi tra più colonne e, soprattutto, come NULL influisce sul loro funzionamento.

Se hai mai cercato la cosa più importante nella tua vita (l’amore, il lavoro dei sogni o la miglior ricetta della pizza), capirai subito a cosa servono le funzioni GREATEST() e LEAST(). Queste funzioni ti aiutano a trovare il valore più grande o più piccolo in una lista di cose. Solo che invece della pizza lavori con numeri, date, stringhe e altri dati in PostgreSQL.

GREATEST()

GREATEST() restituisce il valore più grande tra quelli che gli passi.

Sintassi:

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

LEAST()

LEAST() fa l’opposto: cerca il valore più piccolo.

Sintassi:

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

Esempio:

Supponiamo di avere una tabella students_scores dove sono salvati i voti degli studenti per tre esami:

Uso di GREATEST() e 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;

Risultato:

Come NULL influisce su GREATEST() e LEAST()

Ora arriviamo alla parte più interessante. Insieme ai valori nella tabella, ci possono essere anche i NULL. E come già sai, NULL è una specie di entità misteriosa che indica l’assenza di dati o un valore sconosciuto. Vediamo cosa succede se NULL finisce nelle funzioni GREATEST() e LEAST() in PostgreSQL.

Comportamento di NULL:

In PostgreSQL le funzioni GREATEST() e LEAST() hanno un comportamento particolare: ignorano i valori NULL quando cercano il valore massimo o minimo tra i loro argomenti. Importante: L’unico caso in cui queste funzioni restituiscono NULL è quando tutti i loro argomenti sono NULL.

Esempio:

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

Risultato:

Come vedi, NULL è stato ignorato e le funzioni hanno restituito i valori massimo e minimo tra quelli presenti (10, 20, 5).

Ecco un esempio in cui tutti gli argomenti sono NULL:

Esempio:

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

Risultato:

Come evitare problemi con NULL?

Anche se PostgreSQL di default ignora i NULL, a volte potresti volere un comportamento diverso. Per esempio, se vuoi che NULL venga trattato come un valore specifico (tipo 0 o un altro valore di default) quando cerchi il massimo/minimo. In questi casi puoi usare la funzione COALESCE().

La funzione COALESCE(arg1, arg2, ...) restituisce il primo argomento che non è NULL nella lista. Questo ti permette di sostituire i NULL con un valore sensato prima di passarli a GREATEST() o LEAST().

Esempio 1: Sostituire NULL con 0

Supponiamo di voler considerare l’assenza di voto (NULL) come 0. Possiamo usare COALESCE() per mettere un valore di default.

Ecco la nostra tabella di partenza:

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;

Risultato:

Esempio 2: Sostituire NULL con il valore di un’altra colonna

A volte invece di un valore fisso (tipo 0) vuoi mettere il valore di un’altra colonna. Per esempio, se manca exam_3, vuoi usare il valore di exam_1.

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

Supponiamo di avere questa tabella:

Risultato della query:

Casi pratici

Case 1: Trovare lo sconto massimo

Lavori con la tabella orders, dove ogni ordine può avere tre tipi diversi di sconto. Devi trovare il massimo tra tutti gli sconti per ogni ordine.

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

Risultato:

Case 2: Trovare il prezzo minimo del prodotto

Nella tabella products ci sono i prezzi dei prodotti in tre valute (USD, EUR, GBP). Il tuo compito è trovare il prezzo minimo per ogni prodotto.

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

Se tutti i prezzi sono NULL, anche il risultato sarà NULL

Errori tipici quando usi GREATEST() e LEAST()

Errore 1: Risultato inaspettato a causa di NULL.

Prima nella lezione abbiamo visto in dettaglio come NULL influisce su GREATEST() e LEAST() in PostgreSQL. L’errore principale è che chi è abituato al comportamento di NULL in altri DBMS (dove un solo NULL “contamina” tutto il risultato), si aspetta lo stesso anche in PostgreSQL.

Come si manifesta l’errore: Potresti pensare per sbaglio che se tra gli argomenti c’è un NULL, la funzione restituirà sempre NULL. Di conseguenza, potresti usare COALESCE() su tutti gli argomenti senza motivo, complicando la query e rallentandola, anche se nel tuo caso i NULL dovrebbero solo essere ignorati.

Errore 2: Usare GREATEST() e LEAST() con tipi incompatibili.

Le funzioni GREATEST() e LEAST() sono pensate per confrontare valori dello stesso tipo di dato o tipi che possono essere convertiti tra loro in automatico. Se provi a confrontare valori di tipi completamente diversi e incompatibili, otterrai un errore.

Come si manifesta l’errore: Riceverai un messaggio di errore che ti dice che i tipi di dato non sono compatibili.