Menu

JOIN in PostgreSQL: INNER, LEFT, FULL, CROSS e LATERAL

Un join combina le righe di due tabelle in base a una condizione. INNER JOIN tiene le corrispondenze, LEFT JOIN tiene ogni riga della prima tabella, FULL JOIN tiene quelle di entrambe, CROSS JOIN abbina tutto e LATERAL esegue una subquery per riga.

Questa pagina include editor eseguibili: modifica, esegui e vedi subito l'output.

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

JoinRighe restituite
INNER JOINLe coppie che soddisfano la condizione
LEFT JOINOgni riga di sinistra; colonne di destra NULL dove non c'è corrispondenza
RIGHT JOINOgni riga di destra; lo specchio di LEFT JOIN
FULL JOINOgni riga di entrambi i lati, con dei NULL a riempire i buchi
CROSS JOINOgni 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 NULL per 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 > 20 esclude Grace, perché il suo totale NULL non passa la WHERE. Il join è diventato un inner join. Scrivi ON o.customer_id = c.id AND o.total > 20 per 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.

Illustrazione dei linguaggi di programmazione di Coddy

Impara a programmare con Coddy

Inizia