SQL Serverでcol2=1のcol1をキーに全レコード取得するSQL（EXISTS/IN/JOIN/インデックス最適化）

SQL Serverで「特定条件の行を持つキー（col1）を見つけ、そのキーに紐づく全レコードをまとめて取得したい」という要件は、ログ分析や状態管理テーブルで頻出です。この記事では EXISTS を中心に、IN/JOIN/ウィンドウ関数の書き分けと、重複・NULL・インデックス設計まで実務目線で整理します。

やりたいことを言い換えると「条件を満たすキー集合で全行をフィルタ」

テーブルが (col1, col2) だとして、今回の要件は次の2段階に分解できます。

まず col2 = 1 の行に登場する col1 を集める（キー集合を作る）
そのキー集合に含まれる col1 を持つ行をすべて 返す

つまり「col2=1 を持つ col1 を抽出し、その col1 の全行を返す」クエリです。ここを押さえると、EXISTS / IN / JOIN いずれの書き方も同じ設計思想で理解できます。

サンプルデータで期待結果を明確にする

例として、次のようなデータがあるとします（見やすさのために行番号を付けています）。

行	col1	col2	意味（例）
1	1	0	通常行
2	2	0	通常行
3	2	1	フラグ行
4	2	0	通常行
5	3	0	通常行
6	4	1	フラグ行

このとき、col2=1 の行に出てくる col1 は 2 と 4 です。したがって「col1 が 2 または 4 の全行」を返すのがゴールになります。

期待される出力	col1	col2	ポイント
行3相当	2	1	フラグ行も当然含める
行2相当	2	0	同じ col1 の別行も全部含める
行4相当	2	0	複数行あっても全部
行6相当	4	1	col2=1 の別キーも同様

この「期待結果」を最初に固定しておくと、JOINの重複やNULLの扱いなどで迷いにくくなります。

定番の解決策：EXISTS（自己相関サブクエリ）で“同じcol1にフラグ行があるか”を見る

最も素直で、実務でもまず候補になるのが EXISTS を使った自己相関サブクエリです。考え方はシンプルで、外側の行（a）に対して「同じ col1 で col2=1 の行（b）が存在するなら採用」という条件にします。

SELECT a.col1, a.col2
FROM dbo.Table1 AS a
WHERE EXISTS (
    SELECT 1
    FROM dbo.Table1 AS b
    WHERE b.col2 = 1
      AND b.col1 = a.col1
);

この書き方が強い理由は、要件をそのままSQLに落とせて読みやすいことに加え、SQL Serverの最適化（オプティマイザ）が セミジョイン（Left Semi Join） として扱える点にあります。EXISTSは「存在するかどうか」だけが必要なので、b側で一致が1件見つかった時点で論理的には十分です（実際の実行計画は状況により Hash Match などに変換されることがあります）。

また、サブクエリの SELECT 1 は慣習的な書き方で、返す列は何でもよい（評価されない）ため、ここでは最小限の意図を示すのが目的です。

“まずcol2=1だけ取る”も同時に満たしている

冒頭の要件は「(1) col2=1の行を取りたい」→「(2) そのcol1の全行を取りたい」でした。上のEXISTSは (2) の最終形なので、結果セットの中には当然 col2=1の行も含まれます。別クエリに分けなくても、一発で「フラグ行＋同じキーの全行」が取れます。

追加条件がある場合も書きやすい

例えば、a側（返したい全行）に期間や状態などのフィルタを追加したいケースは多いです。たとえば「直近30日分だけ」など。

SELECT a.col1, a.col2
FROM dbo.Table1 AS a
WHERE a.created_at >= DATEADD(DAY, -30, SYSDATETIME())
  AND EXISTS (
      SELECT 1
      FROM dbo.Table1 AS b
      WHERE b.col2 = 1
        AND b.col1 = a.col1
  );

このように、“全行側にかける条件”と“キー集合を決める条件”を分けて書けるのがEXISTSの扱いやすさです。

同等の書き方：IN で「キー集合に含まれるか」をそのまま書く

