SQL For Practitioners

　日々 SQL を書いていると、年に何度か、これは妙案と膝を叩きたくなるテクニックやトリックに遭遇することがあります。そういうコードを発見してから一週間ぐらいは、わけもなく心楽しく、いつ自分もそれを使ってやろうかとてぐすねひいてチャンスを待ちながら、同僚に向かって「ねえねえ知ってるか･･････」と無理やり説明して喜びを共有させたりするものです（そして相手が無関心な素振りを見せると大人気なく機嫌を損ねるものです）。この種の喜びは、小さいながらも、エンジニアという仕事の醍醐味と呼ぶべきものです。
　ここでは私が今まで出会った洒落た SQL を紹介します。知っていれば、いざというときの力になってくれるはずです。中でも使い勝手の良い CASE 式については独立の一部門としたので、こちらも併せてご覧ください。

1．関係代数: 1-1．二つのテーブルをコンペアする; 1-2．二つのテーブルの一致しない行を選択する; 1-3．完全外部結合で積集合と差集合を作る
2．関数・述語の応用: 2-1．集約関数をネストする; 2-2．IN のリストに列を指定する; 2-3．リスト比較
3．相関サブクエリ: 3-1．別テーブルからの UPDATE; 3-2．重複行を削除する
4．HAVING句の力: 4-1．COUNT(*) は NULL を数える　その１：HVING句での応用; 4-2．歯抜けを探せ
5．集合指向で行こう: 5-1．COUNT(*) は NULL を数える　その２：サブクエリでの応用; 5-2．重複順列・順列・組み合わせ; 5-3．上位 n 位; 5-4．別居中ですか？

１．関係代数

1-1．二つのテーブルをコンペアする

　DB環境を移行したり、テーブルごと納品するような場合、二つのテーブルが全く同一かどうかを調べたい場合があります。「全く同一」とはつまり、同一の列を持ち、同一の行数を持ち、同一のデータ内容を保持している、ということです。一般に、ファイルであれば diff や compare という便利なツールが用意されているのですが、そういう機能をテーブルに対して用意している DBMS というのは、私が知る限りありません。しかし心配無用です。SQL でそのチェックを実現することができるのです。
　いま、tbl_A とtbl_B は行数の同じテーブルだと仮定します。この場合、次の SQL の結果を見ることで、二つのテーブルが全く同一かどうかを判定することができます。

　　SELECT COUNT(*)
　　FROM (
　　　　　SELECT *
　　　　　FROM　 tbl_A
　　　　　UNION
　　　　　SELECT *
　　　　　FROM　 tbl_B
　　);

　tbl_A とtbl_B の行数がともに10行だった場合、上の SQL の結果も10であれば、tbl_A とtbl_B は全く同一です。10以外であれば、そうではありません。なぜそう言えるのでしょう？それは、UNION は重複行を排除するという性質を持つからです。もし tbl_A とtbl_B が全く同一であれば、その二つのテーブルは完全に重複することになり、UNION によって行数が増えることはありません。もし UNION して行数が増えたのなら、それはどこかに一致しない行が存在した証拠なのです。この判定は、テーブルに NULL が存在する場合でも行なえます。
　今なら、皆さんが使用しているデータベースが SQL-92 で追加された INTERSECT 演算子（共通部分）を持っているならば、それを使うことでも同じことが実現できます。昔は INTERSECT を持っているデータベースが少なかったので、UNION で代用していました（UNION は SQL-86 からの古参）。

1-2．二つのテーブルの一致しない行を選択する

　さて、前段のコンペアによって、二つのテーブルが異なるものであることが判明したら、次はどの行が問題の一致しない行であるのかを発見しましょう。いま、主キーに関しては完全に同一であると仮定します。（そもそも主キーに不一致がある場合は、NOT EXISTS を使って簡単に見つけられるので、ここでは省略します。）次のように、もう一度 UNION によるトリックを使います。

　　SELECT primary_key
　　FROM (
　　　　　SELECT *
　　　　　FROM　 tbl_A
　　　　　UNION
　　　　　SELECT *
　　　　　FROM　 tbl_B
　　)
　　GROUP BY primary_key
　　HAVING COUNT(*) > 1;

　お分かりでしょうか？同一ではない行については、重複を排除することができないので、主キーが同じ値の行が複数行返されるのです。
　これら二つの SQL は、どちらも UNION の重複排除という特性を活用したトリックであることが分かります。この特性はソートを発生させるためパフォーマンス悪化の原因にもなるのですが、使い道しだいではこのような力を発揮します。同じことを Oracle であれば MINUS 演算子を使ってもっと簡単に書けますが、汎用性の高いこちらを覚えておくことを薦めます。

