Esempi pratici di utilizzo di JOIN non uguali in SQL

Se pensate che in SQL sia possibile unire due tabelle solo utilizzando il campo comune, sarete entusiasti di sapere che esistono altri modi per unire. Le JOIN non equi utilizzano diversi tipi di operatori di confronto nella condizione di JOIN. In questo articolo, vi guiderò attraverso esempi di JOIN non equi per mostrare i casi d'uso più comuni di questo tipo di JOIN.

JOIN Equi vs. JOIN non Equi

Come si fa di solito a unire due tabelle in SQL? Molto probabilmente si seleziona il campo comune a queste due tabelle e le si unisce utilizzando il segno di uguale nella condizione di unione. Ad esempio, è possibile abbinare l'ID prodotto della tabella prodotti con l'ID prodotto della tabella ordini o il cognome della tabella dipendenti con il cognome del foglio di presenza.

In questi casi, si utilizza una equi JOIN, che è solo un nome di fantasia per una join con un segno di uguale nella condizione di join. Molti principianti di SQL utilizzano le JOIN equi e non sanno nemmeno che è possibile utilizzare una condizione di non uguaglianza nelle JOIN.

Tali join sono chiamati JOIN non equi e sono possibili anche in SQL. Quando si uniscono due tabelle utilizzando altri operatori condizionali, oltre al segno di uguale, entrano in gioco le JOIN non uguali. Gli operatori di confronto, come <, >, <=, >=, != e <> e l'operatore BETWEEN funzionano perfettamente per unire le tabelle in SQL.

Per capire meglio come funziona, consultate questa guida illustrata alle JOIN non uguali in SQL.

Conoscere i dati

Prima di immergerci in diversi esempi di JOIN non-equi, vediamo innanzitutto i dati che esploreremo in questo articolo. Faremo un'analisi dei dati di un'agenzia immobiliare che gestisce un'attività di affitto. Nel nostro database immaginario abbiamo tre tabelle:

• houses con l'ID della casa, il quartiere, l'indirizzo, il numero di camere da letto e l'affitto
• renters con l'ID dell'affittuario, il nome, il quartiere preferito, il numero minimo di camere da letto richieste e l'intervallo di affitto accettabile
• deals con l'ID dell'affare, la data, l'ID dell'affittuario, l'ID della casa e la commissione dell'agente ricevuta dall'affare corrispondente.

Le tre tabelle sono riportate di seguito.

Houses

Renters

Deals

Ora siamo pronti a passare agli esempi di JOIN non equi.

Casi d'uso delle JOIN non equi

Se non avete mai usato JOIN non equi prima d'ora, potreste chiedervi quali sono gli scenari comuni per l'applicazione di questo tipo di JOIN non standard. In effetti, ce ne sono parecchi. È possibile utilizzare le JOIN non-equi per elencare tutte le coppie (uniche) di elementi, identificare i duplicati, elencare gli elementi all'interno di un determinato intervallo di valori o tra determinate date, calcolare i totali progressivi e altro ancora.

Inizieremo con i casi d'uso più comuni delle JOIN non-equi.

Combinazioni di coppie di elenchi

Immaginiamo che i nostri affittuari siano pronti a prendere in considerazione la possibilità di condividere la casa con un'altra famiglia. Vogliamo quindi elencare tutte le possibili coppie di affittuari con il loro quartiere preferito per vedere quali affittuari potrebbero affittare una casa insieme. Ecco la query SQL da utilizzare:

SELECT r1.name, r1.preferred_district, r2.name, r2.preferred_district
FROM renters r1
JOIN renters r2
ON r1.preferred_district = r2.preferred_district AND r1.id != r2.id;

Come si può notare in questo esempio, abbiamo unito la tabella renters con se stessa. Abbiamo utilizzato due condizioni con operatori di confronto diversi nella nostra istruzione JOIN:

• Il segno di uguale standard assicura che vengano accoppiati solo i clienti con lo stesso distretto preferito.
• L'operatore di confronto != assicura che il risultato includa tutte le possibili coppie di affittuari, tranne l'accoppiamento degli affittuari con se stessi.

