データベーススペシャリスト SQL問題について

データベーススペシャリストのSQL問題

平成３１年春期問１１ を見ていて気になったのです

これってもっといいSQLがあるんじゃない？と
気になったので調べてみました。

社員資格テーブル

EMPCODE	QUALCODE
S001	AP
S001	DB
S001	FE
S002	FE
S002	SM
S003	FE
S004	AP
S005	« NULL »

結果

EMPCODE	資格1	資格2
S001	FE	AP
S002	FE	NULL
S003	FE	NULL

上記の社員資格テーブルから結果になるようにデータを抽出するにはどのようなSQLを実行すればよいか？という問題です。

正解は以下のようなSQLになるようにとのことですが、違和感を感じました。

SELECT C1.EMPCODE, C1.QUALCODE AS 資格1, C2.QUALCODE AS 資格2
FROM 社員資格テーブル C1
LEFT OUTER JOIN 社員資格テーブル C2
    ON C1.社員コード = C2.社員コード
    AND C1.QUALCODE = 'FE' 
　  AND C2.QUALCODE = 'AP'
WHERE C1.QUALCODE = 'FE';

確かに上記SQLでも欲しい結果が得られます。
でも、

SELECT C1.EMPCODE, C1.QUALCODE AS 資格1, C2.QUALCODE AS 資格2
FROM 社員資格テーブル C1
LEFT OUTER JOIN 社員資格テーブル C2
    ON C1.社員コード = C2.社員コード
    （AND C1.QUALCODE = 'FE' ） -- この条件文、いらないんじゃ？ 
　  AND C2.QUALCODE = 'AP'
WHERE C1.QUALCODE = 'FE';

C1の集合（外側のWhere文で ’FE’で絞り込まれる） とC2（社員コードと’AP’）の結合なので、外部結合の条件から削除しても結果は同じです。
※設問上、ひっかけ要素として追加しているのだとは思いますが。

ちなみに、実行計画はというと、

SELECT STATEMENT Cost = 6
 +-HASH JOIN OUTER      2 rows (32 bytes)   1 sec.  "C1"."EMPCODE"="C2"."EMPCODE"(+)
    +-TABLE ACCESS FULL      (SAMPLE.EMPQUAL "C1"@"SEL$1")  2 rows (16 bytes)   1 sec.
    +-TABLE ACCESS FULL      (SAMPLE.EMPQUAL "C2"@"SEL$1")  2 rows (16 bytes)   1 sec.

2回、FULL検索が走っていますね。
INDEXや主キーを貼っていないので当然の結果ですが、本来言いたかったことはというと、2回結合する必要ないんじゃない？ということです。

PIVOT使おう！

修正後SQLは以下の通りです。

SELECT * 
FROM EMPQUAL C1
PIVOT ( MAX(QUALCODE) FOR QUALCODE IN ( 'FE' AS QUAL_FE ,
                                        'AP' AS QUAL_AP))
WHERE QUAL_FE = 'FE'

実行計画

SELECT STATEMENT Cost = 4
 +-FILTER            sec.
    +-HASH GROUP BY PIVOT       1 rows (8 bytes)    1 sec.
       +-TABLE ACCESS FULL   (SAMPLE.EMPQUAL "C1"@"SEL$1")  8 rows (64 bytes)   1 sec.

テーブル検索も1回で済んでいます。
今回のレコード数は少ないので、問題にはならないでしょうが、レコードが増えた場合、ちゃんと考えないといけないですね。
PIVOTや分析関数が出る前は、元のSQLのように各集合を頑張って作って、全部結合して集計して、、、なんてやっていましたが、今は一番コストがかかりやすいデータ抽出を減らせる手段があるので便利な時代になりましたね～。