Prodotto cartesiano in SQL: una guida completa

Il prodotto cartesiano è un concetto fondamentale in SQL che può essere tanto potente quanto complesso. Capire come usarlo in modo efficace può migliorare notevolmente le tue capacità di manipolazione dei dati. In questa guida esploreremo a fondo i prodotti cartesiani, offrendo spunti pratici ed esempi. 

Se sei alle prime armi con SQL, ti consigliamo di iniziare dal nostro corso Intermediate SQL per costruire basi solide. Inoltre, il SQL Basics Cheat Sheet, che puoi scaricare, è un utile riferimento perché include tutte le funzioni SQL più comuni.

Cos'è un prodotto cartesiano?

In SQL, il prodotto cartesiano è il risultato di combinare ogni riga di una tabella con ogni riga di un'altra. Questa operazione viene più spesso generata tramite un CROSS JOIN, formando un result set contenente tutte le possibili coppie di righe tra le due tabelle. Pur essendo potente, può produrre result set molto grandi, soprattutto quando si lavora con tabelle con molte righe, quindi va usato con cautela. Anzi, scommetto che molti sviluppatori scoprono i prodotti cartesiani nel modo più duro, cioè dimenticando per sbaglio una condizione di join e ritrovandosi con un result set enorme.

Background matematico

In matematica, un prodotto cartesiano (indicato come A × B) è l'insieme di tutte le possibili coppie ordinate create accoppiando ogni elemento dell'insieme A con ogni elemento dell'insieme B. In SQL, questo si traduce nell'accoppiare ogni riga di una tabella con ogni riga di un'altra.

Esempio di prodotto cartesiano. Immagine dell'autore.

Cross join in SQL

L'operazione di cross join in SQL crea un prodotto cartesiano tra due tabelle unendo ogni riga della prima tabella con ogni riga della seconda. Di conseguenza, il cross join genera tutte le combinazioni possibili di righe per i dati. Vediamo un esempio di esecuzione di un'operazione di cross join in SQL.

Supponiamo di avere due tabelle, Students e Courses:

Tabella Students

Tabella Courses

Nella query seguente, ogni studente è abbinato a ogni corso, mostrando il suo grade e i livelli di difficoltà.

-- Perform the cross join to list each student with every course
SELECT Students.name, Students.grade, Courses.course, Courses.level
FROM Students
CROSS JOIN Courses;Offerto da

Esempio di prodotto cartesiano in SQL risultante da un cross join. Immagine dell'autore.

Quando usare e non usare il prodotto cartesiano in SQL

Il prodotto cartesiano in SQL può essere molto utile quando il suo comportamento è compreso e usato intenzionalmente. Vediamo i casi d'uso (e le possibili insidie) dell'uso del prodotto cartesiano.

Casi d'uso

Ecco alcuni scenari in cui l'uso del prodotto cartesiano può essere utile:

• Generare combinazioni: un prodotto cartesiano è ideale per generare tutte le combinazioni tra due insiemi di valori. Ad esempio, potresti voler abbinare prodotti e sconti, assegnare dipendenti ai turni o creare tutti gli accoppiamenti possibili tra diverse categorie.
• Scenari di testing: quando esegui casi di test, un prodotto cartesiano può essere utile per simulare ogni scenario possibile abbinando vari set di parametri o configurazioni.
• Colmare dati mancanti: un prodotto cartesiano può anche aiutare a individuare lacune generando tutte le combinazioni possibili e poi facendo un left join dei risultati per trovare le voci mancanti, come date assenti in un'analisi di serie temporali.

Possibili insidie

Sebbene il prodotto cartesiano sia utile in alcuni scenari, presenta dei rischi, tra cui i seguenti:

