Guinguette Marais Poitevin

Non dimenticherò mai uno dei miei primi incarichi SQL come giovane sviluppatore junior nel governo federale. Dovevo produrre un rapporto per uno dei nostri clienti per aiutarli a generare alcune statistiche per l’ufficio del ministro. Quando i numeri del rapporto variarono dalle loro stime con un ampio margine, convocarono una riunione tra me e il mio capo. Non avevo idea di dove o che avessi sbagliato, finché il mio capo non sbottò: “Robert, hai creato un prodotto cartesiano”. Dopo aver finito di rimproverarmi davanti ai nostri clienti, la riunione è stata aggiornata e sono andato a riscrivere la mia domanda. Alla fine riuscii a trovare quello che stavano cercando, ma il mio capo non me lo fece mai dimenticare, e lo tirò fuori in ogni riunione successiva: “Robert, ricordi quella volta che hai creato un prodotto cartesiano…”. Ho finito per essere trasferito in un altro dipartimento circa sei mesi dopo. Ora, è vero che il mio capo non era una persona particolarmente indulgente, ma errori come questi possono davvero danneggiare la tua carriera. Oggi, vorrei condividere con voi ciò che ho imparato sui prodotti cartesiani nel corso degli anni, in modo che possiate individuarli e bandirli per sempre dalle vostre query SELECT.

Come generare un prodotto cartesiano

La seguente query estrae dati da due tabelle senza alcun tipo di filtro. Omettere la clausola WHERE può essere utile in situazioni in cui si vogliono vedere tutte le righe di una tabella ma si desidera riordinare o nascondere le colonne non rilevanti:

SELECT name, gender, CONCAT('$', FORMAT(salary, 2)) AS 'Monthly Salary' FROM employees, shops; 

Il problema qui è che la query seleziona da più tabelle. Senza alcuna tabella esplicita, ci ritroviamo con un tipo di join predefinito chiamato Cartesian Join (o Cross Join). Il nome Cross Join si riferisce al fatto che unisce ogni riga della prima tabella ad ogni riga della seconda tabella. In altre parole, le Cartesian Join rappresentano la somma del numero di colonne delle tabelle di input più il prodotto del numero di righe delle tabelle di input.

Si può vedere nei risultati che ogni riga della prima tabella (dipendenti) viene restituita per ogni riga della seconda tabella (negozi). Dato che ci sono tre righe nella tabella negozi, la query produce tre di ogni riga della tabella dipendenti:

Queste sono un sacco di righe per due piccole tabelle. I risultati crescono esponenzialmente quando sono coinvolte più righe e/o tabelle. A causa dello sforzo che una tale query mette sulle risorse del sistema e del fatto che l’insieme di dati risultante contiene troppe informazioni perché l’autore della query possa selezionare ciò che è interessante, le Cartesian Joins sono quasi sempre eseguite per caso. Come abbiamo imparato nel mio racconto ammonitore, è bene sapere come individuarne uno prima che i vostri clienti o supervisori tornino da voi con domande sul perché ci siano così tante righe duplicate. Infatti, la presenza di molti duplicati, combinata con un set di risultati insolitamente grande, è un segno rivelatore che si potrebbe avere un Prodotto Cartesiano tra le mani.

Come i criteri di filtraggio possono mascherare un Prodotto Cartesiano

Le istruzioni SELECT che contengono una clausola WHERE possono facilmente nascondere un Prodotto Cartesiano perché non tutte le righe appariranno in duplice copia. Ecco una query dall’aspetto abbastanza innocente in cui qualcuno ha dimenticato di includere un join di tabella:

SELECT name, gender, CONCAT('$', FORMAT(salary, 2)) AS 'Monthly Salary' FROM shops, employeesWHERE shops.shop = 'Zurich';

Siccome il negozio non appare nel set di risultati, sarebbe facile accettare che l’output sia accurato. Tuttavia, possiamo facilmente verificare che solo i primi due dipendenti della lista lavorano a Zurigo. Gli altri tre sembrano essere sfuggiti ai nostri criteri di filtraggio!

L’output di tutte le colonne mostra più chiaramente cosa sta succedendo. Il filtro ha infatti restituito solo il negozio di Zurigo. Tuttavia, senza un adeguato join di tabelle, la query produce un record per ogni dipendente, che sia collegato o meno al negozio di Zurigo. Questo ha senso se si considera che i dipendenti non sono associati a nessun negozio senza un join. Quindi, il campo shop_id nella tabella dei dipendenti non ha niente a che fare con quello della tabella dei negozi. Quello che la query sta dicendo è “Recupera tutte le righe della tabella dei negozi dove il nome corrisponde a ‘Zurigo’ e tutte le righe della tabella dei dipendenti”:

shop_id	shop	id	shop_id_1	gender	name	salario
	Zurigo			m	Jon Simpson
	Zurigo			f	Barbara Breitenmoser	(NULL)
	Zurigo			f	Kirsten Ruegg
	Zurich			m	Ralph Teller
	Zurich			m	Peter Jonson

Similmente, restringere i risultati della tabella dei dipendenti produce solo le righe di quella tabella che corrispondono ai criteri e tutte le righe dell’altra tabella. Ecco una query che filtra i dipendenti in base allo stipendio:

SELECT name, gender, CONCAT('$', FORMAT(salary, 2)) AS 'Monthly Salary' FROM shops, employeesWHERE employees.salary > 5500;

Risulta una riga della tabella dei dipendenti, che viene visualizzata una volta per ogni riga della tabella dei negozi:

nome	gender	Salario mensile
Kirsten Ruegg	f	$5,600.00
Kirsten Ruegg	f	$5.600.00
Kirsten Ruegg	f	$5,600.00

Ancora una volta, includendo tutte le colonne si conferma questa ipotesi:

id	shop_id	gender	name	salario	shop_id_1	negozio
		f	Kirsten Ruegg			Zurich
		f	Kirsten Ruegg			New York
		f	Kirsten Ruegg			Londra

Ora questi risultati non sembrano immediatamente sospetti perché, a seconda dei criteri e delle colonne visualizzate, i valori duplicati non sono un evento insolito:

La query mostra ancora un dipendente per ogni riga della tabella dei negozi, ma questa volta mostra un dipendente per ogni riga della tabella dei negozi e tre righe per l’unico dipendente corrispondente. In altre parole, tutti e cinque i dipendenti vengono visualizzati per i negozi corrispondenti (quello di Zurigo) e il record del dipendente il cui reddito supera i 5500 dollari (Kristen Ruegg) viene ripetuto per ogni riga della tabella dei negozi.

Come abbiamo visto oggi, i prodotti cartesiani non tendono a fornire informazioni utili. Quindi, la morale della storia è questa: evitate le Cartesian Joins a tutti i costi a meno che non abbiate una ragione cristallina per farlo.

Prendere i dati giusti con le SQL Joins

” Vedi tutti gli articoli del giornalista Rob Gravelle