Modulo 4 - Join e Relazioni tra Tabelle

Modulo 4 - Join e Relazioni tra Tabelle Modulo 4 - Join e Relazioni tra Tabelle

Modulo 4: Join e Relazioni tra Tabelle

Benvenuto al Modulo 4! Ora che sai estrarre e modificare dati da tabelle singole (dai Moduli 2 e 3), passiamo alle relazioni: come combinare dati da più tabelle. Questo è il cuore dei database relazionali, dove i "join" ti permettono di unire tabelle basate su chiavi condivise (es. ID). Senza join, i database sarebbero solo liste isolate!

Per gli esempi, espandiamo il database "negozio". Aggiungiamo una tabella clienti e una ordini per simulare relazioni. Esegui questi comandi per creare e popolare:

-- Tabella clienti
CREATE TABLE clienti (
    id INT PRIMARY KEY,
    nome VARCHAR(50),
    citta VARCHAR(50)
);

INSERT INTO clienti VALUES 
(1, 'Alice', 'Roma'),
(2, 'Bob', 'Milano'),
(3, 'Carlo', 'Napoli');

-- Tabella ordini (relazionata a clienti e prodotti)
CREATE TABLE ordini (
    id INT PRIMARY KEY,
    cliente_id INT,
    prodotto_id INT,
    quantita INT,
    data_ordine DATE,
    FOREIGN KEY (cliente_id) REFERENCES clienti(id),
    FOREIGN KEY (prodotto_id) REFERENCES prodotti(id)
);

INSERT INTO ordini VALUES 
(1, 1, 1, 1, '2023-01-10'),  -- Alice compra Laptop
(2, 2, 2, 2, '2023-02-15'),  -- Bob compra 2 Smartphone
(3, 1, 3, 3, '2023-03-20'),  -- Alice compra 3 Libri SQL
(4, 3, 4, 1, '2023-04-25'),  -- Carlo compra Tazza
(5, 2, 5, 1, '2023-05-30');  -- Bob compra Monitor

-- Tabella clienti
CREATE TABLE clienti (
    id INT PRIMARY KEY,
    nome VARCHAR(50),
    citta VARCHAR(50)
);

INSERT INTO clienti VALUES 
(1, 'Alice', 'Roma'),
(2, 'Bob', 'Milano'),
(3, 'Carlo', 'Napoli');

-- Tabella ordini (relazionata a clienti e prodotti)
CREATE TABLE ordini (
    id INT PRIMARY KEY,
    cliente_id INT,
    prodotto_id INT,
    quantita INT,
    data_ordine DATE,
    FOREIGN KEY (cliente_id) REFERENCES clienti(id),
    FOREIGN KEY (prodotto_id) REFERENCES prodotti(id)
);

INSERT INTO ordini VALUES 
(1, 1, 1, 1, '2023-01-10'),  -- Alice compra Laptop
(2, 2, 2, 2, '2023-02-15'),  -- Bob compra 2 Smartphone
(3, 1, 3, 3, '2023-03-20'),  -- Alice compra 3 Libri SQL
(4, 3, 4, 1, '2023-04-25'),  -- Carlo compra Tazza
(5, 2, 5, 1, '2023-05-30');  -- Bob compra Monitor

(Assumi che la tabella prodotti dal Modulo 2 esista. La FOREIGN KEY enforces relazioni.)

Approfondiamo i punti con sintassi, esempi e consigli.

1. Tipi di Join: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN

Panoramica: I join combinano righe da due+ tabelle basate su una condizione (solitamente uguaglianza di chiavi). Immagina di "unire" colonne da tabelle diverse.

INNER JOIN: Restituisce solo righe con match in entrambe le tabelle (intersezione).

Sintassi: SELECT colonne FROM tabella1 INNER JOIN tabella2 ON condizione;

Esempio:

SELECT c.nome AS cliente, p.nome AS prodotto, o.quantita
FROM ordini o
INNER JOIN clienti c ON o.cliente_id = c.id
INNER JOIN prodotti p ON o.prodotto_id = p.id;

