PostgreSQL: PostgreSQLの高度な絞り込みとパターンマッチング

Q: SIMILAR TOとPOSIX正規表現、どちらが優れていますか？

SIMILAR TOはSQL標準のサブセットで、移植性はありますが機能が限られています。POSIX正規表現（~演算子）は完全な機能を備えており、PostgreSQLプロジェクトでは推奨される選択肢です。

Q: ANYとINの違いは何ですか？

col = ANY(配列)はcol IN (list)と機能的に同等ですが、ANYは配列引数やサブクエリが返す配列を受け入れ、> ANYや< ALLのような特殊な比較もサポートします。INは等価性のみをサポートします。

Q: LIKE '%keyword%'がインデックスを使用できないのはなぜですか？

先頭のワイルドカード'%keyword'は、インデックスのソート順を活用できないためB-treeインデックスが無効になります。解決策: pg_trgm GINインデックス、全文検索（tsvector + tsquery）、または専用の検索エンジン。

最終更新：2026-08-26

1. 学習目標

AND/OR/NOTで複数の絞り込み条件を組み合わせる方法
IN/BETWEEN/IS NULLによる範囲絞り込みとNULL処理
LIKE/ILIKEによるパターンマッチング（PostgreSQLの大文字小文字を区別しない機能）
SIMILAR TOと正規表現演算子~ / ~* / !~ / !~*
ANY/ALL/EXISTSによるサブクエリ絞り込み

2. ストーリー

AliceはECプラットフォームの検索機能開発者です。ユーザーが検索ボックスにキーワードを入力すると、システムは以下の処理を行う必要があります。

商品名と説明に対するあいまい一致
大文字小文字を区別しない検索のサポート
パワーユーザー向けの正規表現による精密検索
販売終了商品の除外
関連度による結果のランク付け

Aliceはこの検索システムを構築するために、PostgreSQLが提供す��様々な絞り込みとパターンマッチング��ールをマスターする必要があります。

3. 概念: AND / OR / NOTの組み合わせ

(1) 論理演算子の優先順位

演算子	優先順位	説明
`NOT`	最高	否定
`AND`	中	両方の条件が真
`OR`	最低	いずれかの条件が真

▶ サンプル: ANDの組み合わせ

SQL

SELECT product_name, unit_price, category
FROM products
WHERE category = 'Electronics'
  AND unit_price > 500
  AND is_active = true;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ サンプル: ORの組み合わせ

SQL

SELECT product_name, unit_price, category
FROM products
WHERE category = 'Electronics'
   OR category = 'Books'
   OR category = 'Toys';

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) 括弧によるロジック制御

ORはANDより優先順位が低いため、括弧を省略すると予期しない結果になる可能性があります。

形式	論理的意味
`a AND b OR c`	`(a AND b) OR c`
`a AND (b OR c)`	`a AND (b OR c)` — 通常意図するもの

▶ サンプル: 括弧によるロジック変更

SQL

SELECT product_name, unit_price, category
FROM products
WHERE is_active = true
  AND (category = 'Electronics' OR category = 'Books');

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) NOTによる否定

NOTは任意のブール式を否定します。IN、BETWEEN、LIKEなどとよく組み合わせて使われます。

▶ サンプル: NOTによる条件否定

SQL

SELECT product_name, unit_price
FROM products
WHERE NOT category = 'Electronics'
  AND is_active = true;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

4. 概念: IN / BETWEEN / IS NULL

(1) INとNOT IN

INは値が指定されたリスト内にあるかをチェックします。複数のORと同等ですが、より簡潔でパフォーマンスも優れています。

形式	説明
`col IN (a, b, c)`	`col = a OR col = b OR col = c`と同等
`col NOT IN (a, b, c)`	`col <> a AND col <> b AND col <> c`と同等

▶ サンプル: INによる複数カテゴリの絞り込み

SQL

SELECT product_name, unit_price, category
FROM products
WHERE category IN ('Electronics', 'Books', 'Toys')
  AND is_active = true;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) BETWEEN範囲絞り込み

BETWEENは境界値を含みます。>= AND <=と同等です。

形式	同等の書き方
`col BETWEEN a AND b`	`col >= a AND col <= b`
`col NOT BETWEEN a AND b`	`col < a OR col > b`

▶ サンプル: 価格範囲絞り込み

