SQL表記の標準化を推進するには
最近、とあるプロジェクトにおいて、プロジェクト管理の１つとして「SQLコーディング規約」あるいは「SQLコーディングチェックリスト」などにより、SQL表記の標準化を図ろうという取り組みに関わることになった。
開発責任者にヒアリングすると、テキストエディタでSQL文を記述しているのでどうしても開発者によって記述のゆらぎが発生し、可読性の悪いSQL文が量産されるということが悩みのタネらしい。
このような場合、SQL Developer等のツールを活用するのが賢い方法であるが、「SQLフォーマッターFor WEB」という秀逸なWebツールがあるので紹介したい。
本ツールは2006年に初版が出たとあるが、現在でも頻繁にアップデートが繰り返されている。
SQLフォーマッターFor WEB
このツールは、以下のような非常にシンプルなインターフェースのツールで、テキストボックスに記述されたSQL文を、ラジオボタンによって選択されたフォーマット・ルールによって整形してくれるスグレモノである。
上のテキストボックスにSQL文を手入力あるいはコピー＆ペーストで入力し、「整形する」ボタンをクリックすると、下のテキストボックスに整形されたSQL文が自動的に表示される。
さらに、右下の「copy」ボタンをクリックすると、クリップボードに整形されたSQL文がコピーされるので、開発で使用しているエディタ等にペーストすればよい。
次のSQL文をこのツールで実際に整形したところを以下に示す。
整形前
SELECT D.DEPARTMENT_NAME, CASE WHEN E.FIRST_NAME IS NOT NULL THEN SUBSTR(E.FIRST_NAME,1,1) || '. ' || E.LAST_NAME ELSE NULL END AS NAME FROM DEPARTMENTS D LEFT OUTER JOIN EMPLOYEES E ON D.DEPARTMENT_ID = E.DEPARTMENT_ID ORDER BY D.DEPARTMENT_NAME , E.LAST_NAME ;
整形後
select
D.DEPARTMENT_NAME
,case
when E.FIRST_NAME is not null then SUBSTR(E.FIRST_NAME, 1, 1) || '. ' || E.LAST_NAME
else null
end as NAME
from
DEPARTMENTS D
left outer join EMPLOYEES E
on D.DEPARTMENT_ID = E.DEPARTMENT_ID
order by
D.DEPARTMENT_NAME
,E.LAST_NAME
;
ちなみに私は、SQL全文を貼り付ける前に大文字に一括変換し
-
- カンマ整形：前
- AND/OR/ON整形：前
- インデント：スペース2
- JOIN形式：パターンB
- 予約語：小文字
- 出力先：色付きエディタ
の設定した上で使用するのが好みだ。
もちろん、これはプロジェクトマネージャの考え方で適宜統一してよい。
オフラインでも使用できる
最近の開発環境は、セキュリティの観点からインターネットとは完全に隔絶されている要件が必須だ。
「SQLフォーマッターFor WEB」はその名の通りWebツールなので基本的にインターネットに接続された状態で使う。
しかし、このツールはJava Scriptで記述されているので、「ファイル」メニューの「ページを別名で保存…」等で任意の場所にページを丸ごと保存（HTMLファイルとスクリプト等が含まれたフォルダ）したものを開発環境に移送し、HTMLファイルをダブルクリックすることでオフラインでも使用することができる。
この場合、色付きエディタが正常に機能しない可能性があるが、実用上は何ら問題ない。
標準SQLについて考える
SQLは元来、IBMの研究者であったエドガー・F・コッドが考案した関係データベースの実装である、関係データベース管理システム（RDBMS）の操作あるいは定義言語である。
しかし、UNIXあるいはLinuxにおいてOracle RDBMSがシェアを大きく獲得したため、IBMは標準化を主体的に策定することで巻き返しを図ってきたと、筆者は一人のOracle技術者として理解をしている。
一方、Oracle RDBMSも標準SQL（ANSI SQL）に積極的に準拠する戦略により対応している。（SQL言語リファレンスの「Oracleと標準SQL」参照）
標準SQLの内部結合
Oracle技術者にとって標準SQL記法に慣れることは、他RDBMSに移行する場合だけでなく可読性を高める目的でも有益である。
以下は、標準SQLによって記述された内部結合であるが、結合条件と検索条件を明確に区分して記述することができる。
標準SQLは結合条件と検索条件を明確に区別できる
標準SQLにより、開発者にとって可読性が向上し、例えばどのカラムにインデックスを作成するのが適切なのかがより容易になるのではないかと考える。
SQL> select
2 D.DEPARTMENT_NAME
3 ,case
4 when E.FIRST_NAME is not null then SUBSTR(E.FIRST_NAME, 1, 1) || '. ' || E.LAST_NAME
5 else null
6 end as NAME
7 from
8 DEPARTMENTS D
9 inner join EMPLOYEES E
10 on D.DEPARTMENT_ID = E.DEPARTMENT_ID -- 結合条件
11 where
12 D.DEPARTMENT_NAME like 'IT%' -- 検索条件
13 order by
14 D.DEPARTMENT_NAME
15 ,E.LAST_NAME
16 ;
DEPARTMENT_NAME NAME
------------------------- -------------------------
IT D. Austin
IT B. Ernst
IT A. Hunold
IT D. Lorentz
IT V. Pataballa
実行計画
----------------------------------------------------------
Plan hash value: 4213409228
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29 | 986 | 7 (15)| 00:00:01 |
| 1 | SORT ORDER BY | | 29 | 986 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 29 | 986 | 6 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPARTMENTS | 3 | 48 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1926 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
3 - filter("D"."DEPARTMENT_NAME" LIKE 'IT%')
統計
----------------------------------------------------------
4 recursive calls
4 db block gets
18 consistent gets
0 physical reads
0 redo size
748 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed
ちなみに「inner」は省略が可能であるが、外部結合ではないことを明示するために省略しない方がよいのではないかと考える。
実行環境は
SQL> select BANNER from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production PL/SQL Release 12.2.0.1.0 - Production CORE 12.2.0.1.0 Production TNS for Linux: Version 12.2.0.1.0 - Production NLSRTL Version 12.2.0.1.0 - Production
である。
Oracle SQL（注：標準SQLでないという意味）で記述した内部結合
Oracle SQLでは結合条件と検索条件がWHERE句に混在しているので、可読性が悪い。
SQL> select
2 D.DEPARTMENT_NAME
3 ,case
4 when E.FIRST_NAME is not null then SUBSTR(E.FIRST_NAME, 1, 1) || '. ' || E.LAST_NAME
5 else null
6 end as NAME
7 from
8 DEPARTMENTS D
9 ,EMPLOYEES E
10 where
11 D.DEPARTMENT_ID = E.DEPARTMENT_ID
12 and D.DEPARTMENT_NAME like 'IT%'
13 order by
14 D.DEPARTMENT_NAME
15 ,E.LAST_NAME
16 ;
DEPARTMENT_NAME NAME
------------------------- -------------------------
IT D. Austin
IT B. Ernst
IT A. Hunold
IT D. Lorentz
IT V. Pataballa
実行計画
----------------------------------------------------------
Plan hash value: 4213409228
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29 | 986 | 7 (15)| 00:00:01 |
| 1 | SORT ORDER BY | | 29 | 986 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 29 | 986 | 6 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPARTMENTS | 3 | 48 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1926 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
3 - filter("D"."DEPARTMENT_NAME" LIKE 'IT%')
統計
----------------------------------------------------------
4 recursive calls
4 db block gets
18 consistent gets
0 physical reads
0 redo size
748 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed
検索条件をON句に記述
標準SQLでは複合キーが結合条件となっている場合、ON句の中で「AND」を使用することで２番目以降の結合条件を記述することができるが、（少なくともOracleの場合）検索条件をON句の中に書くことができる。（「書くことができる」というのは構文エラーにならずに実行できるという意味）
SQL> select
2 D.DEPARTMENT_NAME
3 ,case
4 when E.FIRST_NAME is not null then SUBSTR(E.FIRST_NAME, 1, 1) || '. ' || E.LAST_NAME
5 else null
6 end as NAME
7 from
8 DEPARTMENTS D
9 inner join EMPLOYEES E
10 on D.DEPARTMENT_ID = E.DEPARTMENT_ID -- 結合条件
11 and D.DEPARTMENT_NAME like 'IT%' -- 検索条件
12 order by
13 D.DEPARTMENT_NAME
14 ,E.LAST_NAME
15 ;
DEPARTMENT_NAME NAME
------------------------- -------------------------
IT D. Austin
IT B. Ernst
IT A. Hunold
IT D. Lorentz
IT V. Pataballa
実行計画
----------------------------------------------------------
Plan hash value: 4213409228
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29 | 986 | 7 (15)| 00:00:01 |
| 1 | SORT ORDER BY | | 29 | 986 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 29 | 986 | 6 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPARTMENTS | 3 | 48 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1926 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
3 - filter("D"."DEPARTMENT_NAME" LIKE 'IT%')
統計
----------------------------------------------------------
4 recursive calls
4 db block gets
18 consistent gets
0 physical reads
0 redo size
748 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed
JOIN句はWHERE句よりも先に評価されるため、DEPARTMENTS表を絞り込んだ後に結合することを考えるとJOINに続くON句に記述することは理にかなっているようにも思えるが、実行計画上はWHERE句に書く場合と全く変わらないことがわかる。
標準SQLの左外部結合
次に、標準SQLでの左外部結合の例を紹介する。
この場合「LEFT JOIN」に先行する（左側にある）DEPARTMENTS表のうち絞り込み条件に合致する全行を表示し、結合キー（E.DEPARTMENT_ID）が存在しないEMPLOYEES表側はNullを表示する。
結合条件はON句において「D.DEPARTMENT_ID = E.DEPARTMENT_ID」を記述する。
「LEFT」を「RIGHT」に書き換えるだけで右外部結合を表現することができ、この例ではどの部署にも属さない従業員を含む従業員一覧となる。
SQL> select
2 D.DEPARTMENT_NAME
3 ,case
4 when E.FIRST_NAME is not null then SUBSTR(E.FIRST_NAME, 1, 1) || '. ' || E.LAST_NAME
5 else null
6 end as NAME
7 from
8 DEPARTMENTS D
9 left outer join EMPLOYEES E
10 on D.DEPARTMENT_ID = E.DEPARTMENT_ID
11 where
12 D.DEPARTMENT_NAME like 'IT%'
13 order by
14 D.DEPARTMENT_NAME
15 ,E.LAST_NAME
16 ;
DEPARTMENT_NAME NAME
------------------------- -------------------------
IT D. Austin
IT B. Ernst
IT A. Hunold
IT D. Lorentz
IT V. Pataballa
IT Helpdesk (null)
IT Support (null)
7行が選択されました。
実行計画
----------------------------------------------------------
Plan hash value: 3871261979
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 340 | 7 (15)| 00:00:01 |
| 1 | SORT ORDER BY | | 10 | 340 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 10 | 340 | 6 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 16 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1926 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID"(+))
3 - filter("D"."DEPARTMENT_NAME" LIKE 'IT%')
統計
----------------------------------------------------------
0 recursive calls
4 db block gets
15 consistent gets
0 physical reads
0 redo size
782 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
7 rows processed
このように、部署名が「IT」で始まる「IT Helpdesk」や「IT Support」も表示対象となるが、あいにくどちらも従業員がアサインされていないのでNullが表示されている。
select
D.DEPARTMENT_NAME
,case
when E.FIRST_NAME is not null then SUBSTR(E.FIRST_NAME, 1, 1) || '. ' || E.LAST_NAME
else null
end as NAME
from
DEPARTMENTS D
,EMPLOYEES E
where
D.DEPARTMENT_ID = E.DEPARTMENT_ID(+)
and D.DEPARTMENT_NAME like 'IT%'
order by
D.DEPARTMENT_NAME
,E.LAST_NAME
;
Oracle SQLで外部結合を記述するには、キーが存在しない行をNullで表示する方（この場合E.DEPARTMENT_ID側）に(+)を記述する。
（必然的に、左外部結合であれば右辺側、右外部結合の場合は左辺側に(+)を記述するのだが、可読性はかなり悪い。）
また、標準SQLで外部結合を記述したSQLの実行計画を見ると、Predicate Informationに
2 – access(“D”.”DEPARTMENT_ID”=”E”.”DEPARTMENT_ID”(+))
を確認することができる。（47行目）
つまり、Oracleの場合は標準SQLで記述してもいったんOracle SQLにリライトされた上でパーサ（Parser）に送られるのではないかと思われる。
検索条件をON句に記述した外部結合
それでは、内部結合と同様に検索条件をON句に記述した結果を確認してみよう。
SQL> select
2 D.DEPARTMENT_NAME
3 ,case
4 when E.FIRST_NAME is not null then SUBSTR(E.FIRST_NAME, 1, 1) || '. ' || E.LAST_NAME
5 else null
6 end as NAME
7 from
8 DEPARTMENTS D
9 left outer join EMPLOYEES E
10 on D.DEPARTMENT_ID = E.DEPARTMENT_ID
11 and D.DEPARTMENT_NAME like 'IT%'
12 order by
13 D.DEPARTMENT_NAME
14 ,E.LAST_NAME
15 ;
DEPARTMENT_NAME NAME
------------------------- -------------------------
Accounting (null)
Administration (null)
Benefits (null)
Construction (null)
Contracting (null)
Control And Credit (null)
Corporate Tax (null)
Executive (null)
Finance (null)
Government Sales (null)
Human Resources (null)
IT D. Austin
IT B. Ernst
IT A. Hunold
IT D. Lorentz
IT V. Pataballa
IT Helpdesk (null)
IT Support (null)
Manufacturing (null)
Marketing (null)
DEPARTMENT_NAME NAME
------------------------- -------------------------
NOC (null)
Operations (null)
Payroll (null)
Public Relations (null)
Purchasing (null)
Recruiting (null)
Retail Sales (null)
Sales (null)
Shareholder Services (null)
Shipping (null)
Treasury (null)
31行が選択されました。
実行計画
----------------------------------------------------------
Plan hash value: 3743165598
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 108 | 4536 | 85 (2)| 00:00:01 |
| 1 | SORT ORDER BY | | 108 | 4536 | 85 (2)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 108 | 4536 | 84 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
| 4 | VIEW | VW_LAT_718C084F | 4 | 104 | 3 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS FULL| EMPLOYEES | 4 | 72 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("D"."DEPARTMENT_NAME" LIKE 'IT%')
6 - filter("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
統計
----------------------------------------------------------
0 recursive calls
8 db block gets
31 consistent gets
0 physical reads
0 redo size
1524 bytes sent via SQL*Net to client
630 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
31 rows processed
明らかに、意図するものと異なる結果となった。
少なくともOracleにおいては、外部結合で検索条件をON句に書いてしまうと間違った結果を導いてしまう恐れがあるので注意が必要である。
同様に、内部結合においても検索条件はWHERE句に書くことをおすすめする。（我々が心配しなくてもCBOは結合前に適切に検索条件で絞り込んでくれる。）
FULL OUTER JOINを試す
標準SQLの最も優れている点は、完全外部結合が簡単に表記できることである。
以下のように、「full outer join」により、従業員がアサインされていない部署とどの部署にもアサインされていない従業員をまとめて表示させることができる。
（特に最後の２行に注目）
SQL> select
2 D.DEPARTMENT_NAME
3 ,case
4 when E.FIRST_NAME is not null then SUBSTR(E.FIRST_NAME, 1, 1) || '. ' || E.LAST_NAME
5 else null
6 end as NAME
7 from
8 DEPARTMENTS D
9 full outer join EMPLOYEES E
10 on D.DEPARTMENT_ID = E.DEPARTMENT_ID
11 order by
12 D.DEPARTMENT_NAME
13 ,E.LAST_NAME
14 ;
DEPARTMENT_NAME NAME
------------------------- -------------------------
Accounting W. Gietz
Accounting S. Higgins
Administration J. Whalen
Benefits (null)
Construction (null)
Contracting (null)
Control And Credit (null)
Corporate Tax (null)
Executive L. De Haan
Executive S. King
Executive N. Kochhar
Finance J. Chen
Finance D. Faviet
Finance N. Greenberg
Finance L. Popp
Finance I. Sciarra
Finance J. Urman
Government Sales (null)
Human Resources S. Mavris
IT D. Austin
...................................................
DEPARTMENT_NAME NAME
------------------------- -------------------------
Shipping M. Weiss
Treasury (null)
(null) K. Grant
123行が選択されました。
実行計画
----------------------------------------------------------
Plan hash value: 3058970667
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 122 | 5246 | 7 (15)| 00:00:01 |
| 1 | SORT ORDER BY | | 122 | 5246 | 7 (15)| 00:00:01 |
| 2 | VIEW | VW_FOJ_0 | 122 | 5246 | 6 (0)| 00:00:01 |
|* 3 | HASH JOIN FULL OUTER| | 122 | 4148 | 6 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1926 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
統計
----------------------------------------------------------
0 recursive calls
4 db block gets
15 consistent gets
0 physical reads
0 redo size
4212 bytes sent via SQL*Net to client
696 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
123 rows processed
実行計画を見ると「HASH JOIN FULL OUTER」というオペレーションを確認することができる。（Id=3）
開発の現場で完全外部結合が必要になった場面を見たことはないのだが、非常にシンプルな記述で複雑な処理を行うことができるのはまさに構造化言語であるSQLらしい記述と言えるかもしれない。
ただし、パーティション化された完全外部結合(FULL)は指定できない等の制約があるので、使用する際は注意が必要である。