SQL 述語（Predicate） — LIKE・BETWEEN・ANYの応用

QUESTION 6

LIKE述語 × EXISTS述語 — パターンマッチと存在確認を組み合わせた実践検索

LIKE述語EXISTSパターンマッチユーザー検索

前提知識 ▼

LIKE述語は文字列のパターン一致を検査します。%（任意の0文字以上）と _（任意の1文字）の2種類のワイルドカードがあります。EXISTS と組み合わせることで「名前パターン × 購買実績あり」のような実務頻出の複合フィルタを実現できます。

-- % : 0文字以上の任意文字列
WHERE name LIKE '田%'    -- 「田」で始まる（前方一致）※インデックス有効
WHERE name LIKE '%田%'   -- 「田」を含む（中間一致）※インデックス不可
-- _ : 任意の1文字
WHERE code LIKE 'A_01'   -- A?01 形式（?は任意1文字）
-- ILIKE: 大文字小文字を区別しない（PostgreSQL固有）
WHERE email ILIKE '%@example.com'

前方一致（'田%'）のみインデックスが有効：中間・後方一致では B-Tree インデックスが使えず全スキャンになります。大量データには pg_trgm 拡張 + GIN インデックス または全文検索を使いましょう。

問題 ▼

users テーブルから、name が '田' で始まる（LIKE '田%'）かつ status が 'completed' の注文が1件以上あるユーザーを取得してください。取得列は user_id, name, email、user_id 昇順で返してください。

使用テーブル

▶ users

user_id	name	email	plan
1	田中太郎	tanaka@example.com	premium
2	佐藤花子	sato@example.com	standard
3	田村一郎	tamura@example.com	free
4	山田次郎	yamada@example.com	premium
5	伊藤三郎	ito@example.com	premium

▶ orders

order_id	user_id	status
101	1	completed
102	2	completed
103	3	completed
104	3	completed
105	4	cancelled
106	5	pending

期待出力 ▼

user_id	name	email
1	田中太郎	tanaka@example.com
3	田村一郎	tamura@example.com

模範解答コード ▼

SELECT
  user_id, name, email
FROM   users u
WHERE  u.name LIKE '田%'           -- LIKE: '田'で始まる（前方一致のみインデックス有効）
  AND  EXISTS (                     -- SQが1行でも返せばTRUE（短絡評価）
    SELECT 1                        -- 値は何でもよい。行の存在だけを確認
    FROM   orders o
    WHERE  o.user_id = u.user_id    -- 外側の u.user_id を参照する相関条件（必須）
      AND  o.status  = 'completed'
  )
ORDER BY user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM users u                 → 5行（1行ずつ評価）
  2. WHERE u.name LIKE '田%'       → 田姓の候補に絞る
  3. AND EXISTS(...)              → completed 注文ありで絞る
  4. SELECT user_id, name, email  → 3列を選択
  5. ORDER BY user_id             → 昇順
  */

解説（テーブル変化・ポイント） ▼

SELECT
  user_id, name, email
FROM   users u
WHERE  u.name LIKE '田%'
  AND  EXISTS (
  SELECT 1
  FROM   orders o
  WHERE  o.user_id = u.user_id
    AND  o.status  = 'completed'
)
ORDER BY user_id;

LEGEND ▼

データ取得・読込対象

① FROM users ▼

FROM users uメインクエリ対象の users テーブル全5行です。LIKE と EXISTS の2段階評価が各行に適用されます。

1 / 5

user_id	name	email	plan
1	田中太郎	tanaka@example.com	premium
2	佐藤花子	sato@example.com	standard
3	田村一郎	tamura@example.com	free
4	山田次郎	yamada@example.com	premium
5	伊藤三郎	ito@example.com	premium

全 5行読込

学習ポイント

LIKE のワイルドカード2種：% は0文字以上の任意文字列、_ は任意の1文字です。'田%' は「田中」「田村」に一致しますが '山田' は '山' 始まりのため不一致です。'_田%' なら「山田」「本田」のように2文字目が '田' の名前にも一致します。

AND の短絡評価による効率化：WHERE 句で AND の左辺が FALSE の場合、右辺を評価しません。LIKE を先に書くことでパターン不一致の行は EXISTS サブクエリを実行せずスキップできます。選択率の高いフィルタを AND の前に書くのが実務でのパフォーマンス最適化の基本です。

ILIKE（PostgreSQL固有）：ILIKE は大文字小文字を区別しない検索ができます。メールアドレス検索などに有効ですが、通常の B-Tree インデックスは ILIKE でも効かないため pg_trgm インデックスの併用が実務では必要です。

アンチパターン

中間・後方一致でインデックスが効かない：LIKE '%田%' や LIKE '%郎' は B-Tree インデックスが使えず全テーブルスキャンになります。大量データの名前・本文検索には pg_trgm 拡張 + GIN インデックスや PostgreSQL の FTS（to_tsvector/to_tsquery）を使いましょう。

_ の誤用で想定外の行がマッチ：LIKE '田_郎' は「田 + 任意1文字 + 郎」のみにマッチします。日本人名のスペース区切り（「田村一郎」）には一致しません。姓名の間にスペースがあるデータには '田%郎' や '田%' のほうが安全です。

実務コラム：名前検索と全文検索の使い分け

ECサイトの商品名検索やユーザー名フィルタで LIKE は手軽ですが、前方一致（'田%'）のみインデックスが有効という制約を常に意識する必要があります。PostgreSQL の pg_trgm 拡張を有効にすると CREATE INDEX ON users USING gin(name gin_trgm_ops); の1行で中間一致 LIKE にもインデックスが効くようになります。さらに本格的な全文検索が必要な場合は Elasticsearch や PostgreSQL FTS（tsvector）が選択肢です。述語レベルの知識があることで「どこがボトルネックか」を正確に議論できます。

QUESTION 7

BETWEEN述語 × NOT EXISTS述語 — 価格帯フィルタと未販売商品の検出

BETWEENNOT EXISTS範囲フィルタ在庫・販売分析

前提知識 ▼

BETWEEN は範囲条件を簡潔に書ける述語です。a BETWEEN x AND y は a >= x AND a <= y（両端を含む閉区間）と等価です。NOT EXISTS と組み合わせることで「価格帯 × 売上実績なし」のような在庫・販売分析クエリを実現できます。

-- BETWEEN: 両端（x, y）を含む閉区間
WHERE price BETWEEN 3000 AND 7000   -- >= 3000 AND <= 7000 と等価

-- NOT BETWEEN: 範囲外
WHERE price NOT BETWEEN 3000 AND 7000 -- < 3000 OR > 7000 と等価

-- 日付範囲での注意点（timestamp型）
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'
-- timestamp型では '2024-01-31 00:00:00' まで → 31日の注文が漏れる
-- 正: created_at >= '2024-01-01' AND created_at < '2024-02-01'

BETWEEN の可読性：上下限のセットが視覚的に明確で意図が伝わりやすいです。ただし日付・タイムスタンプ型では末日 00:00:00 までしか含まれない罠があるため、日付範囲には >= start AND < end+1 形式が安全です。

問題 ▼

products テーブルから、category が 'アクセサリ' かつ price が 3,000〜7,000（BETWEEN）の商品のうち、completed の注文が1件もない商品を NOT EXISTS で取得してください。取得列は product_id, name, price, stock、price 昇順で返してください。

使用テーブル

▶ products

product_id	name	category	price	stock
1	ワイヤレスマウス	アクセサリ	3500	80
2	メカニカルキーボード	アクセサリ	8800	45
3	HDMIケーブル 2m	アクセサリ	1200	150
4	USBハブ 7ポート	アクセサリ	4200	30
5	外付けSSD 1TB	ストレージ	9800	20
6	Webカメラ HD	アクセサリ	6500	15

▶ orders

order_id	product_id	status
101	4	completed
102	6	pending
103	1	cancelled

期待出力 ▼

product_id	name	price	stock
1	ワイヤレスマウス	3500	80
6	Webカメラ HD	6500	15

模範解答コード ▼

SELECT
  product_id, name, price, stock
FROM   products p
WHERE  p.category = 'アクセサリ'             -- ① カテゴリフィルタ
  AND  p.price BETWEEN 3000 AND 7000    -- ② 価格帯（両端を含む閉区間）
  AND  NOT EXISTS (                     -- ③ SQが0行 = completed注文が1件もない
    SELECT 1
    FROM   orders o
    WHERE  o.product_id = p.product_id  -- 外側商品との相関条件（必須）
      AND  o.status      = 'completed'  -- 完了済み注文のみ確認
  )
ORDER BY p.price;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM products p                        → 6行読み込み
  2. WHERE p.category = 'アクセサリ'             → カテゴリで絞る
  3. AND p.price BETWEEN ...                → 価格帯で絞る
  4. AND NOT EXISTS(...)                    → 該当注文なしで絞る
  5. SELECT product_id, name, price, stock  → 4列を選択
  6. ORDER BY p.price                       → 価格昇順
  */

解説（テーブル変化・ポイント） ▼

SELECT
  product_id, name, price, stock
FROM   products p
WHERE  p.category = 'アクセサリ'
  AND  p.price BETWEEN 3000 AND 7000
  AND  NOT EXISTS (
  SELECT 1
  FROM   orders o
  WHERE  o.product_id = p.product_id
    AND  o.status     = 'completed'
)
ORDER BY p.price;

LEGEND ▼

データ取得・読込対象

① FROM products ▼

FROM products pメインクエリ対象の products テーブル全6行です。カテゴリ → BETWEEN → NOT EXISTS の3段階フィルタを適用します。

1 / 6

product_id	name	category	price	stock
1	ワイヤレスマウス	アクセサリ	3500	80
2	メカニカルキーボード	アクセサリ	8800	45
3	HDMIケーブル 2m	アクセサリ	1200	150
4	USBハブ 7ポート	アクセサリ	4200	30
5	外付けSSD 1TB	ストレージ	9800	20
6	Webカメラ HD	アクセサリ	6500	15

全 6行読込

学習ポイント

BETWEEN は両端を含む閉区間：price BETWEEN 3000 AND 7000 は price >= 3000 AND price <= 7000 と完全に等価です。3000 と 7000 の両端が含まれます。可読性が高く意図が明確になりますが、等価変換を理解した上で使うことが大切です。

日付 BETWEEN のタイムスタンプ罠：created_at BETWEEN '2024-01-01' AND '2024-01-31' は timestamp 型の場合 2024-01-31 00:00:00 までしか含まれません。1月31日中の注文が漏れます。日付範囲には >= '2024-01-01' AND < '2024-02-01' を推奨します。

多段フィルタとインデックス戦略：category（等値比較）→ price BETWEEN（範囲）→ NOT EXISTS（相関SQ）の順で評価することで行数を効率よく削減できます。EXPLAIN ANALYZE で実行計画を確認し、各フィルタで Index Scan が使われているか確認する習慣をつけましょう。

アンチパターン

NOT BETWEEN と OR 条件の混同：NOT BETWEEN 3000 AND 7000 は < 3000 OR > 7000 です。うっかり NOT (price >= 3000 AND price <= 7000) を price < 3000 AND price > 7000（絶対に TRUE にならない）と書いてしまうミスが多いです。ド・モルガンの法則で AND → OR に変換される点を押さえましょう。

文字列 BETWEEN のコード体系の罠：status BETWEEN 'a' AND 'c' は照合順序（Collation）に依存した辞書順比較になります。文字列の範囲指定には IN や OR を使うほうが意図が明確で安全です。

実務コラム：在庫・価格帯管理と BETWEEN の活用

