Corso MariaDB / Conservare le righe senza corrispondenza con LEFT JOIN

LEFT JOIN conserva le righe della tabella sinistra, aggiunge le colonne corrispondenti della destra e restituisce NULL per la destra quando non esiste una corrispondenza. Scegliere prima l'insieme da mantenere e collocare con attenzione i filtri per non escludere accidentalmente clienti senza ordini che soddisfano i requisiti.

Mettere a sinistra l'insieme da conservare

Per mostrare tutti i clienti registrati e la loro attività d'ordine, partire dai clienti. Il lato destro senza corrispondenza produce NULL nel risultato della query: non crea un ordine memorizzato e non cambia un ordine esistente in NULL. Più ordini corrispondenti producono comunque più righe per un cliente.

La guida INNER JOIN confronta il risultato che contiene soltanto combinazioni corrispondenti. LEFT JOIN è un'unione esterna: conserva il lato sinistro e determina le corrispondenze destre tramite ON. Invertire le tabelle significa conservare un insieme iniziale diverso.

Domanda Progetto dell'unione Significato
Tutti i clienti e gli eventuali ordini customers LEFT JOIN orders Conservare clienti senza ordini
Tutti gli ordini e i relativi clienti orders LEFT JOIN customers Conservare un insieme iniziale diverso
Nessun ordine pagato Condizione paid in ON, poi controllo di assenza Include clienti con soli ordini annullati
Nessun ordine in assoluto Nessun filtro di stato in ON Richiede l'assenza completa di ordini

Il riferimento della sintassi JOIN MariaDB descrive forme e condizioni di unione.

Preparare clienti con storie d'ordine diverse

Collegarsi al server di studio come course_user e selezionare shopcourse. La guida al client e ai file SQL UTF-8 spiega come salvare ed eseguire il codice con SOURCE C:/mariadb-lab/lesson.sql;. Sostituire il percorso con quello reale ed eseguire SQL nel client MariaDB.

Controllare SELECT VERSION(), DATABASE(); e SHOW TABLES; prima di creare c27_customers, c27_products, c27_orders e c27_items. Se i nomi esistono già, verificarne definizioni e righe prima di ripetere. Usare soltanto oggetti di esercitazione eliminabili e non sovrascrivere tabelle applicative.

USE shopcourse;
CREATE TABLE c27_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 c27_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 c27_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_c27_order_customer FOREIGN KEY (customer_id)
  REFERENCES c27_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c27_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_c27_item_order FOREIGN KEY (order_id)
  REFERENCES c27_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c27_item_product FOREIGN KEY (product_id)
  REFERENCES c27_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c27_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c27_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c27_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 c27_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);

L'esempio completo fornisce tutte e quattro le tabelle per essere eseguito autonomamente. SQL conserva nomi e città coreani originali e la spiegazione usa gli ID. 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. Conservare le stringhe in UTF-8.

Il cliente 4 non ha ordini; il cliente 3 ha soltanto un ordine annullato. Definire prima che cosa significhi non aver acquistato nel rapporto: nessun ordine in assoluto e nessun ordine pagato sono condizioni diverse.

Esaminare clienti conservati e NULL sul lato destro

SELECT c.customer_id,c.customer_name,o.order_id,o.status
FROM c27_customers AS c LEFT JOIN c27_orders AS o
 ON o.customer_id=c.customer_id ORDER BY c.customer_id,o.order_id;

Il risultato contiene cinque righe: due per il cliente 1 e una ciascuno per i clienti 2, 3 e 4. Soltanto le colonne d'ordine del cliente 4 sono NULL; identificatore e nome del cliente rimangono. Attendersi esattamente quattro righe confonderebbe clienti e combinazioni cliente-ordine.

Iniziare il controllo da customer_id, order_id e status. NULL in order_id indica l'assenza di un ordine corrispondente a questa condizione, perché order_id nella tabella originale è una chiave che non ammette NULL.

Confrontare il filtro di stato in ON e in WHERE

SELECT c.customer_id,COUNT(o.order_id) AS paid_orders
FROM c27_customers AS c LEFT JOIN c27_orders AS o
 ON o.customer_id=c.customer_id AND o.status='paid'
GROUP BY c.customer_id ORDER BY c.customer_id;
customer_id paid_orders
1 2
2 1
3 0
4 0

Poiché la condizione paid è in ON, i clienti senza ordini pagati rimangono. Spostarla in WHERE o.status='paid' esclude le righe senza corrispondenza e i clienti con soli ordini annullati. Conservare il filtro in ON se il rapporto deve mostrare i clienti 3 e 4 con zero.

Eseguire la forma seguente per confrontare la posizione della stessa condizione. Rimangono soltanto il cliente 1 con due ordini e il cliente 2 con uno.

SELECT c.customer_id,COUNT(o.order_id) AS paid_orders
FROM c27_customers c LEFT JOIN c27_orders o
 ON o.customer_id=c.customer_id
WHERE o.status='paid'
GROUP BY c.customer_id ORDER BY c.customer_id;

Cambiare soltanto la posizione del filtro, confrontare ID e conteggi, poi ripristinare paid in ON quando devono rimanere tutti i clienti. L'uguaglianza dei conteggi per i clienti 1 e 2 non significa che le query considerino lo stesso insieme di clienti.

Contare la chiave degli ordini corrispondenti e non la riga conservata

Un cliente senza ordini ha comunque una riga nel risultato LEFT JOIN, quindi COUNT(*) produce uno per quel cliente. COUNT(o.order_id) conta soltanto gli ordini corrispondenti perché la chiave destra non può essere NULL in un ordine reale. Anche contare customer_id a sinistra produce uno per un cliente senza corrispondenza e non risolve il problema.

SELECT c.customer_id FROM c27_customers AS c
LEFT JOIN c27_orders AS o ON o.customer_id=c.customer_id
WHERE o.order_id IS NULL ORDER BY c.customer_id;

Senza restrizione di stato in ON, la query che cerca l'assenza di corrispondenze restituisce soltanto il cliente 4. Aggiungere paid in ON cambia il risultato nei clienti 3 e 4. Usare una chiave destra non nullable per rilevare l'assenza, non una nota che potrebbe essere NULL anche in un ordine reale corrispondente.

Verificare insieme conservato, posizione del filtro e aggregazione

Prima di applicare DISTINCT a un risultato inatteso, verificare che cosa LEFT JOIN debba conservare, se WHERE rifiuti NULL e che cosa rappresenti una riga. Mostrare chiavi e stati originali quando i conteggi differiscono dai dati di esercizio. La guida a COUNT e ai valori NULL spiega il comportamento di aggregazione utilizzato.

Prima di ripetere, controllare database selezionato, SHOW CREATE TABLE e dati delle quattro tabelle. Salvare ciò che va conservato. Eliminare solo gli oggetti c27_ di questo articolo, iniziando dalle tabelle figlie e poi dalle tabelle padre referenziate. DROP TABLE rimuove struttura e righe e non viene annullato da un normale ROLLBACK: tenere la pulizia separata dagli esperimenti sulle transazioni.

DROP TABLE shopcourse.c27_items;
DROP TABLE shopcourse.c27_orders;
DROP TABLE shopcourse.c27_products;
DROP TABLE shopcourse.c27_customers;