1-3．完全外部結合で積集合と差集合を作る

　普通、外部結合というのは、（ネストすることはあったとしても）一つのテーブルをマスタとして行なうものですが、ごくまれに、二つのテーブルをマスタとして使いたいという、奇妙な要求が持ち込まれることがあります。こういう場合にも対処すべく、SQL-92 から「完全外部結合(FULL OUTER JOIN)」という技が用意されました。
　完全外部結合は双方のテーブルの情報を残すので、ある意味で UNION のような動作をします。そして、結果から NULL を排除することで積集合と差集合を作ることもできます。

**tbl_A**
col_1

a
b
c
d

**tbl_B**
col_1

a
b
c
e

　上のサンプルについて、完全外部結合によって積集合と差集合を求めます。WHERE句の IS NULL、IS NOT NULL 条件に注目してください。


-- 積集合（A ∩ B）　　　　　SELECT A.col_1, B.col_1 　　　　　　FROM 　tbl_A A 　　　　　　　　　　 FULL OUTER JOIN 　　　　　　　　　　　tbl_B B 　　　　　　　　ON　 A.col_1 = B.col_1 　　　　　 WHERE　 A.col_1 IS NOT NULL 　　　　　　　AND　 B.col_1 IS NOT NULL 　　　　　結果：　　　　　A.col_1　　B.col_1 　　　　　-----　　 ----- 　　　　　　　　a　　　　　a 　　　　　　　　b　　　　　b 　　　　　　　　c　　　　　c	-- 差集合（A - B）　　　　　SELECT A.col_1, B.col_1 　　　　　　FROM tbl_A A 　　　　　　　　　　 FULL OUTER JOIN 　　　　　　　　　　　tbl_B B 　　　　　　　　ON　 A.col_1 = B.col_1 　　　　　 WHERE　 A.col_1 IS NOT NULL 　　　　　　　AND　 B.col_1 IS NULL 　　　　　結果：　　　　　A.col_1　　B.col_1 　　　　　-----　　 ----- 　　　　　　　　d　　　NULL	-- 差集合（B - A）　　　　　SELECT A.col_1, B.col_1 　　　　　　FROM tbl_A A 　　　　　　　　　　 FULL OUTER JOIN 　　　　　　　　　　　tbl_B B 　　　　　　　　ON　 A.col_1 = B.col_1 　　　　　 WHERE　 A.col_1 IS NULL 　　　　　　　AND　 B.col_1 IS NOT NULL 　　　　　結果：　　　　　A.col_1　B.col_1 　　　　　-----　　 ----- 　　　　　NULL　　　　　e

　積集合と差集合を求める通常の演算子は、INTERSECT と EXCEPT です。これらを使わずに外部結合を使うことのメリットは、以下の二つです。

まだ INTERSECT や EXCEPT を実装している DBMS が少ない。
INTERSECT や EXCEPT はソートが発生するため、外部結合のが高速の可能性が高い。

　1番の場合、演算子が実装されていない以上、選択の余地はありません。2番の場合は、両者のパフォーマンスを比較して、可読性を下げてもなお速度を確保する必要があるかどうか、トレードオフの計算をして使ってください。

２．関数・述語の応用

2-1．集約関数をネストする

　集約関数は2レベルまでネストすることができます。例えば次のように書くことで、「各月の合計の平均」を取得することができます。

　　SELECT AVG(SUM(col_1))
　　FROM　 SomeTable
　　GROUP BY month;

　集約関数ならどういう組み合わせでもよいので、MAX(AVG(col_1)) とすれば、「平均の最大値」、MIN(SUM(col_1)) とすれば、「合計の最小値」を表現できます。ただし、MAX(AVG(SUM(col_1))) のような3レベルのネストを書くと、エラーになります。

2-2. IN のリストに列を指定する

　普通、IN述語は次のように使います。

　　SELECT *
　　FROM SomeTable
　　WHERE col_1 IN ( '1', '2', '3' );

　これは、「 col_1 = '1' OR col_1 = '2' OR col_1 = '3' 」と同値です。しかしまた、IN はリストに列を含めることができるので、次のような書き方もできるのです。

　　SELECT *
　　FROM SomeTable
　　WHERE '1' IN ( col_1, col_2, col_3 );