La seconda condizione con l'operatore != rende questa JOIN una JOIN non uguale.

Il risultato sembra abbastanza buono, tranne per il fatto che nella tabella sono elencate due volte le stesse coppie di affittuari. Sarebbe più sensato avere solo coppie uniche nel risultato. Questa piccola modifica della nostra query è un trucco utile:

SELECT r1.name, r1.preferred_district, r2.name, r2.preferred_district
FROM renters r1
JOIN renters r2
ON r1.preferred_district = r2.preferred_district AND r1.id < r2.id;

Cambiando l'operatore di confronto nella seconda condizione di join da != a <, elenchiamo solo le coppie in cui il valore dell'ID del primo affittuario è inferiore al valore dell'ID del secondo affittuario. Pertanto, ora abbiamo solo la riga con Michael Lane (ID 2) elencato nella prima colonna e Susan Sanders (ID 3) elencata nella terza colonna, e non la riga in cui Susan Sanders viene prima.

Per elencare tutte le coppie (uniche) di clienti, abbiamo unito la tabella degli affittuari con se stessa, in pratica una self JOIN. Per saperne di più sulle auto-unioni, consultate questa guida completa con esempi facili da seguire.

Identificare i duplicati

Un'altra applicazione comune delle JOIN non equivalenti è quella di trovare i duplicati in un set di dati. Per esempio, supponiamo di voler verificare se la nostra tabella case include dei duplicati, cioè case con lo stesso indirizzo ma con ID diversi.

La nostra query sarà molto simile a quella utilizzata per elencare le coppie uniche, ma questa volta faremo un'auto-giunzione nella tabella case:

SELECT h1.id, h1.address, h2.id, h2.address
FROM houses h1
JOIN houses h2
ON h1.address = h2.address AND h1.id < h2.id;

Anche in questo caso abbiamo due condizioni di join: (1) per verificare se l'indirizzo è lo stesso e (2) per assicurarci di elencare solo le coppie uniche con ID diversi.

La tabella precedente mostra che c'è un duplicato nel nostro set di dati. La casa situata in Rose Street, 5 è citata due volte nella tabella, con l'ID 1 e l'ID 3.

Unire tabelle utilizzando un intervallo di valori

Altre applicazioni popolari di JOIN non equi includono l'unione di due tabelle utilizzando:

• L'operatore BETWEEN per verificare se un determinato valore/data rientra in un intervallo specificato.
• Operatori di confronto come >= o <= per controllare la capacità

Per vedere come funzionano in pratica queste JOIN non-equi, elenchiamo altre case che possiamo suggerire ai nostri affittuari come alternativa. Si tratta di case (1) nel loro quartiere preferito, (2) nella loro fascia di prezzo, (3) con il numero di camere da letto desiderato e (4) non occupate (cioè non elencate nella nostra tabella delle offerte). Ecco la query SQL che possiamo utilizzare:

SELECT r.id, r.name, h.id, h.address, h.rent, h.bedrooms
FROM renters r
JOIN houses h
ON h.district = r.preferred_district
    AND h.rent BETWEEN r.min_rent AND r.max_rent
    AND h.bedrooms >= r.min_bedrooms
WHERE h.id NOT IN (SELECT house_id FROM deals);

Qui, nella condizione JOIN, controlliamo le prime tre condizioni menzionate sopra:

• se il quartiere della casa corrisponde al quartiere preferito dall'affittuario (condizione di uguaglianza)
• Se l'affitto rientra nell'intervallo di valori accettabili per l'affittuario (condizione di non uguaglianza con l'intervallo di valori)
• Se il numero di camere da letto soddisfa i requisiti minimi (condizione di non uguaglianza per verificare la capacità).

Poi, nell'istruzione WHERE, abbiamo usato una sottoquery per filtrare solo le case presumibilmente libere, cioè non ancora presenti nella nostra tabella deals.

Ecco l'elenco delle case che possiamo suggerire ai nostri clienti (si noti che la casa con ID 3 è solo un duplicato della casa che questo cliente sta affittando ora):

