【SQL】LIKEで複数パターン・複数キーワードを検索する方法｜OR・REGEXP・UNION ALL・CASE WHEN・動的検索まで解説

-- products テーブル（商品）
-- id | name                    | category | price | description
--  1 | りんごジュース           | 飲料     |   180 | 国産りんご100%
--  2 | みかんゼリー             | 菓子     |   120 | 愛媛産みかん使用
--  3 | 有機ほうれん草           | 野菜     |   350 | 有機JAS認定
--  4 | オレンジジュース（輸入） | 飲料     |   150 | ブラジル産オレンジ
--  5 | いちごアイス             | 菓子     |   280 | 北海道産いちご
--  6 | 有機トマト               | 野菜     |   400 | 有機JAS認定
--  7 | レモンサワー             | 飲料     |   220 | 国産レモン使用
--  8 | グレープフルーツ果汁     | 飲料     |   200 | 輸入グレープフルーツ

-- customers テーブル（顧客）
-- id | name       | email                    | pref
--  1 | 田中 太郎  | tanaka@example.com       | 東京都
--  2 | 鈴木 花子  | suzuki@gmail.com         | 大阪府
--  3 | 高橋 一郎  | takahashi@yahoo.co.jp    | 愛知県
--  4 | 伊藤 次郎  | ito@example.com          | 東京都
--  5 | 渡辺 美咲  | watanabe@company.co.jp   | 神奈川県

-- 「りんご」または「みかん」を名前に含む商品
SELECT id, name, category, price
FROM products
WHERE name LIKE '%りんご%'
   OR name LIKE '%みかん%';

-- 結果:
-- id=1: りんごジュース ✓
-- id=2: みかんゼリー ✓

-- 3パターン: 「ジュース」または「ゼリー」または「アイス」
SELECT id, name, price
FROM products
WHERE name LIKE '%ジュース%'
   OR name LIKE '%ゼリー%'
   OR name LIKE '%アイス%';

-- 前方一致の複数パターン（インデックスが効く）
SELECT id, name
FROM products
WHERE name LIKE 'りんご%'
   OR name LIKE 'みかん%'
   OR name LIKE '有機%';

-- NG: IN の中に LIKE パターンを書いてもワイルドカードは効かない
SELECT * FROM products
WHERE name IN ('%ジュース%', '%ゼリー%');
-- → '%ジュース%' という文字列と完全一致を探すため 0 件になる（バグ）

-- NG: LIKE IN という構文は存在しない（構文エラー）
-- WHERE name LIKE IN ('%ジュース%', '%ゼリー%');  -- エラー

-- OK: OR で LIKE を並べる（確実に動作する）
SELECT * FROM products
WHERE name LIKE '%ジュース%'
   OR name LIKE '%ゼリー%';

-- OK: 完全一致なら IN を使う（ワイルドカード不要なケース）
SELECT * FROM products
WHERE category IN ('飲料', '菓子');

-- MySQL: REGEXP で「ジュース」または「ゼリー」または「アイス」を含む
SELECT id, name, price
FROM products
WHERE name REGEXP 'ジュース|ゼリー|アイス';

-- 結果: id=1,2,4,5,7 がマッチ

-- 前方一致の複数パターン（^ で行頭を指定）
SELECT id, name
FROM products
WHERE name REGEXP '^(りんご|みかん|有機)';

-- 大文字小文字を区別する場合
SELECT id, name
FROM products
WHERE name REGEXP BINARY 'Juice|juice';

-- NOT REGEXP で除外
SELECT id, name
FROM products
WHERE name NOT REGEXP 'ジュース|アイス';

-- PostgreSQL: ~ で「ジュース」または「ゼリー」を含む
SELECT id, name, price
FROM products
WHERE name ~ 'ジュース|ゼリー';

-- 大文字小文字を区別しない: ~* を使う
SELECT id, name
FROM products
WHERE email ~* '@example|@gmail';