　これは、「 col_1 = '1' OR col_2 = '1' OR col_3 = '1' 」と同値です。普通はこの書き方に使い道はありません。せいぜい、この構文を知らない同僚に向かって書いて見せて「おおっ！？」という相手の反応を楽しむぐらいです。これが有用なのは、繰り返し項目の多いテーブルの場合です。多くの列を OR 条件でつなげなければならない場合には、SQL を非常にスマートに短縮することができます。

2-3. リスト比較

　比較述語は、基本的にはスカラ値同士を比べるときに使います。もちろん、複雑な式も引数に取れますが、式は実行時には結局スカラ値に評価されるので、実質スカラ値の比較をしているのと変わりません。
　ところが SQL-92 から、比較述語の引数にリストを与えることもできるようになりました。リストとは、スカラ値をカンマ区切りで並べたものだと考えてください。例えば、（1, 2, 3）や、（'りんご', 'みかん'）などがそうです。（100）のように、要素数が 1 のリストもありえます。
　リスト比較を使うと、列の比較条件を多く書かねばならない場合などに、SQL を短縮できます。

　　SELECT *
　　　FROM SomeTable
　　WHERE　(col_1, col_2) = ((10, 5));

　これは、次の SQL と同値です。

　　SELECT *
　　　FROM SomeTable
　　WHERE　col_1 = 10
　　　　AND　col_2 = 5;

　リストの要素の一つでも NULL が含まれていた場合、リスト比較は true になりません。これも普通の AND で条件を結合した場合と同様の振る舞いなので分かりやすいでしょう。
　リスト比較は、IN 述語でも使用することができます。

　　--IN述語でリストを使う：単純なリスト比較
　　SELECT *
　　　FROM SomeTable
　　WHERE　(col_1, col_2) IN ( (10, 5),
　　　　　　　　　　　　　　　　　　 (20, 30),
　　　　　　　　　　　　　　　　　　 (100, 200) );

　　--IN述語でリストを使う：サブクエリを使った比較
　　SELECT *
　　　FROM SomeTable
　　WHERE　(col_1, col_2) IN ( SELECT col_1, col_2
　　　　　　　　　　　　　　　　　　　　FROM SomeTable_2 );

　とりわけ、最後の IN 述語でサブクエリを利用するケースでは、サブクエリを繰り返していた SQL を簡潔に記述することができるため、リスト比較が大活躍します。イメージとしては、xy座標のある一点の同一性を比較しているのだと考えると理解しやすいでしょう（もっとも、この比喩が意味を持つのは三次元までなので、より一般的にはベクトルに喩えられます）。
　また、こういう複合的な要素を持つリスト（ベクトル）の比較をちょっと拡張すれば、一行まるごとの比較（行比較）や、複数行をまとめた比較（関係比較）へ拡張できることも、すぐに分かります。実際、SQL-92 ではリスト比較は行比較の一部として定義されています。これから、多くの DBMS がこういう拡張的な機能を実装していくでしょう。

３．相関サブクエリ

3-1．別テーブルからのUPDATE

　時々、tbl_A の col_1 を tbl_B の col_1 で Update したい、と思うことがあります。もちろん可能なのですが、ちょっと素直でない書き方をせねばなりません。次のように相関サブクエリで書きます。

　　UPDATE tbl_A A
　　SET 　　col_1 = ( SELECT DISTINCT col_1
　　　　　　　　　　　　　FROM　　tbl_B B
　　　　　　　　　　　　　WHERE　 A.key = B.key
　　　　　　　);

　これはこれで、決して理解に悩む SQL ではないのですが、私が「素直でない」と呼んだのは、普通に考えた場合、まっさきに次のように書く人が多いのではないか、と思ったからです。

　　UPDATE tbl_A A, tbl_B B
　　SET 　　A.col_1 = B.col_1
　　WHERE　A.key = B.key;

　この書き方は、少なくとも Oracle ではエラーになります。しかし不思議なことに、Microsoft Access ではOKです。Access は DBMS ではないから標準から外れているのだろう、と言われればそれまでですが、別にこのぐらいの SQL、標準として認めてくれてもいいのではないかと私は思います。相関サブクエリはプログラマにとってもオプティマイザにとっても難しく見えます。しかし当面は両方覚えておいて、場合によって使い分ける必要があります。

3-2．重複行を削除する

