SQLでCASE文の結果をWHERE句に指定する方法およびコスト面の考察

投稿日：2019年9月16日更新日：2020年11月1日

本記事では、
・SQLでCASE文の結果をWHERE句に指定する方法について記述します。
・CASE文を使う場合のコストについて考察します。

(０)目次＆概説

(１) NG例
 (２) OK例＆解説
 (３) CASE文の性能考察
　 (３-１) UNIONで書いた時コスト
　 (３-２) CASEで書いた時のコスト
　 (３-３) 考察

(１) NG例

例えば「EMP」表と「DEPT」表の結合を例に考えてみます。
EMP表(TA)はCASE文で「SAL」カラムの多い少ないに応じて「HIGH SAL」または「LOW SAL」の値を動的に取得しています（例文1の4～7行目)
DEPT表(TB)は「DEPTNO」カラムでEMP表(TA)と結合（例文1の13行目)しています。

そしてここからが本題で、CASE文で取得した「SALARY」(別名)をWHERE句に使いたい場合です。（例文１：14行目）のように指定するとエラーになります（※「結局HIGHで絞るならCASE文いらなくね？」というツッコミもあると思いますが、まあ例なので・・笑）

（例文1）※構文エラー

SELECT
  --CASE文で下記のルールで値を動的に取得
  --SALが40万以上なら「HIGH SAL」／40万未満なら「LOW SAL」
  CASE
    WHEN TA.SAL > 400000 THEN 'HIGH SAL'
    WHEN TA.SAL < 400000 THEN 'LOW SAL'
  END AS SALARY
  ,TA.*
  ,TB.*
FROM 
  EMP TA
  ,DEPT TB
WHERE	TA.DEPTNO = TB.DEPTNO
AND 	SALARY = 'HIGH SAL'

（例文１）エラー

(２) OK例＆解説

このエラーを回避するためにFROM句にテーブルをそのまま指定するのではなく、SELECT文の形で指定します。具体的には次の（例文2）の5行目～15行目のように、抽出データの加工はSELECTの部分ではなくFROMの部分で行っています。そうする事でWHERE句等でも指定が可能となります。

（例文2）OK例

SELECT
  TA.*
  ,TB.* 
FROM
 (
  SELECT
	--CASE文で下記のルールで値を動的に取得
	--SALが40万以上なら「HIGH SAL」／40万未満なら「LOW SAL」
	CASE
	  WHEN E.SAL >= 400000 THEN 'HIGH SAL'
	  WHEN E.SAL < 400000 THEN 'LOW SAL'
	END AS SALARY
	,E.*
  FROM EMP E
 ) TA
,DEPT TB
WHERE	TA.DEPTNO = TB.DEPTNO
AND 	SALARY = 'HIGH SAL'

（例文2）結果

＞目次にもどる

(３) CASE文の性能考察

次に性能面での考察を少し行います。今回の例は「EMP」表から「HIGH SAL」と「LOW SAL」の人数をそれぞれ出力するSQLを２通りのやり方で記述し、それぞれの「コスト」と比較を行います。

(３-１) UNIONで書いた時コスト

UNIONで書いた場合のコストは「917」で、実行時間が「0.049秒」でした。構造は、前半の「HIGH SAL」抽出SQLと後半の「LOW SAL」抽出SQLを単純にUNIONしているだけです。
（例文3）

-- ①'HIGH SAL'のデータを抽出するSQL
SELECT
  'HIGH SAL' AS SALARY
  ,COUNT(*)
FROM
	EMP TA
	,DEPT TB
WHERE	TA.DEPTNO = TB.DEPTNO
AND   TA.SAL >= 400000

--①と②を合わせる
UNION

-- ②'LOW SAL'のデータを抽出するSQL
SELECT
  'LOW SAL' AS SALARY
  ,COUNT(*)
FROM
	EMP TA
	,DEPT TB
WHERE	TA.DEPTNO = TB.DEPTNO
AND   TA.SAL < 400000

（例文3）結果

(３-２) CASEで書いた時コスト

CASEで書いた場合のコストは「459」で、実行時間が「0.069秒」でした。細かい部分は違えど、構造は（例文2）と同じです。
（例文4）

SELECT
  SALARY
  ,COUNT(SALARY)
FROM
	(
	  SELECT
		CASE
		  WHEN E.SAL >= 400000 THEN 'HIGH SAL'
		  WHEN E.SAL < 400000 THEN 'LOW SAL'
		END AS SALARY
		,E.*
	  FROM EMP E
	) TA
	,DEPT TB
WHERE	TA.DEPTNO = TB.DEPTNO
GROUP BY SALARY
ORDER BY SALARY

（例文4）結果

(３-３) 考察

CASEを使った方がUNIONよりもコスト(リソース消費)が抑えられたが、実行時間はUNIONの方が速かったため、必ずしもどちがら良いとは断言できず、環境のリソース状況や要件によってどのアプローチが最善かを検討する形になると考えます。

＞目次にもどる