Con la tecnologia in crescita e in evoluzione, è essenziale rimanere aggiornati con le ultime tendenze come sviluppatore. Che tu sia un principiante o un esperto, una solida conoscenza della manipolazione delle stringhe ti aiuta a preparare i dati (ad esempio, generando un formato diverso da quello esistente, rendendolo utilizzabile per la tua azienda) e gestirlo utilizzando le funzioni integrate del server SQL.
Oltre alla manipolazione dei dati, puoi esaminare i set di dati, valutare i valori dei dati e codificarli o decodificarli per ottenere dati più significativi. Di conseguenza, questo ti aiuta a navigare tra i valori mancanti nei set di dati, comprenderne l’impatto sui calcoli e semplificare il lavoro complessivo con il processo dei dati per evitare valori Null che potrebbero rovinare i risultati dell’operazione.
Questa guida ti guida attraverso la funzione coalesce in SQL, che aiuta a creare programmi complessi. Il post presuppone che tu abbia incontrato e lavorato con SQL e stia solo cercando di rafforzare la tua comprensione di questa particolare funzione. La nostra serie di guide SQL può aiutarti a iniziare rapidamente.
Sommario:
Che cos’è COALESCE () in SQL e i suoi usi?
La funzione coalesce in SQL valuta i parametri (argomenti) in un ordine specificato, come gli elenchi, e restituisce il primo valore non nullo. In poche parole, la funzione valuta l’elenco in sequenza e termina all’istanza del primo valore non nullo. Se tutti gli argomenti nell’elenco sono nulli, la funzione restituisce NULL.
Inoltre, la funzione è inclusiva e supportata in altri database come MYSQL, database SQL di Azure, Oracle e PostgreSQL.
Puoi usare Coalesce nei seguenti casi quando:
- Gestione dei valori NULL.
- Esecuzione di più query come una sola.
- Evitare dichiarazioni CASE lunghe e dispendiose in termini di tempo.
Quando viene utilizzato al posto delle istruzioni CASE (o della funzione ISNULL), coalesce accetta molti parametri, a differenza di CASE, che ne richiede solo due. Questo approccio consente di scrivere meno codice e semplifica il processo di scrittura.
Ecco la sintassi:
COALESCE(valueOne, valueTwo, valueThree, …, valueX);
Coalesce nel server SQL ha diverse proprietà, inclusi argomenti dello stesso tipo di dati, che accettano molti parametri e argomenti del tipo intero da collegare in cascata a una funzione yield per restituire un numero intero come output.
Leggi anche: Cheat Sheet SQL definitivo da aggiungere ai segnalibri per dopo
Ma prima di capire come usare la coalescenza, capiamo NULL.
Che cos’è un valore NULL in SQL?
Il marcatore univoco NULL in SQL indica l’inesistenza di un valore nel database. Puoi pensarlo come un valore indefinito o sconosciuto. Per favore, non cadere nella trappola di pensarla come una stringa vuota o un valore zero; è l’assenza di un valore. L’occorrenza nulla nelle colonne della tabella rappresenta le informazioni mancanti.
In un caso d’uso pratico, la colonna di dati in una colonna del database di un sito Web di e-commerce può essere riempita con un valore NULL se un cliente non riesce a fornire il proprio ID. Null in SQL è univoco; è uno stato, al contrario di altri linguaggi di programmazione in cui significa “non puntare a un particolare oggetto”.
I valori NULL in SQL hanno un impatto significativo sui database relazionali. Innanzitutto, consentono di escludere determinati valori mentre si lavora con altre funzioni interne. Ad esempio, puoi generare un elenco di ordini totali in un ambiente di produzione, ma altri devono ancora essere completati. L’utilizzo di NULL come segnaposto consente alla funzione SUM interna di sommare i totali.
Inoltre, considerare i casi in cui è necessario generare la media utilizzando la funzione AVG. Se lavori con valori zero, i risultati sono distorti. Al contrario, il database può rimuovere tali campi e utilizzare NULL, ottenendo output accurati.
I valori NULL non presentano svantaggi. Sono considerati valori di lunghezza variabile, essendo byte o più di essi. Poiché il database lascia spazio a questi byte se superano quanto memorizzato nel database, il risultato è che il database occupa più spazio sul disco rigido rispetto all’utilizzo di valori normali.
Inoltre, quando si lavora con alcune funzioni, sarà necessario personalizzarle per eliminare i valori NULL. Questo, di conseguenza, allunga le procedure SQL.
Gestione dei valori NULL con COALESCE ()
I valori nulli implicano che potresti avere un valore, ma non sei a conoscenza di quale dovrebbe essere il valore. Fino a quando non raccogli dati che riempiono i tuoi campi con valori reali, i valori NULL sono i procuratori.
Sebbene sia possibile utilizzare valori NULL per più tipi di dati nel database, inclusi decimali, stringhe, BLOB e numeri interi, è consigliabile evitarli quando si tratta di dati numerici.
Lo svantaggio è che quando viene utilizzato per valori numerici, probabilmente avrai bisogno di chiarimenti mentre sviluppi il codice che funziona con i dati. Ne parleremo più avanti.
I diversi modi in cui COALESCE() può essere utilizzato per gestire il valore NULL:
Utilizzo di COALESCE () per sostituire i valori nulli con un valore specifico
È possibile utilizzare COALESCE() per restituire valori specifici per tutti i valori null. Ad esempio, potresti avere una tabella chiamata “dipendenti” con una colonna “stipendio”, che può contenere valori nulli se lo stipendio dei dipendenti non è stato accreditato. Quindi, quando si eseguono alcuni calcoli, si potrebbe voler lavorare con un valore specifico, zero in questo caso, per tutte le voci NULL. Ecco come farlo.
SELECT COALESCE(salary, 0) AS adjusted_salary FROM employees;
Utilizzo di COALESCE () per selezionare il primo valore non nullo da più opzioni
A volte, potresti voler lavorare con i primi valori non NULL in un elenco di espressioni. In questi casi, spesso hai più colonne con dati correlati e vorresti dare la priorità ai loro valori non NULL. La sintassi rimane.
COALESCE (expression1, expression2, …)
In un caso pratico, supponiamo di avere una tabella contatti con le colonne nome_preferito e nome_completo. E vorresti generare un elenco di contatti fianco a fianco con i loro nomi preferiti (se disponibili) o i loro nomi completi. Ecco come affrontarlo.
SELECT COALESCE(preferred_name, full_name) AS display_name FROM contacts.
Se il preferred_name non è NULL per questo test case, verrà restituito. In caso contrario, il nome completo viene restituito come nome visualizzato.
Concatenazione di stringhe con SQL Coalesce
Potresti riscontrare problemi con SQL durante la concatenazione di stringhe se sono coinvolti valori null. In tali casi, NULL viene restituito come risultato indesiderato. Ora che NULL non è il risultato desiderato, puoi risolvere il problema utilizzando la funzione coalesce. Di seguito è riportato un esempio.
Una semplice concatenazione di stringhe viene eseguita da:
SELECT ‘Hello, where are you, ‘|| ‘John ’||? AS example
Il codice restituisce:
EsempioCiao, dove sei, John?
Tuttavia, se utilizzi un valore NULL, come mostrato di seguito:
SELECT ‘Hello, where are you, ‘ || null || ‘?’ AS example
L’uscita è ora.
Poiché ogni concatenazione di stringhe di testo che coinvolge un valore NULL restituisce NULL, il risultato precedente è NULL. Il problema è, tuttavia, risolto utilizzando il coalesce (). Usando questa funzione, si restituisce una stringa vuota (o uno spazio) invece di NULL. Ad esempio, supponi di elencare i nomi delle auto con i relativi produttori; ecco la tua domanda.
SELECT car || ‘, manufacturer: ‘ || COALESCE(manufacturer, ‘—') AS car_brand FROM stock
Se il produttore è NULL, avrai il ‘–’ invece di NULL. Ecco i risultati attesi.
car_brandoutlander, produttore: —flying speroni, produttore: Bentleyroyal atleta, produttore: —royal saloon, produttore: Crown
Come puoi vedere, i risultati NULL vengono eliminati, con l’opzione di inserire il tuo valore di stringa sostitutivo.
Funzione SQL Coalesce e Pivoting
Il pivoting SQL è una tecnica utilizzata per trasformare le righe in colonne. Consente di trasporre (ruotare) i dati dalla forma “normalizzata” (con molte righe e meno colonne) a quella “denormalizzata” (meno righe e più colonne). La funzione coalesce può essere utilizzata con il pivot SQL per gestire i valori null nei risultati sottoposti a pivot.
Quando esegui il PIVOT in SQL, trasforma le righe in colonne; le colonne risultanti sono funzioni aggregate di alcuni dati. Se, in ogni caso, un’aggregazione risulta nulla per una particolare cella, è possibile utilizzare `COALESCE` per sostituire i valori nulli con un valore predefinito o una rappresentazione significativa. Di seguito è riportato un esempio.
Considera una tabella, vendite, con le colonne anno, trimestre e entrate e vorresti scambiare i dati; in modo tale da avere anni come colonne e la somma delle entrate per ogni trimestre come valori. Tuttavia, alcuni trimestri non hanno dati sulle entrate, fornendo valori nulli nel risultato pivot. In questo caso, puoi utilizzare COALESCE per sostituire i valori nulli nel risultato sottoposto a pivot con uno zero (0).
SELECT
year,
COALESCE(SUM(CASE WHEN quarter="Q1" THEN revenue END), 0) AS Q1_Revenue,
COALESCE(SUM(CASE WHEN quarter="Q2" THEN revenue END), 0) AS Q2_Revenue,
COALESCE(SUM(CASE WHEN quarter="Q3" THEN revenue END), 0) AS Q3_Revenue,
COALESCE(SUM(CASE WHEN quarter="Q4" THEN revenue END), 0) AS Q4_Revenue
FROM sales
GROUP BY year;
Funzione scalare definita dall’utente e funzione SQL Coalesce
È possibile utilizzare UDF scalari e coalesce per eseguire una logica complessa che gestisce i valori null. La combinazione di queste funzionalità ti aiuterà a ottenere trasformazioni e calcoli di dati più sofisticati nelle query SQL. Si consideri una tabella, Dipendenti, con questa struttura.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary INT,
Bonus INT
);
Potresti voler calcolare i guadagni totali di ciascun dipendente (stipendio più bonus). Tuttavia, ci sono alcuni valori mancanti. In questo caso, la tua UDF scalare può gestire le aggiunte di stipendio e bonus, mentre coalesce gestisce i valori nulli. Ecco l’UDF scalare per i guadagni totali.
CREATE FUNCTION dbo.CalculateTotalEarnings (@salary INT, @bonus INT)
RETURNS INT
AS
BEGIN
DECLARE @totalEarnings INT;
SET @totalEarnings = @salary + COALESCE(@bonus, 0);
RETURN @totalEarnings;
END;
You can then use the scalar UDF with coalesce in a query:
SELECT EmployeeID, FirstName, LastName,
Salary, Bonus, dbo.CalculateTotalEarnings(Salary, Bonus) AS TotalEarnings
FROM Employees;
Convalida dei dati tramite SQL Coalesce
Quando si lavora con i database, potrebbe essere necessario convalidare i valori numerici. Ad esempio, supponiamo di avere le colonne nome_prodotto, prezzo e sconto in una tabella prodotti. Si desidera recuperare i nomi dei prodotti, i prezzi e gli sconti di ciascun articolo. Tuttavia, vorresti trattare tutti i valori di sconto NULL come 0. La funzione coalesce può essere utile. Ecco come usarlo.
SELECT product_name, price, COALESCE(discount, 0) AS discount FROM products
SQL Coalesce e colonne calcolate
Le colonne calcolate sono colonne virtuali calcolate in base a espressioni o altre colonne all’interno di una tabella. Poiché le colonne calcolate non sono archiviate fisicamente nel database, è possibile sfruttarle con la funzione di coalesce durante la gestione di scenari e trasformazioni complessi. Ecco un esempio pratico di caso d’uso.
Considera una tabella `prodotti` con le colonne `prezzo`, `sconto` e `tax_rate`. In questo caso, vuoi creare una colonna calcolata, `total_price`, per rappresentare il prezzo finale del prodotto dopo aver applicato lo sconto e le tasse. Se lo sconto o l’imposta non sono specificati (NULL), ti consigliamo di procedere con i tuoi calcoli utilizzando uno zero. Ecco come sfruttare la coalescenza per adattarsi all’operazione.
CREATE TABLE products( price DECIMAL(10, 2), discount DECIMAL(10, 2), tax_rate DECIMAL(5, 2), total_price AS (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1) );
Nel codice sopra, ecco cosa succede.
L’impostazione precedente consente di generare total_price, una colonna calcolata, con il prezzo finale effettivo, nonostante manchino o abbiano valori NULL.
SQL Coalesce ed espressione CASE
È possibile utilizzare sintatticamente coalesce tramite l’espressione CASE. Ecco un esempio:
SELECT Productname + ‘ ’+ deliverydate productdetails, dealer, CASE WHEN cellphone is NOT NULL Then cellphone WHEN workphone is NOT NULL Then workphone ELSE ‘NA’ END EmergencyContactNumber FROM dbo.tb_EmergencyContact
Nella configurazione di cui sopra, CASE interroga come la funzione COALESCE.
Inoltre, è possibile utilizzare le espressioni COALESCE e CASE nella stessa query. Le due tecniche possono gestire valori NULL e applicare simultaneamente la logica condizionale. Illustriamolo con un esempio.
Considera un caso in cui hai una tabella, prodotti con le colonne product_id, product_name, price e discount. Alcuni dei tuoi prodotti hanno uno sconto specifico mentre altri no. Se un prodotto ha uno sconto, vuoi mostrare il prezzo scontato, altrimenti dovrebbe essere visualizzato il normale.
SELECT
product_id,
product_name,
price,
COALESCE(
CASE
WHEN discount > 0 THEN price - (price * discount / 100)
ELSE NULL
END,
price
) AS discounted_price
FROM products;
Nel codice precedente, `CASE` controlla se lo `sconto` è maggiore di zero e calcola il prezzo scontato, altrimenti restituisce NULL. La funzione `COALESCE` accetta come parametri il risultato di `CASE` e `price`. Restituisce il primo valore diverso da NULL, restituendo effettivamente il prezzo scontato se disponibile o il prezzo normale se non ce n’è.
Parole finali
Questo post ha dimostrato vari modi per utilizzare la funzione `COALESCE` nelle query del database. Valutando i parametri in un ordine specificato e restituendo il primo valore diverso da NULL, la funzione coalesce semplifica le query rendendole efficienti.
Coalesce è una funzione versatile sia che tu stia gestendo valori null, concatenazione di stringhe, pivot di dati, convalida o lavorando con colonne calcolate. Padroneggiando la funzione di coalescenza, gli sviluppatori possono navigare tra i dati mancanti e creare progetti di database privi di errori. Ricorda, per padroneggiare la tecnica; potresti aver bisogno di una pratica più approfondita.
Ora puoi verificare come creare vincoli di chiave esterna in SQL.