Visual Basic データベース講座

Visual Basic 中学校 > データベース講座 >

第10回　テーブルの結合

2007.4.14

SQLの中で、複数のテーブルを結合してあたかも１つのテーブルであるかのように扱う基本的な手法を説明します。

１．複数テーブルの操作

データベースの中には複数のテーブルが存在するのが普通なので、データベースアプリケーションの作成においても、一度に複数のテーブルを相手にしなければならない場合が多いです。

Animals.mdbでも動物の名前や説明だけなら[T_動物マスタ]テーブルだけを見ればわかりますが、分類上の目名や綱名、それに生息地などを取得するには他のテーブルも見なければなりません。

今までも何かにつけて動物の属する「目」を例としてあげてきましたが、この点をもう少しじっくり考えてみましょう。

[T_動物マスタ]テーブルにはいくつかの列がありますが、その中に[目ID]というものがあります。たとえば、 動物ID27のオオアリクイは目IDが14になっています。[T_目マスタ]テーブルで、目ID14のレコードを調べると「異節目」になっています。ですから、オオアリクイは異節目であることがわかります。

この関係を図示すると次のようになります。

■画像１：目IDによる結合

このような図をよく「ダイアグラム」と呼びます。

さて、今度は目ID14の異節目のレコードを見てみると、綱IDが1になっています。[T_綱マスタ]テーブルを見ると、綱ID1は哺乳綱であることがわかります。さらにこのレコードの門IDは2です。[T_門マスタ]テーブルを見ると、綱ID2は脊椎動物門です。

■画像２：４つのテーブルを見て、オオアリクイの分類を取得する。

以上のことからオオアリクイの分類は脊椎動物門・哺乳綱・異節目であることがわかります。

このように複数のテーブルをまたいでいるデータをSQL文で取得することを考えます。話を単純にするためにT_動物マスタとT_目マスタの２つのテーブルで考えます。T_動物マスタだけでも目IDはわかりますが、目の名前を取得したいとしましょう。つまり動物名と目名を次の表のように並べて表示したいというケースです。

SQL文でこのような結果を取得するには次のように書きます。

このSQL文はSELECT句はいままでとほとんど同じで、結果として取得したい項目をカンマで区切って並べています。ただし、２つのテーブルを扱っているために単に列名を書くだけではなく「テーブル名.列名」という形式で項目を指定しています。

ポイントになるのはFROM句です。FROM句ではT_動物マスタとT_目マスタを結合するように指示をしています。それがT_動物マスタ LEFT JOIN T_目マスタの部分です。この２つのテーブルを合体させて１つの大きなテーブルとみなすのです。この操作を結合と呼びます。

結合するときには結合条件を指定する必要があります。今回はT_動物マスタの目IDとT_目マスタの目IDが等しいものを結合するのですからON T_動物マスタ.目ID = T_目マスタ.目IDと記述しています。

FROMの後ろはテーブルを結合しているので長くなっていますがすべてFROM句です。

FROM句の構造を模式化すると次のようになっています。

後で説明しますが、JOINの部分にはLEFT JOINの他にINNER JOINやFULL OUTER JOINなどがあり結合の方法が変わります。

テーブルの結合について、初心者の方がよく見落としてしまう点を先に指摘しておきます。テーブルの結合操作では本当は１つしかないレコードがあたかも複数あるかのようにふるまう場合があります。これを「分身の術」と呼びます。(「分身の術」と呼んでいるのはどうやら私だけらしいので他の人に話すときは注意してください。本当は特に呼び名はないようです。)

たとえば、T_目マスタには有尾目のレコードは目ID53の１つしないはずなのに、上記のSQL文を実行するとイモリにもサンショウウオにも「有尾目」と表示されます。これはONで指定している結合方法をレコードごとに判断しているので条件に合致するレコードがあれば、ほかのレコードと既に結合していても取得してくるようになっているからです。

SELECT句を*にして実行するとこのことがもっとよくわかります。

名前を取得するくらいなら分身してくれた方が便利なのでなんとも思いませんが、たとえば売上金額を集計するプログラムなのではこのことを考慮に入れないと実際にはありもしない金額を計算してしまったりするので要注意です。

