Guida SQLite interattiva: INNER JOIN

Q: Cosa fa INNER JOIN in SQLite?

INNER JOIN restituisce le righe che hanno una corrispondenza in entrambe le tabelle secondo la condizione ON. Le righe di entrambi i lati senza corrispondenza vengono scartate. È il tipo predefinito: in SQLite JOIN e INNER JOIN significano la stessa cosa.

Q: Che differenza c'è tra INNER JOIN e LEFT JOIN in SQLite?

INNER JOIN tiene solo le righe con corrispondenza. LEFT JOIN tiene tutte le righe della tabella di sinistra e riempie con NULL il lato destro quando non c'è corrispondenza. Usa INNER JOIN quando una corrispondenza mancante significa 'salta questa riga', e LEFT JOIN quando significa 'mostrala comunque'.

Q: Si possono unire tre tabelle con INNER JOIN in SQLite?

Sì: aggiungi un'altra clausola JOIN ... ON ... in sequenza. Ogni join collega il risultato parziale a una nuova tabella. Non c'è un limite rigido, ma la leggibilità cala in fretta oltre le quattro o cinque tabelle, e a quel punto spesso una CTE aiuta.

Q: Quando conviene usare USING invece di ON?

USING (column) è una scorciatoia per quando la colonna della join ha lo stesso nome in entrambe le tabelle. È più concisa e riunisce la colonna duplicata in una sola nell'output. Usa ON ogni volta che i nomi delle colonne sono diversi o ti serve una condizione più complessa.

Una join cuce insieme due tabelle

I database relazionali dividono i dati tra più tabelle di proposito: i clienti in una tabella, gli ordini in un'altra, i prodotti in una terza. Così ogni informazione sta in un solo posto. Ma quando vuoi rispondere a una domanda concreta ("quali clienti hanno ordinato cosa?"), devi rimettere insieme i pezzi. È quello che fa una join.

INNER JOIN è il cavallo da tiro. Accoppia le righe di due tabelle ovunque una condizione sia vera e scarta tutto il resto.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name) VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Chen');
INSERT INTO orders (customer_id, total) VALUES (1, 49.99), (1, 12.50), (2, 80.00);

SELECT customers.name, orders.total
FROM customers
INNER JOIN orders ON orders.customer_id = customers.id;

Tre clienti, tre ordini, ma Chen non ha ordini, quindi Chen non compare. Ecco la parte "inner": sopravvivono solo le righe con corrispondenza.

Il modello mentale: accoppia le righe, poi filtra

Leggi una INNER JOIN così: prendi ogni riga della prima tabella, guarda ogni riga della seconda e tieni la coppia solo quando la condizione ON è vera. Concettualmente è un enorme prodotto cartesiano seguito da un filtro. SQLite in realtà non fa così (usa gli indici quando può), ma il modello è corretto per prevedere cosa esce.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 1, 70), (12, 2, 15);

SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id;

Alcune buone abitudini da prendere qui:

Dai un alias alle tabelle (customers AS c) quando le citerai più di una volta. Riduce il rumore.
Qualifica le colonne (c.name, o.total) quando potrebbero plausibilmente appartenere a entrambe le tabelle.
L'ordine in ON o.customer_id = c.id non conta: c.id = o.customer_id funziona allo stesso modo.

INNER JOIN e JOIN

In SQLite (e nell'SQL standard), JOIN da solo significa INNER JOIN. La parola chiave INNER è facoltativa.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);

SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Entrambi gli stili producono lo stesso piano e le stesse righe. Scrivere per esteso INNER JOIN è un piccolo vantaggio di leggibilità nel codice che mescola tipi di join diversi: rende evidente l'intenzione accanto a una LEFT JOIN qualche riga più sotto.

ON e USING

Quando le colonne della join hanno lo stesso nome in entrambe le tabelle, USING (column) è più breve di ON a.col = b.col:

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);

SELECT name, total
FROM customers
INNER JOIN orders USING (customer_id);

USING (customer_id) fa due cose: accoppia le righe con customer_id uguale e riunisce la colonna, che compare una sola volta nel risultato. Usala quando entrambi i lati usano davvero lo stesso nome. Resta su ON quando i nomi sono diversi (orders.customer_id = customers.id) o la condizione è più di una semplice uguaglianza.

Unire tre tabelle

Concatena le join aggiungendo altre clausole JOIN ... ON .... Ognuna collega il risultato parziale a un'altra tabella.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
CREATE TABLE order_items (
    order_id INTEGER,
    product TEXT,
    quantity INTEGER
);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);
INSERT INTO order_items VALUES (10, 'Quaderno', 2), (10, 'Penna', 5), (11, 'Tazza', 1);

