JOIN: Leggere Dati da Tabelle Collegate

Bentornato.

Nella lezione precedente hai imparato le relazioni.

Hai imparato:

• che cos’è una primary key;
• che cos’è una foreign key;
• perché le tabelle dovrebbero essere collegate;
• come funzionano le relazioni one-to-many;
• come PostgreSQL protegge le relazioni.

Molto bene.

Ora sappiamo come collegare le tabelle.

Ma c’è ancora un problema.

Quando facciamo una query sui prodotti, vediamo questo:

Laptop | category_id = 1
Mouse  | category_id = 1
Chair  | category_id = 2

Utile.

Ma non molto amichevole.

Gli umani non vogliono leggere category IDs.

Gli umani vogliono nomi.

Vogliamo questo:

Laptop | Electronics
Mouse  | Electronics
Chair  | Furniture

Qui entra in gioco JOIN.

JOIN ci permette di leggere dati da più tabelle collegate in una sola query.

È qui che i database relazionali iniziano a sembrare potenti.

E leggermente eleganti.

Come SQL con la giacca.

Cosa Imparerai

In questa lezione imparerai:

• cosa fa JOIN;
• perché JOIN è utile;
• come usare INNER JOIN;
• come collegare tabelle usando primary keys e foreign keys;
• come selezionare colonne da più tabelle;
• come usare alias delle tabelle;
• come evitare nomi di colonne confusi;
• come usare LEFT JOIN;
• la differenza tra INNER JOIN e LEFT JOIN;
• come collegare più di due tabelle;
• errori comuni con JOIN.

Alla fine di questa lezione saprai combinare dati da tabelle collegate.

Questo è un passo molto importante.

Senza JOIN, le relazioni sono nascoste.

Con JOIN, le relazioni diventano leggibili.

Il database smette di mostrarti numeri segreti.

E inizia a mostrarti informazioni utili.

Molto gentile.

Molto PostgreSQL.

Prepara il Database

Apri PostgreSQL:

sudo -iu postgres psql

Connettiti al database:

\c learning_postgresql

Se non hai questo database, crealo:

CREATE DATABASE learning_postgresql;

Poi connettiti:

\c learning_postgresql

Ora rimuovi le vecchie tabelle se esistono:

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;

Eliminiamo prima products perché dipende da categories.

PostgreSQL tiene alle relazioni.

Come un genitore database severo.

Crea le Tabelle

Crea la tabella categories:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL
);

Crea la tabella products:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC(10, 2) CHECK (price >= 0),
  category_id INTEGER REFERENCES categories(id)
);

Ora abbiamo due tabelle collegate.

categories.id è la primary key.

products.category_id è la foreign key.

Questa è la relazione:

categories.id -> products.category_id

Una categoria può avere molti prodotti.

Molto normale.

Molto utile.

Molto database.

Inserisci Dati

Inserisci categorie:

INSERT INTO categories (name)
VALUES
  ('Electronics'),
  ('Furniture'),
  ('Office');

Inserisci prodotti:

INSERT INTO products (name, price, category_id)
VALUES
  ('Laptop', 900.00, 1),
  ('Mouse', 25.00, 1),
  ('Keyboard', 70.00, 1),
  ('Desk Chair', 150.00, 2),
  ('Bookshelf', 120.00, 2),
  ('Notebook', 5.00, 3),
  ('Pen', 2.00, 3);

Controlla entrambe le tabelle:

SELECT * FROM categories;
SELECT * FROM products;

La tabella products mostra category_id.

È corretto.

Ma non è bello.

Il database conosce il collegamento.

Ora dobbiamo chiedergli di mostrarlo.

Questo è il lavoro di JOIN.

Il Problema Senza JOIN

Esegui:

SELECT * FROM products;

Vedrai qualcosa tipo:

id | name       | price  | category_id
---|------------|--------|------------
1  | Laptop     | 900.00 | 1
2  | Mouse      | 25.00  | 1
3  | Keyboard   | 70.00  | 1
4  | Desk Chair | 150.00 | 2

Questo è utile per PostgreSQL.

Ma per un umano, category_id = 1 non è molto utile.

Dobbiamo vedere il nome della categoria.

Il nome della categoria è in un’altra tabella.

Quindi dobbiamo combinare:

products
categories

È esattamente quello che fa JOIN.

Che Cos’è JOIN?

JOIN combina righe da due o più tabelle.

Usa una relazione tra le tabelle.

Relazione di esempio:

products.category_id = categories.id

Questo significa:

Un prodotto appartiene a una categoria quando il suo category_id corrisponde all'id della categoria.

Una query base con JOIN appare così:

