スカラサブクエリとNULL～外部結合との同値性
これは昔からよく知られているスカラサブクエリの応用方法ですが、スカラサブクエリを使うと外部結合を表現することができます。通常の外部結合の場合であれば、普通にOUTER JOIN構文を使えばよいのですが、同時に行列変換を伴うケースでは、スカラサブクエリを使った外部結合が効力を発揮します。
これを練習するのにうってつけの例題が、J.セルコ著『SQLパズル 第2版』の「パズル14 電話とFAX」です。次のようなサンプルテーブルを使います（図13）。
図13 サンプルのテーブル（その3）
Personnel
emp_id
（社員ID） | name
（名前） |
|---|
| 001 | 葛西 |
| 002 | 石川 |
| 003 | 三田 |
| 004 | 後藤 |
Phones
emp_id
（社員ID） | phone_type
（電話タイプ） | phone_nbr
（電話番号） |
|---|
| 001 | hom | 044-945-4199 |
| 001 | fax | 044-945-4200 |
| 002 | hom | 03-5589-1234 |
| 003 | fax | 087-296-9999 |
Personnelは人事用の社員マスタ。Phonesは、社員に与えられた電話番号の管理テーブルです。電話タイプが「hom」の番号は固定電話の、「fax」はFAXの番号を意味します。
さて、いま電話タイプは「行持ち」の形で管理されています。これを、図14のように外部結合ですべての社員を網羅しつつ、列持ちへ変換する方法を考えます。
図14 求める結果
Personnel
emp_id
（社員ID） | name
（名前） | hom_nbr
（固定電話の番号） | fax_nbr
（FAX 番号） |
|---|
| 001 | 葛西 | 044-945-4199 | 044-945-4200 |
| 002 | 石川 | 044-945-4199 | 044-945-4200 |
| 003 | 三田 | | 087-296-9999 |
| 004 | 後藤 | | |
スカラサブクエリで外部結合を表現する場合、主となるテーブルだけをFROM句に記述します。今回はもちろんPersonnelテーブルです。また、結果に含めたい列のうち、emp_idとname列はこのPersonnelに存在する列なので、それを使えばOKです。
キーポイントは、hom_nbrとfax_nbrの列を行から列へ展開する方法です。ここでスカラサブクエリを使います（リスト9）。
リスト9 外部結合しつつ、列を行へ展開
最初のhom_nbr列のサブクエリでは、phone_typeを「hom」に制限することで、Phonesテーブルについて、ある社員の固定電話の行だけを対象に展開することができます。fax_nbr列の展開も原理は同じです。
スカラサブクエリは、サブクエリ内の結合条件で1行もヒットしなかった場合、NULLを返すことになっています。そのため、石川氏のFAX番号、三田氏の電話番号、後藤氏の両方の番号については、NULLが返されます。これが、外部結合を代用することのできる秘密です。
ちなみに、この問題をスカラサブクエリではなく普通に外部結合の構文を使って解くことも可能です。その方法を考えることも演習問題としておきましょう。
おわりに
最後に、スカラサブクエリを使うときの注意事項に触れておきます。
スカラサブクエリというのは… 少し妙な表現かもしれませんが、「デリケートな」技術です。必ず単一の値（つまり1行だけ）を返すようにサブクエリ内の条件を設定しなければ、エラーになります。それは195ページからの「存在の階層」節で見たとおりです。
しかし、もしあのItemsテーブルにおいて、たまたまピーマンの値段が200円だったとしたら― たまたま商品グループ単位で最高値の商品が1つになる、という状況になります。こういうケースだけを想定してサブクエリを使っていると、それまでは偶然動作していたSQL文が、ある日突然「このサブクエリは複数行を返します」というエラーを引き起こすことになります。だから、スカラサブクエリを使うときは、戻り値が本当に、いつでも、常に、1行であることを、入念にチェックしてください。でないと、システムのカットオーバー後しばらくして、間違いなくクライアントから一報を受けることになるでしょう。
スカラサブクエリについてのもうひとつの注意点は、パフォーマンスに関するものです。一般的に、SELECT句においてスカラサブクエリを使うのは、かなりコストの高い方法です。したがって、相関サブクエリとして使う場合は、サブクエリ内の結合条件でなるべくインデックスを利用できるよう配慮することが重要です。
今回の例だと、「逆から考える」節では、主キーであるproject_idを結合キーとして使用しているため、データ量が増えたとしても、パフォーマンスは非常によいものが期待できます。一方、「存在の階層」節の結合キーitem_groupは、主キーではありません。したがって、パフォーマンスが悪い場合には、この列に対してインデックスを作成することを検討する必要があります。
このように、テーブルの形を自由に組み替えることができるスカラサブクエリは、使いこなせばSQLプログラミングの幅を広げられる便利な道具です。その一方で、「デリケート」な道具でもある点を、忘れずに使ってください。
演習問題
- 演習1．
リスト8のクエリでは
と、集約関数をWHERE句で使っています。なぜこのようなことが許されるのでしょうか？
- 演習2．
スカラサブクエリを用いずに、外部結合の構文を使って図13から図14への変換を行うクエリを作成してください。
参考資料
- Joe Celko著『SQLパズル 第2版』
（翔泳社、2007、ISBN：978-4-7981-1413-2）
- スカラサブクエリを使った行列変換は「パズル14 電話とFAX」。演習問題の答えも載っていますが、まずは自分で考えてみてください。スカラサブクエリの応用問題としては、「パズル16 主任とアシスタント」「パズル43 卒業」などもおもしろいものです。
- ミック著『達人に学ぶ SQL徹底指南書』
（翔泳社、2008、ISBN：978-4-7981-1516-0）
- 「1-5 外部結合の使い方」では、行列変換を行うための方法を複数紹介しています。実は、スカラサブクエリ以外にも同じことを実現する方法はあります。また、GROUP BYを使ったときの「存在の階層」の理論的背景について詳しく知りたい方は、「1-8 EXISTS述語の使い方」および「2-10 SQLにおける存在の階層」を参照してください。