• Problemi di prestazioni: poiché un prodotto cartesiano moltiplica le righe di una tabella per quelle di un'altra, il risultato può crescere in modo esponenziale. Se il dataset è troppo grande, questo può portare a tempi di esecuzione lenti, uso eccessivo di memoria e persino crash del database.
• Duplicazione involontaria dei dati: senza un filtraggio accurato, i prodotti cartesiani possono produrre righe che appaiono duplicate o irrilevanti per l'analisi, portando a interpretazioni distorte o risultati errati se i duplicati non sono attesi o gestiti correttamente.
• Debug difficile: prodotti cartesiani non intenzionali, come dimenticare di specificare una condizione di join, possono portare a un cross join invece di un inner o outer join. Questo può produrre output inattesi difficili da rintracciare e correggere.

Applicazioni reali del prodotto cartesiano

Anche se il prodotto cartesiano va usato con parsimonia per i potenziali rischi, è utile negli scenari in cui devi combinare ogni possibile coppia di elementi di dati.

Scenari di combinazione dati

Il prodotto cartesiano è utile per calcolare metriche di performance relative, analizzare compatibilità o creare sistemi di ranking. Quando devi confrontare tutti gli elementi in due dataset, puoi usare il prodotto cartesiano per accoppiare ogni elemento con tutti gli altri. 

Per esempio, per confrontare le performance di ogni dipendente con quelle di tutti gli altri, il prodotto cartesiano creerebbe tutte le possibili coppie di dipendenti:

-- Select two columns with employee names from the Employees table
SELECT 
    A.employee_name AS Employee1,
    B.employee_name AS Employee2
FROM Employees AS A
-- Cross join to create all possible pairs of employees
CROSS JOIN Employees AS B;Offerto da

Il prodotto cartesiano è anche importante nell'analisi e-commerce per esplorare l'intera gamma di abbinamenti prodotto-sconto. Supponiamo di avere le tabelle Products e Discounts, un cross join potrebbe creare un elenco di tutte le combinazioni prodotto-sconto:

-- Select product names and discount types
SELECT 
    Products.product_name,
    Discounts.discount_type
FROM Products
-- Generate all product and discount combinations
CROSS JOIN Discounts;Offerto da

Test e validazione

Un prodotto cartesiano consente di creare tutte le combinazioni di parametri possibili negli ambienti di test, essenziale per un testing esaustivo degli scenari. Ad esempio, se testi un sistema con varie configurazioni di OS, dispositivi e browser, usare un cross join garantisce che ogni configurazione possibile venga testata.

-- Select combinations of operating systems, devices, and browsers
SELECT 
    OS.name AS OperatingSystem,
    Device.name AS Device,
    Browser.name AS Browser
FROM OperatingSystems AS OS
-- All combinations with devices
CROSS JOIN Devices AS Device
-- All combinations with browsers           
CROSS JOIN Browsers AS Browser;Offerto da

Un prodotto cartesiano può aiutare a individuare record mancanti, risultando utile per garantire la completezza dei dati. Ad esempio, quando analizzi i dati di vendita giornalieri e vuoi confermare che esistano dati per ogni prodotto ogni giorno, puoi creare un prodotto cartesiano delle tabelle Products e Dates. Poi, puoi eseguire un left join dei dati di vendita effettivi per identificare eventuali date-prodotto mancanti.

-- Select dates and products with no sales
SELECT 
    Dates.date,
    Products.product_name,
    Sales.sales_amount
FROM Dates
-- Create all date and product combinations
CROSS JOIN Products                          
-- Match sales by date and product
LEFT JOIN 
    Sales ON Dates.date = Sales.date 
           AND Products.product_name = Sales.product_name  
-- Filter for combinations with no sales
WHERE Sales.sales_amount IS NULL;Offerto da

Alcune idee sulle prestazioni

Poiché i prodotti cartesiani generano combinazioni di dati tra due tabelle, possono emergere significativi problemi di performance quando sono coinvolte tabelle grandi. Ti suggerisco le seguenti tecniche per l'ottimizzazione delle query e la gestione di dataset di grandi dimensioni per migliorare le prestazioni del database.