SQL

SELECT product_name, unit_price
FROM products
WHERE unit_price BETWEEN 100 AND 500
ORDER BY unit_price;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ サンプル: 日付範囲絞り込み

SQL

SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-06-30'
  AND order_status = 'completed';

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) IS NULL / IS NOT NULL

NULLは何とも等しくあ��ません（自身を含む）。テストには必ずIS NULLまたはIS NOT NULLを使用する必要があります。

形式	結果
`NULL = NULL`	`NULL`（TRUEではない）
`NULL <> NULL`	`NULL`（TRUEではない）
`col IS NULL`	正しいNULLテスト
`col IS NOT NULL`	正しい非NULLテスト

▶ サンプル: 割引のない商品を検索

SQL

SELECT product_name, unit_price, discount_rate
FROM products
WHERE discount_rate IS NULL
  AND is_active = true;

TEXT 📖 参照専用

 count 
-------
     5
(1 row)

(4) NOT INとNULLの落とし穴

NOT INリストにNULLが含まれている場合、��全体が空の結果セットを返す可能性があります。

式	結果	理由
`3 NOT IN (1, 2, NULL)`	`NULL`	`3 <> NULL`はNULL。NULL AND ... はNULL
`3 IN (1, 2, NULL)`	`NULL`	`3 = NULL`はNULL。FALSE OR NULLはNULL

▶ サンプル: NOT INの安全な使用

SQL

-- 危険: サブクエリ内のNULLがNOT INを破壊する可能性
SELECT product_name
FROM products
WHERE category NOT IN (
  SELECT category FROM categories WHERE is_active = false
);

-- 安全: 代わりにNOT EXISTSを使用
SELECT p.product_name
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM categories c
  WHERE c.category = p.category AND c.is_active = false
);

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

5. 概念: LIKE / ILIKEパターンマッチング

(1) LIKEのワイルドカード

ワイルドカード	意味	例
`%`	任意の長さの文字列に一致（空含む）	`'Phone%'`はPhone, Phone Caseに一致
`_`	1文字に一致	`'A_c'`はArc, ABCに一致

▶ サンプル: LIKE前方一致

SQL

SELECT product_name, unit_price
FROM products
WHERE product_name LIKE 'Wireless%'
  AND is_active = true;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ サンプル: LIKE部分一致

SQL

SELECT product_name, unit_price
FROM products
WHERE product_name LIKE '%Battery%'
  AND is_active = true;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) ILIKE: 大文字小文字を区別しない（PostgreSQLの機能）

ILIKEはPostgreSQLの拡張機能で、大文字小文字を区別せずにマッチします。LIKE + LOWER()と同等です。

演算子	大文字小文字区別	標準SQL
`LIKE`	区別する	はい
`ILIKE`	区別しない	いいえ（PGのみ）

▶ サンプル: ILIKE検索

SQL

SELECT product_name, unit_price, description
FROM products
WHERE (product_name ILIKE '%iphone%'
   OR description ILIKE '%iphone%')
  AND is_active = true;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) ワイルドカードのエスケープ

リテラルの%や_を検索する必要がある場合は、ESCAPEでエスケープ文字を指定します。

▶ サンプル: パーセント記号を含む説明の検索

SQL

SELECT product_name, description
FROM products
WHERE description LIKE '%100\%%' ESCAPE '\';

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

6. 概念: 正規表現マッチング

(1) 正規表現演算子の概要

PostgreSQLは4つの正規表現一致演算子と、SIMILAR TO構文を提供しています。

