はじめに
SQLに対するよくある誤解の1つに、「SQLは帳票作成のための言語だ」というものがあります。確かに、SQLは定型・非定型を問わず、さまざまな帳票や統計表を作成するシステムで利用されています。そのこと自体に問題はありませんが、エンジニアにとってもSQLにとっても不幸なことは、SQLの本来の用途ではない結果のフォーマット整形までがSQLに求められることです。あくまでSQLはデータ検索を目的に作られた言語だからです。
しかし同時に、SQLは多くの人が考えているよりずっと強力な言語です。特に近年は、そうした用途にも対応すべく、OLAP関数をはじめとするレポート作成のための機能が多く取り入られるようになりました。システム全体としてソースを簡略化でき、十分なパフォーマンスを得られるなら、SQLの力を利用する価値は十分にあります。
本稿では、外部結合（OUTER JOIN）を利用したフォーマッティングの方法を解説します。外部結合そのものは、DBエンジニアにとってなじみ深い演算ですが、今回は少し変わった角度からその特性を捉えなおしてみたいと思います。前半が外部結合によるフォーマット整形の解説、後半が集合演算の観点から見た外部結合、という内容になっています。
稼働環境
- Oracle
- SQL Server
- DB2
- PostgreSQL
- MySQL（サブクエリを使うものについてはバージョン4.1以上）
対象読者
外部結合の基本的な使い方を知っている方。CASE式、自己結合、相関サブクエリ、スカラ・サブクエリについての知識があると望ましいです（外部結合をまったく知らない場合は、後半の「完全外部結合」と「外部結合で集合演算」から読むと理解しやすいかもしれません）。
外部結合で行列変換 その1（行→列）：クロス表を作る
以前、『CASE式のススメ（前編）』で、集約関数の中でCASE式を使い、クエリの結果をクロス表にする方法を紹介しました。今回考える問題もイメージとしては似ていますが、集約した数値を求めるのではなく、「ヒラ」で展開するところが違います。例として、社員が受講した研修コースを管理する次のようなテーブルを考えましょう。
| 受講者（name） | 講座（course） |
| 赤井 | SQL入門 |
| 赤井 | UNIX基礎 |
| 鈴木 | SQL入門 |
| 工藤 | SQL入門 |
| 工藤 | Java中級 |
| 吉田 | UNIX基礎 |
| 渡辺 | SQL入門 |
最初の問題は、このテーブルから次のようなクロス表を作ることです。「○」なら受講済み、NULLなら未受講です。
| SQL入門 | UNIX基礎 | Java中級 | |
| 赤井 | ○ | ○ | |
| 工藤 | ○ | ○ | |
| 鈴木 | ○ | ||
| 吉田 | ○ | ||
| 渡辺 | ○ |
実のところ、元テーブルと結果の間で情報量に差はありません。誰が何の講座を受講したかという情報は、どちらからも同じだけ分かります。違うのは見た目だけです。だから本当は、ここにSQLがするべき仕事はありません。ありませんが、そこを敢えてSQLでやるのが本稿の主旨です。まず考えられる方法は、表側（受講者一覧）をマスタとした外部結合を行うことです。
SELECT C0.name, CASE WHEN C1.name IS NOT NULL THEN '○' ELSE NULL END AS "SQL入門", CASE WHEN C2.name IS NOT NULL THEN '○' ELSE NULL END AS "UNIX基礎", CASE WHEN C3.name IS NOT NULL THEN '○' ELSE NULL END AS "Java中級" FROM (SELECT DISTINCT name FROM Courses) C0 --このC0が表側になる LEFT OUTER JOIN (SELECT name FROM Courses WHERE course = 'SQL入門' ) C1 ON C0.name = C1.name LEFT OUTER JOIN (SELECT name FROM Courses WHERE course = 'UNIX基礎' ) C2 ON C0.name = C2.name LEFT OUTER JOIN (SELECT name FROM Courses WHERE course = 'Java中級' ) C3 ON C0.name = C3.name;
サブクエリを使って、おおもとの「Courses」テーブルからC0～C3の4つの集合を作っています。自己結合の回でも述べたように、SQLの中で名前を与えれば、テーブルもビューも等しく「集合」として存在することになります。従ってここでは、次のような4つの集合を作ったことになります。
受講者全員を網羅したC0が、いわゆる「受講者マスタ」の役割を果たします（これが最初からテーブルとして用意されている場合は、それを使ってください）。C1～C3が、講座ごとの受講者の集合です。そして、C0をマスタとして、C1～C3を順番に外部結合しています。すると、受講した講座の列には受講者の名前が、未受講の列にはNULLが現れます。仕上げに、CASE式で受講済みを「○」に変換して完成です。
今回は、求める結果の表頭が3列だったので、3回結合しました。列数が増えた場合も原理は同じなので、結合を追加することで対応できます。表側と表頭を入れ替えたクロス表が欲しい場合も、同じ要領で簡単にできます。この方法は、発想が素直で動作が分かりやすいところが長所ですが、インライン・ビューと結合を多用するのでコードが長大になるのが短所です。表頭の列が増えるにつれパフォーマンスも悪化します。
そこで、同じ結果を求める他の方法も考えてみましょう。一般に、外部結合はスカラ・サブクエリで代用できるので、次のようなクエリも考えられます。
SELECT C0.name, (SELECT '○' FROM Courses C1 WHERE course = 'SQL入門' AND C1.name = C0.name) AS "SQL入門", (SELECT '○' FROM Courses C2 WHERE course = 'UNIX基礎' AND C2.name = C0.name) AS "UNIX基礎", (SELECT '○' FROM Courses C3 WHERE course = 'Java中級' AND C3.name = C0.name) AS "Java中級" FROM (SELECT DISTINCT name FROM Courses) C0; --このC0が表側になる
表頭の3列をスカラ・サブクエリで作っているところがポイントです。最終行のFROM句の集合C0は、さっき作った「受講者マスタ」と同じものです。スカラ・サブクエリは、結合条件に一致すれば「○」を、不一致ならNULLを返します。この方法の利点は、講座の増減があったときにも、変更箇所がSELECT句だけで済むので、ソースの修正が簡単なことです。
例えば4列目として「PHP入門」という講座を追加したければ、SELECT句の最後に
(SELECT '○' FROM Courses C4 WHERE course = 'PHP入門' AND C4.name = C0.name ) AS "PHP入門"
と付け加えるたけでOKです（先の外部結合の方法だと、SELECT句とFROM句の2箇所を修正せねばなりません）。これは、仕様変更の場合だけでなく、動的にSQLを組み立てる必要のあるシステムにおいても大きなメリットです。反対に欠点は、パフォーマンスがあまりよくないことです。現在のところ、SELECT句でスカラ・サブクエリ（おまけに相関サブクエリでもある）を使うのは、けっこう高コストな方法です。
3つ目に紹介するのは、CASE式を入れ子にする方法です。CASE式は、SELECT句で集約関数の中にも外にも書くことができます（『CASE式のススメ（後編）』を参照）。そこで1度、SUM関数の結果を1とNULLで出力し、外側のCASE式で1を「○」に変換します。
SELECT name, CASE WHEN SUM(CASE WHEN course = 'SQL入門' THEN 1 ELSE NULL END) = 1 THEN '○' ELSE NULL END AS "SQL入門", CASE WHEN SUM(CASE WHEN course = 'UNIX基礎' THEN 1 ELSE NULL END) = 1 THEN '○' ELSE NULL END AS "UNIX基礎", CASE WHEN SUM(CASE WHEN course = 'Java中級' THEN 1 ELSE NULL END) = 1 THEN '○' ELSE NULL END AS "Java中級" FROM Courses GROUP BY name;
集約せずに取得すると、「Courses」テーブルの行数だけ出力されてしまうので、受講者単位で集約しています。これもスカラ・サブクエリの方法に劣らず簡潔で、仕様変更に強い書き方です。集約関数の戻り値を条件に組み込む書き方は、慣れるまで少し戸惑うかもしれません。考え方としては、「SELECT句では集約関数もスカラ値に評価されるので、定数や列名と同じように扱える」、と考えれば理解しやすいでしょう。
