SQLアタマアカデミー

第10回 結合大全 (4)自己結合

この記事を読むのに必要な時間:およそ 2 分

自己結合

クロス結合,内部結合,外部結合とは分類の基準が違う

自己結合self joinは,文字通り自分自身と結合する演算で,要するに同じテーブル(あるいは同じビュー)を使って結合を行うものです。これは,先に解説してきた3種類の結合とはちょっと毛色が違います。というのも,自己結合というのは演算の種類に基づいた分類ではなく,演算の対象に何を使うかという点だけが問題だからです。その証拠に,自己結合は,⁠自己結合+クロス結合」⁠自己結合+外部結合」というように,ほかの結合と組み合わせて使うことができます。

たとえば,ちょっとパズル的な問題をサンプルに使って考えてみましょう。図7のような1行に1つ数字を持つ10行の「数字」テーブルを作ります。このテーブルに対して「自己結合+クロス結合」を行います。コードはリスト5のものを使います。

図7 自己結合を解説するためのサンプル

Digits

digit(数字)
0
1
2
3
4
5
6
7
8
9

リスト5 自己結合+クロス結合

SELECT D1.digit + (D2.digit * 10) AS seq
  FROM Digits D1 CROSS JOIN Digits D2;

さて,このコードの結果が具体的にどうなるか,ということを考える前に,行数が何行になるかを考えてください。これだけは,本稿をここまで読んだみなさんなら1秒で即答できるはずです。

できましたか? 答えは100行。理由は,クロス結合において行数の計算は,結合対象となるテーブルの行数の掛け算になるからです。この場合,結合対象はDigits(D1)およびDigits(D2)ですので,どちらも10行。したがって10×10が答えになります。ちなみにこのクエリの結果は,0から99までの連番になります図8)⁠

図8 リスト5の実行結果

SEQ
------
0
1
2
…
97
98
99

自己結合の考え方

一般的に自己結合を行う場合,同じテーブルに別名(この場合はD1とD2)をつけて,それらをあたかも別のテーブルであるかのように扱います。というより,クエリの動作を把握するためだけであれば,本当にこれらは別のテーブルであると考えるほうがスムーズです。つまり,D1とD2を,偶然保持するデータがまったく同一だった2つの異なる名前のテーブルとみなすのです図9)⁠そうすると,上のクエリは単純に,D1およびD2を対象としたクロス結合とみなすことができるわけです。

図9 D1とD2はデータが同一の異なる名前のテーブルと考える

画像

以上のような事情から,筆者は個人的に,結合の種類として自己結合という特別の分類は不要だと考えています。同一のテーブルと結合しているということは,物理レベルで見ればそのとおりなのですが,論理レベルで見ればそうした観点は不要になるからです。

COLUMN 結合条件の標準語と方言

結合の条件式の書き方は,標準SQLで厳密に定められています。基本的には,FROM句でJOIN構文を使い,結合条件をON句で記述するというもので,本稿もそのルールに則っています。しかし,この標準的な構文はまだ十分に広まっておらず,特に年配のエンジニアの中には,古い構文や実装依存の方言を使う人が多くいます。

内部結合の場合

具体例を挙げてみましょう。まず内部結合の場合,JOIN構文を使わず,WHERE 句に条件を記述するリストaのような構文でも,結果は同じです。

リストa 古い構文を利用した内部結合リスト2と比較)

SELECT E.emp_id, E.emp_name, E.dept_id, D.dept_name
  FROM Employees E, Departments D
 WHERE E.dept_id = D.dept_id;

これはすべての実装で使うことができる構文ですので,方言というわけではありません。ただ,次に挙げる理由から,この古い構文は今後使わないことを勧めます。

  • ① 結合条件と,単に1つのテーブルに対してフィルタする条件が同じWHERE句に混在するため,可読性が低い
  • ② 可読性が低いのは人間にとってだけでなく,オプティマイザにとっても同様であり,効率的な実行計画が選択されない危険がある
  • ③ いずれこの構文がエラー扱いされるようになる可能性が高い

①と③の理由は主にコードの保守性や開発効率の問題,②の理由はアプリケーションのパフォーマンスに関わる問題です。

まあ,内部結合の場合,古い構文を使っても大きな混乱に至ることはないかもしれませんが,動作の複雑な外部結合の場合はもっと重大です。

外部結合の場合

外部結合の構文は,昔は実装ごとに乱立していました。たとえばOracleの方言では,マスタではない側のテーブルに(+)を使います。先ほどのリスト4のクエリを書き換えるとリストbのようになります。

リストb 古い構文を利用した外部結合リスト4と比較)

SELECT E.emp_id, E.emp_name, E.dept_id, D.dept_name
  FROM Employees E, Departments D
 WHERE E.dept_id (+) = D.dept_id;

これは慣れないと相当に読みにくい構文ですし,方言はいつ削除されて使えなくなるかわからないという保守性の観点でのリスクがつきまといます。しかも,たちの悪いことに,ほかのDBMSでは外部結合のための別の方言を使う必要があって,たとえばSQL Serverでは「*=」という演算子を使うなど,まったく移植性に欠けます注a)⁠

“標準語”のススメ

一度覚えた構文を変えるのは生活習慣を変えるみたいなもので,矯正がたいへんですが,ここはひとつSQLでは一切方言は使わない!ぐらいの覚悟で臨んでください。

「細かいところでうるさいこと言うなあ」と思われるかもしれませんが(筆者も自分で小姑(こじゅうと)みたいだと思います)⁠ちょっとした注意であとあとの無用なトラブルを避けられるのですから,言わないわけにはいきません。

注a)

事実,Microsoftは少し昔の技術文書で「今後使えなくなる可能性があるから方言は使うな」という警告を発しています。

「SQL Server の今後のバージョンでは,"*=" や "=*" といった外部結合演算子のサポートを続行しない恐れがあるので,ANSI標準の結合句を使用することをお勧めします。」[SQL]ANSI 外部結合で外部結合するテーブルを制限する方法」⁠

著者プロフィール

ミック

SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQLアタマアカデミー」を連載中。

著書:『達人に学ぶ SQL徹底指南書』(翔泳社、2008)訳書:J.セルコ『SQLパズル 第2版』(翔泳社、2007)

SQLアタマアカデミー:サポートページ

コメント

コメントの記入