検索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に依存していない。 ↩