Corso MariaDB / Evitare totali duplicati nei JOIN tra più tabelle
Prima di aggregare un JOIN di più tabelle, identificare che cosa rappresenta ogni riga unita. Collegare ordini e righe ordine ripete gli attributi dell'ordine per ogni dettaglio: importi per ordine come le spese di spedizione vanno quindi calcolati a livello di ordine oppure uniti dopo aver riassunto i dettagli.
Distinguere fatti del cliente, dell'ordine, della riga e del prodotto
Una schermata con nome del cliente, data dell'ordine, nome del prodotto e quantità unisce quattro tabelle. La riga finale rappresenta una riga ordine, non un ordine completo. Trascurare questa distinzione duplica non soltanto il conteggio degli ordini, ma anche costi e sconti applicati una volta per ordine.
Il livello di dettaglio è il significato di una riga per un determinato calcolo. La guida alle relazioni tra tabelle spiega le chiavi tra gli oggetti; la guida LEFT JOIN aiuta a scegliere quali righe iniziali conservare quando mancano corrispondenze.
| Valore | Livello originale | Dopo l'unione con le righe ordine |
|---|---|---|
| customer_name | Cliente | Ripetuto per ogni dettaglio corrispondente |
| shipping_fee | Ordine | Ripetuto per ogni riga dell'ordine |
| quantity × unit_price | Riga ordine | Sommabile come importo della merce |
| Prezzo attuale del prodotto | Prodotto | Non deve sostituire il prezzo storico di acquisto |
Il riferimento della sintassi JOIN MariaDB descrive le unioni utilizzate negli esempi.
Preparare prezzi di acquisto e spese di spedizione
Collegarsi al server di studio come course_user e selezionare shopcourse. La guida al client e ai file SQL UTF-8 spiega il salvataggio del codice e l'esecuzione con SOURCE C:/mariadb-lab/lesson.sql;. Sostituire il percorso con quello reale ed eseguire SQL nel client MariaDB.
Prima di creare c28_customers, c28_products, c28_orders e c28_items, controllare SELECT VERSION(), DATABASE(); e SHOW TABLES;. Se gli oggetti esistono già, esaminarne prima definizione e righe. Usare soltanto oggetti di esercitazione eliminabili e non sovrascrivere tabelle applicative. Tutte e quattro le tabelle sono incluse per rendere l'esempio indipendente.
USE shopcourse;
CREATE TABLE c28_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c28_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c28_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c28_order_customer FOREIGN KEY (customer_id)
REFERENCES c28_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c28_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c28_item_order FOREIGN KEY (order_id)
REFERENCES c28_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c28_item_product FOREIGN KEY (product_id)
REFERENCES c28_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c28_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c28_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c28_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c28_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
Nomi e città coreani nel codice restano quelli originali per mantenere coerenti i dati UTF-8. I prodotti da 1 a 4 sono quaderno, penna, tazza e lampada. I clienti 1 e 3 hanno il valore di città Seoul, il cliente 2 Busan e il cliente 4 Daejeon. Confrontare tramite gli ID e conservare le stringhe come fornite.
Gli ordini pagati 101, 102 e 103 hanno rispettivamente due, una e due righe: cinque righe ordine pagate. Ogni ordine ha spese di spedizione pari a 1000. Questi piccoli conteggi consentono di seguire quante volte un valore viene ripetuto dopo l'unione.
Unire quattro tabelle tramite le chiavi delle relazioni
SELECT o.order_id,c.customer_name,p.product_name,i.quantity,i.unit_price,
i.quantity*i.unit_price AS line_total
FROM c28_orders o JOIN c28_customers c ON c.customer_id=o.customer_id
JOIN c28_items i ON i.order_id=o.order_id
JOIN c28_products p ON p.product_id=i.product_id
WHERE o.status='paid' ORDER BY o.order_id,i.product_id;
La merce dell'ordine 101 vale 7000 + 4500 = 11500, quella dell'ordine 102 vale 8000 e quella dell'ordine 103 vale 3500 + 16000 = 19500. Il totale della merce pagata è 39000. Leggere i nomi dalla tabella prodotti, ma calcolare gli importi con unit_price della riga ordine: una successiva variazione del prezzo di catalogo non modifica così i totali storici.
I JOIN seguono le relazioni cliente-ordine-riga-prodotto. Mostrare order_id e product_id durante il controllo delle ripetizioni: un ordine con due righe deve comparire due volte al livello del dettaglio.
Osservare le spese di spedizione conteggiate più volte
SELECT COUNT(*) AS joined_rows,COUNT(DISTINCT o.order_id) AS orders_count,
SUM(o.shipping_fee) AS duplicated_shipping,SUM(i.quantity*i.unit_price) AS sales
FROM c28_orders o JOIN c28_items i ON i.order_id=o.order_id
WHERE o.status='paid';
Il risultato è joined_rows=5, orders_count=3, duplicated_shipping=5000.00 e sales=39000.00. La spedizione corretta è 3000.00 per tre ordini: l'unione con cinque dettagli ha aggiunto due copie del costo. COUNT(DISTINCT order_id) corregge il conteggio degli ordini, ma non corregge automaticamente le altre somme.
Anche SUM(DISTINCT shipping_fee) è sbagliato per questo scopo: il valore 1000 uguale nei tre ordini viene considerato una volta e produce soltanto 1000. Eliminare importi uguali è diverso dal contare ogni ordine esattamente una volta. DISTINCT non è una correzione universale per i totali gonfiati.
Riassumere le righe per ordine prima dell'unione
SELECT COUNT(*) AS orders_count,SUM(o.shipping_fee) AS shipping_total,
SUM(d.goods_total) AS goods_total
FROM c28_orders o
JOIN (SELECT order_id,SUM(quantity*unit_price) AS goods_total
FROM c28_items GROUP BY order_id) d ON d.order_id=o.order_id
WHERE o.status='paid';
La sottoquery in FROM crea una tabella derivata con un goods_total per order_id. Dopo l'unione con gli ordini, orders_count=3, shipping_total=3000.00 e goods_total=39000.00. Un numero maggiore di righe ordine non provoca più copie aggiuntive della spedizione.
Per osservare una variazione nei soli dati di esercizio, aggiungere una riga in una transazione di prova, verificare i totali e annullare con ROLLBACK. La query di dettaglio avrà più righe, mentre la spedizione nel risultato già riassunto resta contata una volta per ordine. Non usare dati applicativi per questa prova.
L'esempio considera ordini pagati che possiedono righe. Se devono comparire anche ordini senza righe, scegliere LEFT JOIN e definire la politica per i totali mancanti. Questa decisione cambia l'insieme del rapporto: un ordine senza righe può avere spese di spedizione anche se manca goods_total.
Esaminare il numero di corrispondenze dopo ogni JOIN
Aggiungere una tabella alla volta mostrando chiavi e conteggi. Una crescita inattesa può indicare chiavi non univoche o una condizione omessa. Unire due raccolte di dettagli indipendenti allo stesso ordine può produrre il prodotto dei loro numeri di righe. Riassumere ogni raccolta al livello richiesto prima di combinare le misure.
Riconciliare manualmente un piccolo ordine prima di fidarsi dell'intero rapporto. Controllare separatamente numero di ordini, importo della merce e spedizione, perché ciascuno può essere corretto o sbagliato indipendentemente dagli altri. Una variazione del catalogo non deve cambiare il totale che usa i prezzi di acquisto memorizzati.
Prima di ripetere, controllare database selezionato, SHOW CREATE TABLE e righe delle quattro tabelle. Salvare ciò che va conservato. Eliminare soltanto gli oggetti c28_ di questo articolo, iniziando dalle tabelle figlie e poi dalle tabelle padre. DROP TABLE rimuove struttura e dati e non viene annullato da un normale ROLLBACK: tenere la pulizia separata dagli esperimenti sulle transazioni.
DROP TABLE shopcourse.c28_items;
DROP TABLE shopcourse.c28_orders;
DROP TABLE shopcourse.c28_products;
DROP TABLE shopcourse.c28_customers;




