SQLのCASE演算子で条件の有無を判断して必要な場合のみWHERE句の条件に含める

SQLでテーブルの情報を抽出(SELECT)する際に、特定の場合だけWHERE句で条件を指定したい場合があります。

例えば、抽出条件(値)が指定されている場合は、WHERE句の条件式に含めたいけど、抽出条件(値)が指定されていない場合は、条件式に含めたくない…。
プログラムからSQLを作成する際に、if文で条件分岐をして

if (抽出条件の値 == "") {
	// 条件がない場合のSQLを生成…
}
else {
	// 条件がある場合のSQLを生成…
}

if (抽出条件の値 == "") {

// 条件がない場合のSQLを生成…

}

else {

// 条件がある場合のSQLを生成…

}

のように、抽出条件の有無によって生成するSQLを変更すればできるけど…。
できれば、SQLの中で抽出条件の有無を判断して、プログラム側での条件分岐はしたくない…。

そんな時は、SQLのCASE演算子を使えば、プログラム側で条件の分岐をしなくても、値のある時だけWHERE句の条件に含めることができます。

1 使用するテーブル
2 会員情報の抽出を行う画面
3 プログラム側でSQLのWHERE句を生成する場合
- 3.1 C#でのサンプル
4 SQLのみでWHERE句を生成する場合

使用するテーブル

ここでは例として、会員の情報を管理するテーブルがあるとします。
会員テーブルには [会員コード] と [会員氏名] と [性別コード] の3つのカラム(列)があるとします。

テーブル名

テーブル名	テーブルID
会員テーブル	TBL_MEMBER

テーブルの列定義

No	カラム名	カラムID
1	会員ID	MEMBER_ID
2	会員氏名	MEMBER_NAME
3	性別コード	SX_CODE

テーブルに登録されているデータ

会員ID	会員氏名	性別コード
001	会員　一郎	1
002	会員　二郎	1
003	会員　一子	2
004	会員　三郎	1
005	会員　二子	2
006	会員　三子	2

※ 性別コード「1:男性」「2:女性」

会員情報の抽出を行う画面

画面はシンプルに以下のような感じで

検索条件

氏名

性別

会員の「氏名」はテキストボックスに直接入力「性別」はドロップダウンリストから選択する形式で検索条件を指定できる仕様としておきます。

プログラム側でSQLのWHERE句を生成する場合

プログラム側で抽出条件の有無よってSQLを分岐すると

C#でのサンプル

// 画面の氏名の入力値を変数に格納
string name = this.textName.Text.Trim();
// 画面の性別の選択値を変数に格納
string sx = listSx.SelectedValue.Trim();
// SQLの生成
string sql = "SELECT * FROM TBL_MEMBER";
// 氏名の条件指定があり＋性別の条件指定がなしか？
if (name != "" && sx == "")
{
	// 氏名の指定があれば WHERE 句の条件に含める
	sql += " WHERE MEMBER_NAME = '" + name + "'";
}
// 性別の条件指定があり＋氏名の条件指定がなしか？
else if (sx != "")
{
	// 性別の指定があれば WHERE 句の条件に含める
	sql += " WHERE SX_CODE = '" + sx + "'";
}
// 氏名と性別の両方の条件指定があるか？
else if (name != "" && sx != "")
{
	// 氏名の指定があれば WHERE 句の条件に含める
	sql += " WHERE MEMBER_NAME = '" + name + "' AND SX_CODE = '" + sx + "'";
}

// 画面の氏名の入力値を変数に格納

string name = this.textName.Text.Trim();

// 画面の性別の選択値を変数に格納

string sx = listSx.SelectedValue.Trim();

// SQLの生成

string sql = "SELECT * FROM TBL_MEMBER";

// 氏名の条件指定があり＋性別の条件指定がなしか？

if (name != "" && sx == "")

{

// 氏名の指定があれば WHERE 句の条件に含める

sql += " WHERE MEMBER_NAME = '" + name + "'";

}

// 性別の条件指定があり＋氏名の条件指定がなしか？

else if (sx != "")