SELECT products.name, categories.name
FROM products
JOIN categories
ON products.category_id = categories.id;

Questo dice a PostgreSQL:

Parti da products.
Collega categories.
Abbina le righe dove products.category_id è uguale a categories.id.
Mostra nome prodotto e nome categoria.

Questo è il cuore di JOIN.

Le tabelle si incontrano.

Le righe si abbinano.

I dati diventano leggibili.

Molto civile.

Il Tuo Primo JOIN

Esegui questa query:

SELECT products.name, categories.name
FROM products
JOIN categories
ON products.category_id = categories.id;

Dovresti vedere qualcosa tipo:

name       | name
-----------|-------------
Laptop     | Electronics
Mouse      | Electronics
Keyboard   | Electronics
Desk Chair | Furniture
Bookshelf  | Furniture
Notebook   | Office
Pen        | Office

Funziona.

Ma c’è un piccolo problema.

Entrambe le colonne si chiamano name.

Questo può confondere.

Un name è il nome del prodotto.

L’altro name è il nome della categoria.

PostgreSQL lo permette.

Il tuo cervello forse no.

Quindi usiamo gli alias.

Alias delle Colonne

Un alias di colonna dà un nome migliore a una colonna del risultato.

Usa AS.

Esempio:

SELECT
  products.name AS product_name,
  categories.name AS category_name
FROM products
JOIN categories
ON products.category_id = categories.id;

Ora il risultato è più chiaro:

product_name | category_name
-------------|---------------
Laptop       | Electronics
Mouse        | Electronics
Keyboard     | Electronics
Desk Chair   | Furniture
Bookshelf    | Furniture
Notebook     | Office
Pen          | Office

Molto meglio.

Gli alias rendono i risultati più facili da leggere.

Il database fa lo stesso lavoro.

Ma gli umani sono meno confusi.

Questo è sempre positivo.

Di solito.

Selezionare Più Colonne

Puoi selezionare più colonne da entrambe le tabelle.

Esempio:

SELECT
  products.id,
  products.name AS product_name,
  products.price,
  categories.name AS category_name
FROM products
JOIN categories
ON products.category_id = categories.id;

Questo dà:

id | product_name | price  | category_name
---|--------------|--------|---------------
1  | Laptop       | 900.00 | Electronics
2  | Mouse        | 25.00  | Electronics
3  | Keyboard     | 70.00  | Electronics

Ora abbiamo dati utili del prodotto e dati leggibili della categoria.

Questo è molto comune nelle applicazioni reali.

Un utente non vuole:

category_id = 1

Un utente vuole:

Electronics

La tua applicazione può mostrare dati migliori perché SQL ha fatto il join.

Buon SQL rende il frontend più felice.

E un frontend felice si lamenta meno.

A volte.

Alias delle Tabelle

Scrivere nomi completi delle tabelle può diventare lungo.

Questo:

SELECT
  products.name AS product_name,
  categories.name AS category_name
FROM products
JOIN categories
ON products.category_id = categories.id;

funziona.

Ma possiamo accorciarlo con alias delle tabelle:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

Qui:

• p significa products;
• c significa categories.

È più corto.

Molto comune.

SQL professionale usa spesso gli alias.

Non perché gli sviluppatori amino lettere misteriose.

Beh, forse un po’.

Ma soprattutto perché le query diventano più facili da scrivere.

JOIN con WHERE

Puoi usare WHERE con JOIN.

Esempio:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
WHERE c.name = 'Electronics';

Questo restituisce solo prodotti Electronics.

Risultato di esempio:

Laptop   | 900.00 | Electronics
Mouse    | 25.00  | Electronics
Keyboard | 70.00  | Electronics

Questo è potente.

Stiamo filtrando usando dati della tabella collegata.

La tabella product non salva Electronics.

Salva category_id.

Ma con JOIN, possiamo filtrare per nome categoria.

Molto utile.

Molto reale.

JOIN con ORDER BY

Puoi anche ordinare risultati collegati.

Esempio:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY p.price DESC;

Questo ordina i prodotti dal più costoso al più economico.

Puoi anche ordinare per nome categoria:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY c.name ASC, p.name ASC;

Questo ordina prima per categoria.

Poi per nome prodotto.

Molto ordinato.

Molto organizzato.

Come un database che ha pulito la sua stanza.

Raro ma bellissimo.

INNER JOIN

Quando scrivi:

JOIN categories

PostgreSQL lo tratta come:

INNER JOIN categories

Quindi queste due query sono equivalenti:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

e:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
INNER JOIN categories AS c
ON p.category_id = c.id;