Ottimizzazione delle query

Considera i seguenti metodi per scrivere query efficienti che coinvolgono prodotti cartesiani.

• Limita il numero di righe prima del join: per ridurre il numero di righe coinvolte nel prodotto cartesiano, applica filtri a ciascuna tabella prima di eseguire il cross join. Ad esempio, usa clausole WHERE per filtrare ogni tabella in base a criteri rilevanti, minimizzando il dataset risultante.

• Usa SELECT con colonne specifiche: quando esegui un prodotto cartesiano, specifica solo le colonne necessarie nella clausola SELECT per ridurre l'uso di memoria e migliorare le prestazioni. Evita SELECT * perché restituisce tutte le colonne, con risultati potenzialmente grandi e ingestibili.

• Applica condizioni per convertire i cross join in inner join: se sono rilevanti solo alcune combinazioni di righe, valuta di aggiungere condizioni aggiuntive al cross join che funzionino come un INNER JOIN. Questo aiuta a limitare l'output restringendo quali righe di ciascuna tabella devono essere abbinate.

• Elaborazione in batch: per dataset grandi, suddividi le query in blocchi gestibili per l'elaborazione in batch. Questo può prevenire overflow di memoria e migliorare i tempi di risposta, soprattutto quando interroghi tabelle di grandi dimensioni.

Gestire dataset di grandi dimensioni

Usa le seguenti tecniche per gestire i dataset di grandi dimensioni risultanti dall'uso di prodotti cartesiani.

• Indicizzazione: aggiungi indici sulle colonne frequentemente usate nei join per migliorare le prestazioni quando filtri o abbini tabelle grandi. Sebbene l'indicizzazione non acceleri direttamente i cross join, è utile quando successivamente filtri o fai join del prodotto cartesiano con altre tabelle, permettendo al database di accedere più rapidamente alle righe.

• Partizionamento di tabelle grandi: il partizionamento può migliorare le prestazioni delle query che coinvolgono prodotti cartesiani. Divide le tabelle in porzioni più piccole e gestibili in base a colonne specificate, consentendo al database di leggere solo le partizioni rilevanti durante l'esecuzione della query.

• Limita le righe di risultato: usa la clausola LIMIT per limitare il numero di righe restituite nei casi in cui ti serve solo un campione del prodotto cartesiano per test o verifiche.

• Usa tabelle temporanee per risultati intermedi: archivia i risultati intermedi in tabelle temporanee se devi riutilizzare più volte un prodotto cartesiano, per ridurre i ricalcoli e velocizzare le operazioni successive.

Altre operazioni SQL utili

In scenari in cui generare un prodotto cartesiano completo potrebbe non essere efficiente, esistono metodi alternativi. I metodi seguenti ti consentono di ottenere i risultati desiderati senza sovraccaricare il database.

Inner e outer join

Un INNER JOIN restituisce solo le righe che hanno corrispondenza tra le colonne specificate in entrambe le tabelle. Questa tecnica limita il risultato a combinazioni rilevanti ed evita le molteplici righe tipiche di un prodotto cartesiano. L'inner join è appropriato quando ti servono solo le righe che soddisfano criteri specifici in entrambe le tabelle. 

Nell'esempio seguente, l'INNER JOIN restituisce solo i prodotti e gli sconti che condividono la stessa categoria.

-- Select products and corresponding discounts by category
SELECT 
    A.product_name,
    B.discount_type
FROM Products AS A
-- Match products and discounts by category
INNER JOIN Discounts AS B ON A.category_id = B.category_id;Offerto da

Gli outer join (LEFT JOIN, RIGHT JOIN e FULL JOIN) restituiscono righe con e senza corrispondenza da una o entrambe le tabelle. Questa tecnica è utile quando vuoi recuperare tutte le righe di una tabella e le eventuali righe corrispondenti dell'altra, ma senza creare tutte le combinazioni possibili. L'outer join è indicato quando vuoi includere dati senza corrispondenza per l'analisi ma non un accoppiamento cartesiano completo.

