Un join costruisce righe a partire da due tabelle, abbinate in base a una condizione: SELECT ... FROM a JOIN b ON a.id = b.a_id. JOIN (INNER JOIN) tiene solo le righe che corrispondono, LEFT JOIN tiene ogni riga della prima tabella e FULL JOIN tiene ogni riga di entrambe.
Tornano tre righe. Grace non ha ordini e l'ordine 13 appartiene a un cliente che non esiste, quindi restano fuori entrambi. I join qui sotto usano le stesse due tabelle.
Tipi di join
| Join | Righe restituite |
|---|---|
INNER JOIN | Le coppie che soddisfano la condizione |
LEFT JOIN | Ogni riga di sinistra; colonne di destra NULL dove non c'è corrispondenza |
RIGHT JOIN | Ogni riga di destra; lo specchio di LEFT JOIN |
FULL JOIN | Ogni riga di entrambi i lati, con dei NULL a riempire i buchi |
CROSS JOIN | Ogni riga di sinistra abbinata a ogni riga di destra |
LEFT JOIN: tenere le righe senza corrispondenza
Grace compare con 0 ordini. Conta o.id, non *: count(*) conterebbe come 1 la riga di NULL di Grace.
- Righe senza corrispondenza (un anti join): aggiungi
WHERE o.id IS NULLper tenere solo i clienti senza ordini.WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)fa lo stesso e rende chiara l'intenzione. - ON vs WHERE: una condizione sulla tabella di destra va in
ON.LEFT JOIN orders o ON o.customer_id = c.id WHERE o.total > 20esclude Grace, perché il suo totaleNULLnon passa laWHERE. Il join è diventato un inner join. ScriviON o.customer_id = c.id AND o.total > 20per tenere tutti i clienti.
FULL JOIN: righe mancanti da una parte o dall'altra
FULL JOIN trova le differenze in entrambe le direzioni, utile quando confronti due elenchi:
Il risultato contiene Grace senza ordini e l'ordine 13 senza cliente. Una chiave esterna su orders.customer_id avrebbe impedito la seconda riga.
CROSS JOIN: tutte le combinazioni
CROSS JOIN non ha condizione. Abbina ogni riga di un lato a ogni riga dell'altro, quindi 3 taglie e 2 colori danno 6 righe:
I due elenchi qui sono liste VALUES a cui si dà un nome come a delle tabelle. Con tabelle vere si scrive FROM sizes CROSS JOIN colors, e FROM sizes, colors fa lo stesso. Un join la cui condizione ON è sempre vera moltiplica le righe allo stesso modo, una causa comune di query che all'improvviso restituiscono fin troppe righe.
LATERAL: una subquery per ogni riga
Una subquery in FROM di norma non vede le altre tabelle dello stesso FROM. Con LATERAL le vede, e viene valutata una volta per ogni riga della tabella che la precede. Così si risolve il "top N per gruppo":
Ada riceve i suoi due ordini più grandi, Linus il suo unico ordine e Grace una riga di NULL grazie a LEFT JOIN ... ON true. CROSS JOIN LATERAL escluderebbe i clienti senza ordini. Con un indice su orders (customer_id, total), ogni subquery legge solo poche voci dell'indice. Per una riga per gruppo, DISTINCT ON è spesso più breve.
Domande frequenti
JOIN e INNER JOIN sono la stessa cosa in PostgreSQL?
Sì. JOIN da solo significa INNER JOIN, e LEFT JOIN significa LEFT OUTER JOIN; le parole chiave INNER e OUTER sono facoltative.
Perché il mio LEFT JOIN restituisce più righe della tabella di sinistra?
Una riga di sinistra compare una volta per ogni riga di destra corrispondente. Un cliente con tre ordini compare tre volte. Aggrega con GROUP BY, oppure fai il join con una subquery che ha già una riga per chiave.
Che differenza c'è tra ON e USING?
ON accetta qualsiasi condizione. USING (id) è una scorciatoia per colonne uguali con lo stesso nome in entrambe le tabelle, e il risultato ha una sola colonna id invece di due.
L'ordine dei join conta per le prestazioni in PostgreSQL?
Di solito no. Il planner riordina gli inner join per trovare un piano economico quando una query ha fino a join_collapse_limit tabelle (8 di default). Gli outer join si possono riordinare solo in alcuni casi, perché spostarli può cambiare il risultato.