Come utilizzare gli operatori di confronto con i NULL in SQL

Il valore SQL NULL ha una funzione speciale. Ha anche dei comportamenti controintuitivi che possono mettere in crisi i principianti di SQL o anche i programmatori più esperti. Scoprite come evitare questi problemi quando usate NULL con gli operatori di confronto.

Questo articolo vi aiuterà a padroneggiare le migliori pratiche per la creazione di query SQL che funzionano con i valori NULL e utilizzano gli operatori di confronto ( =, <>, <, > ) - che, se avete già scritto query SQL, sapete che sono praticamente tutte le query che scriverete! Si tratta di nozioni essenziali e la loro padronanza renderà l'SQL più semplice.

Faremo una breve panoramica sui NULL in SQL, sul perché esistono e su come la loro stranezza influisce sui risultati delle query. Seguiranno alcune query SQL che dimostrano la stranezza. Verranno poi discusse le tecniche standard per scrivere query che gestiscano correttamente i NULL e gli operatori di confronto. Infine, verrà fatto un rapido riepilogo di tutti gli operatori di confronto di SQL e di come ciascuno di essi interagisce con i NULL.

Non preoccupatevi. Non è così difficile come sembra. Cominciamo a esaminare i valori NULL in SQL.

SQL NULL - L'ignoto conosciuto

I database sono destinati a essere un'unica fonte di verità. Ciò che viene registrato nei campi di ogni riga rappresenta ciò che è noto.