Per fare più pratica con questi tipi di SQL non-equi JOIN, consultate il nostro corso completo su SQL JOINs.

Scenari avanzati per l'uso di JOIN non equi

Oltre alle applicazioni comuni delle JOIN non equi menzionate sopra, esistono scenari più avanzati per l'applicazione di questo tipo di JOIN. Vediamo due esempi.

Calcolo dei totali di corsa

Una JOIN non-equi può essere utilizzata per calcolare un totale progressivo di una particolare colonna. Ad esempio, supponiamo di voler conoscere, dopo ogni affare concluso, il totale delle commissioni percepite dall'agente fino a quel momento. Ecco la query SQL che possiamo utilizzare:

SELECT d1.date, d1.agent_fee, SUM(d2.agent_fee) AS total_agent_fee
FROM deals d1
JOIN deals d2
ON d1.date >= d2.date
GROUP BY d1.agent_fee, d1.date
ORDER BY d1.date;

Abbiamo fatto un self-join con la tabella deals e abbiamo usato una condizione non-equi JOIN per sommare tutti i compensi degli agenti ricevuti fino alla data dell'affare. Ecco il risultato.

Si noti che un modo migliore per calcolare un totale progressivo è utilizzare le funzioni della finestra. La nostra guida spiega cos'è un totale progressivo e come scrivere una query SQL per calcolarlo. Per esercitarsi, consultare il corso interattivo LearnSQL.it Window Functions.

Risoluzione dei conflitti tra LEFT JOIN e WHERE

Le JOIN non equi possono essere utili anche in alcune situazioni in cui LEFT JOIN combinato con l'istruzione WHERE non funziona come previsto. In particolare, capita spesso che la condizione WHERE "annulli" l'istruzione LEFT JOIN, facendola funzionare invece come INNER JOIN.

Per esempio, supponiamo di voler elencare tutte le case del nostro database insieme alla data dell'affare corrispondente, se è avvenuto. Vogliamo inoltre considerare solo le transazioni avvenute dopo il 1° marzo.

Poiché siamo interessati a elencare tutte le case, indipendentemente dal fatto che abbiano un accordo corrispondente, utilizzeremo una condizione LEFT JOIN nella nostra query SQL. Aggiungeremo anche una condizione WHERE per considerare solo gli affari conclusi dopo una certa data:

SELECT h.id, h.address, d.date
FROM houses h
LEFT JOIN deals d
ON h.id = d.house_id
WHERE d.date >= '2020-03-01';

Come si può vedere, il risultato non è esattamente quello desiderato. La tabella include solo le case affittate dopo il 1° marzo invece di tutte le case.

Una soluzione è spostare la condizione WHERE su ON e renderla una condizione JOIN. Si tratta di una JOIN non equa, poiché utilizza l'operatore di confronto >=.

SELECT h.id, h.address, d.date
FROM houses h
LEFT JOIN deals d
ON h.id = d.house_id AND d.date >= '2020-03-01';

Ora vediamo tutte le case nel risultato, anche se non hanno offerte corrispondenti.

È ora di fare pratica con le JOIN non equi!

Avete imparato che le JOIN non equi possono essere molto utili in diversi scenari. È possibile elencare le coppie uniche all'interno di una tabella, identificare i duplicati, unire le tabelle utilizzando un intervallo di valori e di date, calcolare i totali progressivi senza utilizzare le funzioni finestra e altro ancora.

Qual è il modo migliore per esercitarsi su SQL JOINs? Sicuramente, scrivendo codice. Passiamo quindi agli esercizi interattivi!

Il corso LearnSQL.it SQL JOINs tratta diversi tipi di JOIN, compresi i JOIN non equi. Avrete l'opportunità di fare pratica con le JOIN non-equi in vari casi d'uso. Inoltre, il corso completo SQL Basics corso offre ulteriore pratica sulle JOIN non-equi quando si spiega l'interrogazione di più tabelle e si esplorano argomenti più approfonditi sulle JOIN.

Buon apprendimento!