Il LEFT JOIN nella query sotto restituisce tutti i prodotti, inclusi quelli senza uno sconto corrispondente, con valori NULL per gli sconti senza corrispondenza.

-- Select products with corresponding discounts, (NULL if no matching discount)
SELECT 
    A.product_name,
    B.discount_type
FROM Products AS A
-- Match products with discounts by category
LEFT JOIN Discounts AS B ON A.category_id = B.category_id;  Offerto da

Ti consiglio il corso di DataCamp Introduction to SQL Server per approfondire come unire tabelle e recuperare dati da più tabelle.

Subquery e CTE

Le subquery permettono di selezionare dati specifici da una tabella e usarli per filtrare i risultati nella query principale. Questo aiuta a evitare combinazioni di righe non necessarie filtrando i dati alle sole voci rilevanti. Le subquery sono utili quando devi pre-filtrare i dati o calcolare valori specifici da usare nella query principale senza ricorrere a un join cartesiano completo.

La subquery filtra gli sconti con valori superiori a 10 prima del join, riducendo i dati elaborati ed evitando un cross join non necessario.

-- Select products with discounts greater than 10 by category
SELECT 
    A.product_name,
    B.discount
FROM Products AS A
-- Subquery to filter discounts over 10
INNER JOIN 
    (SELECT discount, category_id   
     FROM Discounts 
     WHERE discount > 10) AS B
-- Match products with filtered discounts by category
ON A.category_id = B.category_id;   Offerto da

Le CTE, o clausole WITH, permettono di suddividere query complesse in passaggi. Questo metodo aiuta a evitare prodotti cartesiani filtrando o aggregando i dati prima del join. Le CTE sono indicate quando vuoi semplificare query multi-step, soprattutto se devi pre-aggregare dati o eseguire più join, ma evitando un risultato cartesiano completo.

La query seguente usa una Common Table Expression (CTE) per filtrare gli sconti maggiori di 10 prima di unirli ai prodotti in base alla categoria.

-- Filter discounts and join with products by category
WITH FilteredDiscounts AS (
    SELECT discount, category_id
    FROM Discounts
    WHERE discount > 10
)
SELECT 
    A.product_name,
    FD.discount
FROM Products AS A
-- Join with filtered discounts by category
INNER JOIN 
    FilteredDiscounts AS FD ON A.category_id = FD.category_id;Offerto da

Applicazioni innovative del prodotto cartesiano

Sebbene il prodotto cartesiano sia ampiamente usato nelle operazioni SQL di base, ha applicazioni che vanno oltre i casi d'uso tradizionali dei database. Vediamo come può essere impiegato nella preparazione dei dati per il machine learning e nella generazione di dataset sintetici per simulazioni.

Preparazione dei dati per il machine learning

Dataset diversificati e completi sono importanti quando prepari dati per il machine learning per addestrare modelli robusti. I prodotti cartesiani possono creare un dataset completo che copre tutte le combinazioni possibili di valori delle feature, permettendo al modello di apprendere da una gamma più ampia di scenari. 

Ad esempio, quando costruisci modelli di rilevamento frodi, potresti combinare tipi di transazione, regioni e tipi di dispositivo per simulare varie condizioni, aiutando il modello a generalizzare tra casi diversi.

Generazione di dati sintetici

I dati sintetici sono spesso usati per testare i sistemi in un'ampia gamma di condizioni o per proteggere la privacy evitando l'uso di dati reali. I prodotti cartesiani possono aiutare a generare dataset sintetici diversificati, utili in applicazioni che vanno dalle simulazioni all'aumento dei dati per modelli di machine learning.

