MariaDB講座 / 副問い合わせ・EXISTS・NOT EXISTSを使う

MariaDB講座

副問い合わせはSQLの中へ一値や行集合を提供します。EXISTSとNOT EXISTSは関連行の有無を検査するため、一顧客を一度だけ選んだり、支払い済み注文がない顧客を探したりできます。

外側に必要な形を決める

平均価格との比較は一つの数、顧客の適格性は注文があるかという真偽を必要とします。スカラー副問い合わせは一列一行、INは値の一覧、EXISTSは関連する一致の有無です。どこに何行必要かを先に定めます。

一覧のNULLはNOT INの注意点、行を縦につなぐ操作はUNIONで、いずれも存在検査とは異なります。

顧客履歴の異なる例を作る

クライアントの接続手順でcourse_userとしてshopcourseへ接続し、SELECT VERSION(), DATABASE();とSHOW TABLES;を確認します。以下には必要な四表の作成とデータがすべてあり、以前の記事の表を必要としません。UTF-8の.sqlファイルで実行し、同名の表があれば定義と内容を調べてから判断します。

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

顧客名・都市・商品名は原文の韓国語サンプルです。商品1～4はノート、ペン、マグカップ、ライト、顧客1・3はソウル、2は釜山、4は大田です。文字列を維持して実行し、説明とは安定したIDで照合できます。

顧客1はpaid二件、2は一件、3はcanceledだけ、4は注文なしです。ここでは注文ごとでなく一顧客を一行として選びます。

平均という一値と比較する

商品表を平均へまとめて比較します。

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

四商品の平均9500より高いのはライトID4だけです。この位置に集計しない複数価格を返すと複数行エラーになります。LIMIT 1で未定義の一値を隠さず、必要な基準を定めます。

相関EXISTSで顧客を増やさない

現在の顧客と関連するpaidを調べます。

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;

顧客1・2が各一度だけ返ります。SELECT 1は存在検査の値で、注文金額ではありません。相関キーo.customer_id=c.customer_idを外すと、どこかにpaidがあるかという別の質問になります。

外側のキーを参照するのが論理的な相関です。物理的に全内側表を毎回全走査するという断定ではなく、実際の方式は最適化器が選びます。

何が存在しないかを文章で定義する

支払い済みがない顧客です。

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;

3・4が返り、状態条件を外すと注文自体のない4だけになります。NOT INは内側NULLで違う挙動になり得るため、内側キーと外側NULLの意味を検討し、いつも同値とは考えません。EXISTSを選べば必ずJOINより高速になるわけでもありません。

形・相関・実行計画を調べる

まず内側だけを実行して重複やNULLを確認し、既知の外側一行へ対応させます。全顧客が同じ判定なら相関条件を見ます。単純な存在検査に不要な出力や並べ替えを増やさず、性能は代表データと実行計画で比較します。

再実習はDB名、実際の定義と保存行を確認し、必要なデータを保存してから行います。この記事の削除してよい四表だけを、参照する子から親の順に削除します。DROP TABLEは定義とデータを消し、通常のROLLBACKでは戻せません。

DROP TABLE shopcourse.c29_items;
DROP TABLE shopcourse.c29_orders;
DROP TABLE shopcourse.c29_products;
DROP TABLE shopcourse.c29_customers;