要件を「col2=1 の col1 を集め、その集合に含まれる col1 の行を返す」と捉えると、IN でも自然に書けます。

SELECT col1, col2
FROM dbo.Table1
WHERE col1 IN (SELECT col1 FROM dbo.Table1 WHERE col2 = 1);

集合演算っぽく読めるので、SQLに慣れている人ほど直感的に理解しやすいことがあります。EXISTSとINは、データ量や統計情報、インデックスの有無によって実行計画が似通うことも多く、「どちらが常に速い」と断言はできません。とはいえ、迷ったらEXISTS、読みやすさ重視ならIN、という選択が現場ではよくあります。

INを使うときの細かい注意点

NULLが絡む場合：col1 が NULL の行を「同じNULL同士で一致」とみなしたい要件は、INでもEXISTSでもそのままでは満たせません（SQLのNULL比較は “不明” になるため）。通常はキー列にNULLを許さない設計が多いですが、もし許しているなら後述の「NULLの扱い」を参照してください。
サブクエリの重複：INは内部的に集合として扱われやすく、重複行があっても論理結果は変わりません。ただし、状況によっては SELECT DISTINCT を付けた方が分かりやすい／最適化のヒントになることがあります。

SELECT col1, col2
FROM dbo.Table1
WHERE col1 IN (SELECT DISTINCT col1 FROM dbo.Table1 WHERE col2 = 1);

ただし、DISTINCTはケースによっては余計なソートやハッシュ集約が増えることもあるため、実行計画とIOを見ながら判断するのが安全です。

JOINで書く場合：重複の罠と、正しい“半結合”の作り方

JOINで表現する場合、考え方としては次の2ステップです。

col2=1 の col1 を抽出した“キー一覧”を作る
元テーブルとキー一覧を結合して、該当キーの全行を返す

このとき、よくある落とし穴が重複です。もし col2=1 の行が同じ col1 に複数存在すると、単純にJOINすると外側の行が増殖します。

やりがちな（重複する可能性がある）JOIN

SELECT a.col1, a.col2
FROM dbo.Table1 AS a
JOIN dbo.Table1 AS b
  ON b.col1 = a.col1
 AND b.col2 = 1;

このJOINは「b側に一致する行の数だけa側が増える」ため、b側に同じcol1のフラグ行が2件あると、a側の各行が2倍になります。要件が「全行を1回ずつ返す」なら不適です（逆に“フラグ行の件数分だけ増やしたい”なら正しい場合もありますが、今回は違います）。

重複を防ぐJOIN：キー一覧をDISTINCTでユニーク化してから結合

JOINを使うなら、キー一覧は必ず一意化しておくと安全です。

WITH Keys AS (
    SELECT DISTINCT col1
    FROM dbo.Table1
    WHERE col2 = 1
)
SELECT a.col1, a.col2
FROM dbo.Table1 AS a
JOIN Keys AS k
  ON k.col1 = a.col1;

この形は論理的に「キー一覧との結合」なので、読み手が「まずキー集合を作っている」と理解しやすいメリットがあります。一方で、SQL ServerはEXISTS/INをセミジョインへ変換できるのに対し、JOIN＋DISTINCTは書き方によって余計な処理が入る場合もあります。実行計画が複雑になったり、意図せずメモリ消費が増えたりすることもあるので、“JOINで書きたい理由”が明確なときに選ぶのが無難です。

ウィンドウ関数で“一度の走査”にまとめる方法

SQL Serverではウィンドウ関数（OVER句）を使って、「同じ col1 のグループ内に col2=1 が存在するか」を判定する方法もあります。たとえば MAX(CASE …) を使うと、グループ内に1があればフラグが立つ、という形にできます。

SELECT col1, col2
FROM (
    SELECT
        col1,
        col2,
        MAX(CASE WHEN col2 = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY col1) AS has_flag
    FROM dbo.Table1
) AS x
WHERE x.has_flag = 1;

この書き方の良い点は、発想が「グループ判定」なので 要件の本質に近いことです。col2が0/1のフラグでなくても、「特定ステータスが一度でも出たキーの全履歴を取る」といった場面に応用しやすいです。