　重複行というのは、リレーショナル・データベースの世界において NULL と並んで嫌われる存在です。そのため、これを排除するための方法も数多く考えられています。ここでは、key_1 と key_2 について重複するレコードを、自己相関サブクエリを使って削除する方法を紹介します。重複行は2行でなくとも、何行あってもかまいません。

　　DELETE FROM SomeTable S1
　　WHERE　rowid < ( SELECT MAX(rowid)
　　　　　　　　　　　　　　　FROM SomeTable S2
　　　　　　　　　　　　　　WHERE S1.key_1 = S2.key_1
　　　　　　　　　　　　　　　　AND　S1.key_2 = S2.key_2 )

　rowid の代わりに主キーを使ってもいいのですが、主キーが複数のキーから構成される場合は書くのが面倒ですし、主キーが存在せず全列について重複するようなテーブルの場合は主キーを使うことができません。行 ID なら1列で済む上に速度も最速が保証されるので、積極的に使ってください。（Oracle なら rowid、 PostgreSQL なら oid になります。その他の DBMS にはこの種の擬似列はありません。主キーを必ず設定して、それを使うほかありません。）
　この書き方は重複行を排除するための最も基本的な書き方です。速度を追求したい場合は、さらに改善することができます。例えば、次のように EXISTS 述語を使えば、key_1 と key_2 にインデックスが張られている場合、かなりの速度改善が望めます。

　　DELETE FROM SomeTable S1
　　WHERE　EXISTS ( SELECT *
　　　　　　　　　　　　　　 FROM SomeTable S2
　　　　　　　　　　　　　　WHERE S1.key_1 = S2.key_1
　　　　　　　　　　　　　　　　AND　S1.key_2 = S2.key_2
　　　　　　　　　　　　　　　　AND　S1.rowid < S2.rowid )

　最初の SQL は、サブクエリの部分が必ず全表検索を行なってしまいますが、後者の場合、インデックスのスキャンのみで済む可能性が高くなります。

４．HAVING句の力

4-1．COUNT(*) は NULL を数える　その１：HVING句での応用

　COUNT(*) と COUNT(列名) の違いをご存知でしょうか？両者には二つの大きな違いがあります。一つがパフォーマンス上の違い、もう一つが結果集合の違いです。パフォーマンスのことは「SQLを速くするぞ」を参照してください。結果において両者がどう違うか。それは、COUNT(*) は NULL を数えるが、COUNT(列名) は数えない、ということです。例えば次のような、ある講義に出席した学生のレポート提出日を管理するテーブルを考えましょう。

**Report**
学生ID (student_id)	学部番号 (dpt_id)	提出日 (sbmt_date)

100	1	2005/10/10
101	1	2005/09/22
102	2
103	2	2005/09/10
200	2	2005/09/22
201	3
202	4	2005/09/25

　学生がレポートを提出すると、提出日に日付が入ります。未提出の間は NULL です。このテーブルに対して、「 SELECT COUNT(*) FROM Report 」というクエリを発行すると、結果は7が返ります。一方、「SELECT COUNT(sbmt_date) FROM Report」というクエリの結果は5になります。提出日が NULL の2行はカウントされません。言い換えれば、COUNT(*) は全行を数えるが、COUNT(列名) はそうではない、ということです。
　さて、それでは「まだレポートを提出していない学生のいる学部を選択する」というクエリをどう書くか考えます。普通に考えれば、次のように NOT EXISTS を使って書きます。

　　SELECT dpt_id
　　FROM　 Report R1
　　WHERE　NOT EXISTS ( SELECT *
　　　　　　　　　　　　　　　　FROM　 Report R2
　　　　　　　　　　　　　　　　WHERE　R1.dpt_id = R2.dpt_id
　　　　　　　　　　　　　　　　AND　　　R2.sbmt_date IS NULL
　　　　　　　　);

　これは素直な書き方ですし、パフォーマンス的にも良好でしょう。しかし一ひねり加えると、もっと簡潔に次のように書けます。

　　SELECT dpt_id
　　FROM　 Report
　　GROUP BY　dpt_id
　　HAVING　COUNT(*) <> COUNT(sbmt_id);

　COUNT(*) と COUNT(列名) の行数が一致しないということは、COUNT(*) の集合には NULL（＝未提出の学生）が含まれているということです。従ってこの書き方でも、正しい結果が得られます。

4-2．歯抜けを探せ

　次のような連番を持つテーブルを考えます。システムで割り振る ID などを格納する場合は、こういう連番の列を持つテーブルをよく作ります。

