MariaDB-Kurs / Bedingungen mit Unterabfragen, EXISTS und NOT EXISTS ausdrücken
Eine Unterabfrage liefert einen Wert oder eine Zeilenmenge innerhalb einer anderen SQL-Anweisung. EXISTS und NOT EXISTS prüfen, ob passende Zeilen vorhanden sind oder fehlen. Damit wählen Sie etwa jeden Kunden mit bezahlten Bestellungen nur einmal aus oder finden Kunden ohne solche Bestellungen.
Wert, Liste und Existenzprüfung unterscheiden
Ein Preisvergleich mit dem Durchschnitt benötigt eine Zahl. Für die Auswahl eines Kunden reicht unter Umständen die Information, ob eine passende Bestellung existiert. Wählen Sie die Form nach dem benötigten Ergebnis und erhalten Sie die Bedeutung der äußeren Zeile. Die Anleitung zu NULL-Vergleichen erklärt Schwierigkeiten mit NOT IN, wenn die Liste fehlende Werte enthält.
| Form | Benötigtes Ergebnis | Prüfpunkt |
|---|---|---|
| Skalare Unterabfrage | Eine Spalte und höchstens eine Zeile | Mehrere Zeilen verursachen einen Fehler; keine Zeile ergibt NULL |
| IN-Unterabfrage | Eine Werteliste | NULL und Vergleichstyp |
| EXISTS | Mindestens eine passende zugehörige Zeile | Korrelationsschlüssel angeben |
| NOT EXISTS | Keine passende zugehörige Zeile | Genau definieren, was fehlen soll |
Die offizielle EXISTS-Referenz und die Referenz zu skalaren Unterabfragen beschreiben diese Bedingungen.
Kunden mit unterschiedlichen Bestellungen vorbereiten
Verbinden Sie sich als course_user mit dem Lernserver und wählen Sie shopcourse. Die Anleitung zu Client und UTF-8-SQL-Dateien erklärt das Speichern und Ausführen mit SOURCE C:/mariadb-lab/lesson.sql;. Ersetzen Sie den Pfad durch die tatsächliche Datei und führen Sie SQL im MariaDB-Client aus.
Prüfen Sie vor dem Erstellen von c29_customers, c29_products, c29_orders und c29_items mit SELECT VERSION(), DATABASE(); und SHOW TABLES; die Umgebung. Untersuchen Sie vorhandene Definitionen und Daten vor einem Neustart. Verwenden Sie nur entbehrliche Übungstabellen und erhalten Sie Anwendungstabellen.
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);
Das SQL erhält die koreanischen Beispieldaten für Namen und Städte; die Erläuterungen verwenden eindeutige Kennungen. Die Produkte 1 bis 4 sind Notizbuch, Kugelschreiber, Becher und Lampe. Die Kunden 1 und 3 haben die Stadt Seoul, Kunde 2 Busan und Kunde 4 Daejeon. Behalten Sie die UTF-8-Zeichenfolgen beim Nachvollziehen der Ergebnisse bei.
Kunde 1 hat zwei bezahlte Bestellungen, Kunde 2 eine bezahlte, Kunde 3 nur eine stornierte und Kunde 4 keine. Die Existenzabfragen wählen den Kunden einmal aus, statt eine Zeile pro passender Bestellung zu erzeugen.
Einen Preis mit einer skalaren Aggregation vergleichen
SELECT product_id,product_name FROM c29_products
WHERE price > (SELECT AVG(price) FROM c29_products) ORDER BY product_id;
Der Durchschnittspreis der vier Produkte beträgt 9500. Nur die Lampe mit Kennung 4 liegt darüber. AVG fasst die Produktmenge zu einem einzelnen Wert zusammen. Mehrere Preise ohne Aggregation an dieser skalaren Position verursachen dagegen einen Fehler wegen mehrerer Ergebniszeilen.
Definieren Sie den benötigten Vergleichswert, statt eine unklare Auswahl mit LIMIT 1 zu verbergen. Liefert eine skalare Unterabfrage keine Zeile, ist ihr Wert NULL. Ein gewöhnlicher Vergleich damit wird nicht automatisch wahr.
Korreliertes EXISTS ohne vervielfachte Kunden verwenden
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;
Die Kunden 1 und 2 erscheinen je einmal. SELECT 1 liefert einen Wert für die Existenzprüfung und keinen Geldbetrag der Bestellung. Die zweite Bestellung von Kunde 1 vervielfacht seine äußere Zeile nicht. Ohne o.customer_id=c.customer_id würde die Unterabfrage prüfen, ob irgendein Kunde eine bezahlte Bestellung hat.
Eine korrelierte Unterabfrage verweist auf den Schlüssel der äußeren Zeile. Das beschreibt die logische Beziehung und verpflichtet den Optimierer nicht dazu, für jede äußere Zeile die gesamte innere Tabelle zu durchlaufen. Die Anleitung zu INNER JOIN ermöglicht den Vergleich mit einem Ergebnis, das jede einzelne Bestellung erhält.
Mit NOT EXISTS genau festlegen, was fehlt
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;
Die Kunden 3 und 4 haben keine bezahlte Bestellung. Sowohl der Kunde mit ausschließlich stornierter Bestellung als auch der Kunde ohne irgendeine Bestellung erfüllen diese Bedingung. Entfernen Sie nur den Statusfilter, sucht die Abfrage nach dem vollständigen Fehlen von Bestellungen und liefert nur Kunde 4. Beschreiben Sie die gesuchte Abwesenheit zunächst in einem Satz.
NOT IN kann anders reagieren, wenn die innere Liste NULL enthält. Prüfen Sie, ob Schlüssel NULL zulassen und wie ein äußerer NULL-Wert behandelt werden soll, statt eine allgemeine Gleichwertigkeit mit NOT EXISTS anzunehmen. EXISTS garantiert außerdem keine schnellere Abfrage als JOIN.
Ergebnisform, Korrelation und Ausführungsplan untersuchen
Führen Sie die innere Abfrage separat aus, um Werte, Wiederholungen und NULL zu prüfen. Stellen Sie anschließend den Bezug zu einem bekannten äußeren Fall her. Bekommen alle Kunden dieselbe Antwort, prüfen Sie die Korrelationsbedingung. Vermeiden Sie unnötige Spalten oder Sortierungen in einer einfachen Existenzprüfung.
Bewerten Sie die Leistung mit repräsentativen Daten und dem tatsächlichen Ausführungsplan der vollständigen Abfrage. Die Schreibweise einer Bedingung belegt ihren Aufwand nicht von selbst. Unterscheiden Sie diese Bewertung von der Ergebnisprüfung mit der kleinen Übungsdatenmenge.
Prüfen Sie vor einer Wiederholung die ausgewählte Datenbank, SHOW CREATE TABLE und gespeicherte Daten aller vier Tabellen. Sichern Sie benötigte Inhalte und entfernen Sie nur entbehrliche c29_-Objekte, zuerst die abhängigen und danach die referenzierten Tabellen. DROP TABLE entfernt Struktur und Daten und wird durch ein gewöhnliches ROLLBACK nicht rückgängig gemacht. Trennen Sie die Bereinigung von Transaktionsversuchen.
DROP TABLE shopcourse.c29_items;
DROP TABLE shopcourse.c29_orders;
DROP TABLE shopcourse.c29_products;
DROP TABLE shopcourse.c29_customers;