-- SIMILAR TO: SQL 標準に近い正規表現
-- % はワイルドカード（LIKE の % と同じ）
SELECT id, name
FROM products
WHERE name SIMILAR TO '%(ジュース|ゼリー|アイス)%';

-- NOT SIMILAR TO で除外
SELECT id, name
FROM products
WHERE name NOT SIMILAR TO '%(飲料|菓子)%';

-- Oracle: REGEXP_LIKE で複数パターンを検索
SELECT id, name, price
FROM products
WHERE REGEXP_LIKE(name, 'ジュース|ゼリー|アイス');

-- 大文字小文字を区別しない（第3引数 'i'）
SELECT id, name
FROM customers
WHERE REGEXP_LIKE(email, '@example|@gmail', 'i');

-- SQL Server: LIKE を OR で並べるか CHARINDEX を使う
-- CHARINDEX(検索文字列, 対象列) > 0 でパターンに含まれるか判定
SELECT id, name
FROM products
WHERE CHARINDEX('ジュース', name) > 0
   OR CHARINDEX('ゼリー', name) > 0;

-- 方法1: OR + LIKE（オプティマイザがインデックスを使わないことがある）
SELECT id, name FROM products
WHERE name LIKE 'りんご%'
   OR name LIKE 'みかん%'
   OR name LIKE '有機%';

-- 方法2: UNION ALL（各クエリが確実にインデックスを使う）
SELECT id, name FROM products WHERE name LIKE 'りんご%'
UNION ALL
SELECT id, name FROM products WHERE name LIKE 'みかん%'
UNION ALL
SELECT id, name FROM products WHERE name LIKE '有機%';

-- 重複を除きたい場合は UNION（DISTINCT）を使う
SELECT id, name FROM products WHERE name LIKE 'りんご%'
UNION
SELECT id, name FROM products WHERE name LIKE 'みかん%';

-- UNION ALL の方が重複除去のコストがなく高速（重複がないと分かっている場合）

-- どのカテゴリキーワードにマッチするか判定
SELECT
    id,
    name,
    price,
    CASE
        WHEN name LIKE '%ジュース%' THEN 'ジュース系'
        WHEN name LIKE '%ゼリー%'  THEN 'ゼリー系'
        WHEN name LIKE '%アイス%'  THEN 'アイス系'
        WHEN name LIKE '有機%'     THEN '有機系'
        ELSE                         'その他'
    END AS キーワード分類
FROM products
ORDER BY キーワード分類, price;

-- 複数キーワードにマッチするか全てフラグで出す
SELECT
    id,
    name,
    CASE WHEN name LIKE '%ジュース%' THEN 1 ELSE 0 END AS is_juice,
    CASE WHEN name LIKE '%有機%'    THEN 1 ELSE 0 END AS is_organic,
    CASE WHEN name LIKE '%輸入%'    THEN 1 ELSE 0 END AS is_imported
FROM products;

-- 結果:
-- id=1: りんごジュース    is_juice=1, is_organic=0, is_imported=0
-- id=3: 有機ほうれん草   is_juice=0, is_organic=1, is_imported=0
-- id=4: オレンジジュース  is_juice=1, is_organic=0, is_imported=1

-- キーワード分類ごとの件数と平均価格
SELECT
    CASE
        WHEN name LIKE '%ジュース%' OR name LIKE '%果汁%' THEN '飲料系'
        WHEN name LIKE '%ゼリー%'  OR name LIKE '%アイス%' THEN 'スイーツ系'
        WHEN name LIKE '有機%'                              THEN '有機系'
        ELSE                                                   'その他'
    END AS 分類,
    COUNT(*)          AS 件数,
    ROUND(AVG(price)) AS 平均価格,
    MIN(price)        AS 最安値,
    MAX(price)        AS 最高値
