Come affrontare i NULL in SQL: Funzione COALESCE

È inevitabile che alcuni dati nel database non abbiano valore, cosa che in SQL è rappresentata dalla parola chiave NULL. "Nessun valore" qui è diverso da zero, falso o una stringa vuota (ma con delle eccezioni! In Oracle Oracle, NULL equivale a una stringa di lunghezza zero). Quando si gioca con il database, il trattamento di NULLdiventa sempre più problematico, quindi Lo standard MySQL fornisce un aiuto con alcune funzioni, come ad esempio COALESCE.

Introduciamo la COALESCE() funzione:

Definizione

COALESCE restituisce il primo argomento non NULL dell'elenco di argomenti passato. La sua sintassi è la seguente:

COALESCE(x, y, … , n)

Inoltre, è importante che:

• Richiede almeno due argomenti.
• Le espressioni della funzione COALESCE devono valutare lo stesso tipo di dati (ad esempio, l'istruzione SQL SELECT COALESCE (1, 'aa', NULL, 23); produrrà un errore).
• Se tutti gli argomenti elencati hanno come risultato NULL, la funzione restituisce anche NULL.

COALESCE è essenzialmente una dichiarazione abbreviata di CASE presentata di seguito:

CASE
WHEN x IS NOT NULL THEN x
WHEN y IS NOT NULL THEN y
WHEN ... IS NOT NULL THEN …
ELSE n
END

COALESCE - esempi di utilizzo

Ora, date un'occhiata al prototipo del modello di database universitario presentato di seguito. Mostrerò alcuni esempi che richiedono la funzione COALESCE che fa riferimento alle tabelle di questo modello.

Uso di COALESCE con la concatenazione dei valori

Date un'occhiata alla tabella student del modello di database universitario presentato:

Può succedere che una persona in particolare non abbia il secondo nome, quindi alcuni record della colonna middle_name possono essere NULLcome nell'esempio di dati qui sotto.

Proviamo a concatenare nome, secondo nome e cognome dello studente. Questo funziona in PostgreSQL e Oracle.

SELECT
  first_name || ' ' || middle_name || ' ' || last_name AS full_name
FROM student;

La tabella dei risultati contiene una colonna full_name con le stringhe concatenate.

Si noti che solo un record ha un nome completo corretto. Gli altri sono NULL, perché anche il secondo nome era NULL (concatenando le stringhe con il valore NULL, il risultato è anche NULL).

Un risultato del genere non sembra molto bello. Il risultato non mostra il nome completo degli studenti senza secondo nome. In questo caso la funzione COALESCE() viene in soccorso. Con questa funzione si possono sostituire i campi NULL con una stringa vuota, per esempio. Ora, la nuova query si presenta come segue:

SELECT
  first_name || COALESCE(' ' || middle_name || ' ', ' ') || last_name AS full_name
FROM student;

Il risultato è ora completo 🙂

MySQL, MS SQL Server utilizza per la concatenazione la funzione CONCAT() o l'operatore '+'.

Allo stesso modo, la query in MySQL o MS SQL Server avrà il seguente aspetto:

SELECT
  CONCAT(first_name, ' ', middle_name, ' ', last_name)
FROM student;

... e i record che hanno NULLavranno anche :

Con la funzione COALESCE, le NULLche appaiono nella colonna middle_name saranno sostituite.

SELECT
  CONCAT(first_name, ' ', COALESCE(middle_name, ''), ' ', last_name)
FROM student;

Utilizzo di PostgreSQL COALESCE con le funzioni aggregate

In effetti, NULLs può apparire molto problematico. Immaginiamo una situazione diversa. Voglio sapere qual è la media dei voti del corso. Sembra molto facile, vero? Ma, aspettate, aspettate... anche se questa è una query semplice, possiamo incontrare alcuni inconvenienti. Il problema riguarda alcune funzioni aggregate di SQL come SUM(), MAX() o MIN().

ESEMPIO: Uso di COALESCE con la funzione AVG

Proviamo a fare un esempio con la tabella student_courses.

Come si vede qui sotto, gli studenti iscritti al corso con id 1 non hanno ancora un voto.

La query che restituisce il voto medio del corso si presenta come segue:

SELECT
  course_instance_id,
  AVG(grade) AS average_grade_in_course
FROM student_courses
GROUP BY course_instance_id;

Per la funzione AVG(), i campi nonNULL vengono sommati e la somma viene divisa per il numero di campi nonNULL. Quindi, se tutti i record hanno NULL nella colonna voto, anche il voto medio sarà NULL.

La tabella dei risultati è riportata di seguito:

In questo caso, vogliamo inserire un altro valore, come 0 (il voto può essere un valore da 2 a 5, quindi la media di 0 può indicare che gli studenti non hanno voti).

COALESCE() La funzione viene in aiuto nella sostituzione di un valore. La stessa query con COALESCE sarà:

SELECT
  course_instance_id,
  COALESCE(AVG(grade), 0) AS average_grade_in_course
FROM student_courses
GROUP BY course_instance_id;

E ora possiamo ottenere la tabella dei risultati come:

ESEMPIO: Uso di COALESCE con la funzione SUM()

Questo è un esempio simile. Supponiamo una situazione diversa. Vogliamo contare il totale delle ore di assenza per ogni studente. Date un'occhiata alla parte selezionata del modello:

I dati della tabella student_course_attendance hanno il seguente aspetto.

Mentre i dati di student_courses sono:

Osservate attentamente i dati di queste tabelle. Come si vede, due studenti sono iscritti a un determinato corso, mentre solo uno mancava alle lezioni.

La query, che calcolerà la somma del numero totale delle ore di assenza per un course_id = 1 per ogni studente, è la seguente:

SELECT
  student_id,
  SUM(absence_hours) AS total_absence_hours
FROM student_courses sc
LEFT JOIN student_course_attendance sca
  ON sc.id = sca.student_courses_id
WHERE course_instance_id = 1
GROUP BY 1;

... e viene valutata in una tabella di risultati:

L'ora di assenza totale è stata valutata a NULL, perché in effetti questo studente non ha saltato nessuna lezione in questo corso. Non c'era nessun record per questo studente nella tabella student_course_attendance e la funzione SUM() ha restituito NULL. Si può ovviare a questa situazione utilizzando la funzione COALESCE funzione.

La nuova query è la seguente:

SELECT
  student_id,
  COALESCE(SUM(absence_hours), 0)  AS total_absence_hours
FROM student_courses sc
LEFT JOIN student_course_attendance sca
  ON sc.id = sca.student_courses_id
WHERE course_instance_id = 1
GROUP BY 1;

E il risultato è ora:

Abbandoniamo ora il modello universitario e guardiamo un altro esempio.

Uso di COALESCE nella creazione di tabelle PIVOT

Questo caso d'uso sarà presentato per il database PostgreSQL. Si noti che altri database come Oracle e MS SQL Server dispongono di funzioni equivalenti per la creazione di tabelle pivot.

Osservate la tabella sottostante. È il risultato di una query che mostra le vendite totali di ogni marchio in un determinato mese.

Voglio trasporre le righe in colonne (creare una tabella pivot). La tabella dei risultati dovrebbe avere le colonne: brand_id, jan, feb, ..., dec. Si noti che non in tutti i mesi sono stati venduti prodotti di una determinata marca. Diamo un'occhiata all'esempio di Postgres.

SELECT brand_id, jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec

FROM CROSSTAB (
  'SELECT year, month, qty FROM sales ORDER BY 1',
  'SELECT m FROM generate_series(1,12) m'
) AS (
   year int,
   "jan" int,
   "feb" int,
   "mar" int,
   "apr" int,
   "may" int,
   "jun" int,
   "jul" int,
   "aug" int,
   "sep" int,
   "oct" int,
   "nov" int,
   "dec" int
);

Dopo aver eseguito una simile query, riceviamo:

Sfortunatamente, le vendite sono registrate solo in alcuni mesi e solo in quei campi viene inserito il numero di vendite totali. Come probabilmente ci si aspettava, il resto dei campi contiene NULL.

Per eliminare le NULLs indesiderate possiamo usare la funzione COALESCE, che quando serve inserisce uno 0 al posto di NULL. La query riparata si presenta come segue:

SELECT 
  brand_id,
  COALESCE(jan, 0),
  COALESCE(feb, 0),
  COALESCE(mar, 0),
  COALESCE(apr, 0),
  COALESCE(may, 0),
  COALESCE(jun, 0),
  COALESCE(jul, 0),
  COALESCE(aug, 0),
  COALESCE(sep, 0),
  COALESCE(oct, 0),
  COALESCE(nov, 0),
  COALESCE(dec, 0)
FROM CROSSTAB(
  'SELECT year, month, qty FROM sales ORDER BY 1',
  'SELECT m FROM generate_series(1,12) m'
) AS (
   year int,
   "jan" int,
   "feb" int,
   "mar" int,
   "apr" int,
   "may" int,
   "jun" int,
   "jul" int,
   "aug" int,
   "sep" int,
   "oct" int,
   "nov" int,
   "dec" int
);

Riepilogo

NULLI file possono rendere la vita problematica. Se non avete ancora sperimentato il lato negativo dei valori mancanti, lo incontrerete sicuramente. È una questione di tempo. Per ora, ricordate la funzioneCOALESCE , che vi aiuterà ad affrontare i valori indesiderati di NULL.