Corso MariaDB / Esprimere condizioni con sottoquery, EXISTS e NOT EXISTS

Una sottoquery fornisce un valore o un insieme di righe all'interno di un'altra istruzione SQL. EXISTS e NOT EXISTS verificano se esistono o meno righe collegate e permettono di selezionare ogni cliente una volta oppure trovare clienti senza ordini pagati.

Scegliere tra valore singolo, elenco e verifica di esistenza

Confrontare il prezzo di un prodotto con la media richiede un numero. Qualificare un cliente che abbia un ordine pagato richiede soltanto di accertare che quell'ordine esista. Scegliere la forma del risultato in base alla domanda e conservare il significato della riga esterna, senza applicare una sola forma di sottoquery a ogni problema.

Forma Risultato richiesto Controllo
Sottoquery scalare Un valore da una colonna Più righe sono un errore; nessuna riga produce NULL
Sottoquery IN Elenco di valori Esaminare NULL nell'elenco e nel valore esterno
EXISTS Una qualsiasi riga collegata che soddisfi il requisito Includere la chiave di correlazione corretta
NOT EXISTS Nessuna riga collegata che soddisfi il requisito Definire precisamente che cosa deve essere assente

La guida ai confronti con NULL spiega perché NOT IN richiede attenzione se l'elenco contiene NULL. La verifica di esistenza seleziona righe esterne; combinare verticalmente risultati con UNION ha uno scopo diverso. Il riferimento EXISTS di MariaDB descrive la verifica dell'esistenza di righe.

Preparare storie dei clienti distinte

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 c29_customers, c29_products, c29_orders e c29_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 sostituire tabelle applicative. Il blocco comprende tutta la preparazione per eseguire l'articolo autonomamente.

USE shopcourse;
CREATE TABLE c29_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 c29_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 c29_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_c29_order_customer FOREIGN KEY (customer_id)
  REFERENCES c29_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c29_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_c29_item_order FOREIGN KEY (order_id)
  REFERENCES c29_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c29_item_product FOREIGN KEY (product_id)
  REFERENCES c29_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c29_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c29_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c29_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 c29_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. 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. Salvare il testo in UTF-8 e confrontare i risultati tramite gli ID.

Il cliente 1 ha due ordini pagati, il cliente 2 uno, il cliente 3 soltanto un ordine annullato e il cliente 4 nessuno. Gli esempi selezionano un cliente una volta, senza produrre una riga separata per ogni ordine corrispondente.

Confrontare il prezzo con un aggregato scalare

SELECT product_id,product_name FROM c29_products
WHERE price > (SELECT AVG(price) FROM c29_products) ORDER BY product_id;

La media dei quattro prezzi è 9500, quindi soltanto la lampada con ID 4 la supera. AVG trasforma le righe dei prezzi in un valore. Restituire più prezzi non aggregati in questa posizione scalare causa un errore per più righe. Definire il valore di confronto voluto invece di nascondere una scelta indefinita con LIMIT 1.

Una sottoquery scalare senza righe vale NULL anche se la colonna originale non lo ammette. Un confronto del prezzo con questo NULL non risulta vero e non seleziona il prodotto. Il riferimento delle sottoquery scalari MariaDB descrive forma e condizioni del risultato.

Usare EXISTS correlato senza moltiplicare i clienti

SELECT c.customer_id,c.customer_name FROM c29_customers c
WHERE EXISTS (SELECT 1 FROM c29_orders o
 WHERE o.customer_id=c.customer_id AND o.status='paid')
ORDER BY c.customer_id;

I clienti 1 e 2 compaiono ciascuno una volta. SELECT 1 serve alla verifica di esistenza e non rappresenta quantità o importo degli ordini. Il secondo ordine del cliente 1 non duplica la riga esterna. Anche una riga della sottoquery con valore NULL conta come riga esistente per EXISTS.

Una sottoquery correlata usa la chiave della riga esterna. Senza o.customer_id=c.customer_id, verifica soltanto se esista un ordine pagato nell'intera tabella. Poiché i dati ne contengono, passerebbero tutti i clienti. Eliminare soltanto la correlazione per confrontare il risultato di quattro clienti, poi ripristinarla per tornare ai clienti 1 e 2.

Questa spiegazione logica non impone la strategia fisica dell'ottimizzatore. Non presumere che ogni esecuzione rilegga necessariamente l'intera tabella interna per ogni riga esterna.

Definire l'assenza desiderata con NOT EXISTS

SELECT c.customer_id,c.customer_name FROM c29_customers c
WHERE NOT EXISTS (SELECT 1 FROM c29_orders o
 WHERE o.customer_id=c.customer_id AND o.status='paid')
ORDER BY c.customer_id;

I clienti 3 e 4 non hanno ordini pagati. Un cliente con soli ordini annullati e uno senza ordini soddisfano entrambi questa condizione. Eliminare soltanto il filtro di stato cambia la domanda in nessun ordine di qualsiasi stato: rimane soltanto il cliente 4. Descrivere l'assenza con una frase prima di scrivere il filtro.

NOT IN può comportarsi diversamente se l'elenco interno contiene NULL. Controllare se chiavi di confronto e valori esterni ammettano NULL e se le righe esterne NULL debbano qualificarsi. Non presumere un'equivalenza universale tra NOT IN e NOT EXISTS. Scegliere EXISTS non garantisce neppure prestazioni migliori di un JOIN.

Esaminare forma del risultato, correlazione e piano di esecuzione

Eseguire separatamente la query interna per esaminare valori, duplicati e NULL, poi collegarla a una riga esterna nota. Se tutti i clienti ottengono lo stesso esito, verificare la correlazione. Evitare colonne e ordinamenti superflui in una semplice verifica di esistenza. Valutare le prestazioni con dati rappresentativi e il piano effettivo, invece di assumere che una sintassi sia sempre più veloce.

Prima di ripetere, controllare database selezionato, SHOW CREATE TABLE e dati delle quattro tabelle. Salvare ciò che va conservato. Eliminare soltanto gli oggetti c29_ 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.c29_items;
DROP TABLE shopcourse.c29_orders;
DROP TABLE shopcourse.c29_products;
DROP TABLE shopcourse.c29_customers;