NULL e gestione dei dati mancanti in SQL

Gestire i dati mancanti (cioè i NULL) in SQL può essere una sfida. I NULL possono creare molte trappole, soprattutto se non si capisce come funzionano. In questo articolo parleremo della gestione dei NULL in SQL. Spiegheremo anche come evitare gli errori più comuni quando si lavora con i NULL.

I dati mancanti nel database sono una realtà inevitabile della vita. Le ragioni per cui si possono avere dati mancanti nel database sono molteplici: i dati completi non sono ancora disponibili, gli utenti forniscono informazioni incomplete, modifiche allo schema del database, malfunzionamenti del database, errori umani e altro ancora. Lavorare con i dati mancanti nelle query SQL è impegnativo. In questo articolo mostrerò le insidie più comuni associate ai dati incompleti in SQL.

Se volete esercitarvi a lavorare con dati mancanti o sconosciuti in SQL, vi consiglio la nostra traccia Pratica su SQL . Al momento in cui scriviamo, contiene 10 corsi per esercitarsi con l'SQL - e continuiamo ad aggiungerne altri! I corsi sono suddivisi in sezioni e molti di essi hanno una sezione dedicata a NULL. Raccomando in particolare questi corsi per esercitarsi a gestire i NULL in SQL:

• Pratica su SQL di base: A Store
• Pratica su SQL: Università
• Pratica SQL di base: una corsa tra le query!
• Basic Pratica su SQL: Blog e dati sul traffico

Che cos'è il NULL in SQL?

In SQL, NULL rappresenta un valore mancante o indefinito in un database. Viene utilizzato per indicare che il valore di un campo è assente o sconosciuto.

Un problema comune per i principianti con NULLs è che il database spesso non mostra esplicitamente NULLs. Nella tabella seguente, il campo like per l'ID post 1, il campo location per l'ID post 2 e il campo views per l'ID post 3 sono tutti NULL. Tuttavia, un database non mostra esplicitamente NULL, ma un campo vuoto. È necessario essere consapevoli che NULL è una possibilità per indovinare che questi campi sono NULL.

id	title	location	views	likes
1	Quick Morning Routines!	London	94,365
2	Eco-Friendly Living Tips		123,891	6,587
3	Healthy Snacks on the Go	Paris		9,457

È importante notare, tuttavia, che NULL è diverso da una stringa vuota o da uno zero. NULL è l'assenza di valore; significa che il valore è sconosciuto. Come vedremo tra poco, NULL stesso non è un valore reale. Molte persone dicono o scrivono qualcosa come "Ci sono NULL valori in questo campo", ma questo è tecnicamente scorretto.

Quando si progetta una tabella in un database, un progettista di database può decidere che NULL non è consentito per un particolare campo. Ciò avviene impostando un vincolo NOT NULL per una colonna al momento della creazione della tabella. Anche le chiavi primarie (colonne che identificano le righe di una tabella) sono NOT NULL per impostazione predefinita.

È considerata una buona pratica evitare NULLs nella progettazione del database. Pertanto, il maggior numero possibile di colonne dovrebbe essere definito come NOT NULL. È meglio consentire NULLs solo quando è strettamente necessario. Tuttavia, i dati possono essere disordinati e a volte NULL è inevitabile.

L'insieme di dati

In questo articolo, utilizzeremo i dati della tabella posts. Immaginiamo di dover prelevare i dati dalla nostra piattaforma di social media preferita per farne un'analisi. I dati della piattaforma sono memorizzati in questa tabella. Ecco i campi:

• id - L'ID del post.
• title - Il titolo del post.
• url - L'URL (indirizzo web) del post.
• creator - Il nome del creatore del post.
• published - La data di pubblicazione del post.
• type - Il tipo di post.
• location Dove è stato pubblicato il post; questo può essere NULL se il luogo è sconosciuto o irrilevante.
• views - Il numero di visualizzazioni di ciascun post; questo può essere NULL se il creatore sceglie di non rendere questo dato pubblicamente visibile.
• likes - Il numero di "mi piace" dati al post; questo può essere NULL se il creatore sceglie di non rendere questo dato pubblicamente visibile.
• dislikes - Il numero di dislikes dati al post; questo può essere NULL perché la piattaforma non mostra più questo dato. Tuttavia, potremmo avere i dati dei non mi piace per i post più vecchi.