２．さまざまな結合

２－１．まえおき

テーブルの結合で問題となるのは、結合相手のレコードが複数存在する場合とまったく存在しない場合です。

■画像３：結合の相手が複数いる場合は…？

■画像４：結合の相手がいない場合は…？

複数存在する場合は前に書いたように１つしかないレコードがあたかも複数あるかのように取得でき、これを私は「分身の術」と呼んでいるのでした。 上記の画像３の例でいくと、１つしかないはずの「オオアリクイ」が３つに分身します。

まったく存在しない場合の動作はJOIN句によって異なります。

そのテストには残念ながらわれわれの動物データベースAnimals.mdbでは難しいので、テスト用に小さくてシンプルな人物データベースを用意しました。

ダウンロードしてデータベースエクスプローラ(またはサーバーエクスプローラ)に登録してください。

データベースのダウンロード    WhosWho.lzh    9KB

このデータベースには歴史上の人物を登録した[T_人物マスタ]テーブルと、国の一覧である[T_国マスタ]テーブルの２つのテーブルがあります。

レコード数もそれぞれ8件と5件しかありません。以下はその全レコードです。

このデータベースのポイントを少し指摘しておきます。

• 国ID=3の国が登録されていない。
• 孔子とヌルハチは国ID=3なので、対応する国が存在しない。
• インドとロシアはT_国マスタには存在するが、対応する人物がT_人物マスタにいない。

これらの特徴を踏まえて、これから説明する５つの結合を見比べてみると結合の性質がよりはっきりするでしょう。対応する国がない孔子やヌルハチがどうなるのかなどが見所です。

２－２．LEFT JOIN - 左外部結合

LEFT JOINは左側のテーブルのすべてのレコードを含み、それに対応する右側のレコードを結合する操作です。この操作を左外部結合と呼びます。

左とか右とか言うのはFROM句の中でテーブルをテーブルを指定する順番のことです。この次の説明するRIGHT JOINの説明も参考にして、左と右の違いを理解するようにしてください。

LEFT JOINはLEFT OUTER JOINの省略形であって、ほとんどのデータベースエンジンではLEFT JOINと書くべきところをLEFT OUTER JOINと書くこともできます。しかし、多くの人はより短いLEFT JOINという書き方の方を好むようです。

まず、先程の例と同じようにLEFT JOINを使って２つのテーブルを結合させてみましょう。

このSQL文を実行すると次の結果が得られます。

結果の中に値が「NULL」なっているものがありますが、これは値が存在しないことを示しています。国ID=3のデータはT_国マスタには存在しないのでこのような結果になります。これを利用して存在しないデータを抽出するという技法もあります。

さて、上記結果の特徴をまとめてみると次の通りになります。

• すべての人物が結果に含まれている。
• 対応する国のがない人物でも結果に含まれている。
• 対応する人物がいない国は結果に含まれていない。

この結果から見ても、最初に説明したように、左側のテーブル(T_人物マスタ)のすべてのレコードが含まれており、それに対応する右側のテーブル(T_国マスタ)のレコードが結合されているこということがわかります。

２－３．RIGHT JOIN - 右外部結合

RIGHT JOINは右側のテーブルのすべてのレコードを含み、それに対応する左側のレコードを結合する操作です。この操作を右外部結合と呼びます。RIGHT JOINはRIGHT OUTER JOINの省略形です。

RIGHT JOINはLEFT JOINと左右の立場が変わっているだけで他は同じです。

ですので、LEFT JOINのところで紹介した例と同じ例をLEFTのところをRIGHTに変えて試して結果を比較してみましょう。

実行するSQL文は次の通りです。

結果は次の通りです。

この結果の特徴をまとめてみると次の通りです。

• すべての国が結果に含まれている。
• 対応する人物がいない国でも結果に含まれている。
• 対応する国がない人物は結果に含まれていない。

まさにLEFT JOINとRIGHT JOINが逆であることがわかるでしょう。

ところで、LEFT JOINとRIGHT JOINはテーブルを左側と右側に分けて考えますが、左と右の違いは単にFROM句の中で登場する位置だけですから、テーブルを書く順番を変えることで結果を変えることもできます。

