検索SQLで、検索欄が空欄のとき、全検索にする

概要

検索SQLで、検索欄が空欄のとき、全検索にするSQLは、以下である。

疑似コード

SELECT
    *
FROM
    A
WHERE
    CASE
        WHEN (検索値) = '' THEN
            (検索値)
        ELSE
            (検索対象列)
    END = (検索値)

説明

検索欄に値が入っていたとき

検索値による検索が実行される。

　検索値「abc」の例

WHERE句

WHERE
    CASE
        WHEN 'abc' = '' THEN
            'abc'
        ELSE
            (検索対象列)
    END = 'abc'

CASE式の評価が終了した後

WHERE
    (検索対象列) = 'abc'

検索欄が空欄のとき

WHERE句の条件式は常にTrueになるので、条件式はなかったのと同じになり、全検索になる。

　検索値「（なし）」の例

WHERE句

WHERE
    CASE
        WHEN '' = '' THEN
            ''
        ELSE
            (検索対象列)
    END = ''

CASE式の評価が終了した後

WHERE
    '' = ''

サンプルプログラム

売上テーブルを商品名で検索するプログラムを作成する。

• 売上（sales）テーブル

売上金額（sales）	売上日（sales_date）	商品(item)
100	2018-01-14	キャベツ
300	2018-01-15	桃
200	2018-01-15	ニンジン
100	2018-01-16	キャベツ

検索プログラムの作成

mysqlのストアドプロシージャで作成する。¹

ストアドプロシージャ

CREATE PROCEDURE search (IN searchItem VARCHAR(30))

    SELECT 
        *
    FROM
        sample.sales
    WHERE
        CASE
            WHEN searchItem = '' THEN
                searchItem
            ELSE
                item
        END = searchItem;

検索の実行

検索値が入っていたとき

ストアドプロシージャの呼び出し

CALL search('キャベツ')

検索結果

売上金額（sales）	売上日（sales_date）	商品(item)
100	2018-01-14	キャベツ
100	2018-01-16	キャベツ

絞り込みが行われる。

検索値が空のとき

ストアドプロシージャの呼び出し

CALL search('')

検索結果

売上金額（sales）	売上日（sales_date）	商品(item)
100	2018-01-14	キャベツ
300	2018-01-15	桃
200	2018-01-15	ニンジン
100	2018-01-16	キャベツ

絞り込みが行われない。

検証環境

• MySQL 5.7.17

---

1. このTIPSは標準SQLで書けるので、MySQLに依存していない。 ↩