**SeqTable**
連番 (seq)

1
2
4
5
7
10

　見てお分かりのように、この列、「連番」という名前なのに、数が連続していません。3、6、8、9 が歯抜けになっています。

1　2　3　4　5　6　7　8　9　10

　最初の任務は、このテーブルに歯抜けが存在するか否かをチェックすることです。こういうとき、手続き型言語の考え方に慣れ親しんだ人は、ソートして一行づつ次の行と比較する、という方法を考えがちです。しかし SQL の場合、そういう行単位の発想はフィットしません。SQL の強味は複数行（＝集合）を一度に扱えることにあります。従ってここでも、行集合全体を一まとめとして捉える観点から考えるべきです（それがすなわち集合指向）。
　HAVING句を使えば、わずか3行で書けます。

　　-- 行が返れば歯抜けあり
　　SELECT COUNT(*)
　　FROM SeqTable
　　HAVING COUNT(*) <> MAX(seq);

　このクエリの結果が一行でも返れば、歯抜けあり、一行も返らなければ歯抜けなしです。COUNT(*) で数えた行数と、連番の最大値が一致したなら、それは最初から最後まで歯抜けなくカウントアップできたという証拠だからです。歯抜けがあれば、COUNT(*) が MAX(seq) より小さくなります。
　なお、SELECT句で COUNT(*) を指定していますが、ここは適当な定数を指定してもかまいません。ただし、テーブルの列を指定することはできません。なぜならば、GROUP BY 句なしで集約するということは、テーブル全体を一行に集約することですが、そのときテーブルの列の値は、いったいどれを使うべきか不明だからです（1 ですか、2 ですか、それとも 10 ですか？）。

　　--この書き方は間違い。SELECT句に列は指定できない
　　SELECT seq
　　 FROM　SeqTable
　　HAVING COUNT(*) <> MAX(seq);

　これでこのテーブルに歯抜けが存在することが判明しました。次の任務は歯抜けの数字のうち、その連続する数列の最初の数字を求めることです。すなわちこの例なら、3、6、8 を求めることです。9 を一緒に求めるのは、結構難しいので、とりあえずこの三つの数字を得るには、次のように書きます。

　　SELECT seq + 1
　　　FROM SeqTable
　　 WHERE (seq + 1) NOT IN ( SELECT seq
　　　　　　　　　　　　　　　　　　 FROM SeqTable )
　　　 AND seq < 10;

　このクエリは、テーブルに存在する数値について、それより一つ大きい数値がテーブルに存在しないこと、という条件で、歯抜けになる直前の数値を求め、最後にそれに 1 を加えています。
　いかがでしょう、一行づつループで比較を行う手続き型言語と、集合全体を一度に操作する集合指向言語の発想の違い、少し実感していただけたでしょうか。

５．集合指向で行こう

5-1．COUNT(*)はNULLを数える　その２：サブクエリでの応用

　前段でCOUNT(*) は NULL をカウントするという特性を利用したトリックを紹介しました。ここでは、この特性のもう一つの側面を使ってみましょう。その側面とは、「 NULL を集合に含むならば、その集合の要素は最低でも一つある」というものです。当たり前のことじゃないか、と思うでしょうが、これが以外に便利なのです。次のようなテーブルを例に考えましょう。

**Personnel**
社員ID (emp_id)	部署番号 (dpt_id)	上司ID (chief_id)

100	1
101	1	100
200	2	202
201	2	202
202	2
300	3
400	4

　どこかの会社の人事テーブルだと想像してください。社員の属する部署番号と、直属の部長の ID を保持しています。部長の行の上司IDは NULL です。本当は、社員と上司の関係は正規化して別テーブルに持つことが望ましいのですが、こういう一つのテーブル内に全ての情報を保持する欲張りなテーブルを見かけることも少なくありません。さて、このテーブルから、「社員を一人以上持つ部長」を抽出するクエリを考えます。すなわち、社員番号が100と202の社員を選択したいのです。単純に部長を抽出するだけなら、「上司IDが NULL 」という条件だけでいいのですが、それでは社員番号が300と400の社員も含まれてしまいます。
　やり方としては、最も素直に考えるなら、5．のときと同じように、EXISTS を使う方法が考えられます。しかしやはり少し長い SQL になります。COUNT(*) を使うなら、次のような相関サブクエリとして書けます。