先程のLEFT JOINの例をもう一度見てみます。

このSQL文では「LEFT JOIN」をはさんで左がT_人物マスタ、右がT_国マスタになっています。

このSQL文は、次のSQL文とまったく同じ意味です。

このSQL文では「RIGHT JOIN」をはさんで左がT_国マスタ、右がT_人物マスタになっています。

ただし、SELECT句を*にしている場合は、結果の列の順番だけは違いがあります。これは列の順番が自動的に決定されるためです。SELECT句で明示的に列の名前を指定している場合は、テーブルを書く順番によらず取得できる列の順番は同じです。

博士のワンポイントレッスン V太：う～ん。左とか右とかよくわからないなぁ。 FROM T_人物マスタ LEFT JOIN T_国マスタ と書く場合と、 FROM T_国マスタ LEFT JOIN T_人物マスタ と書く場合は違うということですよね？ 博士：その通りじゃ！ B子：LEFT JOINは左側に書いたテーブルからはすべてのレコードを取得するのよ。だから、どちらのテーブルを左に書くかはとても大事だわ。 じゃあ、 ON T_人物マスタ.国ID = T_国マスタ.国ID と書く場合と、 ON T_国マスタ.国ID = T_人物マスタ.国ID と書くのとでは、どう違いますか？ ON節の中の書く順番のことね。えっと、これは…。 なるほど。ON節の中では右も左も関係ないのじゃ。好きな順番で書いてよいぞ。LEFT JOINでもRIGHT JOINでもその他の場合でもON節の中は自由じゃ。

２－４．INNER JOIN - 内部結合

INNER JOINは両方のテーブルに存在するレコードだけを結合する操作です。そのため左と右の区別はありません。この操作を内部結合と呼びます。

このSQL文の実行結果は次の通りです。

この結果の特徴をまとめると次の通りです。

• 対応する国がある人物だけが結果に含まれている。
• 対応する人物がいる国だけが結果に含まれている。

INNER JOINについては以上なのですが、このことの意味をよく考えるとINNER JOINには他の結合にはない重要な性質があることがわかります。

他の結合ではFROM句で最初に指定しているテーブルのレコードはすべて取得できるのに、INNER JOINではそうとは限りません。つまり、INNER JOINにはFROM句で最初に指定しているテーブルのレコードに条件を付ける効果があるのです。

抽出条件という観点から考えると、INNER JOINの効果は「対応するレコードが存在するものだけを抽出する機能」とも言えます。

このようなレコードの抽出機能は全SQLの中で、WHERE句とINNER JOINだけにあります。WHERE句はもともとレコードに抽出条件を付けるのが目的なのでわかりやすいのですが、INNER JOINにこのような効果があることを初学者は見逃しがちです。

今後、SQL文を書いていく中で、抽出されるべきデータが抽出されていないと思ったら、WHERE句を見直すだけではなく、INNER JOINが適切に書かれているかも忘れずに見直すようにしてください。

２－５．FULL OUTER JOIN - 完全外部結合

FULL OUTER JOINは両方のテーブルに存在するすべてのレコードを取得し、対応するレコードがある場合にはそれらを結合する操作です。この操作を完全外部結合と呼びます。

つまり、左右両方のテーブルのすべてのレコードを取得し、その中で対応する者同士は結合します。

実はAccessではFULL OUTER JOINはサポートされていないため、このSQL文はエラーになります。仮にSQL ServerなどFULL OUTER JOINがサポートされているデータベースを使用して実行すると、結果は次の通りになります。

この結果の特徴をまとめると次の通りです。

• すべての人物が結果に含まれている。
• すべての国が結果に含まれている。
• 対応する人物と国は結び付けられている。

LEFT JOINとRIGHT JOINの結果を１つにくっつけてダブっているレコードをまとめたものと考えることができます。