{

// 性別の指定があれば WHERE 句の条件に含める

sql += " WHERE SX_CODE = '" + sx + "'";

}

// 氏名と性別の両方の条件指定があるか？

else if (name != "" && sx != "")

{

// 氏名の指定があれば WHERE 句の条件に含める

sql += " WHERE MEMBER_NAME = '" + name + "' AND SX_CODE = '" + sx + "'";

}

こんな感じでしょうか。
if文を使って分岐すれば、氏名と性別の条件指定の形式によって
氏名のみ条件の指定があれば

SELECT * FROM TBL_MEMBER WHERE MEMBER_NAME = '氏名の入力値'

1	SELECT * FROM TBL_MEMBER WHERE MEMBER_NAME = '氏名の入力値'

性別のみ条件の指定があれば

SELECT * FROM TBL_MEMBER WHERE SX_CODE = '性別の選択値'

1	SELECT * FROM TBL_MEMBER WHERE SX_CODE = '性別の選択値'

氏名と性別の両方の条件の指定があれば

SELECT * FROM TBL_MEMBER WHERE MEMBER_NAME = '氏名の入力値' AND SX_CODE = '性別の選択値'

1	SELECT * FROM TBL_MEMBER WHERE MEMBER_NAME = '氏名の入力値' AND SX_CODE = '性別の選択値'

氏名と性別の両方の条件の指定がなければ

SELECT * FROM TBL_MEMBER

1	SELECT * FROM TBL_MEMBER

の異なるSQLが生成され、それぞれの結果が取得されるので問題ないんですが、if文を記述するのが面倒なので、これをif文を使わずに同様の結果が取得できるようにします。

SQLのみでWHERE句を生成する場合

C#でのサンプル

// 画面の氏名の入力値を変数に格納
string name = this.textName.Text.Trim();
// 画面の性別の選択値を変数に格納
string sx = listSx.SelectedValue.Trim();
// SQLの生成
string sql = "SELECT * FROM TBL_MEMBER";
string sql += " WHERE MEMBER_NAME = CASE WHEN '" + name + "' <> '' THEN '" + name + "' ELSE MEMBER_NAME END";
string sql += " AND SX_CODE = CASE WHEN '" + sx + "' <> '' THEN '" + sx + "' ELSE SX_CODE END";

// 画面の氏名の入力値を変数に格納

string name = this.textName.Text.Trim();

// 画面の性別の選択値を変数に格納

string sx = listSx.SelectedValue.Trim();

// SQLの生成

string sql = "SELECT * FROM TBL_MEMBER";

string sql += " WHERE MEMBER_NAME = CASE WHEN '" + name + "' <> '' THEN '" + name + "' ELSE MEMBER_NAME END";

string sql += " AND SX_CODE = CASE WHEN '" + sx + "' <> '' THEN '" + sx + "' ELSE SX_CODE END";

WHERE句にCASEを使って条件の有無によって条件を分岐しています。
ポイントは、条件の指定がない場合(空文字列の場合)のELSE以降の部分
ELSE MEMBER_NAME END
ELSE SX_CODE END
で条件を照合する列に対して照合する値に条件を照合する列を指定している箇所です。
WHERE句に「WHERE 0 = 0」と記述すれば、条件を指定していない場合と同様の結果が得られるのと同様に
「条件を照合する列」に「条件を照合する列」を指定するということは、「条件を照合する列」=「条件を照合する列」です。
「条件を照合する列」を0に置き換えると 0 = 0 になるので、結果として条件を指定していない時と同様の結果になります。
プログラムからSQLを作成する方法とは別に、SQLのみで同様の結果を得るようにするなら以下のようになります。

SQLServer(T-SQL)でのサンプル

-- 変数の定義
DECLARE @NAME VARCHAR(20)
DECLARE @SX VARCHAR(1)

SET @NAME = '会員　三郎'
SET @SX = '1'

SELECT
	MEMBER_ID,
	MEMBER_NAME,
	CASE 
		WHEN SX_CODE = '1' THEN '男性'
		WHEN SX_CODE = '2' THEN '女性'
		ELSE '不明' END AS SEIBETSU
FROM
	TBL_MEMBER