SELECT c.nome AS cliente, p.nome AS prodotto, o.quantita
FROM ordini o
INNER JOIN clienti c ON o.cliente_id = c.id
INNER JOIN prodotti p ON o.prodotto_id = p.id;

Risultato (tutti gli ordini con match):

cliente	prodotto	quantita
Alice	Laptop	1
Bob	Smartphone	2
Alice	Libro SQL	3
Carlo	Tazza	1
Bob	Monitor	1

LEFT JOIN: Restituisce tutte le righe dalla tabella sinistra, e match dalla destra (NULL se no match).
- Esempio: LEFT JOIN clienti c ON o.cliente_id = c.id; (tutti ordini, anche senza cliente – ma nel nostro caso tutti matchano).
RIGHT JOIN: Opposto: tutte dalla destra, match dalla sinistra.
- Esempio: Per vedere tutti i clienti, anche senza ordini: SELECT c.nome, o.id FROM clienti c RIGHT JOIN ordini o ON c.id = o.cliente_id;.

FULL JOIN: Tutte le righe da entrambe, con NULL dove no match (unione completa).

Esempio: Aggiungi un cliente senza ordini (INSERT INTO clienti VALUES (4, 'Dario', 'Firenze');), poi:

SELECT c.nome, p.nome
FROM clienti c
FULL JOIN ordini o ON c.id = o.cliente_id
FULL JOIN prodotti p ON o.prodotto_id = p.id;

SELECT c.nome, p.nome
FROM clienti c
FULL JOIN ordini o ON c.id = o.cliente_id
FULL JOIN prodotti p ON o.prodotto_id = p.id;

(Mostra tutto, con NULL per mismatch.)

Consigli: Usa alias (es. o per ordini) per brevità. INNER è il default se ometti "INNER".

2. Self-Join e Cross-Join

Self-Join: Join di una tabella con se stessa (utile per gerarchie o confronti).

Esempio: Supponi una tabella dipendenti con colonna manager_id (riferita a id). Per trovare dipendenti e manager:

SELECT d.nome AS dipendente, m.nome AS manager
FROM dipendenti d
INNER JOIN dipendenti m ON d.manager_id = m.id;

SELECT d.nome AS dipendente, m.nome AS manager
FROM dipendenti d
INNER JOIN dipendenti m ON d.manager_id = m.id;

Cross-Join: Prodotto cartesiano – ogni riga sinistra con ogni destra (no condizione). Utile per combinazioni, ma attenzione: esplode con grandi tabelle!
- Esempio:
```
SELECT c.nome, p.nome
FROM clienti c
CROSS JOIN prodotti p;
```
```
SELECT c.nome, p.nome
FROM clienti c
CROSS JOIN prodotti p;
```
  (Tutti i clienti con tutti i prodotti – 4 clienti x 5 prodotti = 20 righe.)
Consigli: Self-join usa alias diversi. Cross-join è raro; usa per test o generazione dati.

3. Usare ON e USING per Condizioni di Join

ON: Specifica la condizione esplicitamente (flessibile per != o complessi).
- Esempio: ON o.cliente_id = c.id AND o.quantita > 1; (join con filtro extra).
USING: Abbrevia se colonne hanno stesso nome (es. se entrambe hanno id_cliente).
- Esempio: INNER JOIN clienti USING (id_cliente); (automatico ON id_cliente = id_cliente).
Consigli: ON è più comune e leggibile. Per join multipli, catena come negli esempi.

Obiettivi del Modulo

Combinare dati da più tabelle per query reali (es. report vendite).
Capire differenze tra join per evitare perdite dati.
Progettare query con relazioni, rispettando integrità (FOREIGN KEY).

Esercizi Suggeriti