演算子	大文字小文字区別	説明
`~`	区別する	正規表現に一致
`~*`	区別しない	正規表現に一致（大文字小文字無視）
`!~`	区別する	正規表現に一致しない
`!~*`	区別しない	正規表現に一致しない（大文字小文字無視）
`SIMILAR TO`	区別する	SQL標準の正規表現サブセット（`%` `_` `

▶ サンプル: ~ 正規表現一致

SQL

SELECT product_name, unit_price
FROM products
WHERE product_name ~ '^(Wireless|Bluetooth)'
  AND is_active = true;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ サンプル: ~* 大文字小文字を区別しない正規表現

SQL

SELECT product_name, unit_price
FROM products
WHERE product_name ~* 'iphone\s*(1[0-9])?'
  AND is_active = true;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) SIMILAR TO

SIMILAR TOはLIKEとPOSIX正規表現の中間に位置します。|、[]、*、+などをサポートしますが、SQLスタイルのワイルドカード%と_を使用します。

機能	LIKE	SIMILAR TO	POSIX ~
`%`ワイルドカード	はい	はい	いいえ（`.*`を使用）
`_`ワイルドカード	はい	はい	いいえ（`.`を使用）
`	` 代替	いいえ	はい
`[]` 文字クラス	いいえ	はい	はい
`+*{m,n}` 量指定子	いいえ	一部	完全

▶ サンプル: SIMILAR TO一致

SQL

SELECT product_name, unit_price
FROM products
WHERE product_name SIMILAR TO '(Wireless|Bluetooth)%'
  AND is_active = true;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ サンプル: !~ 正規表現一致の除外

SQL

SELECT product_name, unit_price
FROM products
WHERE product_name !~ '(Refurbished|Used)'
  AND is_active = true;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

7. 概念: ANY / ALL / EXISTSサブクエリ絞り込み

(1) ANYとALL

演算子	意味	同等の形式
`col = ANY(array)`	配列内のいずれかの値と等しい	`col IN (...)`
`col > ANY(array)`	少なくとも1つの値より大きい	—
`col > ALL(array)`	すべての値より大きい	—

▶ サンプル: ANYはINと同等

SQL

SELECT product_name, unit_price
FROM products
WHERE category = ANY(ARRAY['Electronics', 'Books', 'Toys'])
  AND is_active = true;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ サンプル: ALLとサブクエリの比較

SQL

SELECT product_name, unit_price, category
FROM products p
WHERE unit_price > ALL (
  SELECT unit_price FROM products
  WHERE category = 'Books' AND is_active = true
);

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) EXISTSサブクエリ

EXISTSはサブクエリが行を返すかどうかをチェックします。実際の値は気にせず、結果が存在するかどうかのみを確認します。INより安全で（NULLの落とし穴がない）、通常、相関サブクエリでは高速です。

使用法	説明
`EXISTS (subquery)`	サブクエリが行を返せばTRUE
`NOT EXISTS (subquery)`	サブクエリが行を返さなければTRUE

▶ サンプル: EXISTSで注文のある顧客を検索

SQL

SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
);

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) IN vs EXISTS選択ガイド

シナリオ	推奨	理由
サブクエリ結果セットが小さい	`IN`	オプティマイザがサブクエリを先に実行し、小さいセットを構築
外部テーブルが小さく、サブクエリテーブルが大きい	`EXISTS`	オプティマイザが外部行ごとにサブクエリを実行し、早期��了
サブクエリにNULLが含まれる可能性	`EXISTS`	NOT INのNULLの落とし穴を回避
独立したサブクエリ（非相関）	`IN`	サブクエリ結果を実体化可能

8. フローチャート: パターンマッチングの選択

flowchart TD
    A[パターンマッチングが必要？] --> B{大文字小文字を区別？}
    B -->|区別する| C{正規表現が必要？}
    B -->|区別しない| D{正規表現が必要？}
    C -->|単純なワイルドカード| E[LIKE]
    C -->|OR/文字クラスが必要| F[SIMILAR TO]
    C -->|完全な正規表現| G["~ 演算子"]
    D -->|単純なワイルドカード| H[ILIKE]
    D -->|完全な正規表現| I["~* 演算子"]
    E --> J[結果を返す]
    F --> J
    G --> J
    H --> J
    I --> J

    style A fill:#e1f5fe
    style J fill:#c8e6c9

9. 総合サンプル

AliceはECサイトの検索機能の中核クエリロジックを実装します。

SQL

-- Step 1: ILIKEによる基本的なキーワード検索（大文字小文字を区別しない）
SELECT product_id, product_name, unit_price, description
FROM products
WHERE is_active = true
  AND (product_name ILIKE '%keyboard%' OR description ILIKE '%keyboard%')
ORDER BY unit_price ASC;

-- Step 2: パワーユーザー向け高度な正規表現検索
SELECT product_id, product_name, unit_price, description
FROM products
WHERE is_active = true
  AND product_name ~* '(mechanical|wireless)\s*keyboard'
  AND unit_price BETWEEN 50 AND 300
ORDER BY unit_price DESC;

-- Step 3: 特定カテゴリの商品をNULL処理付きで検索
SELECT product_id, product_name, unit_price, category
FROM products
WHERE category IN ('Electronics', 'Computer Accessories', 'Gaming')
  AND is_active = true
  AND discount_rate IS NOT NULL
  AND unit_price BETWEEN 20 AND 500
ORDER BY unit_price DESC NULLS LAST;

-- Step 4: 完了注文が少なくとも1件ある商品（EXISTS）
SELECT p.product_id, p.product_name, p.unit_price
FROM products p
WHERE p.is_active = true
  AND EXISTS (
    SELECT 1 FROM order_items oi
    JOIN orders o ON o.order_id = oi.order_id
    WHERE oi.product_id = p.product_id
      AND o.order_status = 'completed'
      AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
  )
  AND NOT EXISTS (
    SELECT 1 FROM product_flags pf
    WHERE pf.product_id = p.product_id
      AND pf.flag_type = 'recalled'
  )
ORDER BY p.unit_price DESC
FETCH FIRST 50 ROWS ONLY;

❓ よくある質問

Q LIKEとILIKEのパフォーマンス差は大きいですか？

A ILIKEは大文字小文字を無視する必要があるため、標準のB-treeインデックスを使用できません。大規模データではPostgreSQLのpg_trgm拡張機能を使用してGINインデックスを構築し、ILIKEクエリを高速化してください。

Q NOT INサブクエリがNULLを返した場合はどうなりますか？

A サブクエリ結果にNULLが含まれる場合、NOT IN全体が空の結果を返す可能性があります。解決策: 1) NOT EXISTSに置き換える。2) サブクエリにWHERE col IS NOT NULLを追加してNULLを除外する。

Q BETWEENはTIMESTAMPに使用できますか？

A はい。ただしBETWEENは境界値を含みます。TIMESTAMPの場合、BETWEEN '2025-01-01' AND '2025-01-31'は1月31日の時刻部分を除外します。代わりに>= AND <を使用することを推奨します。

Q SIMILAR TOとPOSIX正規表現、どちらが優れていますか？

A SIMILAR TOはSQL標準のサブセットで、移植性はありますが機能が限られています。POSIX正規表現（~演算子）は完全な機能を備えており、PostgreSQLプロジェクトでは推奨される選択肢です。

Q ANYとINの違いは何ですか？

A col = ANY(配列)はcol IN (list)と機能的に同等ですが、ANYは配列引数やサブクエリが返す配列を受け入れ、> ANYや< ALLのような特殊な比較もサポートします。INは等価性のみをサポートします。

Q LIKE '%keyword%'がインデックスを使用できないのはなぜですか？

A 先頭のワイルドカード'%keyword'は、インデックスのソート順を活用できないためB-treeインデックスが無効になります。解決策: pg_trgm GINインデックス、全文検索（tsvector + tsquery）、または専用の検索エンジン。

📖 まとめ

AND/OR/NOTの演算子優先順位に注意し、括弧でロジックを明示的に制御してください
INは複数値の等価性に適し、BETWEENは範囲絞り込みに適し、IS NULLは欠損値を処理します
LIKEは単純なワイルドカードマッチングを行い、ILIKEは大文字小文字を区別しません（PostgreSQLのみ）
POSIX正規表現~ / ~*は完全な正規表現機能を提供し、SIMILAR TOはLIKEと正規表現の中間に位置します
EXISTSはINより安全で（NULLの落とし穴がない）、通常相関サブクエリでは高速です
ANY/ALLは配列/サブクエリに対する量化比較を提供します

📝 練習問題

⭐ productsテーブルからcategoryがElectronicsまたはBooksでis_active = trueの商品を検索し、unit_price降順でソートするクエリを書いてください。
⭐⭐ ILIKEを使用してproduct_nameとdescriptionからユーザーのキーワード（例: "wireless mouse"）を検索し、unit_price BETWEEN 20 AND 200で絞り込み、discount_rate IS NULLの商品を除外するクエリを書いてください。
⭐⭐⭐ EXISTSを使用して過去90日間に注文した顧客を検索し、NOT EXISTSを使用して"suspended"フラグの付いた顧客を除外し、顧客登録時刻の降順でソートし、ページネーションで最初の20行を表示するクエリを書いてください。

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて