MariaDB-Kurs / Abfragen mit WITH und gemeinsamen Tabellenausdrücken aufteilen

Ein gemeinsamer Tabellenausdruck, kurz CTE für Common Table Expression, benennt mit WITH ein Zwischenergebnis innerhalb einer SQL-Anweisung. Damit lassen sich Filter und Aggregationen in verständliche Schritte zerlegen. Eine CTE erstellt keine dauerhafte Tabelle und garantiert keine schnellere Ausführung.

Jedem Zwischenergebnis eine klare Aufgabe geben

Bezahlte Bestellungen auszuwählen, Positionen zu summieren und Kundennamen anzufügen, kann in einem stark verschachtelten Ausdruck schwer zu prüfen sein. In dieser Übung bezeichnet paid_orders die passenden Bestellungen und order_totals einen Warenwert pro Bestellung. Legen Sie für jeden Namen fest, was eine Zeile bedeutet und welche Spalten sie mit anderen Ergebnissen verbinden.

Ergebnis Eine Zeile steht für Wichtige Spalten
paid_orders Eine bezahlte Bestellung order_id, customer_id
order_totals Den Warenwert einer Bestellung order_id, goods_total
Abschließendes SELECT Eine passende Bestellung mit Positionen Bestellung, Kundenname und Warenwert

Die Namen erklären die Logik, ändern aber allein keine Zeilenanzahl einer Beziehung. Die Erklärung zu mehrfach gezählten Summen bei JOIN zeigt, weshalb diese Einheit vor einer Summierung feststehen muss.

Einen unabhängigen Bestelldatensatz vorbereiten

Verbinden Sie sich als course_user mit dem Lernserver und wählen Sie shopcourse. Speichern Sie die Blöcke bei Bedarf in einer UTF-8-Datei und führen Sie sie im Client mit SOURCE C:/mariadb-lab/lesson.sql; aus. Ersetzen Sie den Pfad durch die tatsächliche Datei. Die Anleitung zu MariaDB-Client und SQL-Dateien erklärt Verbindung und Ausführung. Geben Sie SQL im MariaDB-Client ein.

Prüfen Sie mit SELECT VERSION(), DATABASE(); und SHOW TABLES; die Umgebung, bevor Sie c31_customers, c31_products, c31_orders und c31_items erstellen. Diese Namen gehören ausschließlich zur Übung. Untersuchen Sie vorhandene Definitionen und Datensätze, bevor Sie über eine Entfernung entscheiden; erhalten Sie Anwendungsobjekte. Prüfen Sie in der Serverdokumentation, ob Ihre Version WITH unterstützt.

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);

Das SQL erhält die koreanischen Originalnamen und Städte. Die Produkte 1 bis 4 sind Notizbuch, Kugelschreiber, Becher und Lampe. Die Kunden 1 und 3 haben den Stadtwert Seoul, Kunde 2 Busan und Kunde 4 Daejeon. Behalten Sie die UTF-8-Zeichenfolgen für reproduzierbare Ergebnisse bei. Die Erläuterungen verwenden Kennungen zur Zuordnung.

Die Bestellungen 101, 102 und 103 sind bezahlt (paid), Bestellung 104 ist storniert (canceled). Der Warenwert verwendet Menge und gespeicherten Einzelpreis jeder Position. Versandkosten werden nicht addiert; der historische Preis wird nicht durch den aktuellen Katalogpreis ersetzt.

Mehrere CTEs vor dem abschließenden SELECT definieren

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;

Schreiben Sie WITH einmal und trennen Sie die Definitionen durch Kommas. Auf jeden Namen folgen AS und ein SELECT in Klammern. Nach der letzten Definition folgt das SELECT, das die Ergebnisse verwendet. Das Semikolon gehört ans Ende der gesamten Anweisung. Ein Semikolon nach einer Zwischendefinition beendet die Anweisung vor ihrer Verwendung.

Bestellung Kundenkennung Warenwert
101 1 11500.00
102 1 8000.00
103 2 19500.00

Das tatsächliche SELECT zeigt customer_name, den gespeicherten koreanischen Namen. Die Erklärungstabelle identifiziert den Kunden über seine Kennung. Kunde 1 erscheint zweimal, weil er zwei bezahlte Bestellungen hat. Das Ergebnis enthält eine Zeile pro Bestellung, nicht pro Kunde.