　　SELECT emp_id
　　FROM　 Personnel P1
　　WHERE　chief_id IS NULL
　　AND　　　( SELECT COUNT(*)
　　　　　　　　 FROM　　Personnel P2
　　　　　　　　 WHERE 　P1.dpt_id = P2.dpt_id
　　　　　　　) > 1;

　相関サブクエリの意味は、同一の部署の件数を数えて、それが1よりも多いこと、すなわち部下が一人以上いる部署であること、というものです。部下を一人も持たない部署であっても、COUNT(*) はNULLを数えるため、部署ごとにグルーピングされた集合は必ず一つ以上の要素を持つことが保証されます。その性質を利用したのが上のクエリです。

5-2．重複順列・順列・組み合わせ

　商品マスタのようなテーブルに、「りんご、みかん、バナナ」の3レコードが登録されているとします。売上分析などの際には、これらの商品の組み合わせが取得できると便利です。「組み合わせ」と一言でいっても、その種類は二つあります。一つが、並び順を意識した順序対（ordered pair）、もう一つが非順序対（unordered pair）です。順序対は、<1, 2> のように尖った括弧で、非順序対は {1, 2} のような括弧で表記します。順序が違えば別物なので、<1, 2> ≠ <2, 1> ですが、{1, 2} = {2, 1} です。
　さて、次のように単純にクロス結合すると、順序対が得られます。

　　SELECT P1.prd_name prd_1, P2.prd_name prd_2
　　FROM　 Products P1,
　　　　　　　Products P2;

　　結果：
　　prd_1　　　　　　prd_2
　　------　　　　　------
　　りんご　　　　　　りんご
　　りんご　　　　　　みかん
　　りんご　　　　　　バナナ
　　みかん　　　　　　りんご
　　みかん　　　　　　みかん
　　みかん　　　　　　バナナ
　　バナナ　　　　　　りんご
　　バナナ　　　　　　みかん
　　バナナ　　　　　　バナナ

結果行数の計算は重複順列になるので ₃Π₂ = 9 です。しかし、この結果には冗長な (りんご, りんご)という行が含まれていますし、（りんご, みかん）と（みかん, りんご）という組み合わせも異なる行として現れます。これは、先に述べたように順序を意識した集合だからです。
　ここから、冗長な集合を排除する修正を考えます。まず、（りんご, りんご）のような同一要素の組み合わせを除外すると、

　　SELECT P1.prd_name prd_1, P2.prd_name prd_2
　　FROM　 Products P1,
　　　　　　　Products P2
　　WHERE P1.prd_name <> P2.prd_name;

　　結果：
　　prd_1　　　　　　prd_2
　　------　　　　　------
　　りんご　　　　　　みかん
　　りんご　　　　　　バナナ
　　みかん　　　　　　りんご
　　みかん　　　　　　バナナ
　　バナナ　　　　　　りんご
　　バナナ　　　　　　みかん

結果行数の計算は順列で ₃P₂ = 6 です。これもまだ順序対です。ここからさらに、（りんご, みかん）と（みかん, りんご）のような順序を入れ替えた組み合わせを同一と見なしてまとめます。

　　SELECT P1.prd_name prd_1, P2.prd_name prd_2
　　FROM　 Products P1,
　　　　　　　Products P2
　　WHERE P1.prd_name > P2.prd_name;

　　結果：
　　prd_1　　　　　　prd_2
　　------　　　　　------
　　りんご　　　　　　みかん
　　りんご　　　　　　バナナ
　　みかん　　　　　　バナナ

結果行数の計算は組み合わせで ₃C₂ = 3 です。ここまで絞ってようやく非順序対が得られました。きっと実務で一番使うのはこれでしょう。このように等号以外の演算子を使う結合を「非等値結合」と言います。それを自己結合と組み合わせているので、「自己非等値結合」です。属性の組み合わせを作りたいときに多用するので、覚えておくとよいでしょう。
　また、この例題のように、>、< などの比較演算子は数値型の列に限らず、文字型でも辞書順比較として機能するということも、ちょっとしたワンポイントです。

5-3．上位 n 位

