CODING FACTORY

MariaDB講座 / LEFT JOINで一致しない行も残す

MariaDB講座

LEFT JOINは左側の行を残し、右側に一致がなければ右の列をNULLとして返します。最初に残す母集団を決め、条件の位置によって注文のない顧客まで消していないか確認します。

残したい母集団を左に置く

すべての登録顧客を出すならcustomersから始めます。注文のNULLは問い合わせの結果で補われたもので、実際の注文を新規保存したりNULLへ書き換えたりしません。一顧客に二注文があれば結果も二行です。

「注文が一度もない」と「支払い済みがない」は違います。後者はpaid条件をONに入れ、右の一致がないことを検査します。INNER JOINは一致だけ、NOT EXISTSも不在を表せます。

注文履歴の違う顧客を準備する

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

USE shopcourse;
CREATE TABLE c27_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 c27_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 c27_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_c27_order_customer FOREIGN KEY (customer_id)
  REFERENCES c27_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c27_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_c27_item_order FOREIGN KEY (order_id)
  REFERENCES c27_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c27_item_product FOREIGN KEY (product_id)
  REFERENCES c27_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c27_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c27_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c27_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 c27_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で照合できます。

顧客4は注文なし、顧客3はキャンセルだけです。「購入なし」がどちらの意味かを先に文章で定義します。

左側と右側NULLを観察する

次の全結果を読みます。

SELECT c.customer_id,c.customer_name,o.order_id,o.status
FROM c27_customers AS c LEFT JOIN c27_orders AS o
 ON o.customer_id=c.customer_id ORDER BY c.customer_id,o.order_id;

顧客1に二行、2・3・4に各一行で計5行です。注文列がNULLなのは4だけで、顧客のIDと名前は残ります。四顧客だから必ず四行とはならず、顧客と注文の組を返しています。

ONで支払い済みを関連条件へ含めます。

SELECT c.customer_id,COUNT(o.order_id) AS paid_orders
FROM c27_customers AS c LEFT JOIN c27_orders AS o
 ON o.customer_id=c.customer_id AND o.status='paid'
GROUP BY c.customer_id ORDER BY c.customer_id;

顧客1～4のpaid_ordersは2・1・0・0です。次は同じ条件をWHEREへ移します。

SELECT c.customer_id,COUNT(o.order_id) AS paid_orders
FROM c27_customers c LEFT JOIN c27_orders o
 ON o.customer_id=c.customer_id
WHERE o.status='paid'
GROUP BY c.customer_id ORDER BY c.customer_id;

今度は顧客1・2だけになり、キャンセルのみの3やNULLで補った4は落ちます。条件位置だけを変えて顧客IDと件数を比較し、全顧客が必要ならONに戻します。

右側のキーを数え、不在を検査する

注文がなくても左側の結果は一行なのでCOUNT(*)は1になり得ます。COUNT(o.order_id)なら実際の注文で非NULLのキーを数えます。必ずある顧客IDを数えても解決しません。

SELECT c.customer_id FROM c27_customers AS c
LEFT JOIN c27_orders AS o ON o.customer_id=c.customer_id
WHERE o.order_id IS NULL ORDER BY c.customer_id;

状態制限なしでは顧客4だけ、ONへpaid条件を加えると3・4です。NULLを許すメモでは、実在する注文でもNULLになり得るため不在の検査に使わず、右側の非NULLキーを使います。

重複除去より先に意味を確認する

予想外の結果へDISTINCTを加える前に、残す側、NULLを拒否するWHERE、結果一行の意味を調べます。初期キーと状態を表示して確認すると、件数と母集団を混同しにくくなります。集計のNULLはCOUNTの規則でも確認できます。

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

DROP TABLE shopcourse.c27_items;
DROP TABLE shopcourse.c27_orders;
DROP TABLE shopcourse.c27_products;
DROP TABLE shopcourse.c27_customers;

関連する公式リファレンス

MariaDB講座
同じカテゴリーの他の記事