一方で、ウィンドウ関数は内部的に パーティション（col1）単位の計算が必要になるため、データ量やインデックス状況によってはソートやハッシュが発生します。EXISTSが高速に決まる環境では、あえてウィンドウ関数にしない方が速いこともあります。

手法別の特徴を整理（どれを選ぶべきか）

同じ結果を返すクエリでも、保守性・重複リスク・最適化されやすさが少しずつ違います。現場で迷ったとき用に、特徴を表にまとめます。

書き方	読みやすさ	重複リスク	最適化されやすさ	向いている場面
EXISTS（自己相関）	高い	低い	高い（セミジョイン化されやすい）	迷ったらまずこれ。追加条件も書きやすい
IN（サブクエリ）	高い	低い	高い（状況によりEXISTSと同等）	「キー集合に含まれるか」が直感的に書きたい
JOIN（キー一覧と結合）	中	中（DISTINCT無しだと高）	中（書き方次第）	キー一覧を別用途にも使いたい、段階的に組みたい
ウィンドウ関数	中〜高	低い	中（ソート/ハッシュの可能性）	「グループ内に条件行がある」系をまとめて表現したい

パフォーマンスの要点：インデックスは“どこを絞って、どこを返すか”で決まる

今回のクエリは本質的に次の2つをやっています。

絞り込み側：col2=1 の行から col1 を見つける（キー集合を作る）
取得側：その col1 を持つ行を全件返す（該当キーの全行を読む）

つまり、インデックスも「絞り込みを速くするもの」と「該当キーの行を集めるもの」の両方が効く可能性があります。代表的な選択肢を整理します。

複合インデックス： (col1, col2) と (col2, col1) の考え方

インデックス例	効きやすい部分	想定される効果	注意点
(col1, col2)	“同じcol1にcol2=1があるか”の探索	EXISTSで b.col1=a.col1 を起点に探しやすい	col2=1 を起点にキー集合を作る場合は不利なことも
(col2, col1)	col2=1 の抽出（キー集合の生成）	col2=1 が少ないほどキー抽出が高速化	該当キーの全行取得には別のアクセスが必要になる場合

どちらが正解かは、データ分布次第です。ざっくり言うと、

col2=1 が少ない（選択度が高い） → (col2, col1) が効きやすい
col1でグルーピングして見ることが多い → (col1, col2) が効きやすい

SQL Serverなら“フィルター付きインデックス”が刺さることがある

col2=1 が全体の一部に過ぎない場合、SQL Serverの フィルター付きインデックス（filtered index）が非常に有効なことがあります。col2=1 の行だけを小さなインデックスとして持てるので、キー集合の抽出が軽くなります。

CREATE INDEX IX_Table1_col1_only_flag
ON dbo.Table1 (col1)
WHERE col2 = 1;

このインデックスは「col2=1 の行に含まれる col1 を探す」処理に直撃します。EXISTSでもINでも、最適化の結果としてこのインデックスが使われる可能性があります。

ただし、注意点もあります。

col2=1 が多い（たとえば全体の半分以上）なら、フィルターの旨味は薄くなりがちです
更新が多いテーブルでは、インデックス増加による更新コストも加味が必要です
環境によっては統計情報やパラメータにより計画が揺れることがあります

カバリング（INCLUDE）で“戻り列”を支える

サンプルでは返す列が col1, col2 だけですが、実務では日時や状態、メッセージなど列が増えがちです。返す列が増えるほど、非クラスタ化インデックスだけで結果を返せず、キー参照（Key Lookup）が多発して遅くなることがあります。

その場合、アクセス頻度が高いクエリに限って INCLUDE でカバリングするのも手です（列数が多いとインデックス肥大化するため、無差別に入れないのがコツです）。

CREATE INDEX IX_Table1_col1_col2_cover
ON dbo.Table1 (col1, col2)
INCLUDE (created_at, status);

どの列をINCLUDEすべきかは「そのクエリで頻繁にSELECTされ、かつWHERE/JOINのキーにならない列」を優先すると考えやすいです。

チューニングは“体感”ではなくIOと計画で判断する