FROM products
GROUP BY
    CASE
        WHEN name LIKE '%ジュース%' OR name LIKE '%果汁%' THEN '飲料系'
        WHEN name LIKE '%ゼリー%'  OR name LIKE '%アイス%' THEN 'スイーツ系'
        WHEN name LIKE '有機%'                              THEN '有機系'
        ELSE                                                   'その他'
    END
ORDER BY 件数 DESC;

-- 例: ユーザーが「りんご みかん 有機」と入力（スペース区切り）した場合
-- アプリ側でキーワードを分割して SQL を構築する

-- Python の例（擬似コード）
-- keywords = ["りんご", "みかん", "有機"]
-- conditions = " OR ".join([f"name LIKE '%{kw}%'" for kw in keywords])
-- sql = f"SELECT * FROM products WHERE {conditions}"
-- → WHERE name LIKE '%りんご%' OR name LIKE '%みかん%' OR name LIKE '%有機%'

-- 注意: 上記は SQL インジェクション対策が必要！
-- 必ずプレースホルダー（パラメータバインド）を使う

-- Python pymysql での安全な実装
-- keywords = ["りんご", "みかん", "有機"]
-- conditions = " OR ".join(["name LIKE %s"] * len(keywords))
-- params = [f"%{kw}%" for kw in keywords]
-- sql = f"SELECT * FROM products WHERE {conditions}"
-- cursor.execute(sql, params)

-- FULLTEXT INDEX を使ったキーワード検索（中間一致の代替として高速）
-- まず FULLTEXT INDEX を作成
ALTER TABLE products ADD FULLTEXT INDEX ft_name_desc (name, description);

-- BOOLEAN MODE で複数キーワードの AND 検索
-- +キーワード: 必須（AND 相当）
SELECT id, name, price
FROM products
WHERE MATCH(name, description) AGAINST ('+りんご +国産' IN BOOLEAN MODE);

-- いずれかのキーワードを含む（OR 相当、+ なし）
SELECT id, name, price
FROM products
WHERE MATCH(name, description) AGAINST ('りんご みかん 有機' IN BOOLEAN MODE);
-- ↑ 「りんご」OR「みかん」OR「有機」を含む行を関連度順で返す

-- 除外キーワード（- を前置）
SELECT id, name, price
FROM products
WHERE MATCH(name, description) AGAINST ('ジュース -輸入' IN BOOLEAN MODE);
-- ↑ 「ジュース」を含み「輸入」を含まない

-- PostgreSQL の全文検索
-- to_tsquery で OR 検索（|）
SELECT id, name
FROM products
WHERE to_tsvector('japanese', name) @@ to_tsquery('japanese', 'りんご | みかん | 有機');

-- pg_trgm 拡張で LIKE の中間一致を高速化（GIN インデックス）
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

-- インデックスを使った中間一致（複数 OR も高速）
SELECT id, name FROM products
WHERE name LIKE '%ジュース%'
   OR name LIKE '%ゼリー%';
-- GIN インデックスがあれば %キーワード% でも高速

-- 「ジュース」と「アイス」の両方を含まない商品
-- AND NOT LIKE: 両方を除外（ジュースでも アイスでもない）
SELECT id, name FROM products
WHERE name NOT LIKE '%ジュース%'
  AND name NOT LIKE '%アイス%';

-- 「ジュース」または「アイス」を含まない（少なくとも片方を含まない）
-- → 上記と違い、「ジュースアイス」はどちらの条件も False になるため残らない
-- 実務では AND NOT LIKE の方が直感的

-- REGEXP で一括除外（MySQL）
SELECT id, name FROM products
WHERE name NOT REGEXP 'ジュース|アイス';

-- Oracle で一括除外
SELECT id, name FROM products
WHERE NOT REGEXP_LIKE(name, 'ジュース|アイス');

-- SQL Server で一括除外
SELECT id, name FROM products
WHERE name NOT LIKE '%ジュース%'
  AND name NOT LIKE '%アイス%';
  -- SQL Server は REGEXP がないため AND NOT LIKE を並べる