Osservate la tabella seguente, adattata da What Is a NULL in SQL (un'ottima risorsa per approfondire i NULL in SQL). Questa potrebbe essere una parte di un database creato da un superfan dei Simpson.

name	social_sec_no	status	spouse
Homer Simpson	000-00-5000	married	Marjorie Bouvier
Nedward Flanders	000-00-4000	married	Maude Flanders
Waylon Smithers	000-00-8000	single	NULL
Dr Nick Riviera	000-00-7000	NULL	NULL

Il NULL rappresenta due cose diverse. Waylon Smithers è noto per essere single, quindi il NULL in questa riga nella colonna coniuge rappresenta un valore inesistente. Ma sappiamo così poco del Dr. Nick che i valori NULL nelle colonne coniuge e status rappresentano un valore sconosciuto.

Per mantenere l'integrità di un database, sono necessarie entrambe le interpretazioni di NULL. Per aiutare i programmatori a mantenere l'integrità nonostante i valori mancanti e sconosciuti, SQL incorpora i NULL nella sua logica ternaria.

Cos'è la logica ternaria e come funziona in SQL?

La logica binaria utilizza due valori: Vero e Falso, 0 e 1, ecc. La logica ternaria utilizza tre valori. In SQL, questi tre valori sono Vero, Falso e Sconosciuto. Nella logica ternaria SQL, NULL equivale al valore sconosciuto.

Ecco come i tre valori della logica ternaria funzionano con gli operatori logici di SQL NOT, OR e AND:

NOT
TRUE	FALSE
FALSE	TRUE
UNKNOWN	UNKNOWN

OR	TRUE	FALSE	UNKNOWN
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	UNKNOWN
UNKNOWN	TRUE	UNKNOWN	UNKNOWN

AND	TRUE	FALSE	UNKNOWN
TRUE	TRUE	FALSE	UNKNOWN
FALSE	FALSE	FALSE	FALSE
UNKNOWN	UNKNOWN	FALSE	UNKNOWN

Il risultato: Qualsiasi operazione logica che coinvolga un NULL dà come risultato un valore sconosciuto , ad eccezione di TRUE OR NULL e FALSE AND NULL. Interessante, ma non è necessario memorizzarlo. Come vedrete, l'SQL ci aiuta ad aggirare questa logica complicata. Il che è comodo, perché le operazioni di confronto in SQL utilizzano la stessa logica ternaria.

Operazioni di confronto SQL e valori NULL

Ecco una query che utilizza gli operatori di confronto e logici. Indovinate quante righe restituisce:

SELECT spouse
  FROM simpsons
 WHERE      spouse = NULL
    OR NOT (spouse = NULL) 

Cominciamo con la prima operazione di confronto:

WHERE      spouse = NULL

Qualunque cosa contenga la colonna di confronto (stipendi, nomi di animali domestici, ecc.), se verifichiamo che è uguale a NULL, il risultato è sconosciuto. Questo vale anche se il valore della colonna è NULL. È questo che confonde i programmatori esperti di altri linguaggi. Ad esempio, in Python il valore None sembra simile al NULL di SQL e può essere confrontato con se stesso:

>>> None == None
True

Ma la prima riga della clausola WHERE restituirà unknown. Quindi, quando la nostra query viene valutata, avrà l'aspetto seguente:

SELECT spouse
  FROM simpsons
 WHERE unknown
    OR NOT (spouse = NULL) 

Osserviamo la seconda riga della condizione WHERE:

   OR NOT (spouse = NULL)
	 

Anche questo confronto restituirà unknown. Dalla tabella di verità precedente si evince che NOT unknown restituirà unknown. Quindi ora la nostra query è diventata:

SELECT spouse
  FROM simpsons
 WHERE unknown
    OR unknown 
	 

La tabella di verità OR di cui sopra ci dice che il risultato sarà sconosciuto.

Una clausola WHERE richiede condizioni vere. Il fatto che il risultato non sia falso non è sufficiente. Quindi, nonostante la query sembri restituire tutte le righe, la logica ternaria di SQL e la natura di SQL NULL fanno sì che vengano restituite zero righe.

Gli altri operatori di confronto di SQL

Per questi esempi, utilizzeremo una tabella diversa, pet_owners:

name	pet_count
Bob	5
Cate	2
Alice	NULL
Steve	22

NULL e l'operatore <

Utilizzando la tabellapet_owners , vediamo chi ha meno di 5 animali domestici.

Il risultato:

name
Cate

Perché? Alice, che non ha compilato il modulo di indagine sugli animali domestici, ha NULL per la sua pet_count. Il valore di ogni NULL è sconosciuto. NULL è < 5? È sconosciuto, quindi Alice non può essere inclusa nei risultati.

NULL e l'operatore >

Ora vedremo chi ha più di 3 animali domestici.

SELECT p.name
  FROM pet_owners p
 WHERE pet_count > 3
	 

Il risultato:

name
Bob
Steve

Perché? Ancora una volta è il valore sconosciuto di NULL. Così come non si sa se NULL 3 < 5, it is unknown if NULL >. Alice è esclusa dai risultati.

NULL e l'operatore <=

Con un sottile cambiamento, ora otterremo un elenco di tutti coloro che non hanno più di 5 animali domestici.

SELECT p.name
  FROM pet_owners p
 WHERE pet_count <= 5
	 

Il risultato:

name
Bob
Cate

Cambiando la prima query dall'operatore < all'operatore <=, Bob viene aggiunto all'insieme dei risultati, ma non Alice. Nella prima query, il numero di animali domestici di Bob (5) non è inferiore a 5. Ma è inferiore o uguale a Alice. Ma è minore o uguale a 5, quindi ora è incluso nel risultato della query. Alice continua a non comparire.

Quando si esamina la riga di Alice, si può pensare a <= come abbreviazione di "NULL < 5 O NULL = 5". Sappiamo che "NULL < QUALSIASI COSA" restituirà sconosciuto; sappiamo anche che "NULL = QUALSIASI COSA" restituirà sconosciuto. Ancora una volta, Alice viene esclusa dai risultati.

NULL e l'operatore >=

Vediamo ora chi ha almeno 3 animali domestici.

SELECT p.name
  FROM pet_owners p
 WHERE pet_count >= 3
	 

Il risultato:

name
Bob
Steve

Come <=, possiamo pensare a >= come a una combinazione logica di due operazioni di confronto. Quindi, per Alice, il confronto è equivalente a "NULL > 3 OPPURE NULL = 3". Dovrebbe essere chiaro che il risultato può essere solo un valore sconosciuto.

Operatori di confronto SQL che funzionano con i NULL

Per gestire correttamente i NULL, SQL mette a disposizione due operatori di confronto speciali: IS NULL e IS NOT NULL. Questi operatori restituiscono solo vero o falso e rappresentano la migliore pratica per incorporare i valori NULL nelle query.

Per ottenere il risultato atteso, possiamo riscrivere la query dei Simpson in questo modo:

SELECT spouse
  FROM simpsons
 WHERE spouse IS NULL
    OR spouse IS NOT NULL
	 

Ora la query restituirà ogni riga, come ci aspettavamo.

SQL NULL e Outer Joins

Questo è un argomento più avanzato. Se non conoscete il sito SQL JOINs, probabilmente dovreste leggere prima 7 esempi di JOIN SQL con spiegazioni dettagliate.

È comune utilizzare una clausola WHERE insieme all'operatore IS NOT NULL per eliminare le righe con valori NULL. Ma a volte questo può essere un modo inefficiente per ottenere un risultato. Ecco perché.

I join esterni ( LEFT, RIGHT o FULL JOIN ) possono essere considerati come un INNER JOIN (che restituisce le righe corrispondenti) più le righe non corrispondenti con le colonne riempite di NULL.

Un LEFT JOIN restituisce tutte le righe della tabella di sinistra della join con le righe corrispondenti della tabella di destra (o valori NULL se non c'è corrispondenza). Una RIGHT JOIN restituisce tutte le righe della tabella destra con le righe corrispondenti (o i valori NULL) della tabella sinistra. Una FULL JOIN è come una INNER JOIN che restituisce anche tutte le righe non corrispondenti delle tabelle destra e sinistra estese da NULL.

Se la clausola WHERE della query filtra le righe che sono state estese da NULL, in pratica si annulla la join esterna. Si dovrebbe riscrivere la query utilizzando una clausola INNER JOIN.

In altri casi, i NULL interagiscono con la clausola WHERE e causano risultati errati. Vediamo un esempio che lo dimostra. Potete eseguirlo voi stessi utilizzando questo SQL Fiddle(qui trovate una guida ad altri siti da utilizzare per esercitarvi con l'SQL).

Per questo esempio, utilizzeremo due tabelle, una per i valori customers e una per orders.

id	name	email
1	Alice	alice@gmail.com
2	Bob	bob@hmail.com
3	Cate	cate@imail.com

Tabella customers

id	order_date	cust_email	amount
1	2021-02-04	bob@hmail.com	50
2	2021-02-05	cate@imail.com	20
3	2021-02-06	cate@imail.com	40
4	2021-02-06	bob@hmail.com	15

Tabella orders

Vogliamo vedere l'importo speso da tutti i nostri clienti registrati dal 4 febbraio 2021 ("2021-02-04"). Iniziamo con una INNER JOIN. In questa query ci sono alcune funzioni SQL aggiuntive che potrebbero essere nuove per voi, ma LearnSQL vi copre. Abbiamo articoli su COALESCE, sulle funzioni aggregate come SUM e sulla clausola GROUP BY. Non è necessario preoccuparsi di cosa fanno queste funzioni in questo momento. Concentratevi su ciò che ogni query restituisce, a partire da questa:

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
INNER JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

La query produce questo risultato:

name	Total
Bob	15
Cate	60

Sembra buono, ma abbiamo 3 clienti. Se vogliamo vedere tutti i clienti, dobbiamo usare LEFT OUTER JOIN (in breve LEFT JOIN ). Questa includerà tutte le righe della tabella di sinistra dell'istruzione FROM, anche se non ci sono dati corrispondenti nella tabella di destra. Questo ci permette di ottenere la nostra prossima query:

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
LEFT JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

I risultati potrebbero sorprendervi:

name	Total
Bob	15
Cate	60

Perché succede questo? Perché Alice è ancora assente? Una query più semplice ci darà un indizio:

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
LEFT JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

Il risultato:

name	order_date
Bob	2021-02-04
Cate	2021-02-05
Cate	2021-02-06
Bob	2021-02-06
Alice	(null)

Come già detto, LEFT JOIN include tutte le righe della tabella di sinistra. Se non ci sono righe corrispondenti nella tabella di destra, le colonne vengono riempite con NULL.

La clausola WHERE esegue il filtraggio dopo JOIN, quindi tutte le righe per Alice saranno rimosse perché il confronto con NULL, ad esempio con un order_date inesistente, restituisce un'informazione sconosciuta. WHERE restituisce solo una riga in cui le condizioni sono valutate come VERE.

Il modo per risolvere questo problema è spostare l'espressione condizionale, qui o.order_date > '2021-02-04', all'interno di JOIN includendola nella clausola ON:

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
LEFT JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

Poiché stiamo utilizzando una clausola LEFT JOIN, Alice rimane nell'insieme dei risultati nonostante la condizione aggiuntiva della data. Il suo NULL viene trasformato in uno "0" più pulito dalla funzione COALESCE(). Il risultato della query è ora quello atteso:

name	Total
Alice	0
Bob	15
Cate	60

Per saperne di più sui NULL in SQL

Ora dovreste aver capito come SQL tratta i valori NULL e le migliori pratiche per lavorarci. Conoscete gli operatori IS NULL e IS NOT NULL e come la logica ternaria di SQL restituisca sempre unknown quando qualcosa viene confrontato con un NULL , tranne in due casi speciali. Avete anche visto come riscrivere le query in modo da non dover filtrare i fastidiosi NULL.

Ma c'è ancora molto da sapere su come lavorare con i NULL in SQL. Vi suggerisco di continuare a imparare con gli articoli Come ORDER BY e NULL lavorano insieme in SQL e Valori NULL e la clausola GROUP BY.

Se si desidera acquisire una vera padronanza di SQL, si consiglia di seguire la traccia SQL Practice Set o il percorsoSQL Practice . Offrono un'esperienza di apprendimento approfondita e facile da seguire che vi aiuterà a perfezionare il vostro mestiere.