Accessでの完全外部結合 FULL OUTER JOINがサポートされていないAccessでは、１つの操作で完全外部結合を実現することはできませんが、複数の操作を組み合わせることによって同じ結果を得ることができます。 次の例はLEFT JOINとRIGHT JOINとUNIONを組み合わせて完全外部結合を実現しています。これはAccessでも使用でき、FULL OUTER JOINと同じ結果になります。 SELECT * FROM T_人物マスタ LEFT JOIN T_国マスタ ON T_人物マスタ.国ID = T_国マスタ.国ID UNION SELECT * FROM T_人物マスタ RIGHT JOIN T_国マスタ ON T_人物マスタ.国ID = T_国マスタ.国ID UNIONについては後述します。

２－６．CROSS JOIN - デカルト結合

CROSS JOINは２つのテーブルのレコードのすべての組み合わせを結合する操作です。この操作はクロス結合やデカルト結合と呼ばれます。

この結合は「すべて」を結合するため結合条件の指定が無用なのでON節は記述しません。

すべての組み合わせを結合するため、結合の結果の件数は莫大になります。たとえば、片方のテーブルが１０件、他方のテーブルが２２件あったとすると、結果は２２０件になります。

CROSS JOINはサンプルデータ作成などの目的で膨大な数のレコードを簡単に生成したい時や、単純にレコードの数を２倍、３倍にしたいときなど特殊な用途で使用されるだけで、通常はあまり利用する機会はありません。

いくつかのデータベースでは、「CROSS JOIN」と書かずに次のようにFROM句にカンマで区切ってテーブルを記述することでデカルト結合が行われます。

Accessではどちらの形式も利用できます。

T_人物マスタには８件、T_国マスタには5件のレコードがありますので結果は４０件になります。結果が多いのでここでは表を２つに分けて折り返して結果を掲載します。

３．複数テーブルの結合

テーブルの結合は対象のテーブルが３つ以上でも行うことができます。この場合は、まず２つのテーブルを結合してから、その結果ともう１つのテーブルを結合するというように順々に結合していくことになります。

イメージとしては次のようなSQL文になります。

左に書いた結合から先に実行されていきますが、かっこをつけたりサブクエリを使用することによって結合の順序を変更することも可能です。

また、LEFT JOINやRIGHT JOIN, INNER JOINなど各種の結合を取り混ぜて書くことも可能です。

Accessではかならずかっこを付ける必要があり、かっこをつけないで３つ以上のテーブルの結合を指示するとエラーになってしまいます。ただし、VBのクエリデザイナ経由で実行するときはクエリデザイナが自動的に必要なかっことを付けてくれるのでとりあえず気にする必要はありません。

とは言え、実際にプログラムの中でこのようなSQL文を使用するときは自動的にかっこが付くなどということは一切ありませんので、注意してください。

次の例ではAnimals.mdbのT_動物マスタとT_目マスタとT_綱マスタを結合します。

Access用にかっこをつけて書くと次のようになります。

かっこになれるために、さらに複雑な結合例も紹介しましょう。上記の３つのテーブルに加えて、T_門マスタも結合すると次のようになります。

このくらいになってくると、SQL文も見にくくなってくるので、適当なところで改行を入れて見やすく書くのが一般的です。幸いSQL文は単語の途中でなければ好きな所に改行を入れることができます。

たとえば、上述のSQL文は次のように記述しても意味は同じです。

今度はSELECT句に*ではなく、列名を指定する例とテーブルに別名を付ける例を紹介します。

４つのテーブルのすべての列を表示するのではなく、門名と綱名と目名と動物名だけを表示することにしましょう。

次のようになります。

T_動物マスタの[名前]だけ、ASを使って「動物名」という別名にしています。このようにASを使って列に別名を付けることができるというのは前にも説明しました。今回は同じASを使ってテーブルに別名を付ける例も説明します。

このくらいのSQL文なら別名を付ける必要もありませんが、複雑になってくると重宝する場合もあります。

以下の例ではT_動物マスタに「MainTable」という別名を付けています。

なお、ほとんどのデータベースでは「AS」は省略可能ですが、可読性の向上を考えて「AS」を記述することをお勧めします。