　「我が社の高給取りな連中はいったい幾らぐらいもらってるんだろ？」 ―― ここまでストレートに下世話な仕様はさすがにないでしょうが、数値型の列について上位 n 位を取得したいという要望は珍しくありません。n が 1 の場合は、極値関数 MAX と MIN を使えばすみますが、これはいわば極値関数の一般化問題です。
　最も単純に考えるなら、ORDER BY でソートして、最初の n 行のみを利用して残りは捨てる、という方法になります。また、実装によっては、結果集合を n 行に制限する独自拡張を持っていることもあります^[1]。しかし、対象のテーブルが非常に巨大である場合、ORDER BY によるソートはメモリも CPU も無駄遣いしますし、実装依存の機能に頼ることは汎用性に難があります。
　標準 SQL の範囲でこの仕様を実現する方法は、実はけっこう多く知られています。ここでは、そのうちの代表的な二つ ―― 結果を列持ちで表現するものと、行持ちで表現するもの ―― を紹介します。それぞれ一長一短があるので、場合によって使い分けてください。
　まず最初は列持ちの方法です。人事テーブルから上位3位までの高給を取得すると考えてください。スカラ・サブクエリを使って、次のように書きます。

　　SELECT ( SELECT MAX(salary)
　　　　　　　　FROM　Personnel ) AS top_1,　　　　　　　　　　　　　　 --第1位
　　　　　　　 ( SELECT MAX(salary)
　　　　　　　　 FROM　Personnel
　　　　　　　　 WHERE salary NOT IN (top_1) ) AS top_2, 　　　　　　--第2位
　　　　　　　 ( SELECT MAX(salary)
　　　　　　　　 FROM　Personnel
　　　　　　　　 WHERE salary NOT IN (top_1, top_2) ) AS top_3　　--第3位
　　FROM　 dual;

　ポイントが NOT IN 述語にあることはすぐに理解されるでしょう。まず1列目で、普通に給料の最大値を求めます。これが第1位です。次に、第1位の値を除いた残りの集合から最大値を求めます。これが第2位です。そしてさらに、第1位と第2位の値を除いた残りの集合から最大値を求めます。これが第3位です。dual テーブルは、使用されないので何でもかまいません。お分かりのように、これは補集合の概念を利用しています。直観的に理解しやすく、速度も速いすぐれものです。欠点は、結果を1行で返すため、他の関係との結合が不可能であることと、n の値を動的に変更することが困難なことです。（やろうと思えば動的に列を追加するロジックをホスト言語で書くこともできなくはありませんが、スマートさに欠けます。）
　こうした欠点を克服するには、次に紹介する第2の方法（行持ち）を使用します。少し難しく感じるかもしれませんが、とにかくまず SQL を見てください。

　　SELECT MIN(P1.salary)　　　　 --境界値
　　FROM　 Personnel P1,　　　　　--部分集合の要素を与える集合
　　　　　　　Personnel P2　　　　　--部分集合の境界値を与える集合
　　WHERE P1.salary >= P2.salary
　　GROUP BY P2.salary
　　HAVING COUNT(DISTINCT P1.salary) <= 3 ;

最初の SQL に比べると随分短くなりました。結果は3行になるので、結合も容易ですし、HAVING 句の「<= 3」という条件をパラメータにすれば、「上位 n 位」の動的な変更も容易です。しかし、一体どういう動作をしているのか、すぐには分からないのが大きな欠点です。特に難しいのが、「WHERE P1.salary >= P2.salary」という自己非等値結合の部分です。ここを重点的に見ましょう。
　いま、具体的に、人事テーブルに入っている給料の値が 100, 200, 300, 400, 500 の五つであると想定します。あまり現実的でない例ですが、話を簡単にするためです。すると、結合の結果を P2.salary でグループ化するということは、次の五つの集合を作ることを意味します。

【自己非等値結合で作られる同心円的な包含集合】

**S1:100**
100
200
300
400
500

**S2:200**
200
300
400
500

｜
｜
｜
｜
｜
｜
｜
｜
｜

**S3:300**
300
400
500

**S4:400**
400
500

**S5:500**
500

←捨てられる

　使われる→

ここで、各集合の名前の右に書かれた太字の数字は、P2.salary の値です。これが各集合の下限を決める境界値になります。S2 と S3 の間の縦棒は、HAVING 句の「要素の数が3以下である」という条件によって、S1 と S2 が除外されることを意味しています。最終的に選択されるのは、S3、S4、S5 の各集合の最小値、すなわち、赤字で記されている三つの数字になります。これが、SELECT 句の MIN 関数の意味です。セルコは、このような集合を「互いに包含関係にある、同心円的な集合」という簡にして要をえた表現をしています^[2]。確かに、この五つは、

　　　　S1 ⊃ S2 ⊃ S3 ⊃ S4 ⊃ S5

という包含関係にあることが見てとれます。なかなか手の込んだ、しかし見事な解答です。