SELECT c.name, o.id AS order_id, i.product, i.quantity
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
INNER JOIN order_items i ON i.order_id = o.id
ORDER BY c.name, o.id;

Leggila dall'alto in basso: i clienti si collegano agli ordini, gli ordini si collegano agli articoli. Ogni riga dell'output rappresenta una combinazione di cliente, ordine e articolo. Tutto ciò che non trova corrispondenza in un punto qualsiasi della catena viene scartato: è la regola della inner join applicata a ogni passaggio.

Filtrare con WHERE

ON dice come accoppiare le righe. WHERE filtra il risultato accoppiato. Nel caso specifico delle inner join, mettere una condizione extra in ON o in WHERE produce le stesse righe, ma per convenzione le condizioni di join stanno in ON e i filtri sulle righe in WHERE.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, country TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada', 'UK'), (2, 'Boris', 'FR'), (3, 'Chen', 'UK');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 80), (12, 3, 12);

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'UK' AND o.total > 20;

Si legge come "unisci clienti e ordini, poi tieni solo i clienti del Regno Unito con un ordine sopra 20". Due ruoli, due clausole: chi rileggerà il codice fra sei mesi (magari tu) ti ringrazierà. (Quando inizierai a scrivere LEFT JOIN, la distinzione tra ON e WHERE smetterà di essere solo estetica, ma questo è il tema della prossima pagina.)

Più condizioni in ON

ON può contenere qualsiasi espressione booleana, non solo un'uguaglianza. È utile quando la relazione coinvolge più colonne, o quando vuoi filtrare il lato destro al momento della join.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL, status TEXT);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES
    (10, 1, 30, 'paid'),
    (11, 1, 50, 'cancelled'),
    (12, 2, 15, 'paid');

SELECT c.name, o.id, o.total
FROM customers c
INNER JOIN orders o
    ON o.customer_id = c.id
   AND o.status = 'paid';

L'ordine annullato sparisce perché la seconda condizione non è soddisfatta. Con una inner join potresti anche scrivere WHERE o.status = 'paid' e ottenere lo stesso risultato. La versione con ON tiene la logica di "cosa conta come corrispondenza" vicino alla join.

Errori comuni

Alcune cose che mettono in difficoltà:

Dimenticare la clausola ON. FROM a INNER JOIN b senza ON è un errore di sintassi in SQLite. (Una semplice virgola, FROM a, b, invece compila, produce un cross join e non è quasi mai quello che volevi.)
Duplicati inattesi. Se un cliente ha tre ordini, il suo nome compare tre volte nel risultato. È il comportamento corretto della join, non un bug. Aggrega con GROUP BY se vuoi una riga per cliente.
Righe mancanti. Se un cliente doveva comparire e non c'è, la condizione di join non ha trovato corrispondenza: controlla se ci sono NULL nelle colonne della join, oppure usa LEFT JOIN.
Nomi di colonna ambigui. SELECT id FROM customers JOIN orders ON ... dà errore perché entrambe le tabelle hanno un id. Qualificalo: c.id oppure o.id.

Prossimo passo: LEFT JOIN

INNER JOIN è perfetta quando una corrispondenza mancante significa "salta questa riga". Ma a volte vuoi elencare tutti i clienti, anche quelli senza ordini, con dei NULL al posto dei dati mancanti. È LEFT JOIN, il prossimo argomento.

Domande frequenti

Cosa fa INNER JOIN in SQLite?

INNER JOIN restituisce le righe che hanno una corrispondenza in entrambe le tabelle secondo la condizione ON. Le righe di entrambi i lati senza corrispondenza vengono scartate. È il tipo predefinito: in SQLite JOIN e INNER JOIN significano la stessa cosa.

Che differenza c'è tra INNER JOIN e LEFT JOIN in SQLite?

INNER JOIN tiene solo le righe con corrispondenza. LEFT JOIN tiene tutte le righe della tabella di sinistra e riempie con NULL il lato destro quando non c'è corrispondenza. Usa INNER JOIN quando una corrispondenza mancante significa 'salta questa riga', e LEFT JOIN quando significa 'mostrala comunque'.

Si possono unire tre tabelle con INNER JOIN in SQLite?

Sì: aggiungi un'altra clausola JOIN ... ON ... in sequenza. Ogni join collega il risultato parziale a una nuova tabella. Non c'è un limite rigido, ma la leggibilità cala in fretta oltre le quattro o cinque tabelle, e a quel punto spesso una CTE aiuta.

Quando conviene usare USING invece di ON?

USING (column) è una scorciatoia per quando la colonna della join ha lo stesso nome in entrambe le tabelle. È più concisa e riunisce la colonna duplicata in una sola nell'output. Usa ON ogni volta che i nomi delle colonne sono diversi o ti serve una condizione più complessa.

Concetti correlati