Ora che abbiamo esaminato i dati, usiamoli per capire NULL.

Operatori di confronto con NULL

Anche qualcosa di apparentemente semplice come il comportamento di NULL quando viene utilizzato con gli operatori di confronto può essere controintuitivo e sorprendente per i principianti.

Supponiamo di voler scoprire quante righe della tabella posts hanno campi mancanti views. Per esempio:

SELECT COUNT(*)
FROM posts
WHERE views = NULL;

Risultato: 0 righe

Ottimo, zero righe con campi mancanti. Incredibile! Ma quante sono le viste non mancanti?

SELECT COUNT(*)
FROM posts
WHERE views <> NULL;

Risultato: 0 righe

Anche qui zero? Deve esserci qualcosa di sbagliato.

Il problema è che si devono usare gli operatori IS NULL and IS NOT NULL per verificare la presenza di NULLs:

SELECT COUNT(*)
FROM posts
WHERE views IS NULL;

Risultato: 34 righe

SELECT COUNT(*)
FROM posts
WHERE views IS NOT NULL;

Risultato: 66 righe

Perché questi risultati sono così diversi dalle due query precedenti?

Logica a tre valori in SQL

I problemi di confronto per NULL derivano dal fatto che NULL non è un valore reale. Questo è il punto più importante da capire per lavorare con NULL in modo efficiente.

SQL utilizza una logica a tre valori. Ogni condizione logica in SQL può avere uno dei tre valori seguenti: TRUE FALSE o NULL. NULL significa "non lo so". Ogni volta che si utilizza una condizione in WHERE, SQL restituisce le righe per le quali la condizione logica in WHERE è TRUE. Non restituisce le righe per le quali la condizione è FALSE (come ci si aspetterebbe) e per le quali la condizione è NULL (che non è sempre quello che ci si aspetta).

Consideriamo un esempio di come funziona WHERE:

SELECT COUNT(*)
FROM posts
WHERE views < 100;

Questa query conta i post per i quali la colonna views ha un valore e questo valore è inferiore a 100. I post con visualizzazioni sconosciute non vengono contati. Questo può essere controintuitivo: quando si visualizzano i dati dei post, si vede il vuoto in molti campi delle visualizzazioni. Si pensa intuitivamente che questo vuoto sia sicuramente inferiore a 100. Ma il vuoto significa che il valore delle visualizzazioni è inferiore a 100. Ma il vuoto significa che il database non ha i dati; non avendo i dati, non può dire se sono meno di 100 o meno. Quindi le righe che hanno un campo views vuoto non vengono contate.

SELECT COUNT(*)
FROM posts
WHERE views < likes;

Questa query restituisce le righe in cui il numero di visualizzazioni e il numero di "mi piace" sono entrambi noti e il valore views è inferiore al valore likes. Non restituisce le righe in cui il campo è vuoto. Non restituisce le righe in cui il valore views è sconosciuto o il valore likes è sconosciuto.

Se si desidera includere nel risultato i post con i campi views vuoti, è necessario filtrare esplicitamente per NULL:

SELECT COUNT(*)
FROM posts
WHERE views < likes OR views IS NULL;

Torniamo di nuovo alle query con cui abbiamo iniziato:

SELECT COUNT(*)
FROM posts
WHERE views = NULL;

SELECT COUNT(*)
FROM posts
WHERE views <> NULL;

La condizione WHERE confronta la colonna views con NULL. Tuttavia, NULL significa "Non conosco il valore". Il database non può dire se views è uguale (o meno) a un valore sconosciuto. Può essere o non essere, quindi il database dice NULL - cioè "non lo so" - e quelle righe non vengono restituite nel risultato.

Ricordate:

• Testate NULL con IS NULL e IS NOT NULL
• Gli operatori di confronto (come <, <=, >, >=, =, <> e LIKE) restituiscono NULL se uno degli argomenti è NULL. Se si desidera includere NULL, verificarlo esplicitamente con IS NULL o IS NOT NULL.

L'uso di NULL nelle funzioni SQL

