Nullo (SQL)
Null (null) è un indicatore speciale utilizzato nel linguaggio di query strutturato ( SQL ) per indicare che un valore non esiste all'interno di un database. Introdotto dal creatore del modello di database relazionale EF Codd , la sua funzione è quella di risolvere il requisito che i veri sistemi di gestione di database relazionali (DBMS) possano rappresentare informazioni "sconosciute" o "non applicabili". Allo stesso modo, Codd ha anche introdotto l'uso della lettera greca minuscola omega (ω) per rappresentare il Null nella teoria dei database. NULL è anche una parola riservata nel linguaggio SQL per identificare lo speciale marcatore Null.
Null è stato fonte di controversia e fonte di dibattito a causa della sua associazione con la logica ternaria (in inglese: Three-Valued Logic , abbreviato 3VL), le sue restrizioni sul suo utilizzo in SQL e la difficoltà di gestirlo in SQL. Sebbene funzioni e predicati speciali servano a gestire efficacemente i Null, la concorrenza ritiene che la risoluzione di questo tipo di problemi aggiunga complessità e contraddizioni non necessarie all'interno del modello relazionale dei database.
Storia
Null è stato introdotto da EF Codd come metodo per rappresentare i dati mancanti nel modello relazionale. Codd in seguito ha rafforzato la sua richiesta affinché tutti gli RDBMS includano la funzione nulla per indicare i dati mancanti in un doppio articolo pubblicato sulla rivista ComputerWorld. Codd ha introdotto anche la logica ternaria, costituita dalla veridicità dei valori Vero (vero), Falso (falso) e Sconosciuto (Sconosciuto), che sono strettamente legati al concetto di Nullo. Il valore true Unknown viene generato quando Null viene confrontato con qualsiasi dato o con un altro Null.
Codd ha affermato nel suo libro "Il modello relazionale per la gestione dei database pubblicato nel 1990, versione 2" che un singolo comando Null tramite lo standard SQL era inadeguato e dovrebbe essere sostituito da due flag Null separati per indicare il motivo per cui mancano i dati. Questi due tipi di indicatori Null sono comunemente chiamati "Valori A" e "Valori I", che rappresentano rispettivamente "dati mancanti ma applicabili" e "dati mancanti ma non applicabili". La raccomandazione di Codd avrebbe dovuto richiedere un'espansione del sistema logico di SQL per accogliere il sistema logico quaternario. A causa della sua ulteriore complessità, l'idea di più valori nulli non ha ottenuto un riconoscimento generale.
Logica ternaria (Logica a tre valori -3VL-)
Poiché non appartiene ad alcun dominio di dati, Null non è propriamente considerato un "valore", ma in realtà è un indicatore che indica l'assenza di un valore. Per questo motivo, i confronti Null non restituiscono mai true o false, ma come risultato logico ternario, sconosciuto. Il risultato logico della seguente espressione, che confronta il valore 10 con Null, è Sconosciuto:
10 = NULL -- Risultati in Sconosciuto (Sconosciuto)
Tuttavia, alcune operazioni Null possono restituire valori se il valore Null non è rilevante per il risultato dell'operazione. Questo è il caso dell'esempio seguente, considerando che l'istruzione OR viene valutata sotto forma di un breve giro:
VERO O NULLO : risulta vero
In questo caso, il fatto che il valore a destra di OR sia sconosciuto è irrilevante, perché il risultato dell'operazione dovrebbe essere True indipendentemente dal valore a destra. SQL applica i risultati logici ternari, quindi le applicazioni SQL devono fornire valori logici ternari specializzati. Le regole che regolano i valori logici ternari in SQL sono mostrate nelle seguenti tabelle (p e q rappresentano stati logici):
|
|
|
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
I confronti operativi di base in SQL risultano in Sconosciuto confrontando qualsiasi cosa con Null, quindi lo standard SQL prevede calcoli di predicati collazionati speciali per le due funzioni specifiche di Null.
Il test IS NULL e IS NOT NULL determina se i dati sono o meno Null.
Tipi di dati
Null non è inteso come numero intero, carattere o altro tipo di dati specifico. Per questo motivo, a volte è consigliabile eseguire il cast di Null in modo esplicito su un tipo di dati specifico. Ad esempio, se le funzioni sovraccaricate sono supportate dal DBMS (Database Management System), SQL potrebbe non essere in grado di decidere la funzione corretta senza conoscere i tipi di dati di tutti i parametri, compresi quelli per i quali ha agito Null.
Linguaggio di manipolazione dei dati
I valori logici ternari SQL si trovano nel Data Manipulation Language (DML) nei confronti dei predicati di asserzioni DML e FAQ. L'istruzione WHERE fa sì che l'istruzione DML agisca solo su quelle righe per le quali il calcolo dei predicati restituisce True. Le righe per cui questi calcoli restituiscono False o Unknown non derivano da istruzioni DML come INSERT, UPDATE o DELETE e vengono eliminate dalle domande SELECT. Interpretare Sconosciuto e Falso come lo stesso risultato logico è un errore comune che si incontra quando si ha a che fare con i Null. L'esempio seguente lo dimostra:
SELEZIONA *
DA t
DOVE i = NULL ;
L'esempio sopra restituisce sempre logicamente zeri per le righe perché il confronto della colonna i con Null restituisce sempre un valore Unknown, anche per quelle righe in cui i è Null. Il risultato Unknown fa sì che l'istruzione SELECT elimini ogni riga (tuttavia, in pratica, gli strumenti SQL recupereranno le righe usando il confronto Null).
Espressioni CASE
Le espressioni SQL CASE funzionano secondo le stesse regole del DMS WHERE for Null. Poiché può essere valutata come una serie di condizioni di confronto di uguaglianza, una semplice espressione CASE non può verificare direttamente Null. Un controllo per l'esistenza di Null in una semplice espressione CASE restituisce sempre Sconosciuto, come segue:
SELEZIONA CASO i QUANDO NULLO ALLORA 'Is Null' -- Questo non verrà mai restituito.
QUANDO 0 ALLORA 'È Zero' -- Verrà restituito quando i = 0
QUANDO 1 ALLORA 'È uno' -- Verrà restituito quando i = 1
FINE
DA t ;
Poiché l'espressione i = NULL restituisce Sconosciuto indipendentemente dal valore contenuto nella colonna i (anche se contiene Null), il risultato ' IS NULL ' non verrà mai restituito. Un'espressione CASE cercata restituisce anche il primo valore per il quale il risultato del confronto dei predicati restituisce True, inclusi i confronti dei predicati che utilizzano "IS NULL" AND "IS NOT NULL" . L'esempio seguente mostra come utilizzare un'espressione CASE cercata per verificare la presenza di Null:
SELECT CASE WHEN i IS NULL THEN 'Is Null' -- Verrà restituito quando i è NULL
WHEN i = 0 THEN 'Zero' -- Questo restituirà i = 0
WHEN i = 1 THEN 'One' -- Questo ritornerà i = 1
FINE
DA t ;
Nell'espressione CASE cercata, viene restituito 'Result Null' per tutte le righe in cui i è Null.
Revisione delle limitazioni
Il luogo principale in cui la logica ternaria SQL si interseca con SQL Data Definition Language (DDL) è sotto forma di controllo dei vincoli. Un controllo di revisione o vincolo posizionato su una colonna opera in base a un insieme di regole leggermente diverso da quelle per la clausola DML WHERE. Mentre una clausola DML WHERE deve restituire True per una riga, un controllo non deve restituire False. Ciò significa che un controllo avrà esito positivo se il risultato del controllo è Vero o Sconosciuto. L'esempio seguente mostra una tabella con un controllo che impedirà l'inserimento di qualsiasi valore intero nella colonna i, ma consentirà l'inserimento di Null poiché il risultato del controllo valuterà sempre Sconosciuto per Null.
CREATE TABLE t (
i INTEGER ,
CONSTRAINT ck_i CHECK ( i < 0 AND i = 0 AND i > 0 ) );
Affinché una colonna rifiuti i valori Null, è possibile applicare il vincolo NOT NULL, come mostrato nell'esempio seguente. Il vincolo NOT NULL è semanticamente equivalente a un controllo del vincolo con un predicato IS NOT NULL.
CREATE TABLE t ( i INTEGER NOT NULL );
Estensioni della procedura
SQL/PSM (SQL Stored Persistent Modules) definisce le estensioni procedurali a SQL, come l'istruzione IF. Tuttavia, le principali versioni di SQL hanno storicamente incluso le proprie estensioni procedurali. Le estensioni procedurali per il ciclo e il confronto funzionano con regole di confronto Null, in modo simile alle istruzioni e alle query DML. Il frammento di codice seguente, in formato ISO SQL standard, illustra l'uso di Null 3VL in un'istruzione IF.
SE i = NULL ALLORA
SELEZIONA 'Il risultato è vero'
ALTRO SE NON ( i = NULL ) ALLORA
SELEZIONA 'Il risultato è falso'
ALTRO
SELEZIONA 'Il risultato è sconosciuto' ;
L'istruzione IF esegue azioni solo per quei confronti che restituiscono True. Per le istruzioni che restituiscono False o Unknown, l'istruzione IF passa il controllo alla clausola ELSEIF e infine alla clausola ELSE. Il risultato di questo codice sarà sempre il messaggio "Risultato sconosciuto" poiché i confronti Null restituiscono sempre Sconosciuto.
JOIN dichiarazioni
L' istruzione JOIN in SQL consente la combinazione di record da due o più tabelle all'interno dello stesso database relazionale. Esistono tre tipi di JOIN in SQL: interno, esterno e incrociato. I join esterni, comprese le istruzioni di tabella di sinistra, tabella di destra e full join, producono automaticamente Null come segnaposto per i valori mancanti nelle tabelle correlate. Per i join esterni della tabella sinistra, ad esempio, i valori Null vengono prodotti al posto delle righe della tabella mancanti che appaiono sul lato destro dell'operatore JOIN esterno della tabella sinistra. Nell'esempio seguente vengono utilizzate due tabelle per dimostrare la produzione dell'indicatore Null nel join esterno sinistro. La prima tabella (Dipendente) contiene i numeri e i nomi identificativi del dipendente, mentre la seconda tabella (Numero di telefono) contiene i numeri di telefono relativi all'ID dipendente, come mostrato di seguito.
|
|
L'esempio SQL seguente esegue un outer join di una tabella sinistra tra le due tabelle precedenti.
SELEZIONA e . ID , es . Cognome , es . Nome , p . Numero
DA Dipendente e
SINISTRA OUTER JOIN Numero di telefono pn ON e ._ id = pn . id ;
L'output generato in questa serie di domande mostra come SQL utilizza Null come segnaposto per i valori mancanti sul lato destro della tabella (Numeri di telefono), come mostrato di seguito:
| ID | Cognome | Nome | Numero |
|---|---|---|---|
| 1 | Sono partiti | Roberto | 555-2323 |
| Due | ambrogio | alberto | NULLO |
| 3 | Alfonso | Katia | 555-9876 |
| 4 | Barbadillo | William | NULLO |
Inner Join e Cross Join sono disponibili anche nello standard SQL e non generano flag Null per i valori mancanti nelle tabelle correlate.
È necessario prestare attenzione quando si utilizzano colonne nullable nei criteri di join SQL. Poiché un Null non è uguale a nessun altro Null, i Null trovati in una colonna di una tabella non verranno uniti ai Null nelle colonne correlate di un'altra tabella che utilizza gli operatori di confronto di uguaglianza standard. La funzione SQL COALESCE o le espressioni CASE possono essere utilizzate per "simulare" l'uguaglianza Null nei criteri di join e anche i predicati "IS NULL" e "IS NOT NULL" possono essere utilizzati nei criteri di join.
Il seguente predicato verifica l'uguaglianza dei valori A e B e considera Null come uguale. L'operatore IFNULL è obbligatorio poiché A = B restituisce un valore Null se almeno uno dei valori (A o B) è Null e NULL OR FALSE è esso stesso Null.
IFNULL ( A = B , FALSE ) OR ( A È NULL E B È NULL )
Concatenazione matematica e caratteri
Poiché Null non è un valore di dati, ma un indicatore di un valore sconosciuto, l'utilizzo di operatori matematici su Null produce un valore sconosciuto, rappresentato da Null. Nell'esempio seguente, moltiplicando 10 per Null risulta Null:
10 * NULL -- Il risultato è NULL
Questo può portare a risultati imprevisti. Ad esempio, quando si tenta di dividere Null per zero, le piattaforme possono restituire Null invece di generare un'"eccezione dati - divisione per zero" prevista. Sebbene questo comportamento non sia definito dallo standard SQL ISO, molti prodotti DBMS commerciali trattano questa operazione in modo simile. Ad esempio, le piattaforme Oracle, PostgreSQL, MySQL Server e Microsoft SQL Server restituiscono Null per l'operazione:
NULLA / 0
Anche le serie di operazioni concatenate, comuni in SQL, restituiscono Null quando uno degli operandi è Null. Nell'esempio seguente viene illustrato il risultato Null restituito usando Null con l'operatore SQL su una stringa concatenata.
'omelette' || NULLA || ' Patate ' - Il risultato è NULL
Questo non è vero per tutte le implementazioni di database. In Oracle RDBMS, ad esempio, NULL e una stringa vuota sono considerati la stessa cosa e quindi 'Tortilla' il NULL 'Potato' risulta in 'Tortilla Potato'.
Funzioni aggiunte
SQL definisce le funzioni aggregate per semplificare i calcoli aggregati dei dati del server. Quasi tutte le funzioni aggregate eseguono un passaggio di eliminazione Null, in modo che i valori Null non siano inclusi nel risultato finale del calcolo. Questa rimozione implicita di Null, tuttavia, può influenzare i risultati delle funzioni aggregate. La tabella esemplificata di seguito deriva risultati diversi restituiti a ciascuna colonna quando viene applicata la funzione di aggregazione SQL AVG (media):
| Yo | j |
|---|---|
| 150 | 150 |
| 200 | 200 |
| 350 | 350 |
| NULLO | 0 |
La funzione di aggregazione SQL AVG restituisce 233 se applicata alla colonna i, ma restituisce 175 se applicata alla colonna j. La fase di rimozione della funzione di aggregazione Null spiega la differenza in questi risultati. L'unica funzione di aggregazione che non rimuove implicitamente il Null è la funzione COUNT.
Raggruppamento e classificazione
Poiché SQL:2003 definisce tutti i contrassegni Null come disuguali tra loro, è necessaria una definizione speciale per raggruppare insieme i Null per eseguire determinate operazioni. SQL definisce "qualsiasi due valori uguali tra loro, o qualsiasi coppia di Null", come "non distinti". Questa definizione di non distinto consente a SQL di raggruppare e ordinare i valori Null quando viene utilizzata la clausola GROUP BY (e altre parole chiave che eseguono il raggruppamento).
Altre operazioni SQL, clausole e parole chiave utilizzano "non distinti" nel trattamento dei valori Null. Questi includono quanto segue:
- La clausola di classificazione PARTITION BY e le funzioni di windowing come ROW_NUMBER.
- UNION, INTERSECT e l'operatore EXCEPT, che trattano i NULL allo stesso modo per le determinazioni di confronto/eliminazione a righe.
- La parola chiave DISTINCT viene utilizzata nelle domande SELECT.
- Vincoli univoci sulle colonne in cui è possibile trovare la funzione Null, che consentono di archiviare nella colonna un solo valore Null.
Lo standard SQL non definisce in modo esplicito la classe di ordinamento predefinita per i valori Null. Invece, sui sistemi conformi, i Null possono essere ordinati prima o dopo tutti i valori dei dati utilizzando rispettivamente le clausole NULLS FIRST o NULLS LAST dell'elenco ORDER BY. Tuttavia, non tutti i fornitori di DBMS implementano questa funzionalità. I fornitori che non implementano questa funzionalità possono specificare un trattamento diverso per la classificazione Null nel DBMS.
Funzioni di gestione nulle
SQL definisce due funzioni per gestire in modo esplicito Null: COALESCE e NULLIF. Entrambe queste funzioni sono un'abbreviazione per le espressioni CASE ricercate.
COALESCE
La funzione COALESCE accetta un elenco di parametri, restituendo il primo valore non nullo dell'elenco:
COALESCE ( valore1 , valore2 , valore3 , ...)
COALESCEè definito come la seguente espressione SQL CASE:
CASE QUANDO il valore1 NON È NULL ALLORA il valore1 QUANDO il valore2 NON È NULL ALLORA il valore2 QUANDO il valore3 NON È NULL ALLORA il valore3 ... FINE
Alcuni DBMS SQL implementano funzioni specifiche del fornitore simili per COALESCE. Alcuni sistemi implementano una funzione ISNULL o altre funzioni simili funzionalmente simili a COALESCE.
NULLIF
La funzione NULLIF accetta due parametri. Se il primo parametro è uguale al secondo, NULLIF restituisce Null. In caso contrario, viene restituito il valore del primo parametro.
NULLIF ( valore1 , valore2 )
Pertanto, NULLIF è un'abbreviazione per la seguente espressione CASE:
CASE WHEN valore1 = valore2 THEN NULL ELSE valore1 END
Vedi anche
- SQL
- SQL:2003
- Linguaggio di manipolazione dei dati
- Le 12 regole di Codd
- Sistema di gestione delle banche dati
Collegamenti esterni
- Oracle NULL archiviati 2013-04-12 su Wayback Machine .
- Riferimento null Transact-SQL
- Trattamento dei valori NULL nelle istruzioni SQL ( collegamento interrotto disponibile su Internet Archive ; vedi cronologia , prima e ultima versione ).