EXISTSとIN、JOIN、ウィンドウ関数は、環境によって勝ち負けが変わります。チューニングするなら、次のように機械的に確認するのが安全です。

実際の実行プランで、Hash Match / Nested Loops / Merge などの戦略を確認する
SET STATISTICS IO, TIME ON で論理読み取り（logical reads）と時間を比較する
統計情報が古いと推定が外れやすいので、必要なら統計更新も検討する

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- 比較したいクエリを実行

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

特に今回のような「条件行を持つキーの全行取得」は、データ分布（col2=1の割合、col1の偏り）で最適解が変わりやすい部類です。“一般論の速さ”より、あなたのテーブルでどう動くかを基準に選びましょう。

NULLや複合キーなど、現場で詰まりやすいポイント

col1 がNULLのときは“同じNULL”で一致しない

SQLでは NULL = NULL は真になりません（比較結果はUNKNOWN）。そのため、col1がNULLを取り得る設計で「col1がNULLでも、col2=1があれば同じNULLの行を全部返したい」という要件だと、単純なEXISTS/INでは取りこぼします。

もし本当にNULL同士を同一キーと扱う必要があるなら、意図を明示して条件を書く必要があります（ただし、そもそもキー列にNULLを許さない設計に寄せる方がトラブルは減ります）。

SELECT a.col1, a.col2
FROM dbo.Table1 AS a
WHERE EXISTS (
    SELECT 1
    FROM dbo.Table1 AS b
    WHERE b.col2 = 1
      AND (
            (b.col1 = a.col1)
         OR (b.col1 IS NULL AND a.col1 IS NULL)
      )
);

ただし、この分岐はインデックスが効きにくくなることもあるため、設計上のNULL許容は慎重に判断してください。

キーが複数列（複合キー）なら、比較も同じ列を揃える

実務では「col1だけでなく、col3も合わせてキー」など複合キーになることがあります。その場合は、EXISTSの相関条件を複合にするだけです。

SELECT a.col1, a.col3, a.col2
FROM dbo.Table1 AS a
WHERE EXISTS (
    SELECT 1
    FROM dbo.Table1 AS b
    WHERE b.col2 = 1
      AND b.col1 = a.col1
      AND b.col3 = a.col3
);

このときのインデックスも、(col1, col3, col2) のように比較に使う列を先頭に寄せると効きやすくなります。

“キー集合を作る条件”が複雑な場合はEXISTSが読みやすい

例えば「col2=1 かつ、type=’A’ の行が存在する col1 の全行を返す」など、キー集合の条件が増えるとINよりEXISTSの方が読みやすくなることが多いです。

SELECT a.col1, a.col2
FROM dbo.Table1 AS a
WHERE EXISTS (
    SELECT 1
    FROM dbo.Table1 AS b
    WHERE b.col1 = a.col1
      AND b.col2 = 1
      AND b.type = 'A'
);

「何をトリガーに全行を取るのか」が条件ブロックとしてまとまるため、保守時に事故が起きにくくなります。

SELECTだけでなくUPDATE/DELETEにも応用できる

今回のパターンは「条件行を持つキーに対して、キー配下の全行に操作する」という形なので、更新系にもそのまま応用できます。たとえば「col2=1 を持つ col1 の全行にフラグを立てる」なら次のようになります。

UPDATE a
SET a.processed = 1
FROM dbo.Table1 AS a
WHERE EXISTS (
    SELECT 1
    FROM dbo.Table1 AS b
    WHERE b.col2 = 1
      AND b.col1 = a.col1
);

削除も同様ですが、削除は影響が大きいので、必ず事前にSELECTで対象件数を確認し、トランザクションやバックアップ方針も含めて慎重に進めてください。

実務での結論：まずEXISTS、必要に応じてIN/JOIN/ウィンドウ関数を使い分ける

要件が「col2=1 を持つ col1 の全行を返す」なら、EXISTS が最も安全で意図も明確
集合として読みたいなら IN も同等（NULLや追加条件が増えるとEXISTSが楽）
JOINは DISTINCTでキーを一意化しないと重複事故が起きやすい
グループ判定の表現としては ウィンドウ関数も有力だが、ソート/ハッシュのコストを要確認
件数が多いなら、(col2, col1) の複合インデックスやフィルター付きインデックス（col2=1）が効く可能性が高い

最終的には「あなたのテーブルの分布」と「実行計画」が答えです。まずはEXISTSで正しく動く形を作り、統計IOと実行計画を見ながら、インデックスや書き方を最小限の変更で詰めていくのが、失敗しにくい進め方です。

この記事を書いた人

佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

SQL Serverでcol2=1のcol1をキーに全レコード取得するSQL（EXISTS/IN/JOIN/インデックス最適化）

やりたいことを言い換えると「条件を満たすキー集合で全行をフィルタ」

サンプルデータで期待結果を明確にする

定番の解決策：EXISTS（自己相関サブクエリ）で“同じcol1にフラグ行があるか”を見る

“まずcol2=1だけ取る”も同時に満たしている

追加条件がある場合も書きやすい

同等の書き方：IN で「キー集合に含まれるか」をそのまま書く

INを使うときの細かい注意点

JOINで書く場合：重複の罠と、正しい“半結合”の作り方

やりがちな（重複する可能性がある）JOIN

重複を防ぐJOIN：キー一覧をDISTINCTでユニーク化してから結合

ウィンドウ関数で“一度の走査”にまとめる方法

手法別の特徴を整理（どれを選ぶべきか）

パフォーマンスの要点：インデックスは“どこを絞って、どこを返すか”で決まる

複合インデックス： (col1, col2) と (col2, col1) の考え方

SQL Serverなら“フィルター付きインデックス”が刺さることがある

カバリング（INCLUDE）で“戻り列”を支える

チューニングは“体感”ではなくIOと計画で判断する

NULLや複合キーなど、現場で詰まりやすいポイント

col1 がNULLのときは“同じNULL”で一致しない

キーが複数列（複合キー）なら、比較も同じ列を揃える

“キー集合を作る条件”が複雑な場合はEXISTSが読みやすい

SELECTだけでなくUPDATE/DELETEにも応用できる

実務での結論：まずEXISTS、必要に応じてIN/JOIN/ウィンドウ関数を使い分ける

この記事を書いた人

コメント

コメントするコメントをキャンセル

SQL Serverでcol2=1のcol1をキーに全レコード取得するSQL（EXISTS/IN/JOIN/インデックス最適化）

やりたいことを言い換えると「条件を満たすキー集合で全行をフィルタ」

サンプルデータで期待結果を明確にする

定番の解決策：EXISTS（自己相関サブクエリ）で“同じcol1にフラグ行があるか”を見る

“まずcol2=1だけ取る”も同時に満たしている

追加条件がある場合も書きやすい

同等の書き方：IN で「キー集合に含まれるか」をそのまま書く

INを使うときの細かい注意点

JOINで書く場合：重複の罠と、正しい“半結合”の作り方

やりがちな（重複する可能性がある）JOIN

重複を防ぐJOIN：キー一覧をDISTINCTでユニーク化してから結合

ウィンドウ関数で“一度の走査”にまとめる方法

手法別の特徴を整理（どれを選ぶべきか）

パフォーマンスの要点：インデックスは“どこを絞って、どこを返すか”で決まる

複合インデックス： (col1, col2) と (col2, col1) の考え方

SQL Serverなら“フィルター付きインデックス”が刺さることがある

カバリング（INCLUDE）で“戻り列”を支える

チューニングは“体感”ではなくIOと計画で判断する

NULLや複合キーなど、現場で詰まりやすいポイント

col1 がNULLのときは“同じNULL”で一致しない

キーが複数列（複合キー）なら、比較も同じ列を揃える

“キー集合を作る条件”が複雑な場合はEXISTSが読みやすい

SELECTだけでなくUPDATE/DELETEにも応用できる

実務での結論：まずEXISTS、必要に応じてIN/JOIN/ウィンドウ関数を使い分ける

この記事を書いた人

関連記事

コメント

コメントする コメントをキャンセル

コメントするコメントをキャンセル