NULL è altrettanto problematico negli operatori e nelle funzioni. La maggior parte delle funzioni e degli operatori restituisce NULL quando viene dato NULL come argomento.

Esempio 1: Immaginiamo di voler restituire l'intestazione di ogni post. (L'intestazione è composta dal titolo, da un trattino e dalla posizione). Ecco la query:

SELECT 
  title || ‘ - ‘ || location
FROM posts;

Risultato:

Quick Morning Routines! - London
Healthy Snacks on the Go - Paris

La query restituisce NULL se title o location sono mancanti. Il post con ID 2 ha NULL come risultato della nostra query, poiché il suo location è sconosciuto.

Esempio 2: Lo stesso vale per gli operatori aritmetici. Supponiamo di voler calcolare l'impegno del post come somma di likes e dislikes:

SELECT 
  title, 
  likes, 
  dislikes, 
  likes + dislikes AS engagement
FROM posts;

Risultato:

title	likes	dislikes	engagement
Quick Morning Routines!	25	15	30
Eco-Friendly Living Tips	10
Healthy Snacks on the Go		34

Se uno dei campi likes o dislikes è NULL, allora il valore restituito nella colonna engagement è anche NULL.

Esempio 3: Lo stesso comportamento è mostrato da funzioni regolari, come UPPER():

SELECT 
  title, 
  UPPER(creator)
FROM posts;

title	UPPER(creator)
Quick Morning Routines!	JENNY
Eco-Friendly Living Tips
Healthy Snacks on the Go	RACHEL82

Il creatore del post "Eco-Friendly Living Tips" è sconosciuto e quindi l'espressione UPPER(creator) restituisce NULL.

Funzioni che funzionano con i NULL

Fortunatamente, esistono funzioni in SQL che aiutano a mitigare questi problemi con NULL.

COALESCE

COALESCE() accetta molti argomenti e restituisce il primo valore nonNULL dei suoi argomenti. In genere si usa per sostituire NULL con un valore significativo in un'altra funzione o espressione. Potremmo modificare la nostra query di fidanzamento in questo modo:

SELECT 
  title, 
  likes, 
  dislikes, 
  COALESCE(likes, 0) + COALESCE(dislikes, 0) AS engagement
FROM posts;

Ogni volta che il valore likes o dislikes è NULL, la funzione COALESCE() lo sostituisce con 0. Il nuovo valore viene utilizzato nel calcolo e si evitano i risultati di NULL:

title	likes	dislikes	engagement
Quick Morning Routines!	25	15	30
Eco-Friendly Living Tips	10		10
Healthy Snacks on the Go		34	34

Si può anche usare COALESCE() per dare un'etichetta significativa a NULLs nei risultati. La seguente query sostituisce NULL con "Unknown" nel set di risultati; il campo stesso rimane NULL nel database:

SELECT 
  title, 
  COALESCE(location, ‘Unknown’) AS location
FROM posts;

Ecco il risultato:

title	location
Quick Morning Routines!	London
Eco-Friendly Living Tips	Unknown
Healthy Snacks on the Go	Paris

Ricordate: si usa la funzione COALESCE():

• Per fornire un'etichetta significativa a NULL nei report.
• Per dare un valore a NULL nei calcoli.

NULLIF

Un'altra funzione che lavora con NULL è NULLIF. Questa è un po' strana: prende due argomenti e restituisce NULL se gli argomenti sono uguali. In pratica, si usa NULLIF per evitare la divisione per zero:

SELECT 
  title, 
  likes / NULLIF(views, 0)
FROM posts;

Si vuole calcolare il rapporto tra likes e views per i messaggi. Tuttavia, se il valore di views è 0, si potrebbe avere un errore di divisione per zero. Per evitare ciò, si utilizza la funzione NULLIF. Se views è uguale a zero, NULLIF(views, 0) restituisce NULL.

NULL La divisione in NULL dà come risultato ed evita l'errore di divisione per zero. In questo caso si sfrutta la cascata di NULL sui risultati dei calcoli.

NULL nelle funzioni GROUP BY e aggregate

Quando si lavora con valori mancanti, è bene sapere come si comporta NULL nelle funzioni GROUP BY e aggregate.

NULL e GROUP BY

GROUP BY mettono le righe in gruppi basati su valori comuni in una determinata colonna. È quindi possibile applicare funzioni aggregate a ciascun gruppo e calcolare sommatorie per ciascun gruppo. Questa query conta il numero di post per ogni località:

SELECT 
  location, 
  COUNT(*)
FROM posts
GROUP BY location;

Con GROUP BY, tutte le righe con NULL nella colonna vengono inserite in un gruppo; si calcolano le statistiche per questo gruppo come per tutti gli altri.

Nel nostro esempio, tutti i post con una località sconosciuta vengono inseriti in un gruppo:

location	COUNT
London	45
Paris	23
	12
…	…

NULL e funzioni aggregate

In generale, anche le funzioni aggregate ignorano NULLs. Ma ci sono alcune varianti importanti nel modo in cui alcune funzioni aggregate gestiscono NULLs.

Le funzioni SUM(), MIN(), MAX() ignorano tutte NULLs:

SELECT 
  type, 
  SUM(views), 
  MIN(views), 
MAX(views)
FROM posts
GROUP BY type;

type	SUM	MIN	MAX
video	230,485	5,632	100,589
image	159,340	1,289	45,003
text	34,224	256	3,341
infographics

La funzione SUM() tratta NULL come se fosse 0, quindi NULL non influenza il risultato di SUM. Ma se tutti i valori del gruppo sono NULL, il risultato di SUM() è NULL. Nel nostro esempio, non abbiamo dati di visualizzazione per il gruppo infografica, quindi la somma è NULL per questo gruppo.

Anche le funzioni MIN() e MAX() ignorano NULL; esse restituiscono i valori minimi e massimi dei valori noti. Solo se tutti i valori del gruppo sono NULL queste funzioni restituiranno NULL. Il nostro gruppo di infografiche non ha dati, quindi i valori minimi e massimi sono riportati come NULL.

La funzione COUNT() è un po' più sottile quando si tratta di gestire NULL. Esistono tre varianti della sintassi di COUNT: COUNT(*), COUNT(expression), COUNT(DISTINCT). Potete leggerle nel nostro articolo Qual è la differenza tra COUNT(*), COUNT(1), COUNT(colonna) e COUNT(DISTINCT)?

SELECT 
  COUNT(*), 
  COUNT(location), 
  COUNT(DISTINCT location)
FROM posts;

COUNT	COUNT	COUNT
100	78	52

L'espressione COUNT(*) conta tutte le righe del set di risultati. Ci sono 100 messaggi nella nostra tabella posts tabella, quindi questa espressione restituisce 100.

L'espressione COUNT(location) conta i valori nonNULL nella colonna indicata. Nel nostro esempio, conterà i messaggi in cui la colonna location non è NULL. Ignorerà i post con località sconosciute.

Infine, l'espressione COUNT(DISTINCT location) conta i valori distinti nonNULL; in altre parole, ignora i valori ripetuti. Conterà il numero di località diverse presenti nella nostra tabella. posts tabella.

La funzione AVG() ignora NULL. In genere è quello che ci si aspetta. Tuttavia, bisogna fare attenzione quando si usa AVG() con COALESCE(). Tutte le seguenti varianti restituiscono valori diversi: AVG(views), AVG(COALESCE(views,0)), COALESCE(AVG(views)).

Ricordare:

• Le righe con NULLs nelle colonne GROUP BY vengono inserite in un gruppo separato.
• Le funzioni aggregate ignorano NULL e utilizzano solo valori noti nei calcoli.
• Utilizzare COALESCE se si desidera sostituire un valore sconosciuto con un valore specifico.

NULL e JOIN

È necessario ricordare NULL quando si usa JOIN, soprattutto con OUTER JOINs come LEFT JOIN o FULL JOIN. Ci può essere NULLs nelle colonne provenienti dalla tabella giusta.

Immaginiamo di avere un'altra tabella, commentsche contiene dati sui commenti ai post. Le informazioni sono contenute nelle seguenti colonne:

• id - Un identificatore unico per ogni commento.
• post_id - L'ID del post a cui si riferisce il commento.
• content - Il contenuto del commento
• author - L'autore del commento
• upvotes - Il numero di upvotes assegnati a questo commento; può essere NULL
• downvotes - Il numero di downvotes assegnati a questo commento; può essere NULL

Vogliamo contare quanti commenti ci sono per ogni post, ma vogliamo includere i post senza commenti nei risultati. Si deve usare posts LEFT JOIN comments per includere tutti i post.

Inoltre, bisogna ricordarsi di usare COUNT(comments.id) e non COUNT(*) quando si contano i commenti. Quest'ultimo conterà le righe indipendentemente dal fatto che la riga sia collegata al commento. Il modo corretto è usare COUNT(comments.id). Se non ci sono commenti, l'id è NULL e non viene contato.

SELECT 
  posts.title, 
  COUNT(comments.id)
FROM posts
LEFT JOIN comments
  ON posts.id = comments.post_id;

Un altro problema da tenere presente è che la condizione WHERE a volte può "annullare" la condizione OUTER JOIN. Nella query qui sotto, vogliamo trovare i commenti con upvotes superiore a 100. Se il post ha alcuni commenti con un numero di upvotes sconosciuto, questi non saranno inclusi nel risultato. Se il post ha alcuni commenti con un numero sconosciuto di voti alti, questi commenti non saranno inclusi nel risultato. Se il post ha solo commenti con un numero sconosciuto di voti in su, il post non sarà incluso affatto, nonostante l'uso di LEFT JOIN. La condizione WHERE "annulla" di fatto la condizione LEFT JOIN:

SELECT 
  posts.title, 
  comments.content
FROM posts
LEFT JOIN comments
  ON posts.id = comments.post_id
WHERE upvotes > 100;

Ricordate:

• LEFT JOIN, RIGHT JOIN o FULL JOIN possono introdurre NULL nel risultato.
• La condizione WHERE può "annullare" la condizione OUTER JOIN.

NULL in ORDER BY

Quando si crea un report, spesso si desidera ordinare i dati in un ordine specifico, ad esempio in ordine alfabetico, ascendente o discendente. Come si comporta NULL nell'ordinamento?

Quando si ordina in base a una colonna che contiene NULL, le righe con NULL vengono visualizzate per prime o per ultime, a seconda del motore di database utilizzato. Ad esempio, MySQL colloca NULLs per primo nell'ordinamento ascendente, mentre Oracle lo colloca per ultimo nell'ordinamento ascendente. È possibile verificare il comportamento predefinito del database nella sua documentazione.

Se non si ricorda il comportamento predefinito o non lo si gradisce, si possono usare gli operatori NULLS FIRST o NULLS LAST dopo ORDER BY per specificare il comportamento desiderato:

SELECT 
  title, 
  views
FROM posts
ORDER BY views DESC NULLS LAST;

In questo modo tutte le righe che contengono un NULL vengono elencate per ultime:

title	views
Quick Morning Routines!	120,365
…	…
Eco-Friendly Living Tips	256
Easy At-Home Workouts for All Levels
Healthy Snacks on the Go

Per maggiori informazioni su come NULL lavora con ORDER BY, si veda Come ORDER BY e NULL lavorano insieme in SQL.

Gestire i dati mancanti con NULL in SQL!

Gestire NULL e i dati mancanti in SQL è un'abilità importante per chiunque lavori con i dati. Comprendere le sfumature di NULL, il suo comportamento in diverse operazioni e le migliori pratiche per la gestione dei dati mancanti garantisce l'accuratezza delle query e l'affidabilità delle analisi.

Per approfondire la conoscenza di SQL, prendete in considerazione l'acquisto del pacchettoCompleto per sempre SQL. Questa offerta, che prevede un pagamento unico, fornisce l'accesso a vita a tutti i corsi SQL attuali e futuri. I corsi coprono tutto, dalle query di base all'SQL avanzato; ciò che imparerete vi sarà utile a tutti i livelli della vostra carriera. Inoltre, non dimenticate di dare un'occhiata al nostro percorsoPratica su SQL con 10 corsi di pratica SQL e oltre 1.000 esercizi. Migliorate le vostre competenze a lungo termine con LearnSQL.it!