Guida SQLite interattiva: Self join

Q: Cos'è un self join in SQLite?

Un self join è un normale JOIN in cui una tabella viene unita con se stessa. Dai alla stessa tabella due alias diversi così SQLite può trattarli come fonti di righe separate, poi abbini le righe su una colonna che collega una riga a un'altra: quasi sempre una relazione genitore/figlio come dipendente/responsabile.

Q: Perché servono gli alias in un self join?

Senza alias, SQLite non sa a quale copia della tabella ti riferisci quando scrivi il nome di una colonna. Dare a ogni istanza il suo alias (come e per il dipendente e m per il responsabile) ti permette di scrivere e.manager_id = m.id senza ambiguità. Gli alias non sono facoltativi: senza, la query non viene nemmeno analizzata.

Q: Quando conviene un self join e quando una subquery?

Usa un self join quando vuoi nel risultato colonne di entrambe le righe, per esempio il nome del dipendente e il nome del responsabile sulla stessa riga. Usa una subquery quando ti serve solo filtrare o cercare un singolo valore. Per le gerarchie molto profonde nessuno dei due va bene: lo strumento giusto è una CTE ricorsiva.

Un self join è solo un join con gli alias

Un self join non ha niente di speciale. È un normale JOIN in cui entrambi i lati sono la stessa tabella. Il trucco è che SQLite ha bisogno di un modo per distinguere le due copie, quindi dai un alias a ciascuna.

Lo usi ogni volta che una riga di una tabella fa riferimento a un'altra riga della stessa tabella. Il caso classico: una tabella employees in cui ogni riga ha un manager_id che punta a un altro dipendente:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT * FROM employees;

Ada non ha un responsabile. Boris e Cleo fanno capo ad Ada. Diego ed Esme fanno capo a Boris. La relazione vive tutta dentro una tabella, ed è proprio lì che un self join si rende utile.

La forma di base

Per abbinare ogni dipendente al nome del suo responsabile, unisci employees con se stessa. Una copia fa la parte del "dipendente", l'altra quella del "responsabile":

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name AS dipendente,
    m.name AS responsabile
FROM employees AS e
INNER JOIN employees AS m
    ON e.manager_id = m.id;

Leggila come due tabelle che per caso condividono lo stesso spazio. e è la riga del dipendente; m è la riga del responsabile. La condizione di join e.manager_id = m.id le allinea: per ogni dipendente, trova la riga di m il cui id corrisponde al manager_id del dipendente.

Nota che Ada non compare. Il suo manager_id è NULL, e INNER JOIN scarta le righe senza corrispondenza.

Tenere le righe senza corrispondenza: LEFT JOIN

Se vuoi tutti nel risultato, comprese le persone senza responsabile, passa a LEFT JOIN:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name AS dipendente,
    m.name AS responsabile
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.id
ORDER BY e.id;

Ora Ada compare con NULL nella colonna del responsabile. Stesso meccanismo del self join, solo che il tipo di join fa quello che LEFT JOIN fa sempre: tiene ogni riga del lato sinistro e lascia vuoti dove non c'è corrispondenza.

È la forma che di solito vuoi quando mostri un elenco di persone. "Nessun responsabile" è un'informazione; eliminare la riga no.

Gli alias non sono facoltativi

Prova il join senza alias e SQLite non ha idea di cosa intendi:

SELECT name, manager_id FROM employees JOIN employees ON manager_id = id;
-- Error: ambiguous column name: name

Ogni colonna compare due volte, una per ciascuna copia della tabella, e SQLite non può scegliere. Gli alias risolvono il problema dando a ogni istanza il suo nome. Scegli alias che descrivano il ruolo della riga, non la tabella:

e e m per dipendente/responsabile.
parent e child per le gerarchie.
a e b quando confronti coppie qualsiasi.

L'alias è l'unico motivo per cui un self join si legge bene.

Trovare coppie dentro una tabella