ECやSaaSのダッシュボードでは「この価格帯で在庫があり、かつまだ1回も売れていない商品を見つけたい」というクエリは定期バッチや管理画面でよく書かれます。BETWEEN + NOT EXISTS の組み合わせは「範囲内 × 実績なし」を簡潔に表現できる実務定番パターンです。在庫管理・デッドストック検出・セール対象選定など幅広いシーンで活用できます。

QUESTION 8

IS NULL述語 × IN サブクエリ — NULL安全な絞り込みと動的リストフィルタ

IS NULLIN サブクエリNULL安全三値論理

前提知識 ▼

SQLの三値論理（TRUE / FALSE / UNKNOWN）では、NULL との比較はすべて UNKNOWN を返します。WHERE 句では UNKNOWN は FALSE と同様に行が除外されます。NULL かどうかを調べるには IS NULL / IS NOT NULL の使用が必須です。

-- ✗ = NULL は常に UNKNOWN（絶対に使わない）
WHERE shipped_at = NULL     -- 常に UNKNOWN → 全行除外（意図と真逆）
WHERE shipped_at <> NULL    -- 常に UNKNOWN → 全行除外

-- ✓ IS NULL / IS NOT NULL が唯一正しい書き方
WHERE shipped_at IS NULL     -- NULL の行のみ残す（未発送検出）
WHERE shipped_at IS NOT NULL -- NULL 以外の行のみ（発送済み）

-- NULL は演算に伝播する
SELECT amount + discount_amount         -- discount が NULL → 結果も NULL
SELECT COALESCE(discount_amount, 0)   -- COALESCE で NULL を代替値に変換

三値論理の本質：NULL = NULL の評価結果は TRUE ではなく UNKNOWN です。「NULL 同士は等しい」とは見なされません。PostgreSQL では NULL IS NOT DISTINCT FROM NULL で NULL 同士を等しいとして比較できます。

問題 ▼

orders テーブルから、status が 'processing' かつ shipped_at が NULL（未発送）の注文のうち、plan が 'premium' のユーザーの注文を IN サブクエリで取得してください。取得列は order_id, user_id, amount, status、amount 降順で返してください。

使用テーブル

▶ orders

order_id	user_id	amount	status	shipped_at
101	1	8000	completed	2024-01-15
102	2	5000	processing	NULL
103	1	12000	processing	NULL
104	3	3000	completed	2024-01-20
105	4	9500	processing	2024-01-25
106	3	7000	processing	NULL

▶ users

user_id	name	plan
1	田中太郎	premium
2	佐藤花子	premium
3	鈴木一郎	standard
4	山田次郎	free

期待出力 ▼

order_id	user_id	amount	status
103	1	12000	processing
102	2	5000	processing

模範解答コード ▼

SELECT
  o.order_id, o.user_id, o.amount, o.status
FROM   orders o
WHERE  o.status     =  'processing'       -- ① 処理中の注文のみ
  AND  o.shipped_at IS NULL              -- ② 未発送（= NULL では機能しない！）
  AND  o.user_id    IN (                 -- ③ premiumユーザーの注文に絞り込む
    SELECT user_id                       -- 必ず単一列を返す
    FROM   users
    WHERE  plan = 'premium'              -- premiumのuser_idリストを動的生成
  )
ORDER BY o.amount DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. サブクエリ先行評価                      → premium ユーザーの IN リストを生成
  2. FROM orders o                  → 6行読み込み
  3. WHERE o.status = 'processing'  → 処理中に絞る
  4. AND o.shipped_at IS NULL       → 未出荷に絞る
  5. AND o.user_id IN (...)         → 該当ユーザーに絞る
  6. SELECT order_id, user_id, ...  → 4列を選択
  7. ORDER BY o.amount DESC         → 金額降順
  */

解説（テーブル変化・ポイント） ▼

SELECT
  o.order_id, o.user_id, o.amount, o.status
FROM   orders o
WHERE  o.status     = 'processing'
  AND  o.shipped_at IS NULL
  AND  o.user_id    IN (
  SELECT user_id
  FROM   users
  WHERE  plan = 'premium'
)
ORDER BY o.amount DESC;

