はじめに
SQLで「関連するデータを持つ行」を取得する際、INNER JOINとEXISTSのどちらを使うべきか迷ったことはありませんか？見た目の違いはあっても、結果は同じに見える場面が多いため、なんとなく使い分けている人も多いかもしれません。
本記事では、INNER JOINとEXISTSの構文と挙動、そしてパフォーマンス差について、10万件のユーザーと15万件の関連データを用いて実験・検証してみます。
MySQL テーブル構成とデータ準備
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE purchases (
id INT PRIMARY KEY,
user_id INT,
product VARCHAR(100)
);
それぞれに以下のような大量データを挿入します：
-- ユーザー10万件
INSERT INTO users (id, name)
SELECT
seq,
CONCAT('user_', seq)
FROM (
SELECT
@row := @row + 1 AS seq
FROM
information_schema.columns,
information_schema.columns,
(SELECT @row := 0) AS init
LIMIT 100000
) AS seed;
-- 購入履歴15万件（ユーザーIDに偏りあり）
INSERT INTO purchases (id, user_id, product)
SELECT
seq,
FLOOR(1 + RAND() * 100000),
CONCAT('item_', FLOOR(RAND() * 100))
FROM (
SELECT
@row := @row + 1 AS seq
FROM
information_schema.columns,
information_schema.columns,
(SELECT @row := 0) AS init
LIMIT 150000
) AS seed;
これにより、特定のユーザーには複数の購入履歴が紐付きます。
SELECT
*
FROM
purchases
ORDER BY
user_id desc
limit 10;
などでuser_idがmax100000であること、1つのIDに複数のproductが紐づいていることを確認してください。
※ランダム生成なのでmaxが99999とかになることもあります。
INNER JOINを使った「購入履歴のあるユーザー」抽出
SELECT DISTINCT
u.id
FROM
users AS u
INNER JOIN
purchases AS p
ON
u.id = p.user_id;
INNER JOINは、両テーブルで一致する行を返します。この場合、購入履歴のあるユーザーのみが取得されます。ただし、同じユーザーが複数購入している場合は重複するため、DISTINCTでの除外が必要です。
EXISTSを使った同様の取得方法
SELECT
u.id
FROM
users AS u
WHERE
EXISTS (
SELECT
1
FROM
purchases AS p
WHERE
p.user_id = u.id
);
この構文では、ユーザーごとにサブクエリで購入履歴が存在するかを調べています。重複行が存在しないため、DISTINCTも不要です。
パフォーマンス比較（10万×15万）
インデックスは張らずに、MySQLでクエリ時間を計測してみました。
INNER JOIN と EXISTS のパフォーマンス比較
| クエリ | 実行回数 (COUNT_STAR) |
合計実行時間 (total_time_sec) |
平均実行時間 (avg_time_sec) |
|---|---|---|---|
| SELECT DISTINCTROW `u`.`id` FROM `users` AS `u` INNER JOIN `purcha…` |
10 | 3.8297 | 0.3830 |
| SELECT `u`.`id` FROM `users` AS `u` WHERE EXISTS (SELECT ? FROM …) |
10 | 1.6099 | 0.1610 |
- INNER JOIN + DISTINCT：約3.8秒
- EXISTS：約1.6秒
INNER JOINは中間結果が膨大になり、DISTINCTでの重複排除にも時間がかかります。一方、EXISTSは条件を満たす1件さえ見つかればサブクエリを終了するため、効率が良いです。
EXPLAINによる実行計画の違い
INNER JOINの実行計画では：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | p | NULL | ALL | NULL | NULL | NULL | NULL | 150080 | 100.00 | Using where |
| 1 | SIMPLE | u | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.p.u_id | 1 | 100.00 | Using index |
- users: equal reference
- purchases: full scan
- 重複行をJOINで大量に生成 → DISTINCTでフィルタリング
EXISTSの場合：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | u | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 97689 | 100.00 | Using index |
| 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | testdb.u.id | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | p | NULL | ALL | NULL | NULL | NULL | NULL | 150080 | 100.00 | NULL |
- users: index full scan
- purchases: full scan
- サブクエリ: equal reference
- 不要なデータの生成が少ない
”結合結果が大きくなるほど、EXISTSの効率が際立ちます。”
↑ってチャッピー（ChatGPT）のコメントなんだけど、DISTINCTが効率が悪いって考えの方が近いんじゃないかな。
MySQL 使い分けの指針
- INNER JOINは結果を結合して「複数の列を同時に取得」したいときに有効。つまりDISTINCTしないなら早い
- EXISTSは「存在するかどうか」だけを調べる用途に最適
- EXISTSは重複を持たない構造になっており、パフォーマンスに優れることが多い
- データ量が多くなるほど、JOINよりEXISTSの利点が顕著になる
次回のテーマ
LEFT JOINとNOT EXISTSの違いとパフォーマンス比較 をやってみたいと思います。