INNER JOIN restituisce solo righe che hanno una corrispondenza in entrambe le tabelle.

Quindi se un prodotto non ha una categoria corrispondente, non appare.

Ma siccome usiamo una foreign key, PostgreSQL normalmente impedisce prodotti con categorie non valide.

Quindi INNER JOIN spesso è esattamente quello che serve.

Pulito.

Severo.

Utile.

LEFT JOIN

LEFT JOIN restituisce tutte le righe della tabella a sinistra.

Anche se non c’è una corrispondenza nella tabella a destra.

Esempio:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
LEFT JOIN categories AS c
ON p.category_id = c.id;

Questo significa:

Mostra tutti i prodotti.
Se esiste una categoria, mostrala.
Se non esiste, mostra NULL.

Nella nostra tabella attuale, ogni prodotto ha una categoria valida.

Quindi il risultato sembra uguale a INNER JOIN.

Ma LEFT JOIN diventa utile quando alcune righe potrebbero non avere dati collegati.

Per esempio:

users e profiles
customers e orders
posts e comments

A volte vuoi mostrare tutti gli utenti, anche quelli senza profilo.

Ecco dove LEFT JOIN è utile.

INNER JOIN vs LEFT JOIN

Regola semplice:

Usa INNER JOIN quando vuoi solo righe con corrispondenza.

Usa LEFT JOIN quando vuoi tutte le righe della prima tabella, anche se la seconda tabella non ha corrispondenza.

Esempio:

INNER JOIN:
Solo prodotti con categorie.

LEFT JOIN:
Tutti i prodotti, con categoria se disponibile.

Pensa a INNER JOIN come a una festa severa:

Niente match, niente ingresso.

Pensa a LEFT JOIN come a una festa più gentile:

Entra comunque. Mostreremo NULL se manca il tuo amico.

Le feste dei database sono strane.

Ma educative.

Crea un Esempio per LEFT JOIN

Permettiamo un prodotto senza categoria.

Prima eliminiamo la vecchia tabella products:

DROP TABLE IF EXISTS products;

Creiamola di nuovo:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC(10, 2) CHECK (price >= 0),
  category_id INTEGER REFERENCES categories(id)
);

Questa tabella permette ancora category_id vuoto, perché non abbiamo scritto NOT NULL.

Inserisci prodotti:

INSERT INTO products (name, price, category_id)
VALUES
  ('Laptop', 900.00, 1),
  ('Mouse', 25.00, 1),
  ('Mystery Box', 50.00, NULL);

Ora esegui INNER JOIN:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
INNER JOIN categories AS c
ON p.category_id = c.id;

Mystery Box non appare.

Perché?

Perché non ha una categoria corrispondente.

Ora esegui LEFT JOIN:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
LEFT JOIN categories AS c
ON p.category_id = c.id;

Ora Mystery Box appare.

La sua categoria è NULL.

Questa è la differenza.

Parola piccola.

Comportamento grande.

JOIN di Più di Due Tabelle

Puoi collegare più di due tabelle.

Immagina:

customers
orders
products

Ma facciamo un esempio più semplice:

authors
posts
comments

Per ora useremo:

customers
orders

Elimina vecchie tabelle se servono:

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

Crea customers:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

Crea orders:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  total NUMERIC(10, 2) CHECK (total >= 0),
  customer_id INTEGER REFERENCES customers(id)
);

Inserisci customers:

INSERT INTO customers (name)
VALUES
  ('Anna'),
  ('Marco'),
  ('Sofia');

Inserisci orders:

INSERT INTO orders (total, customer_id)
VALUES
  (49.99, 1),
  (120.00, 1),
  (35.50, 2);

Ora fai il join:

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

Risultato:

order_id | customer_name | total
---------|---------------|-------
1        | Anna          | 49.99
2        | Anna          | 120.00
3        | Marco         | 35.50

Nota che Sofia non ha orders.

Con INNER JOIN, Sofia non appare.

Con LEFT JOIN, possiamo mostrarla.

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

Ora Sofia appare con total NULL.

Molto utile per i report.

Per esempio:

Mostra tutti i clienti, anche quelli senza ordini.

Questa è una situazione classica da LEFT JOIN.

Errori Comuni

Dimenticare la Condizione ON

Sbagliato:

SELECT *
FROM products
JOIN categories;

Non è quello che vuoi.

Devi dire a PostgreSQL come le tabelle sono collegate.

Corretto:

SELECT *
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

La condizione ON è il ponte.

Senza di essa, non stai collegando bene le tabelle.

Stai invitando il caos a cena.

Collegare le Colonne Sbagliate

Sbagliato:

ON p.id = c.id

Questo può abbinare per caso righe con lo stesso ID.

Ma quella non è la relazione.

Corretto:

ON p.category_id = c.id

La foreign key punta alla primary key.

Usa la relazione vera.

Non collegare colonne solo perché i numeri sembrano amichevoli.

I numeri mentono.

Soprattutto nei database.

Non Usare Alias

Questo funziona:

SELECT products.name, categories.name
FROM products
JOIN categories
ON products.category_id = categories.id;

Ma questo è più chiaro:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

Gli alias rendono le query più leggibili.

E SQL leggibile ha meno probabilità di tradirti a mezzanotte.

Selezionare Colonne Ambigue

Questo può creare confusione:

SELECT name
FROM products
JOIN categories
ON products.category_id = categories.id;

Entrambe le tabelle hanno una colonna name.

PostgreSQL potrebbe lamentarsi o la query potrebbe essere poco chiara.

Meglio:

SELECT
  products.name AS product_name,
  categories.name AS category_name
FROM products
JOIN categories
ON products.category_id = categories.id;

Sii sempre chiaro quando le colonne hanno lo stesso nome.

Al database piace la chiarezza.

Anche agli umani.

Più o meno.

Pratica

Scrivi queste query.

Mostra nomi prodotti con nomi categorie:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

Mostra nomi prodotti, prezzi e nomi categorie:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

Mostra solo prodotti Electronics:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
WHERE c.name = 'Electronics';

Mostra prodotti ordinati per prezzo:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY p.price DESC;

Mostra tutti i prodotti, anche quelli senza categorie:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
LEFT JOIN categories AS c
ON p.category_id = c.id;

Esegui le query.

Modificale.

Rompile.

Sistemele.

È ancora il modo migliore per imparare.

SQL non entra nel cervello solo con la teoria.

Entra tramite errori.

Errori molto educativi.

Mini Challenge

Crea due tabelle:

authors
posts

Regole:

• un author può avere molti posts;
• ogni post appartiene a un author;
• il nome dell’author è obbligatorio;
• il titolo del post è obbligatorio;
• il contenuto del post può essere testo lungo.

Crea le tabelle:

CREATE TABLE authors (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  content TEXT,
  author_id INTEGER REFERENCES authors(id)
);

Inserisci dati:

INSERT INTO authors (name)
VALUES
  ('Anna'),
  ('Marco'),
  ('Sofia');

INSERT INTO posts (title, content, author_id)
VALUES
  ('First Post', 'Hello from PostgreSQL.', 1),
  ('SQL Notes', 'JOIN makes tables readable.', 1),
  ('Database Life', 'Data wants structure.', 2);

Ora scrivi query per:

• mostrare tutti i posts con nomi degli authors;
• mostrare solo posts di Anna;
• mostrare authors e i loro posts usando LEFT JOIN;
• mostrare authors anche se non hanno posts;
• ordinare posts per title.

Questa challenge è molto vicina ai veri sistemi blog.

Authors.

Posts.

Relazioni.

Query.

Molto reale.

Niente magia.

Solo chiavi e joins.

Bellissimo piccolo meccanismo.

Riepilogo

Oggi hai imparato:

• JOIN combina dati da tabelle collegate;
• JOIN usa relazioni tra tabelle;
• INNER JOIN restituisce solo righe corrispondenti;
• LEFT JOIN restituisce tutte le righe della tabella a sinistra;
• le foreign keys aiutano a collegare tabelle correttamente;
• ON definisce come le tabelle sono collegate;
• gli alias rendono le query più leggibili;
• gli alias delle colonne rendono i risultati più chiari;
• WHERE può filtrare risultati collegati;
• ORDER BY può ordinare risultati collegati;
• collegare colonne sbagliate produce dati sbagliati;
• JOIN rende visibili le relazioni.

Questo è un passo enorme.

Ora le tue tabelle non sono più isolate.

Puoi collegare dati.

Puoi mostrare risultati leggibili.

Puoi smettere di mostrare agli utenti ID misteriosi e iniziare a mostrare nomi veri.

Questo è progresso database.

Query piccola.

Grande upgrade.

Prossima Lezione

Nella prossima lezione impareremo le aggregate functions.

Conteremo righe.

Calcoleremo totali.

Troveremo medie.

Troveremo valori minimi e massimi.

In altre parole, inizieremo a fare domande tipo:

Quanti prodotti abbiamo?
Qual è il valore totale di tutti gli orders?
Qual è il prezzo medio?
Quale prodotto è il più costoso?

PostgreSQL risponderà.

Educatamente.

Se scriviamo bene la query.