LEGEND ▼

データ取得・読込対象

① FROM orders ▼

FROM orders oメインクエリ対象の orders テーブル全6行です。shipped_at が NULL（未発送）と日付が設定されている行（発送済み）が混在しています。

1 / 7

order_id	user_id	amount	status	shipped_at
101	1	8000	completed	2024-01-15
102	2	5000	processing	NULL
103	1	12000	processing	NULL
104	3	3000	completed	2024-01-20
105	4	9500	processing	2024-01-25
106	3	7000	processing	NULL

全 6行読込

学習ポイント

= NULL は絶対に使わない：SQL では NULL = NULL の評価結果は TRUE ではなく UNKNOWN です。WHERE 句で UNKNOWN は除外扱いになるため WHERE shipped_at = NULL と書くと全行が除外されます。NULL の判定には IS NULL / IS NOT NULL のみが有効です。これは習得後も本番バグの原因になり続ける最重要ポイントです。

NULL の演算伝播：amount + discount_amount のような算術演算は、いずれかの項が NULL だと結果全体が NULL になります（NULL伝播）。COALESCE(discount_amount, 0) で NULL を代替値に変換してから計算するパターンが実務標準です。

IS DISTINCT FROM（NULL安全な等値比較）：PostgreSQL では a IS NOT DISTINCT FROM b が a = b OR (a IS NULL AND b IS NULL) と等価で、UPDATE の差分検出（値が変わっていない行をスキップ）などで使われます。NULL を含む列の比較処理で重要なパターンです。

アンチパターン

shipped_at = NULL（最も多い NULL バグ）：WHERE shipped_at = NULL は全行が UNKNOWN → 0件が返ります。「データが存在するのに0件」という現象に遭遇したら IS NULL / IS NOT NULL を疑いましょう。ORM が生成するクエリにもこのバグが含まれることがあります。

COUNT での NULL の扱いの混同：COUNT(shipped_at) は NULL を除いてカウントしますが、COUNT(*) は NULL を含む全行をカウントします。「出荷済み件数」を集計したいのに COUNT(*) を使うと未発送も含まれます。列名指定の COUNT(shipped_at) が正解です。

実務コラム：NULL の設計思想と COALESCE の使いどころ

「shipped_at が NULL = 未発送」のように NULL に意味を持たせる設計は実務で頻出ですが、「値がないこと」と「意味的な状態」を NULL で兼用すると混乱しやすいです。shipped_at IS NULL を多用するなら、shipped_status カラムを別途設けて ENUM 管理するほうがクエリが明快になります。SELECT 句では COALESCE(note, '—') のように NULL を表示用に変換するパターンも実務定番です。

QUESTION 9

ANY述語 / ALL述語 — 複数値との部分比較・全件比較（競合価格分析）

ANY述語ALL述語複数値比較競合分析

前提知識 ▼

ANY述語（SOME とも書ける）は「サブクエリの値のうちいずれか1つとの比較が TRUE なら TRUE」を返します。ALL述語は「サブクエリの値すべてとの比較が TRUE なら TRUE」を返します。

-- ANY: いずれか1つとの比較がTRUE → TRUE（OR の連鎖に相当）
WHERE price < ANY (SELECT price FROM competitor)
-- ↑ 等価: WHERE price < (SELECT MAX(price) FROM competitor)

-- ALL: すべての値との比較がTRUE → TRUE（AND の連鎖に相当）
WHERE price > ALL (SELECT price FROM competitor)
-- ↑ 等価: WHERE price > (SELECT MAX(price) FROM competitor)

-- 特殊な等価変換
WHERE col =  ANY (list)  -- = IN (list) と等価
WHERE col <> ALL (list)  -- = NOT IN (list) と等価（NULLの罠あり）

ALL + NULL の罠：ALL のリストに NULL が含まれると val <> NULL が UNKNOWN になり ALL 全体が FALSE になります。<> ALL（= NOT IN 相当）は NULL 混入で全行除外される問題があるため、実務では NOT EXISTS を優先しましょう。