WHERE
	MEMBER_NAME = CASE WHEN @NAME <> '' THEN @NAME ELSE MEMBER_NAME END
AND
	SX_CODE = CASE WHEN @SX <> '' THEN @SX ELSE SX_CODE END

-- 変数の定義

DECLARE @NAME VARCHAR(20)

DECLARE @SX VARCHAR(1)

SET @NAME = '会員　三郎'

SET @SX = '1'

SELECT

MEMBER_ID,

MEMBER_NAME,

CASE

WHEN SX_CODE = '1' THEN '男性'

WHEN SX_CODE = '2' THEN '女性'

ELSE '不明' END AS SEIBETSU

FROM

TBL_MEMBER

WHERE

MEMBER_NAME = CASE WHEN @NAME <> '' THEN @NAME ELSE MEMBER_NAME END

AND

SX_CODE = CASE WHEN @SX <> '' THEN @SX ELSE SX_CODE END

上記の例では、会員氏名の条件を「=」(完全一致)で照合していますが、これだと使いにくいので、「LIKE」(部分一致)で検索できるように変更してみます。
「LIKE」を使う場合でも、基本的には「=」と同様です。
「会員氏名 = ‘抽出条件の値’」となっている箇所を「会員氏名 LIKE ‘%抽出条件の値%’」に変更するだけです。

-- 変数の定義
DECLARE @NAME VARCHAR(20)
DECLARE @SX VARCHAR(1)

SET @NAME = '三'
SET @SX = '1'

SELECT
	MEMBER_ID,
	MEMBER_NAME,
	CASE 
		WHEN SX_CODE = '1' THEN '男性'
		WHEN SX_CODE = '2' THEN '女性'
		ELSE '不明' END AS SEIBETSU
FROM
	TBL_MEMBER
WHERE
	MEMBER_NAME LIKE '%' + CASE WHEN @NAME <> '' THEN @NAME ELSE MEMBER_NAME END + '%'
AND
	SX_CODE = CASE WHEN @SX <> '' THEN @SX ELSE SX_CODE END

-- 変数の定義

DECLARE @NAME VARCHAR(20)

DECLARE @SX VARCHAR(1)

SET @NAME = '三'

SET @SX = '1'

SELECT

MEMBER_ID,

MEMBER_NAME,

CASE

WHEN SX_CODE = '1' THEN '男性'

WHEN SX_CODE = '2' THEN '女性'

ELSE '不明' END AS SEIBETSU

FROM

TBL_MEMBER

WHERE

MEMBER_NAME LIKE '%' + CASE WHEN @NAME <> '' THEN @NAME ELSE MEMBER_NAME END + '%'

AND

SX_CODE = CASE WHEN @SX <> '' THEN @SX ELSE SX_CODE END

実行して得られる結果表

上記のSQLを実行すると、以下のような結果表を取得することができます。

会員ID	氏名	性別
004	会員　三郎	男性

ちなみに、上記の方法でデータを抽出する際に、対象の列(会員氏名、性別コード)の登録値がNULLになっている場合は、正しくデータを取得することができないので、NULLが登録される可能性がある列と照合を行う場合は
SQLServerならISNULL関数を使って

ISNULL(照合する列, '')

1	ISNULL(照合する列, '')

MySQLならIFNULL関数を使って

IFNULL(照合する列, '')

1	IFNULL(照合する列, '')

としてNULLを”(空文字列)に変換する必要があります。
ISNULL、IFNULLの代わりにここでもCASE演算子を使って

CASE WHEN 照合する列 IS NULL THEN '' ELSE 照合する列 END

1	CASE WHEN 照合する列 IS NULL THEN '' ELSE 照合する列 END

と記述することもできます。

今回ご紹介した記事は、別になくてもプログラムを記述する際には困るものではありませんが、個人的にはプログラムでif分岐をするよりもソースがシンプルになる＋DBの関数オブジェクトとして作成する際も簡単なのでよく使っています。

SQLのCASE演算子はとても便利な演算子なので、皆さんもいろいろと使ってみて、スマートなSQLを書いてみてください。

おすすめの書籍

リンク