MySQL 入れ子JOINや副問い合わせを活用した柔軟な絞り込み手法を紹介します
本記事では、ECサイトなどでよく見られる「ユーザー」「注文」「支払い」の3テーブル構成を使いながら、JOINと副問い合わせを活用した柔軟な絞り込み手法を紹介します。INNER JOIN や LEFT JOIN、NOT EXISTS、入れ子JOIN などを用いて、実務でも役立つデータの抽出方法を確認していきます。
今回の用意したMySQLのデータ構造（テストデータ）
-- users テーブル
INSERT INTO users (id, name)
VALUES
(1, 'Alice'), (2, 'Bob'), (3, 'Charlie'), (4, 'Diana'), (5, 'Ethan'),
(6, 'Fiona'), (7, 'George'), (8, 'Hannah'), (9, 'Ian'), (10, 'Julia');
-- orders テーブル（全15件）
INSERT INTO orders (id, user_id, item)
VALUES
(1, 1, 'Book'), (2, 2, 'Pen'), (3, 3, 'Notebook'), (4, 1, 'Bag'),
(5, 4, 'Tablet'), (6, 6, 'Headphones'), (7, 7, 'Camera'), (8, 8, 'Charger'),
(9, 9, 'Mouse'), (10, 10, 'Keyboard'), (11, 2, 'Laptop'),
(12, 3, 'Monitor'), (13, 5, 'Desk'), (14, 6, 'Phone Case'), (15, 10, 'Speaker');
-- payments テーブル（全12件）
INSERT INTO payments (id, order_id, amount)
VALUES
(1, 1, 1200), (2, 2, 300), (3, 3, 500), (4, 4, 2500), (5, 5, 15000),
(6, 6, 9800), (7, 7, 42000), (8, 8, 1600), (9, 9, 2400),
(10, 10, 5600), (11, 11, 89000), (12, 13, 12000);
1. INNER JOIN による絞り込み
支払いが完了している注文だけを対象に、ユーザー名・商品・金額を取得します。
SELECT
u.name,
o.item,
p.amount
FROM
users AS u
INNER JOIN
orders AS o
ON
u.id = o.user_id
INNER JOIN
payments AS p
ON
o.id = p.order_id;
INNER JOIN 出力例
| name | item | amount |
|---|---|---|
| Alice | Book | 1200 |
| Alice | Bag | 2500 |
| Bob | Pen | 300 |
| Bob | Laptop | 89000 |
| Charlie | Notebook | 500 |
| Diana | Tablet | 15000 |
| Fiona | Headphones | 9800 |
| George | Camera | 42000 |
| Hannah | Charger | 1600 |
| Ian | Mouse | 2400 |
| Julia | Keyboard | 5600 |
| Ethan | Desk | 12000 |
2. LEFT JOIN + NULL 判定で未払い注文を抽出
支払いが存在しない注文（未払い）を取り出します。LEFT JOIN を使い、NULL を条件にします。
SELECT
o.*
FROM
orders AS o
LEFT JOIN
payments AS p
ON
o.id = p.order_id
WHERE
p.id IS NULL;
未払い注文の抽出結果
| id | user_id | item |
|---|---|---|
| 12 | 3 | Monitor |
| 14 | 6 | Phone Case |
| 15 | 10 | Speaker |
3. サブクエリで支払い総額が多いユーザーを抽出
支払い合計額が 20,000円以上のユーザー名を取得します。
SELECT
u.name
FROM
users AS u
WHERE
u.id IN (
SELECT
o.user_id
FROM
orders AS o
INNER JOIN
payments AS p
ON
o.id = p.order_id
GROUP BY
o.user_id
HAVING
SUM(p.amount) >= 20000
);
支払い総額が2万円以上のユーザー
| name |
|---|
| Alice |
| Bob |
| Diana |
| George |
| Ethan |
4. 入れ子JOINとWHERE句を使った絞り込み
支払い金額が 10,000円以上の注文だけを対象に、ユーザー名・商品・金額を取得します。
※１つのクエリに同じ一時テーブルを使いまわせないので
payments2を作っています。
CREATE TEMPORARY TABLE payments2 (
id INT PRIMARY KEY,
order_id INT,
amount INT
);
INSERT INTO payments2
SELECT
*
FROM
payments
;
SELECT
u.name,
o.item,
p2.amount
FROM
users AS u
INNER JOIN
( SELECT
o.*
FROM
orders AS o
INNER JOIN
payments AS p
ON
o.id = p.order_id
WHERE
p.amount >= 10000 ) AS o
ON
u.id = o.user_id
INNER JOIN
payments2 AS p2
ON
o.id = p2.order_id;
DROP TABLE IF EXISTS payments2;
| name | item | amount |
|---|---|---|
| Diana | Tablet | 15000 |
| George | Camera | 42000 |
| Bob | Laptop | 89000 |
| Ethan | Desk | 12000 |
5. NOT EXISTS を使って未払い注文を抽出
サブクエリと NOT EXISTS を使って、支払いが存在しない注文を抽出します。
SELECT
*
FROM
orders AS o
WHERE
NOT EXISTS (
SELECT
1
FROM
payments AS p
WHERE
p.order_id = o.id
);
| id | user_id | item |
|---|---|---|
| 12 | 3 | Monitor |
| 14 | 6 | Phone Case |
| 15 | 10 | Speaker |
MySQL まとめ
このように、JOINや副問い合わせを柔軟に組み合わせることで、「支払い済みかつ高額」「未払いだけを除外」「ユーザーごとの条件付き集計」など、業務で必要とされる複雑な絞り込みもSQLだけで実現できます。
次回は、JOINの順序やON句 vs WHERE句の違いによる絞り込み挙動の変化について解説する予定です。