Rainbow Planet (GT×IT×SP×SA)

IT技術を分かりやすく簡潔にまとめることによる学習の効率化、また日常の気付きを記録に残すことを目指します。

01_IT技術 (Technology) 02_OracleDB

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

投稿日:2019年9月16日 更新日:

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

(0)目次&概説

(1) NG例
(2) OK例&解説
(3) CASE文の性能考察
 (3-1) UNIONで書いた時コスト
 (3-2) CASEで書いた時のコスト
 (3-3) 考察

(1) NG例

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

そしてここからが本題で、CASE文で取得した「SALARY」(別名)をWHERE句に使いたい場合です。(例文1: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'

(例文1)エラー

(2) 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)結果

目次にもどる

(3) CASE文の性能考察

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

(3-1) 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)結果

(3-2) 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)結果

(3-3) 考察

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

 >目次にもどる

Adsense審査用広告コード


Adsense審査用広告コード


-01_IT技術 (Technology), 02_OracleDB

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

APサーバ(Java)⇒DBサーバ(OracleDB 11g)へJDBC接続する方法まとめ

(0)目次 (1) 前提条件 (2) JDBCドライバーのインストール  (2-1) JDBCドライバーをダウンロード  (2-2) APサーバにアップロード・解凍 (3) プログラムの実装  (3- …

Linux(CentOS 6)のパケットフィルタリング方法(iptables)

(0)目次&概説 >(1) “iptables”の概要 >(2) “iptables”の解説  >(2-1) “iptables&#8221 …

Javaの動的Webプロジェクト作成での事前設定と雛形Webプロジェクト作成

「動的Webプロジェクト」とはHTMLのような静的ページのみならず、ServletやJSPを用いてWebアプリケーション開発をする際に作成します。本記事ではEclipseにて「動的Webプロジェクト」 …

Linuxサーバ(CentOS6)にOracleDB11gをインストールする(その1)

掲題の通り、LinuxにOracleDB(11g)をインストールする方法について書きます。 ■目次 (0)前提条件 (1)インストール要件の確認 (2)ユーザ/グループ作成 (3)Oracle DBソ …

Thumbs.dbが作られないように設定する方法(Windowsサーバー)

(0)目次&概説 (1) Thumbs.dbとは  (1-1) Thumbs.dbとは  (1-2) Thumbs.dbが作られるタイミング  (1-3) Thumbs.dbが作られる場所 (2) T …

Top