🐬

LEFT JOIN vs INNER JOIN vs サブクエリ

2024/12/27に公開

それぞれのアプローチを比較してみましょう。
LEFT JOIN + WHERE による絞り込み
SELECT u1.user_id
FROM users u1
LEFT JOIN items i1 ON u1.item_id = i1.item_id
WHERE u1.category_id = 1111
AND i1.keyword_id = 2222
INNER JOIN による絞り込み
SELECT u1.user_id
FROM users u1
INNER JOIN items i1 ON u1.item_id = i1.item_id
WHERE u1.category_id = 1111
AND i1.keyword_id = 2222
サブクエリでの事前絞り込み + JOIN
SELECT u1.user_id
FROM (
    SELECT user_id, item_id
    FROM users
    WHERE category_id = 1111
) u1
LEFT JOIN (
    SELECT item_id
    FROM items
    WHERE keyword_id = 2222
) i1 ON u1.item_id = i1.item_id

 パフォーマンスの比較：LEFT JOIN + WHERE
WHEREの条件で NULL を含むすべての行を確認する必要がある
一旦結合してから絞り込むため、中間結果が大きくなる可能性がある
メモリ使用量が多くなる可能性がある
INNER JOIN
最も効率的なケースが多い
結合時点で不要なレコードを除外できる
オプティマイザが最適な実行計画を立てやすい
メモリ使用量を抑えられる
サブクエリでの事前絞り込み
各テーブルで事前に絞り込むため、結合対象のレコード数が減る
ただし、サブクエリの実行コストが加わる
テーブルサイズが大きい場合に効果的な場合がある

 一般的な優先順位：INNER JOIN （最も高速なケースが多い）
サブクエリでの事前絞り込み（大規模データの場合に有効）
LEFT JOIN + WHERE（通常は最も遅い）
ただし、以下の要因で実際の性能は変わる可能性があります：
インデックスの有無と種類
テーブルのサイズ
データの分布
データベースエンジンの特性
WHERE条件の選択性
実際のパフォーマンスを確認するには：
EXPLi1N を使用して実行計画を確認
実際のデータで実行時間を計測
インデックスの使用状況を確認
これらを行った上で、その環境に最適な方法を選択することをお勧めします。

パフォーマンスの比較：

一般的な優先順位：

Discussion