Esercizio 1: Crea le tabelle clienti e ordini. Scrivi un INNER JOIN per elencare clienti e i prodotti ordinati, filtrando per quantita >1.
Esercizio 2: Usa LEFT JOIN per mostrare tutti i clienti, inclusi quelli senza ordini (aggiungi un cliente extra).
Esercizio 3: Prova un self-join su una nuova tabella amici (id, nome, amico_id) per trovare catene di amici.
Bonus: Cross-join clienti e prodotti, poi filtra con WHERE per "offerte potenziali".

English version

Module 4: Joins and Relationships between Tables

Welcome to Module 4! Now that you know how to extract and edit data from single tables (from Modules 2 and 3), let's move on to relationships: how to combine data from multiple tables. This is the heart of relational databases, where "joins" allow you to join tables based on shared keys (e.g. IDs). Without joins, databases would just be isolated lists!

For examples, let's expand the "store" database. Let's add a customers and an orders table to simulate relationships. Run these commands to create and populate:

-- Customer table
CREATE TABLE customers ( 
id INT PRIMARY KEY, 
name VARCHAR(50), 
city VARCHAR(50)
);

INSERT INTO customer VALUES
(1, 'Alice', 'Roma'),
(2, 'Bob', 'Milan'),
(3, 'Carlo', 'Napoli');

-- Order table (related to customers and products)
CREATE TABLE orders ( 
id INT PRIMARY KEY, 
customer_id INT, 
product_id INT, 
INT quantity, 
order_date DATES, 
FOREIGN KEY (customer_id) REFERENCES customers(id), 
FOREIGN KEY (product_id) REFERENCES products(id)
);

INSERT INTO orders VALUES
(1, 1, 1, 1, '2023-01-10'), -- Alice buys Laptop
(2, 2, 2, 2, '2023-02-15'), -- Bob buys 2 Smartphones
(3, 1, 3, 3, '2023-03-20'), -- Alice buys 3 SQL Books
(4, 3, 4, 1, '2023-04-25'), -- Carlo buys Tazza
(5, 2, 5, 1, '2023-05-30'); -- Bob buys Monitor

-- Customer table
CREATE TABLE customers ( 
id INT PRIMARY KEY, 
name VARCHAR(50), 
city VARCHAR(50)
);

INSERT INTO customer VALUES
(1, 'Alice', 'Roma'),
(2, 'Bob', 'Milan'),
(3, 'Carlo', 'Napoli');

-- Order table (related to customers and products)
CREATE TABLE orders ( 
id INT PRIMARY KEY, 
customer_id INT, 
product_id INT, 
INT quantity, 
order_date DATES, 
FOREIGN KEY (customer_id) REFERENCES customers(id), 
FOREIGN KEY (product_id) REFERENCES products(id)
);

INSERT INTO orders VALUES
(1, 1, 1, 1, '2023-01-10'), -- Alice buys Laptop
(2, 2, 2, 2, '2023-02-15'), -- Bob buys 2 Smartphones
(3, 1, 3, 3, '2023-03-20'), -- Alice buys 3 SQL Books
(4, 3, 4, 1, '2023-04-25'), -- Carlo buys Tazza
(5, 2, 5, 1, '2023-05-30'); -- Bob buys Monitor

(Assume that the products table from Module 2 exists. The FOREIGN KEY enforces relationships.)

We delve deeper into the points with syntax, examples and advice.

1. Types of Joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN

Overview: Joins combine rows from two+ tables based on a condition (usually key equality). Imagine "merging" columns from different tables.
INNER JOIN: Returns only rows with matches in both tables (intersection).
Syntax: SELECT columns FROM table1 INNER JOIN table2 ON condition;
Example:

SELECT c.customer AS name, p.product AS name, o.quantity 
FROM orders o 
INNER JOIN customers c ON o.customer_id = c.id 
INNER JOIN products p ON o.product_id = p.id;

SELECT c.customer AS name, p.product AS name, o.quantity 
FROM orders o 
INNER JOIN customers c ON o.customer_id = c.id 
INNER JOIN products p ON o.product_id = p.id;

Result (all orders with match):

customer	product	quantity
Alice	Laptops	1
Bob	Smartphones	2
Alice	SQL Book	3
Carlo	Mug	1
Bob	Monitors	1