博士のワンポイントレッスン B子：博士。テーブルの別名ってどんなところで便利なんですか？ 博士：たとえば、テーブルの名前が長い時は短い別名を付ける場合があるのぉ。 V太：えー。かえってわかりにくくなりそうだなぁ。僕は別名付けないでいいや。 確かに、あんまり別名をたくさんつけたら、SQL文をぱっと見ただけではどのテーブルのどの列だかわかりにくくなってしまうわね。無計画にやたらと別名を付けるのはやめた方がよさそうね。 それから、同じテーブル同士を結合する「自家結合」の場合は嫌でも別名を付ける必要があるぞ。 同じテーブル同士を結合？！なんですかそれ？…いや、いいです。聞きたくないです。

発展学習  -  高度な結合 発展学習では意欲的な方のために現段階では特に理解する必要はない項目を解説します。 本文では単一列同士の単純な結合だけを説明しました。実際には複数の列を指定して結合することもよくあります。その場合はON節の中でANDで条件をつないでいきます。 SELECT ... FROM tableA LEFT JOIN tableB ON tableA.column1 = tableB.column1 AND tableA.column2 = tableB.column2 　 このようにすると２つの列の値が一致するレコード同士だけを結合することになります。 　 さらに、結合条件には = 以外の演算子や、関数やリテラルを使った式を使用することもできます。 SELECT ... FROM tableA LEFT JOIN tableB ON tableA.column1 <= tableB.column1 このように書くと、tableA.column1よりtableB.column1の方が値が大きいレコードと結合します。 =以外の結合は、抽象性が高くわかりにくいです。初心者はあまり気にしない方がいいでしょう。

４．UNION

JOINによる結合は感覚的に言うとレコードを横につないでいくイメージでした。UNIONを使うと縦につなぐことができます。

UNIONを使用すると複数のテーブルを合体して、１つのテーブルであるかのように扱うことができます。この合体は複数のテーブルから取得した全レコードを、あたかも１つのテーブルから取得したかのようにまとめて取得できるということを意味します。

たとえば、ある企業で現在販売している商品の一覧を登録してある[T_商品マスタ]というテーブルがあったとします。そして、これとは別に今では販売していない商品を登録してある[T_過去商品マスタ]というテーブルがあったとします。

UNIONを使うとこの２つのテーブルを合体させて過去から現在まですべての商品が登録されている大きなテーブルのイメージを取得することができます。

使い方も簡単で２つのSELECT文の間に一言UNIONと入れるだけです。

もちろん２つのテーブルの構造がまったく同じである必要があります。列の名前は異なっていてもよいのですが、列の順番と型が一致している必要があります。

SELECT句に*を使用している場合はテーブルの定義自体で列の順番と型が一致している必要がありますが、SELECT句に型が一致するように列名を書いていけば、テーブルの定義自体が一致していなくても 合体することができます。

なお、UNIONを使った合体では重複する列は自動的に排除されます。つまり一方のテーブルにある列で、UNION相手の他方のテーブルにもまったく同じ列がある場合には、この列は１つだけが採用され、同じ列が２つ結果に含まれるということはありません。通常はこれで便利なのですが同じ列があろうとなかろうとすべての列を結果に含みたい場合にはUNIONの代わりにUNION ALLを使用します。

Animals.mdbでは[T_目マスタ]と[T_綱マスタ], [T_門マスタ]は列の順番と型が一致しているのでUNIONで 合体することができます。これらのテーブルを合体しても利点が思いつきませんが、UNIONの例として紹介しておきます。

SELECT句に項目を指定することで、T_動物マスタとT_目マスタをUNIONで 合体する例も紹介しておきます。上記の例と同様で利点は思いつきません。

この例でどのように列を一致させているか表にすると次の通りです。

この例で、注目すべきはT_動物マスタにはT_目マスタの「読み」に相当する項目がない点です。相当する項目がないからといって、UNION操作を諦める必要はありません。上記のように文字なら''などを補うことで対応する列(=読み)と型さえ一致させておけば大丈夫です。数値型の場合は0などを使用します。また、文字でも数値でもその他の型でもNULLを使って一致させることもできます。

次の例では、双方の先頭に数値型の列を追加してUNIONします。

同じテーブル同士を合体することもできます。

この例では動物マスタから目ID=2のレコードと目ID=5のレコードを抽出したものを合体させて１つの結果にまとめます。

ただ、このような目的でUNIONを使うのであればWHEREを使うべきでしょう。