I self join non servono solo per le gerarchie. Ogni volta che vuoi confrontare righe della stessa tabella, lo schema funziona. Ecco un elenco di prodotti in cui vogliamo tutte le coppie con lo stesso prezzo:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('Tazza',    9.99),
    ('Quaderno', 9.99),
    ('Penna',    2.50),
    ('Matita',   2.50),
    ('Lampada', 29.00);

SELECT a.name AS product_a, b.name AS product_b, a.price
FROM products AS a
INNER JOIN products AS b
    ON a.price = b.price
   AND a.id < b.id;

Due cose da notare. Primo, a.price = b.price è la vera condizione di abbinamento. Secondo, a.id < b.id è ciò che impedisce alla query di restituire ogni coppia due volte (una come (Tazza, Quaderno), un'altra come (Quaderno, Tazza)) e di abbinare ogni riga con se stessa. Vale la pena ricordare il trucco del <: salta fuori ogni volta che elenchi coppie.

Salire di due livelli

Un self join gestisce un passo in una gerarchia. Vuoi il responsabile del responsabile di ogni dipendente? Fai il join tre volte:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name      AS employee,
    m.name      AS manager,
    gm.name     AS grand_manager
FROM employees AS e
LEFT JOIN employees AS m  ON e.manager_id = m.id
LEFT JOIN employees AS gm ON m.manager_id = gm.id
ORDER BY e.id;

Ogni nuovo alias rappresenta un livello in più verso l'alto dell'albero. Funziona bene per due o tre passi, ma crolla in fretta: dovresti conoscere la profondità della gerarchia mentre scrivi la query e aggiungere un join per ogni livello. È il muro che le CTE ricorsive sono nate per abbattere.

Quando non usare un self join

Un self join è lo strumento giusto quando ti servono nel risultato colonne di entrambi i lati della relazione. Se devi solo filtrare, per esempio trovare tutti i dipendenti il cui responsabile è Ada, spesso una subquery si legge meglio:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT name
FROM employees
WHERE manager_id = (SELECT id FROM employees WHERE name = 'Ada');

Niente acrobazie con gli alias, e l'intenzione è inconfondibile. La regola pratica: vuoi nell'output dati di entrambe le righe? Self join. Ti serve solo un valore con cui confrontare? Subquery.

Per gerarchie di profondità arbitraria (organigrammi, alberi di file, commenti annidati) nessuno dei due schemi è scalabile. Lì entrano in gioco le CTE ricorsive.

Prossimo passo: le subquery

Self join e subquery risolvono problemi che si sovrappongono, e sapere quale usare ti risparmia parecchie occhiate perplesse all'SQL in futuro. La prossima pagina approfondisce le subquery (scalari, correlate e con IN) e dove ciascuna dà il meglio.

Domande frequenti

Cos'è un self join in SQLite?

Un self join è un normale JOIN in cui una tabella viene unita con se stessa. Dai alla stessa tabella due alias diversi così SQLite può trattarli come fonti di righe separate, poi abbini le righe su una colonna che collega una riga a un'altra: quasi sempre una relazione genitore/figlio come dipendente/responsabile.

Perché servono gli alias in un self join?

Senza alias, SQLite non sa a quale copia della tabella ti riferisci quando scrivi il nome di una colonna. Dare a ogni istanza il suo alias (come e per il dipendente e m per il responsabile) ti permette di scrivere e.manager_id = m.id senza ambiguità. Gli alias non sono facoltativi: senza, la query non viene nemmeno analizzata.

Quando conviene un self join e quando una subquery?

Usa un self join quando vuoi nel risultato colonne di entrambe le righe, per esempio il nome del dipendente e il nome del responsabile sulla stessa riga. Usa una subquery quando ti serve solo filtrare o cercare un singolo valore. Per le gerarchie molto profonde nessuno dei due va bene: lo strumento giusto è una CTE ricorsiva.

Concetti correlati