Forem: Nicola Iantomasi

T-SQL avanzato: tecniche da ricordare

Nicola Iantomasi — Sat, 21 Dec 2024 09:26:50 +0000

In SQL Server, padroneggiare le tecniche avanzate di T-SQL è fondamentale per sviluppare applicazioni database efficienti e manutenibili. In questo articolo approfondito, esploreremo alcune delle funzionalità più potenti di T-SQL, con esempi pratici e spiegazioni dettagliate. Per approfondimenti e altri articoli tecnici su SQL Server, vi invito a visitare il mio sito su SQL Server www.nicolaiantomasi.com.

Gestione Avanzata dei NULL nelle JOIN

Uno degli aspetti più sottovalutati di T-SQL è la corretta gestione dei NULL nelle operazioni di JOIN. Consideriamo un esempio pratico:

CREATE TABLE dbo.Magazzino(
    Codice1 VARCHAR(5),
    Codice2 VARCHAR(5),
    Nome VARCHAR(50)
);

CREATE TABLE dbo.Prezzario(
    Codice1 VARCHAR(5),
    Codice2 VARCHAR(5),
    Prezzo DECIMAL(18,4)
);

-- Dati di esempio con NULL intenzionali
INSERT INTO dbo.Magazzino VALUES 
    ('A1', 'B1', 'Prodotto1'),
    ('A1', 'B2', 'Prodotto2'),
    ('A2', NULL, 'Prodotto3');