5-4．別居中ですか？

　例えば、次のような住所録のようなテーブルを考えます。主キーは { 家族ID, 家族内連番 } で、これで1人の人間が特定できます。年賀状用などでこういう住所録を作っている人も、結構いると思います。

**AddressTbl**
家族ID (family_id)	家族内連番 (family_seq)	名前 (name)	住所 (address)

100	1	前田　義明	東京都港区虎ノ門3-2-29
100	2	前田　由美	東京都港区虎ノ門3-2-92
200	1	加藤　茶	東京都新宿区西新宿2-8-1
200	2	加藤　洋	東京都新宿区西新宿2-8-1
200	3	加藤　勝	東京都新宿区西新宿2-8-1
300	1	マイク・ハマー	ニューヨーク3番街
400	1	Ludwig Wittgenstein	Kundmanngasse 19, 1030 Wien Österreich
400	2	Paul Wittgenstein	Kundmanngasse 19, 1030 Wien Österreich

　前田夫妻に注目です。別に二人は別居中なのではなく、単に夫人の住所が間違っているだけです。このテーブルに登録されているデータは、本当は家族 ID が同じなら住所も同じでなければなりません。これは修正が必要です。では、前田夫妻のような「同じ家族だけど住所が不一致なレコード」を検出するにはどうすればよいでしょう？。
　幾つかの方法が考えられますが、ここでも部分的に自己非等値結合を使うと簡潔に書けます。

　　SELECT A1.family_id, A1.address
　　FROM　 AddressTbl A1,
　　　　　　　AddressTbl A2
　　WHERE A1.family_id = A2.family_id
　　　 AND　A1.address <> A2.address ;

　「同じ家族で、かつ、住所が違う」を逐語的に SQL に翻訳しただけなので、意味的に悩む箇所はないでしょう。もし自己結合がイメージしにくかったら、全く同じデータを保持するテーブル A1 と A2 が実際に二つあると想像してください。SQL においては、実テーブルだろうとインライン・ビューだろうと、名前を付けられた関係は等しく集合として扱われ^[3]、そして異なる名前が与えられれば異なる集合として扱われるからです。
　さて、これで一応、要件を満たす SQL はできました。ここで少し蛇足的なことを言うと、本当はそもそもこういう間違ったデータをテーブルに登録してしまうのは良くない設計方針です。出来ることなら、CHECK 制約を使用するなどして登録前にガードするべきです。さらに欲を言えば、「家族 ID → 住所」という関数従属が存在するので、正規化するべきです^[4]。
　しかし、そのようなささやかな（でもないのかな）要望が却下されることもしばしばです。「いやあ、データが汚いのは分かってるんだけどさ、とりあえずテーブルに入れちゃって、後でチェックして修正してよ」という声が聞こえるのは、幻聴ではありません。この SQL は、そんな苦しい状況下で汚いデータと闘わねばならない DB エンジニアを助けてくれます。

註
[1] 例えば、Oracle の rownum、PostgreSQL や MySQL の LIMIT など。

[2] J.セルコ『プログラマのためのSQL　第2版』(2001) p.311

[3] このテーブル（基底関係）とビュー（仮想関係）の平等原則を「交換可能性の原理（Principle of Interchangebility）」と呼びます。

[4] 「データベースに間違ったデータを入れてはならない」という原則は、その主張の強さに程度の差はあれ、多くの論者が支持しています。

データベースは真であることがわかっている命題の集まりとして考えることができる。そのような集まりに一貫性のないものを追加できたりしたら、全てが無駄になる。･･････一貫性のないデータベースから得られた答えは決して信用できない。（C.J.デイト『C.J.Dateのデータベース実践講義』 p.132）

DBMS が整合性を保証できるのは、全ての必要な整合性制約が宣言され、かつそれを実行可能な場合に限られる。もし必要な制約が宣言されていなかったりしたら ―― あるいは、せめてプロシージャで実装されていなかったりしたら ―― ユーザはアプリケーション側のコードでこれを実行しなければならない。しかし、これは非常に大きな労力を要する。（F.パスカル『Practical Issues in Database Management』 p.65）

この原則を支持する最大の理由は、SQL の動作を狂わせる要因を最大限排除するというものです。それは実にもっともで、私も異存はありません。しかしそれ以外にも、「関係は真な命題の集合である」という理論上のエレガンスを守りたいという動機も、恐らくあるのではないかと思われます。

戻る