MariaDB講座 / WITH・CTEで問い合わせを読みやすい段階に分ける
WITHで定義するCTE（共通表式）は、一つのSQL文の中で中間結果に名前を付ける仕組みです。条件や集計を読みやすく分けられますが、永続表を作るものでも、必ず高速になる指定でもありません。
中間結果ごとに責任を一つにする
paid_ordersは支払い済み一注文、order_totalsは一注文の商品合計を表します。各名前について列、キーと一行の意味を説明できるようにします。名前を付けただけで、明細から注文への単位が自動で変わるわけではありません。
結合時の集計重複を避けて注文ごとにまとめる段階を見える形にします。順位はウィンドウ関数、累計はSUMとLAG、自己参照する再帰は再帰CTEで扱います。
独立した注文データを用意する
course_userでshopcourseへ接続し、SELECT VERSION(), DATABASE();とSHOW TABLES;で対象を確認します。以下はこの記事専用の四表を用意する完全な例です。UTF-8ファイルでの実行もできます。同名表がある場合は定義と行を保存して用途を調べ、確認なしに置き換えません。
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);
文字列は原文の韓国語を保っています。商品1～4はノート、ペン、マグカップ、ライト、顧客1・3はソウル、2は釜山、4は大田です。UTF-8で保存・接続し、IDと数値で結果を照合します。paidは101・102・103、104はcanceledです。
WITHに対応するサーバー版を確認します。注文合計の中間結果にはキャンセル分もあり、最終結合の前後のどこで状態を絞るかを意図して選びます。
複数CTEの後に最終SELECTを書く
WITHは先頭に一度書き、名前付き定義をカンマで区切ります。
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;
最終結果は注文101が顧客1・11500、102が顧客1・8000、103が顧客2・19500です。各ASの括弧内にSELECTを書き、その後に最終SELECTを続けます。中間定義の直後にセミコロンを置くと文が終わり、後の文から名前を使えません。
中間の行を先に調べる
内側のSELECTを個別に実行するか、最終SELECTだけを確認用へ変えます。
WITH paid_orders AS ( SELECT order_id,customer_id FROM c31_orders WHERE status='paid' ) SELECT * FROM paid_orders ORDER BY order_id;
paid_ordersは101・102・103です。状態を絞らないorder_totalsは104を含む四注文になり得ます。値が違う場合は、各段階の件数とキーを確認してから結合を調べます。一顧客一行へ変えるなら、さらに適切な集計が必要です。
有効範囲と実行方式を分けて考える
CTEはその一文だけの名前です。後の別SQLでも定義を使うならVIEW、一時的な実体行が必要なら一時表などを検討します。CTE自体をDROPする必要はありません。
最適化器は問い合わせや版に応じて統合・実体化を選ぶため、WITHが一度だけ評価される、常に高速という保証はありません。読みやすさと性能を分け、性能はEXPLAINで確認します。構文エラーなら括弧・カンマ・文末、列不足なら中間SELECTの出力を読みます。
再実習では実際のDB・表定義・保存行を調べ、必要なデータをバックアップしてから、この記事の削除可能な表だけを子から親の順に消します。DROP TABLEは定義と行を消し、通常のROLLBACKでは戻せません。
DROP TABLE shopcourse.c31_items; DROP TABLE shopcourse.c31_orders; DROP TABLE shopcourse.c31_products; DROP TABLE shopcourse.c31_customers;