LEFT JOIN: Returns all rows from the left table, and match from the right (NULL if no match).
Example: LEFT JOIN customers c ON o.customer_id = c.id; (all orders, even without customers – but in our case they all match).
RIGHT JOIN: Opposite: all from the right, match from the left.
Example: To see all customers, even without orders: SELECT c.name, o.id FROM customers c RIGHT JOIN orders or ON c.id = o.customer_id;.
FULL JOIN: All lines from both, with NULL where no match (full join).
Example: Add a customer without orders (INSERT INTO customers VALUES (4, 'Dario', 'Firenze');), then:

SELECT c.name, p.name 
FROM customers c 
FULL JOIN orders or ON c.id = o.customer_id 
FULL JOIN products p ON o.product_id = p.id;

SELECT c.name, p.name 
FROM customers c 
FULL JOIN orders or ON c.id = o.customer_id 
FULL JOIN products p ON o.product_id = p.id;

(Show all, with NULL for mismatch.)

Advice: Use aliases (e.g. or for orders) for brevity. INNER is the default if you omit "INNER".

2. Self-Join and Cross-Join

Self-Join: Join a table with itself (useful for hierarchies or comparisons).
Example: Suppose an employees table with column manager_id (referring to id). To find employees and managers:

SELECT d.employee AS name, m.manager AS name 
FROM employees d 
INNER JOIN employees m ON d.manager_id = m.id;

SELECT d.employee AS name, m.manager AS name 
FROM employees d 
INNER JOIN employees m ON d.manager_id = m.id;

Cross-Join: Cartesian product – every left row with every right (no condition). Useful for combinations, but be careful: it explodes with large tables!
Example:

SELECT c.name, p.name 
FROM customers c 
CROSS JOIN products p;

SELECT c.name, p.name 
FROM customers c 
CROSS JOIN products p;

(All customers with all products – 4 customers x 5 products = 20 rows.)

Tips: Self-join uses different aliases. Cross-join is rare; use for testing or data generation.

3. Use ON and USING for Join Conditions

ON: Specify the condition explicitly (flexible for != or complexes).
Example: ON o.customer_id = c.id AND o.quantity > 1; (join with extra filter).
USING: Shorten if columns have the same name (e.g. if both have customer_id).
Example: INNER JOIN customers USING (customer_id); (automatic ON customer_id = customer_id).
Tips: ON is more common and readable. For multiple joins, chain as in the examples.

Module Objectives

Combine data from multiple tables for real queries (e.g. sales reports).
Understand differences between joins to avoid data loss.
Design queries with relationships, respecting integrity (FOREIGN KEY).

Suggested Exercises

Exercise 1: Create the tabs elle customers and orders. Write an INNER JOIN to list customers and the products ordered, filtering by quantity >1.
Exercise 2: Use LEFT JOIN to show all customers, including those without orders (add an extra customer).
Exercise 3: Try a self-join on a new friends table (id, name, friend_id) to find chains of friends.
Bonus: Cross-join customers and products, then filter with WHERE for "potential offers".

Puoi seguire anche il mio canale YouTube https://www.youtube.com/channel/UCoOgys_fRjBrHmx2psNALow/ con tanti video interessanti

I consigli che offriamo sono di natura generale. Non sono consigli legali o professionali. Quello che può funzionare per una persona potrebbe non essere adatto a un’altra, e dipende da molte variabili.

Magnetismo

Cerca nel blog

Modulo 4 - Join e Relazioni tra Tabelle

Modulo 4: Join e Relazioni tra Tabelle

1. Tipi di Join: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN

2. Self-Join e Cross-Join

3. Usare ON e USING per Condizioni di Join

Obiettivi del Modulo

Esercizi Suggeriti

English version

Module 4: Joins and Relationships between Tables

1. Types of Joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN

2. Self-Join and Cross-Join

3. Use ON and USING for Join Conditions

Module Objectives

Suggested Exercises

Commenti

Posta un commento