INSERT INTO dbo.Prezzario VALUES 
    ('A1', 'B1', 3.24),
    ('A2', NULL, 1.4);`

Una comune svista è pensare che questa query funzioni correttamente:

SELECT * 
FROM dbo.Magazzino m
INNER JOIN dbo.Prezzario p 
    ON m.Codice1 = p.Codice1 
    AND m.Codice2 = p.Codice2;

Il problema? In SQL, NULL = NULL restituisce NULL, non TRUE. La soluzione corretta è:

SELECT * 
FROM dbo.Magazzino m
INNER JOIN dbo.Prezzario p 
    ON m.Codice1 = p.Codice1 
    AND (m.Codice2 = p.Codice2 OR 
        (m.Codice2 IS NULL AND p.Codice2 IS NULL));

Questa query gestisce correttamente i casi in cui Codice2 è NULL in entrambe le tabelle, un requisito comune in applicazioni reali.

Window Functions: Oltre il GROUP BY

Le Window Functions sono uno degli strumenti più potenti di T-SQL moderno. Permettono calcoli sofisticati mantenendo il dettaglio dei dati originali. Vediamo alcuni esempi pratici:
Calcolo di Totali Progressivi

WITH Vendite AS (
    SELECT * FROM (VALUES
        ('2024-01-01', 1000),
        ('2024-01-02', 1500),
        ('2024-01-03', 800)
    ) v(Data, Importo)
)
SELECT 
    Data,
    Importo,
    SUM(Importo) OVER (
        ORDER BY Data 
    ) AS TotaleProgressivo,
    SUM(Importo) OVER () AS TotaleComplessivo,
    Importo * 100.0 / SUM(Importo) OVER () AS PercentualeSuTotale
FROM Vendite;

Questa query mostra diverse funzionalità:

Calcolo del totale progressivo giorno per giorno
Calcolo del totale complessivo per confronto
Calcolo della percentuale sul totale

La clausola OVER è fondamentale: definisce la "finestra" su cui operare. ROWS BETWEEN specifica l'intervallo di righe da considerare.
Analisi Trend con LAG e LEAD

SELECT 
    Data,
    Importo,
    LAG(Importo) OVER (ORDER BY Data) AS ImportoPrecedente,
    LEAD(Importo) OVER (ORDER BY Data) AS ImportoSuccessivo,
    Importo - LAG(Importo) OVER (ORDER BY Data) AS Differenza,
    CASE 
        WHEN LAG(Importo) OVER (ORDER BY Data) IS NULL THEN NULL
        ELSE (Importo - LAG(Importo) OVER (ORDER BY Data)) * 100.0 / 
             LAG(Importo) OVER (ORDER BY Data)
    END AS VariazionePercentuale
FROM Vendite;

Questo codice illustra diverse funzionalità per l'analisi dei dati in sequenza temporale. In particolare, dimostra come la funzione LAG ci permetta di accedere ai valori delle righe precedenti nella sequenza, mentre LEAD ci consente di vedere i valori delle righe successive. Grazie a queste funzioni, possiamo facilmente calcolare sia le variazioni assolute che quelle percentuali tra diversi punti temporali dei nostri dati.

Ranking e Partitioning

WITH VenditeClienti AS (
    SELECT * FROM (VALUES
        ('A', '2024-01-01', 1000),
        ('A', '2024-01-02', 1500),
        ('B', '2024-01-01', 800),
        ('B', '2024-01-02', 800)
    ) v(Cliente, Data, Importo)
)
SELECT 
    Cliente,
    Data,
    Importo,
    ROW_NUMBER() OVER (PARTITION BY Cliente ORDER BY Importo DESC) AS RigaNum,
    RANK() OVER (PARTITION BY Cliente ORDER BY Importo DESC) AS Rank,
    DENSE_RANK() OVER (PARTITION BY Cliente ORDER BY Importo DESC) AS DenseRank
FROM VenditeClienti;

Questo esempio mostra diversi modi di numerare e classificare i dati all'interno di partizioni. Innanzitutto, utilizzando PARTITION BY possiamo suddividere i nostri dati in sezioni separate per ogni cliente. Per quanto riguarda la numerazione, ROW_NUMBER ci fornisce una sequenza di numeri senza duplicati, anche se dobbiamo fare attenzione poiché in caso di valori identici l'ordine potrebbe non essere deterministico. Quando abbiamo bisogno di gestire i pari merito, possiamo utilizzare RANK, che lascerà dei "vuoti" nella numerazione in questi casi, oppure DENSE_RANK che invece mantiene una sequenza continua senza interruzioni.

Pivot Dinamiche con SQL Dinamico

Un uso avanzato di T-SQL è la creazione di pivot dinamiche. Invece di codificare staticamente le colonne:

DECLARE @cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME(Categoria), ',')
FROM (SELECT DISTINCT Categoria FROM Vendite) AS cats;

SET @sql = N'
SELECT *
FROM (
    SELECT Anno, Categoria, Importo
    FROM Vendite
) p
PIVOT (
    SUM(Importo) 
    FOR Categoria IN (' + @cols + ')
) AS pvt;';

EXEC sp_executesql @sql;

Punti chiave:

QUOTENAME protegge da SQL injection
STRING_AGG concatena i valori (sostituisce il vecchio XML PATH)
sp_executesql esegue SQL dinamico in modo sicuro

Il T-SQL avanzato richiede una comprensione profonda di come SQL Server elabora i dati. L'uso corretto di queste tecniche può migliorare significativamente sia la qualità che la manutenibilità del codice.

SQL Server: Usare l'Istruzione LIKE all'Interno di una WHERE

Nicola Iantomasi — Sun, 27 Oct 2024 15:26:54 +0000

Il linguaggio SQL offre potenti strumenti per filtrare i dati, e tra questi, la combinazione della clausola WHERE *con l'istruzione **LIKE * si rivela particolarmente versatile per ricerche su colonne contenenti stringhe. Questo articolo esplora l'utilizzo di LIKE in SQL, con un focus particolare sulla sintassi T-SQL del database SQL Server, mostrando come filtrare dati basandosi **sull'inizio, il contenuto o la fine di una stringa.

Utilizzo di LIKE all'Interno di una Condizione WHERE

Esaminiamo tre scenari comuni: come estrarre prodotti il cui codice inizia, contiene, o termina con "XY". Le rispettive condizioni nella WHERE saranno:

WHERE CodiceProdotto LIKE 'XY%'
WHERE CodiceProdotto LIKE '%XY%'
WHERE CodiceProdotto LIKE '%XY'

Il carattere % funge da jolly, rappresentando qualsiasi numero di caratteri (incluso zero). È importante notare che la seconda condizione WHERE CodiceProdotto LIKE '%XY%' cattura anche codici che iniziano o terminano con "XY".
Per default, le ricerche su SQL Server non sono case-sensitive, a meno che non si utilizzi la clausola COLLATE.
Per ricerche più sofisticate, SQL Server offre ulteriori operatori:

WHERE CodiceProdotto LIKE '_XY%' trova codici con "XY" in seconda e terza posizione.
WHERE CodiceProdotto LIKE '[^A]%' trova codici che non iniziano con "A".
WHERE CodiceProdotto LIKE '[AB]%[12]' trova codici che iniziano con "A" o "B" e terminano con "1" o "2".

Impatto sulle Performance dell'Utilizzo di LIKE in una WHERE

Mentre è generalmente consigliato usare LIKE con moderazione per questioni di performance, esiste un caso d'uso frequente dove LIKE è la scelta ottimale: quando si cercano righe dove una colonna inizia con una specifica sequenza di caratteri.
Confrontiamo queste due query supponendo che sia presente un indice nonclustered sulla colonna CodiceProdotto:

SELECT COUNT(*) FROM dbo.Prodotti WHERE LEFT(CodiceProdotto,2) = 'XY';

SELECT COUNT(*) FROM dbo.Prodotti WHERE CodiceProdotto LIKE 'XY%';

Entrambe estraggono prodotti il cui codice inizia con "XY", ma la seconda query è significativamente più performante. Utilizzando W*HERE CodiceProdotto LIKE 'XY%', il piano di esecuzione effettua una **ricerca ottimizzata* sull'indice (index seek), anziché una semplice scansione (index scan) come nella prima query.

È cruciale notare che questo vantaggio non si applica a tutti gli usi di LIKE. Ad esempio, WHERE CodiceProdotto LIKE '%XY' risulterà in una scansione meno efficiente.

Per approfondire l'analisi dei piani di esecuzione e comprendere meglio come SQL Server ottimizza le query, vi consiglio di dare un'occhiata al programma del mio corso su SQL Server.

Considerazioni Aggiuntive sull'Uso di LIKE

Indici Full-Text: Per ricerche complesse su grandi volumi di testo, considerare l'uso di indici full-text, che possono offrire prestazioni superiori rispetto a LIKE per pattern di ricerca complessi.
Escape di Caratteri Speciali: Quando si cerca un carattere che è anche un carattere speciale in LIKE (come % o _), usare la clausola ESCAPE. Per esempio: WHERE CodiceProdotto LIKE '%10\%%' ESCAPE '\' cerca codici che contengono esattamente "10%".
LIKE con Variabili: Quando si usa LIKE con variabili, fare attenzione alla concatenazione di stringhe per evitare SQL injection. Preferire l'uso di parametri quando possibile.

L'uso efficace di LIKE in combinazione con WHERE può significativamente migliorare la capacità di query e filtro dei dati in SQL Server. Tuttavia, è sempre importante bilanciare la flessibilità offerta da LIKE con le considerazioni sulle performance, specialmente quando si lavora con grandi volumi di dati.

Cosa sono e come gestire le transazioni su SQL Server

Nicola Iantomasi — Tue, 07 Nov 2023 11:53:54 +0000

Gestire correttamente gli errori durante l'esecuzione delle transazioni su SQL Server è vitale per mantenere l'integrità dei dati e assicurare il funzionamento corretto delle operazioni.

Facciamo prima però un passo indietro e capiamo perché in alcuni casi è fondamentale usare le transazioni.

Le transazioni

Per spiegare le transazioni prendiamo ad esempio il database di una banca in cui ogni giorno dobbiamo eseguire per migliaia di volte queste due operazioni: aggiungere una riga nella tabella dei movimenti dei conti correnti e aggiornare il valore della colonna saldo corrispondente.

Cosa succederebbe se l'aggiunta del movimento andasse a buon fine, mentre l'aggiornamento del saldo per un qualsiasi motivo (anche per un banale timeout o errore di rete) genera un errore?

Sicuramente avremo molti clienti scontenti! In altri termini il nostro database si troverebbe in uno stato inconsistente e con importanti problemi di Data Quality.

Un altro esempio: dobbiamo cancellare le righe da una tabella con una certa data di riferimento per inserirne subito dopo delle nuove con la medesima data.

Cosa succede se, una volta confermata la cancellazione, l'inserimento dovesse fallire? Anche in questo caso avremmo una perdita dei dati, o comunque una situazione sicuramente meno consistente rispetto a quella di partenza.

Questi sono solo alcuni esempi per cui risulta fondamentale avere sui Database un meccanismo per gestire le transazioni.

L'obiettivo è eseguire alcuni specifici insiemi di operazioni di modifica dei dati in modo atomico, in modo da confermare tutti gli aggiornamenti contemporaneamente oppure annullarli se anche uno sola di essi non va a buon fine.

Su un Database SQL Server, se lavoriamo con le impostazioni di Default, dobbiamo esplicitare l'operazione di apertura di una transazione con l'istruzione

BEGIN TRAN

per poi decidere di confermare o meno le modifiche tramite una Commit o una Rollback. Tuttavia il percorso non è sempre così lineare perché, come vedremo nel prossimo paragrafo, alcuni errori potrebbero chiudere automaticamente la transazione. Inoltre il nostro obiettivo è salvare il codice all'interno di procedure automatiche, che riescano a gestire in autonomia il rollback in caso di errori, senza che qualcuno stia a lanciare l'istruzione manualmente.

Impatto degli errori sulle transazioni di SQL Server

Su SQL Server un errore di divisione per zero ha un impatto diverso sulla transazione rispetto a un errore di conversione. Dopo aver avviato esplicitamente la transazione con BEGIN TRAN, un errore di divisione per zero lascia la transazione aperta, mentre un errore di conversione causa il rollback automatico della transazione.

Questo è solo un esempio, altri errori potrebbero provocare comportamenti diversi. Invece di enumerare tutti i casi possibili, studiamo come arricchire il nostro codice con il costrutto TRY-CATCH, in modo da gestire in automatico la maggior parte dei possibili errori.

Try Catch in SQL

Il costrutto Try - Catch può essere usato in generale su SQL Server per gestire gli errori. Ad esempio l'istruzione

BEGIN TRY
   SELECT 1/0;
 END TRY
 BEGIN CATCH
   SELECT 2; 
 END CATCH

seleziona il valore 2. L'utilizzo di Try-Catch diventa davvero interessante se utilizzato per gestire in automatico gli errori durante una transazione. Analizziamo ad esempio questo codice:

BEGIN TRY

 /* calcoli che non effettuano aggiornamenti dei dati */

 BEGIN TRAN

  DELETE FROM Tabella 
  WHERE DataRiferimento = @Datarif

  INSERT INTO Tabella (colonna1, colonna2)
  SELEC colonna1, colonna2 
  FROM  Staging_tabella

 COMMIT;
END TRY
BEGIN CATCH
 IF @@TRANCOUNT > 0 ROLLBACK;

 /* gestione errore */

 THROW;
END CATCH

Prima di entrare nei dettagli del try-catch vi ricordo che specificare l'elenco delle colonne dopo una Insert come fatto in questo esempio rientra nelle buone pratiche di uno sviluppatore SQL.

Analizziamo il codice precedente: la presenza del blocco BEGIN TRY sposta immediatamente il flusso al blocco CATCH in caso di errori. In questo modo la Commit, essendo l'ultima istruzione del TRY, verrà eseguita solo quando sia la Delete e la Insert andranno a buon fine. Vi ricordo che è importante fare in modo che il codice dentro il blocco BEGIN TRAN sia eseguito nel modo più veloce possibile per evitare l'insorgere di Deadlock. A tal proposito ho dedicato un video sul mio canale Youtube

Tornando al nostro codice, nel blocco Catch viene eseguito subito un rollback nel caso ci sia qualche transazione aperta (l'IF serve perché l'errore si potrebbe generare anche nella parte di calcoli tra l'apertura del blocco Try e l'apertura del blocco Tran). Poi viene gestito l'errore, ad esempio andando a scrivere in una tabella di Log o inviando una mail, e infine possiamo (in base alla situazione) sollevare o meno nuovamente l'errore che ha portato il codice a passare nel blocco Catch.

Concludiamo l'articolo menzionando che non tutti gli errori possono essere gestiti dal costrutto TRY-CATCH (come ad esempio alcuni errori di sintassi o timeout delle query quando lanciate da un client). In alcuni di questi casi potremmo prendere in considerazione l'utilizzo dell'istruzione SET XACT_ABORT

Indici statistici: descrizione e implementazione in pandas

Nicola Iantomasi — Wed, 01 Nov 2023 18:45:43 +0000

Nel campo della statistica, gli indici sono valori numerici fondamentali che forniscono un riassunto efficiente di grandi quantità di dati. Questo articolo esplorerà due categorie chiave di indici, quelli univariati e multivariati, parlando anche della relativa implementazione con la libreria Pandas di Python.

Indici di statistica univariati con Pandas

Gli indici univariati consentono un'analisi approfondita di una singola variabile all'interno di un insieme di dati. Partiamo allora creando un _DataFrame _con una sola colonna _Voti _

import pandas as pd
Voti = pd.DataFrame(data=[25,23,28,25,24,29,25],columns = ["Voto"] )
Voti

Tramite il metodo describe otteniamo con una sola riga di codice il valore dei principali indici statistici di posizione e di variabilità. Facciamo attenzione però al valori di std che corrisponde alla stima della deviazione standard della popolazione da cui è estratto il campione (al denominatore della formula ho n-1).

Voti.describe()

Per calcolare gli indici di forma possiamo creare prima una colonna con i valori standardizzati e poi applicare le formule degli indici di Fisher e di curtosi.

Voti["Voto_StandardScaler"] = (Voti["Voto"]-media)/deviazione_standard
AsimmetriaFisher = Voti["Voto_StandardScaler"].pow(3).mean()
Curtosi = Voti["Voto_StandardScaler"].pow(4).mean() - 3

Indici di statistica bivariati con Pandas

Gli indici multivariati sono utilizzati per descrivere le relazioni tra due o più variabili in un insieme di dati. In base alla classificazione delle variabili statistiche è possibile calcolare l'associazione per tutte le combinazioni possibili:

due variabili qualitative: si utilizzano comunemente l'indice Chi quadrato e la sua versione normalizzata, la *V di Cramer *
una variabile qualitativa e una quantitativa per i quale usiamo generalmente l'indice eta quadro
due variabili quantitative di cui il coefficiente di correlazione di Pearson e quello di Spearman sono due esempi

Nel prossimo paragrafo ci concentreremo sugli indici relativi alle variabili qualitative

Indice Chi Quadrato e indice V di Cramer con Pandas

Vediamo il codice per calcolare gli indici Chi Quadrato e V di Cramer con Pandas. Partiamo da un Dataframe con due colonne che contengono dati di tipo qualitativo, contenenti ad esempio il colore degli occhi e l'area geografica, di nome DatiCramer.

Partiamo calcolando le frequenze osservate
FreqOsservate = DatiCramer.groupby(by = ["Occhi","Area geografica"], dropna = False, as_index = False).agg(conteggio = ("Occhi", np.size))

Eseguiamo il pivot dei dati

FreqOsservatePivot = FreqOsservate.pivot(columns = "Area geografica",
index = "Occhi", ).fillna(0)

Creiamo la tabella di contingenza con l'ausilio della libreria scipy

import scipy.stats as stats
stats.chi2_contingency(FreqOsservatePivot, correction=False)

A questo punto possiamo calcolare l'indice chi-quadrato

indice_chi_quadrato = stats.chi2_contingency(FreqOsservatePivot,
correction=False)[0]

e l'indice V di Cramer

n = len(DatiCramer)
k = len(DatiCramer["Occhi"].unique())
r = len(DatiCramer["Area geografica"].unique())
indice_v_cramer = pow(indice_chi_quadrato / (n*max(k-1,r-1)),0.5)
indice_v_cramer

Indice di correlazione di Pearson e di Spearman con Pandas

Il calcolo degli indici di associazione tra due variabili quantitative è molto più immediato. Pandas infatti ci mette a disposizione il metodo corr utilizzabile per calcolare le correlazioni tra tutte le coppie di variabili numeriche. Se non specifichiamo il parametro method, verrà calcolare l'indice di correlazione lineare di Pearson

Dati.corr()

Se invece volessi calcolare il coefficiente di Spearman, mi basterebbe scrivere

Dati.corr(method="spearman")

Utilizzo di Python per la gestione dei dati: esplorazione delle librerie Pandas e Pymongo

Nicola Iantomasi — Tue, 24 Oct 2023 07:05:37 +0000

Python si è affermato come uno dei linguaggi di programmazione più popolari nel mondo, essendo particolarmente efficace nel settore della data science e del trattamento dei dati. Due delle sue librerie più utilizzate in questo ambito sono Pandas e Pymongo. In questo post vedremo qualche esempio d'uso di queste potenti librerie per importare e lavorare con dati da varie fonti.

Gestione dei dati CSV con Pandas

Pandas è la libreria di Python per l'importazione e la manipolazione dei dati all'interno di un DataFrame. Un DataFrame è una struttura simile a una tabella con righe ordinate e nominate, chiamate indici del DataFrame.
Il metodo read_csv è spesso usato per importare dati da un file csv. Esso permette di configurare diverse opzioni come il delimitatore, il qualificatore, la riga dell'intestazione, quelle da skippare, i formati delle date e dei numeri. Ecco un esempio di codice.

import pandas as pd clienti = pd.read_csv(filepath_or_buffer = "Fatture.csv", sep = ";", header = 0, decimal=',')

In particolare abbiamo configurato:

il nome del file "Fatture.csv";
il carattere separatore (il punto e virgola ";");
la posizione della riga di intestazione (0 corrisponde alla prima);
il separatore delle cifre decimali (la virgola ",").

In maniera simile, ci sono altri metodi per importare dati da altre fonti che organizzano i dati in tabelle, come Excel e i database SQL.

Gestione dei dati JSON con Pandas

Il formato JSON è largamente usato come formato di interscambio. Pandas fornisce:

il metodo read_json per importare direttamente dati da file in formato JSON
il metodo json_normalize per creare un Dataframe a partire da una variabile di tipo dizionario.

Risulta importante osservare che a prescindere del formato iniziale (json, excel o csv) l'output sarà sempre un DataFrame. Questo è molto comodo per uniformare i dati importati in una struttura comune, che poi può essere lavorata in modo uniforme. Qui in particolare trovi degli esercizi sull'analisi dei dati. D'altro lato però, potrebbe generare qualche difficoltà nella gestione delle chiavi JSON più complesse.

Gestione dei dati con Pymongo

Pymongo è una libreria Python utilizzata per connettersi e interagire con i database NoSQL di MongoDb. Ecco il codice per connettersi ad un database locale installato a soli fini di test o esercizio personale.

from pymongo import MongoClient client = MongoClient("mongodb://localhost:27017/")

In generale, è importante rispettare gli standard di sicurezza aziendali quando crei la connessione. Come minimo, dovresti evitare di scrivere direttamente nel codice l'username e la password.

Dopo, puoi connetterti a un database MongoDb e usare le funzioni offerte da Pymongo per gestire i dati. Con questo codice, ad esempio, salvi nella variabile _result _le fatture con tipologia "A".

db = client.DatabaseMongoDB result = db.fatture.find({"tipologia":{$eq:"A"}});

La variabile _result _è un array di pymongo. Applicando il metodo _next _possiamo lavorare il contenuto del cursore "un documento alla volta"
document = result.next()

A questo punto posso utilizzare tutte le funzionalità dei dizionari di Python. Ad esempio con questo codice

document["id_fattura"]

estraggo la chiave _id_fattura _del documento.

Possiamo lavorare con i cursori anche tramite cicli for

for document in result: print(document)

Oltre a leggere i dati, hai anche la possibilità di inserire, aggiornare o eliminare documenti dal database, ed eseguire query più complesse di aggregazione dei dati.

Linguaggio SQL e Join tra tabelle: una guida pratica all'uso

Nicola Iantomasi — Mon, 16 Oct 2023 14:47:01 +0000

Essere abili nella manipolazione dei dati con SQL è essenziale per qualsiasi Data Analyst. Uno degli strumenti più rilevanti in questo contesto sono le JOIN, che permettono di collegare i dati di diverse tabelle.

Sintassi delle Join

Per combinare i dati in più tabelle con il linguaggio SQL, dobbiamo scrivere una JOIN all'interno della clausola FROM e il comando ON per specificare la condizione secondo cui le righe devono essere unite. Ad esempio, per fondere le tabelle "post" e "comments" sulla colonna "post_id", si può utilizzare la seguente query:

SELECT Fatture.*, Corrieri.NomeCorriere FROM Fatture INNER JOIN Corrieri ON Fatture.IdCorriere = Corrieri.IdCorriere;

Per ottenere un risultato che abbia senso nell'analisi dei dati, è importante assicurarsi che IdCorriere sia:

una chiave primaria nella tabella Corrieri
una chiave esterna nella tabella Fatture.

In caso contrario, si rischia di generare un output con dati duplicati o mancanti.

Scegliere la sintassi con JOIN esplicita

Un aspetto importante che merita ulteriore enfasi è la scelta della sintassi con la JOIN esplicita, piuttosto che utilizzare una virgola nella FROM e aggiungere una condizione nella WHERE, come in questo esempio.

Ci sono diversi motivi per cui questa opzione è preferibile:

SELECT Fatture.*, Corrieri.NomeCorriere FROM Fatture, Corrieri WHERE Fatture.IdCorriere = Corrieri.IdCorriere;

Leggibilità: La join separa la parte del codice che gestisce il collegamento tra le tabelle da quella che imposta altri filtri sui dati.
Facilità di modifica: Supponiamo che tu debba trasformare un'INNER JOIN in una LEFT JOIN. Se hai già esplicitato la JOIN, tutto ciò che devi fare è modificare una parola! Al contrario, su molti database, sarebbe necessario riscrivere completamente la query.

Aggiungo ulteriori dettagli su questo paragrafo nel mio blog

Rinominare colonne e tabelle nelle Join

Per garantire la leggibilità delle query SQL, è buona norma usare nomi descrittivi e concisi per colonne e tabelle quando si utilizzano le JOIN. Questo facilita la lettura e permette di intervenire rapidamente se qualcosa nella nostra query non funziona come previsto. Ad esempio, potremmo riscrivere la query precedente in questo modo:

SELECT f.*, c.NomeCorriere FROM Fatture as f LEFT JOIN Corrieri as c ON f.IdCorriere = c.IdCorriere;

Tipologie di join:
Ci sono cinque tipi principali di JOIN, a seconda dell'obiettivo della query.

INNER JOIN: unisce le tabelle utilizzando solo le righe che hanno corrispondenze in entrambe le tabelle.
LEFT JOIN: oltre al risultato dell’INNER JOIN, include anche le righe della tabella di sinistra che non hanno corrispondenze.
RIGHT JOIN: l'opposto del precedente, include al risultato dell’INNER JOIN anche le righe della tabella di destra che non hanno corrispondenze.
FULL JOIN: combina i risultati di LEFT e RIGHT, visualizzando sia le righe della tabella di sinistra che quelle di destra che non hanno corrispondenza.
CROSS JOIN: effettua tutte le combinazioni possibili, senza nessun criterio

In questo video ragioniamo su quale tipologia di Join è meglio usare

Conclusione sull'utilizzo delle Join

In conclusione, le JOIN sono uno strumento cruciale per la gestione dei dati in SQL. Tuttavia, è necessario fare attenzione a scegliere il tipo di Join appropriato in base al contesto e a formulare correttamente la condizione nell'ON.

Guida SQL: istruzione Where

Nicola Iantomasi — Thu, 12 Oct 2023 07:48:47 +0000

In SQL, filtrare i dati di una tabella è un'operazione molto importante per estrarre solo le informazioni di interesse. L'uso della parola chiave WHERE è il principale strumento per effettuare questa operazione. Cambiando le condizioni all'interno della clausola WHERE, è possibile selezionare righe specifiche della tabella in modo da ottenere solo le informazioni necessarie.

Operatori AND e OR nella Where SQL

Per poter scrivere filtri complessi a piacere nella clausola WHERE, si possono utilizzare gli operatori AND e OR per combinare più condizioni tra di loro. Ad esempio, se volessimo selezionare solo le righe in cui la colonna "età" è maggiore o uguale a 25 e la colonna "provincia" è "Roma", possiamo utilizzare l'operatore AND:

SELECT * 
FROM   tabella 
WHERE  eta >= 25 
   AND provincia = 'Roma';

Se invece avessimo voluto selezionare le righe in cui la colonna "provincia" fosse "Roma" o la colonna "provincia" fosse "Milano", avremmo utilizzato l'operatore OR:

SELECT * 
FROM   tabella 
WHERE  provincia = 'Roma' 
    OR provincia = 'Milano';

Operatore IN:

Un altro modo per filtrare più valori con una sola query è utilizzare l'operatore IN. Ad esempio, per selezionare le righe in cui la colonna "provincia" è "Roma", "Milano" o "Torino", possiamo utilizzare la seguente query, più veloce da scrivere rispetto all'OR:

SELECT *
FROM tabella
WHERE provincia IN ('Roma', 'Milano', 'Torino');

Filtro sulle date

Se vogliamo filtrare su colonne contenenti date, dobbiamo tenere conto della sintassi specifica del DBMS in uso. Ad esempio, utilizzando MySQL, per selezionare le righe in cui la colonna "data" è successiva al 2020-01-01, possiamo utilizzare la seguente query:

SELECT * 
FROM   tabella 
WHERE  data > '2020-01-01';

Valori NULL

Infine, è importante tenere conto dei NULL. Per selezionare le righe in cui la colonna "provincia" è NULL, possiamo utilizzare 'IS NULL', ad esempio:

SELECT * 
FROM   tabella 
WHERE  provincia IS NULL;

Al contrario, per selezionare le righe in cui la colonna "provincia" non è null, possiamo utilizzare 'IS NOT NULL':

SELECT * FROM tabella WHERE provincia IS NOT NULL;

Conclusioni e approfondimenti

In generale, filtrare i dati di una tabella tramite la clausola WHERE e gli altri operatori del linguaggio SQL è essenziale per estrarre solo le informazioni di interesse. Nel nostro esempio abbiamo visto come utilizzare gli operatori AND e OR, l'operatore IN, come filtrare le date e come gestire i valori NULL. Ricordate che ogni DBMS ha la sua sintassi specifica, quindi è importante consultare la documentazione ufficiale per gli esempi precisi.

In questo articolo del mio blog parlo di come utilizzare la condizione WHERE su colonne di tipo Datetime di SQL Server

Invece in questo video sul mio canale Youtube parlo di come scrivere le prima query con SQL e utilizzare l'istruzione WHERE