Corso MariaDB / Suddividere le query con WITH e le CTE

Un'espressione di tabella comune, o common table expression (CTE), assegna con WITH un nome a un risultato intermedio valido in una sola istruzione SQL. Aiuta a separare condizioni e aggregazioni complesse in passaggi leggibili, ma non crea una tabella permanente e non garantisce automaticamente un'esecuzione più veloce.

Assegnare una responsabilità a ogni risultato intermedio

Selezionare ordini pagati, calcolarne gli importi e aggiungere nomi dei clienti in un'unica espressione annidata può nascondere condizioni e significato delle righe. Qui paid_orders rappresenta gli ordini che soddisfano il filtro e order_totals un importo della merce per ordine. Ogni nome deve avere colonne, chiavi e significato della riga comprensibili.

Risultato Una riga rappresenta Colonne principali
paid_orders Un ordine pagato order_id, customer_id
order_totals Totale della merce di un ordine order_id, goods_total
SELECT finale Un ordine che soddisfa i requisiti Nome del cliente e importo della merce

Un nome descrittivo facilita il controllo ma non cambia da solo numero o significato delle righe. Riassumere richiede comunque un GROUP BY appropriato. Il riferimento WITH di MariaDB spiega definizione e ambito delle CTE.

Preparare dati di ordine indipendenti

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

Prima di creare c31_customers, c31_products, c31_orders e c31_items, controllare SELECT VERSION(), DATABASE(); e SHOW TABLES;. Se gli oggetti esistono già, esaminarne prima definizione e righe. Usare soltanto tabelle di esercitazione eliminabili e non sostituire tabelle applicative. Verificare anche il supporto di WITH nella versione del server utilizzata.

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

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.

Tre ordini sono pagati e uno è annullato. Riassumere prima le righe per ordine rende più facile seguire il problema delle moltiplicazioni nei JOIN. La preparazione comprende tutte le tabelle e non dipende dagli esercizi precedenti.

Definire più CTE prima del SELECT finale

WITH paid_orders AS (
 SELECT order_id,customer_id FROM c31_orders WHERE status='paid'
), order_totals AS (
 SELECT order_id,SUM(quantity*unit_price) AS goods_total
 FROM c31_items GROUP BY order_id
)
SELECT p.order_id,c.customer_name,t.goods_total
FROM paid_orders p JOIN order_totals t ON t.order_id=p.order_id
JOIN c31_customers c ON c.customer_id=p.customer_id
ORDER BY p.order_id;
Ordine ID cliente Totale della merce
101 1 11500.00
102 1 8000.00
103 2 19500.00

Scrivere WITH una volta all'inizio, separare le definizioni nominate con virgole e racchiudere ogni SELECT tra parentesi dopo AS. Il SELECT finale segue tutte le definizioni. Un punto e virgola intermedio termina l'istruzione e rende il nome della CTE indisponibile per la query successiva.

paid_orders filtra per stato, order_totals raggruppa le righe per order_id e il SELECT finale unisce entrambi ai clienti. Il cliente 1 conserva due righe perché il risultato è a livello di ordine. Cambiare il rapporto in una riga per cliente richiede un'ulteriore aggregazione appropriata.

Verificare le righe intermedie prima del risultato finale

Se gli importi non corrispondono, eseguire separatamente il SELECT interno di ogni CTE. paid_orders deve contenere gli ID 101, 102 e 103. order_totals non filtrato può contenere quattro totali, incluso 1500 per l'ordine annullato 104. La restrizione agli ordini pagati avviene quando questi risultati si uniscono nel SELECT finale.

WITH paid_orders AS (
 SELECT order_id,customer_id FROM c31_orders WHERE status='paid'
)
SELECT * FROM paid_orders ORDER BY order_id;

In alternativa, mantenere la definizione e cambiare temporaneamente il SELECT finale per ispezionarla. Cambiare soltanto paid in canceled nell'esempio completo restituisce l'ordine 104 del cliente 3 con totale 1500. Ripristinare paid per ottenere i tre ordini iniziali.

L'unione con order_totals è INNER JOIN, quindi esclude ordini senza righe. Se occorre conservarli, scegliere LEFT JOIN e una politica per il totale mancante. La guida LEFT JOIN spiega gli effetti del lato conservato.

Comprendere l'ambito di una istruzione e le scelte dell'ottimizzatore

Una CTE vale soltanto nell'istruzione che la definisce. Eseguire SELECT da paid_orders dopo la fine non legge il risultato precedente perché non esiste una tabella permanente con quel nome. Per una definizione riutilizzabile tra istruzioni, valutare una VIEW; per memorizzare righe temporanee, valutare una tabella temporanea appropriata.

L'ottimizzatore può combinare o materializzare strutture intermedie secondo query e versione. WITH non promette una sola valutazione o maggiore velocità. Valutare la leggibilità separatamente dalle prestazioni e ispezionare EXPLAIN quando queste contano. Le CTE ricorsive che si riferiscono a sé stesse sono un tema distinto da questa scomposizione non ricorsiva.

Controllare nomi, colonne e livello di aggregazione

Per errori di sintassi, verificare prima parentesi, virgole e punti e virgola. Per colonne mancanti, controllare l'elenco prodotto dal SELECT della CTE. Preferire nomi univoci e colonne esplicite che non nascondano le tabelle di base. Una CTE successiva può usare una CTE già definita nello stesso WITH.

Non esiste un oggetto CTE permanente da eliminare: la pulizia riguarda soltanto le tabelle di esercizio. Prima di ripetere, controllare database selezionato, SHOW CREATE TABLE e righe delle quattro tabelle, salvando ciò che va conservato. Eliminare solo gli oggetti c31_ 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.c31_items;
DROP TABLE shopcourse.c31_orders;
DROP TABLE shopcourse.c31_products;
DROP TABLE shopcourse.c31_customers;