Ad esempio, potresti combinare diverse condizioni di mercato, tassi di interesse e profili dei clienti in simulazioni finanziarie per testare come un modello o un algoritmo si comporta rispetto a queste variabili.

Di seguito un semplice esempio di come potresti creare dati sintetici “dal nulla”, per così dire, in SQL e poi usare un CROSS JOIN per espanderli in un dataset più grande. L'idea è che ogni piccola tabella rappresenti una dimensione dei nostri dati di test e, quando le uniamo con un cross join, otteniamo tutte le combinazioni possibili.

Esempio 1: combinare più dimensioni

Supponiamo di voler simulare diversi scenari finanziari basati su condizioni di mercato, tassi di interesse e profili dei clienti. Possiamo definire ogni dimensione tramite una Common Table Expression (CTE) o una tabella temporanea usando valori letterali. Quindi, un CROSS JOIN le unisce in ogni possibile combinazione:

WITH MarketConditions AS (
    SELECT 'Bull' AS condition
    UNION ALL
    SELECT 'Bear'
    UNION ALL
    SELECT 'Sideways'
),
InterestRates AS (
    SELECT 1.5 AS rate
    UNION ALL
    SELECT 2.0
    UNION ALL
    SELECT 3.25
),
CustomerProfiles AS (
    SELECT 'Younger' AS profile
    UNION ALL
    SELECT 'Mid-career'
    UNION ALL
    SELECT 'Retired'
)
SELECT 
    mc.condition,
    ir.rate,
    cp.profile
FROM 
    MarketConditions mc
CROSS JOIN 
    InterestRates ir
CROSS JOIN 
    CustomerProfiles cp;Offerto da

Il result set avrà 3×3×3 = 27 righe. Ogni riga è una combinazione unica di condizione di mercato, tasso di interesse e profilo cliente.

Esempio di generazione di dati sintetici usando il prodotto cartesiano. Immagine dell'autore.

Esempio 2: generare righe usando sequenze o semplici VALUES

Un'altra tecnica consiste nel generare un intervallo di numeri o intervalli temporali facendo un cross join di piccole tabelle con se stesse. Questo può essere usato per produrre grandi volumi di righe sintetiche per test rapidi. Ad esempio, possiamo usare la query seguente per generare i numeri da 1 a 10.

WITH
Nums AS (
    SELECT 1 AS n
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
    UNION ALL SELECT 10
)
SELECT n
FROM Nums;Offerto da

Uso del prodotto cartesiano per generare righe usando sequenze. Immagine dell'autore.

Possiamo poi fare un cross join dello stesso set da 10 righe con se stesso per ottenere 10×10=100:

WITH
Nums AS (
    SELECT 1 AS n
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
    UNION ALL SELECT 10
)
SELECT A.n AS n1, B.n AS n2
FROM Nums A
CROSS JOIN Nums B
ORDER BY A.n, B.n;Offerto da

Come risultato otterrai 100 righe, ciascuna con una coppia di valori (n1,n2). Puoi estendere questa logica a centinaia, migliaia o persino milioni di righe—tenendo però conto di prestazioni e storage.

Usando questi approcci—CTE (o clausole VALUES) per definire le dimensioni e CROSS JOIN per la combinazione—puoi creare rapidamente dataset sintetici in SQL senza dipendere da tabelle esistenti.

Conclusione

Capire il prodotto cartesiano in SQL apre a una serie di possibilità per la manipolazione e l'analisi dei dati. Padroneggiando questo concetto, puoi migliorare la tua capacità di lavorare con dataset complessi e ottimizzare le tue query. Per approfondimenti avanzati, dai un'occhiata al nostro percorso di competenze SQL Server Fundamentals per affinare la tua esperienza. 

A seconda del tuo percorso professionale, ti consiglio di iscriverti al career track di DataCamp Associate Data Analyst in SQL per acquisire le skill richieste per diventare data analyst oppure di provare il career track Associate Date Engineer in SQL per diventare data engineer.