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)は指定できない等の制約があるので、使用する際は注意が必要である。