Die Zeilen jeder Stufe einzeln untersuchen

Bei einem abweichenden Wert führen Sie das innere SELECT jeder CTE separat aus. paid_orders muss die drei Bestellungen 101, 102 und 103 enthalten. order_totals gruppiert die Positionen aller vier Bestellungen, einschließlich der stornierten Bestellung 104 mit dem Warenwert 1500.00. Die abschließende Verknüpfung mit paid_orders entfernt sie aus dem angezeigten Ergebnis.

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

Dieser Block erhält die Definition von paid_orders und verwendet ein einfaches abschließendes SELECT für ihre Untersuchung. Prüfen Sie eine andere Stufe ebenso: Erhalten Sie ihre Definition und wählen Sie deren Spalten aus. Kehren Sie danach zum vollständigen SELECT zurück und prüfen Sie die Reproduktion des ursprünglichen Ergebnisses.

Der innere JOIN mit order_totals verlangt Positionen in der Bestellung. Eine bezahlte Bestellung ohne Positionen erschiene in paid_orders, aber nicht im abschließenden SELECT dieses Beispiels. Soll der Bericht sie mit Warenwert null enthalten, benötigen Sie eine äußere Verknüpfung und eine ausdrücklich festgelegte Behandlung des fehlenden Werts. Eine Zeile pro Kunde erfordert wiederum eine weitere Aggregation; ein anderer CTE-Name führt sie nicht aus.

Geltungsbereich und Ausführungsentscheidungen verstehen

Der CTE-Name gilt nur innerhalb der Anweisung mit seinem WITH. Ein zweites unabhängiges SELECT kann paid_orders nicht ohne erneute Definition verwenden. Es entsteht keine dauerhafte Tabelle, die nach Abschluss entfernt werden müsste. Für eine über mehrere Anweisungen wiederverwendbare Definition prüfen Sie eine View, für vorübergehend gespeicherte Zeilen eine zum Arbeitsablauf passende temporäre Tabelle.

Namen innerhalb einer WITH-Klausel müssen verschieden sein. Eine nicht rekursive CTE darf eine zuvor in derselben Klausel definierte CTE verwenden. Rekursion für das Durchlaufen von Hierarchien durch Rückverweise ist ein anderes Thema als die hier gezeigte nicht rekursive Zerlegung.

WITH bedeutet weder, dass ein Zwischenergebnis genau einmal berechnet wird, noch dass die Abfrage schneller läuft. Zusammenführung oder Materialisierung von Zwischenergebnissen hängen vom Optimierer, der Abfrage und der Version ab. Trennen Sie bessere Lesbarkeit von Leistungsmessungen. Untersuchen Sie bei Bedarf den Plan mit EXPLAIN.

Die offizielle MariaDB-Referenz zu WITH dokumentiert Syntax, Geltungsbereich und CTE-Verweise.

Fehler eingrenzen und Übungstabellen entfernen

Prüfen Sie bei Syntaxfehlern Klammern, Kommas und das abschließende Semikolon. Fehlt eine Spalte, untersuchen Sie die SELECT-Liste der CTE, die sie liefern soll. Bevorzugen Sie ausdrücklich ausgewählte Spalten und Namen, die keine vorhandenen Tabellen unnötig verdecken. Bei falschen Summen prüfen Sie zuerst Verbindungsschlüssel und Zeilenbedeutung, statt Wiederholungen mit DISTINCT zu verstecken.

Prüfen Sie vor einer erneuten Vorbereitung die ausgewählte Datenbank, Definitionen und Daten; sichern Sie benötigte Inhalte. Es gibt keine dauerhaften CTE-Objekte zu löschen. Gehören die folgenden vier Tabellen ausschließlich zur entbehrlichen Übung, entfernen Sie zuerst die abhängigen und danach die referenzierten Tabellen. DROP TABLE entfernt Struktur und Datensätze und wird durch ein gewöhnliches ROLLBACK nicht rückgängig gemacht. Trennen Sie die Bereinigung von Transaktionsversuchen.

DROP TABLE shopcourse.c31_items;
DROP TABLE shopcourse.c31_orders;
DROP TABLE shopcourse.c31_products;
DROP TABLE shopcourse.c31_customers;