Help us understand the problem. What is going on with this article?

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

More than 3 years have passed since last update.

概要

検索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のストアドプロシージャで作成する。1

ストアドプロシージャ
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に依存していない。 

nogitsune413
社内SEとして働いています。 学んだ事や調べた技術を投稿しています。
http://nogitsune413.hatenablog.com/
qiitadon
Qiitadon(β)から生まれた Qiita ユーザー・コミュニティです。
https://qiitadon.com/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away