問題 ▼

products（自社）テーブルから、同一カテゴリの競合製品（competitor_prices テーブル）の価格のいずれかより安い商品を < ANY で取得してください。取得列は product_id, name, price、price 昇順。（解説タブで ALL バリアントも確認してください）

使用テーブル

▶ products（自社）

product_id	name	category	price
1	モデルA ヘッドフォン	オーディオ	12000
2	モデルB ヘッドフォン	オーディオ	8500
3	モデルC ヘッドフォン	オーディオ	15000
4	モデルD ヘッドフォン	オーディオ	6000

▶ competitor_prices（競合）

comp_id	name	category	price
1	競合X ヘッドフォン	オーディオ	9000
2	競合Y ヘッドフォン	オーディオ	11000
3	競合Z ヘッドフォン	オーディオ	13500

期待出力 ▼

product_id	name	price
4	モデルD ヘッドフォン	6000
2	モデルB ヘッドフォン	8500
1	モデルA ヘッドフォン	12000

模範解答コード ▼

-- ① ANY: 競合のいずれかより安い商品（競合の最大値未満）
SELECT product_id, name, price
FROM   products p
WHERE  p.category = 'オーディオ'
  AND  p.price < ANY (           -- いずれか1つより安ければ TRUE
    SELECT price                 -- ↑ MAX(price) 未満と等価
    FROM   competitor_prices c
    WHERE  c.category = 'オーディオ'
  )
ORDER BY p.price;

/*
  実行順序（ANY の場合）:
  1. サブクエリ先行評価                   → 競合価格リストを生成
  2. FROM products p             → 4行読み込み
  3. WHERE p.category = 'オーディオ'  → カテゴリで絞る
  4. AND p.price < ANY (...)     → いずれか未満で絞る
  5. SELECT + ORDER BY p.price   → price 昇順
  */

解説（テーブル変化・ポイント） ▼

SELECT product_id, name, price
FROM   products p
WHERE  p.category = 'オーディオ'
  AND  p.price < ANY (
  SELECT price
  FROM   competitor_prices c
  WHERE  c.category = 'オーディオ'
)
ORDER BY p.price;

LEGEND ▼

データ取得・読込対象

① FROM products（自社） ▼

FROM products p WHERE p.category = 'オーディオ'メインクエリ対象の自社製品テーブルです。同カテゴリ（オーディオ）の4製品に対して ANY 評価を行います。

1 / 5

product_id	name	price
1	モデルA ヘッドフォン	12000
2	モデルB ヘッドフォン	8500
3	モデルC ヘッドフォン	15000
4	モデルD ヘッドフォン	6000

全 4行読込

SELECT product_id, name, price
FROM   products p
WHERE  p.category = 'オーディオ'
  AND  p.price > ALL (
  SELECT price
  FROM   competitor_prices c
  WHERE  c.category = 'オーディオ'
)
ORDER BY p.price;

LEGEND ▼

データ取得・読込対象

① FROM products（自社） ▼

FROM products p WHERE p.category = 'オーディオ'ALL 述語のバリアントです。「すべての競合より高い」製品を探します。p.price > ALL は p.price > MAX(competitor) と等価です。

1 / 4

product_id	name	price
1	モデルA ヘッドフォン	12000
2	モデルB ヘッドフォン	8500
3	モデルC ヘッドフォン	15000
4	モデルD ヘッドフォン	6000

全 4行読込

学習ポイント

ANY / ALL の論理的な意味：ANY（=SOME）は「いずれか1つとの比較が TRUE → 全体 TRUE」（OR の連鎖に相当）、ALL は「すべての比較が TRUE → 全体 TRUE」（AND の連鎖に相当）です。p.price < ANY(list) は p.price < MAX(list)、p.price > ALL(list) は p.price > MAX(list) と等価変換できます。

= ANY は IN と等価：col = ANY (1, 2, 3) は col IN (1, 2, 3) と同じ結果です。実務では IN のほうが可読性が高いため、等値比較には IN を使うのが一般的です。<> ALL は NOT IN と等価ですが NULL の罠があるため NOT EXISTS を優先しましょう。

サブクエリが空（0行）のときの挙動：ANY のサブクエリが0行を返すと ANY は FALSE になります（比較できる値がないため）。ALL のサブクエリが0行のときは ALL は TRUE になります（すべての比較が「空虚に真」= vacuous truth）。この非対称な挙動を押さえておきましょう。

アンチパターン

<> ALL（= NOT IN）での NULL 混入：ALL のリストに NULL が含まれると val <> NULL が UNKNOWN になり全体が FALSE になります。つまり <> ALL は NOT IN と同じ NULL の罠を持ちます。NOT EXISTS を使えば NULL の影響を受けません。

ANY / ALL のサブクエリで複数列を返す：ANY・ALL のサブクエリは必ず単一列を返す必要があります。複数列を返すと構文エラーになります。IN と同様のルールです。

実務コラム：競合価格分析とスカラーサブクエリへの書き換え

ANY / ALL は可読性の面でやや直感的でないため、実務では MAX / MIN を使ったスカラーサブクエリに書き換えるケースが多いです。たとえば price < ANY (SELECT price FROM competitor) は price < (SELECT MAX(price) FROM competitor) と等価で後者のほうが意図が明確です。一方で「特定条件を満たすいずれかより」という複雑な条件では ANY が簡潔になることもあります。どちらの書き方も読めるようにしておくことが実務では重要です。

QUESTION 10

複数EXISTS の応用総合 — AND EXISTS + NOT EXISTS による精密な行選択

複数EXISTSAND EXISTS + NOT EXISTS実務総合ユーザー行動分析

前提知識 ▼

複数の EXISTS / NOT EXISTS を AND で連結することで「条件Aを満たす行が存在し、かつ条件Bを満たす行は存在しない」という精密な行選択が実現できます。これは実務の行動分析・セグメント抽出で最も頻出するパターンのひとつです。

-- 複数 EXISTS を AND で連結
WHERE EXISTS (               -- ① 条件A を満たす行が存在する
  SELECT 1 FROM orders o1
  WHERE  o1.user_id = u.user_id
    AND  o1.status = 'completed'
)
AND NOT EXISTS (             -- ② 条件B を満たす行は存在しない
  SELECT 1 FROM orders o2
  WHERE  o2.user_id = u.user_id
    AND  o2.status = 'cancelled'
)

複数EXISTS の威力：「completedの注文は存在するが、cancelledの注文は存在しない優良ユーザー」のような複雑な行動パターンを、サブクエリを追加するだけでシンプルに表現できます。JOIN + 集計では難しい条件も EXISTS の組み合わせで直感的に書けます。

問題 ▼

users テーブルから、plan が 'premium' または 'standard' のユーザーのうち、status が 'completed' の注文が1件以上あり、かつ status が 'cancelled' の注文が1件もないユーザーを取得してください。取得列は user_id, name, plan、user_id 昇順で返してください。

使用テーブル

▶ users

user_id	name	plan
1	田中太郎	premium
2	佐藤花子	standard
3	鈴木一郎	premium
4	山田次郎	free
5	伊藤三郎	standard

▶ orders

order_id	user_id	status
101	1	completed
102	1	cancelled
103	2	completed
104	3	completed
105	3	pending
106	5	pending

期待出力 ▼

user_id	name	plan
2	佐藤花子	standard
3	鈴木一郎	premium

模範解答コード ▼

SELECT
  user_id, name, plan
FROM   users u
WHERE  u.plan IN ('premium', 'standard')  -- ① 対象プランのみ
  AND  EXISTS (                           -- ② completed注文が1件以上ある
    SELECT 1
    FROM   orders o1
    WHERE  o1.user_id = u.user_id         -- 外側との相関条件
      AND  o1.status  = 'completed'
  )
  AND  NOT EXISTS (                       -- ③ cancelled注文が1件もない
    SELECT 1
    FROM   orders o2                      -- 別エイリアスで同テーブルを参照
    WHERE  o2.user_id = u.user_id         -- 外側との相関条件
      AND  o2.status  = 'cancelled'
  )
ORDER BY user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM users u                     → 5行（1行ずつ評価）
  2. WHERE u.plan IN (...)            → プランで絞る
  3. AND EXISTS(...completed...)      → completed 注文ありで絞る
  4. AND NOT EXISTS(...cancelled...)  → cancelled 注文なしで絞る
  5. SELECT user_id, name, plan       → 3列を選択
  6. ORDER BY user_id                 → 昇順
  */

解説（テーブル変化・ポイント） ▼

SELECT
  user_id, name, plan
FROM   users u
WHERE  u.plan IN ('premium', 'standard')
  AND  EXISTS (
  SELECT 1
  FROM   orders o1
  WHERE  o1.user_id = u.user_id
    AND  o1.status  = 'completed'
)
  AND  NOT EXISTS (
  SELECT 1
  FROM   orders o2
  WHERE  o2.user_id = u.user_id
    AND  o2.status  = 'cancelled'
)
ORDER BY user_id;

LEGEND ▼

データ取得・読込対象

① FROM users ▼

FROM users uメインクエリ対象の users テーブル全5行です。IN → EXISTS → NOT EXISTS の3段階評価を順に適用します。

1 / 6

user_id	name	plan
1	田中太郎	premium
2	佐藤花子	standard
3	鈴木一郎	premium
4	山田次郎	free
5	伊藤三郎	standard

全 5行読込

学習ポイント

同テーブルを複数の EXISTS で参照：orders テーブルを o1（completed 確認）と o2（cancelled 確認）の異なるエイリアスで2回参照しています。エイリアスを変えることで同テーブルへの独立した相関サブクエリを書けます。JOINでは複数の集計条件を1行で表現するのが難しい場面でも、EXISTS の連結は直感的に書けます。

EXISTS + NOT EXISTS の組み合わせの実用例：「有料プランに加入し、かつ直近30日に購入があり、かつ一度もキャンセルがないユーザー」のようなセグメント抽出はマーケティング施策でよく使われます。AND EXISTS (期間条件付きSQ) AND NOT EXISTS (キャンセルSQ) のように述語を積み重ねるだけで条件を精密に制御できます。

評価の短絡動作とパフォーマンス：AND の評価は左から順に行われ、FALSE が確定した時点でそれ以降をスキップします。最も絞り込み効果の高い（選択率の低い）条件を AND の前に書くことで後続の EXISTS 評価コストを削減できます。実務ではインデックスが効く IN フィルタを先頭に配置するパターンが推奨されます。

アンチパターン

同エイリアスで同テーブルを2回参照する：EXISTS (...FROM orders o ...) AND NOT EXISTS (...FROM orders o ...) のように同名エイリアス o を両方のサブクエリで使うと、スコープの混乱やバグの原因になります。各サブクエリに一意のエイリアス（o1, o2）を付けるのが正しいパターンです。

EXISTS の代わりに COUNT で判定する：WHERE (SELECT COUNT(*) FROM orders WHERE user_id=u.user_id AND status='completed') > 0 は動作しますが、全件をカウントしてから比較するため非効率です。EXISTS は1件見つかった時点で即終了（短絡評価）するため、存在確認には必ず EXISTS を使いましょう。

実務コラム：ユーザーセグメント抽出と AND EXISTS の積み重ね

「直近30日に購入 × 過去にキャンセルなし × 特定カテゴリを買ったことがある」のような多条件の行動セグメント抽出は、CRMやマーケティング施策で日常的に使われます。AND EXISTS / AND NOT EXISTS を積み重ねる設計は、各条件を独立した EXISTS として追加・削除できるため保守性が高いという利点があります。一方で EXISTS が多くなると実行計画が重くなることもあるため、EXPLAIN ANALYZE でボトルネックを確認しながら必要に応じてインデックスを追加しましょう。