学生向けプログラミング入門

学生向けにプログラミングを解説。Java、C++、Ruby、PHP、データベース、Ruby on Rails

HSQLDBを使用したデータベース入門その3 テーブルの結合(2020年5月更新)

<<前  [TOP]  次>>


これまでの検索は少数の例外を除いて、基本的には一つのテーブルを検索するものでした。
しかし二つ以上のテーブルを組み合わせなければ検索が出来ないことは決して珍しくありません。


例えばある本の出版社を知りたいとしましょう。
ところがテーブル「書籍台帳」には出版社のコードは載っていますが出版社の名前自体は載っていないということがあります。


出版社の名前を調べるためにはテーブル「出版社一覧」を用いて出版社コードから出版社名を調べなければなりません。
要するにある本の出版社名を調べるためには「書籍台帳」と「出版社一覧」という二つのテーブルが必要であり、二つのテーブルを見比べて双方の「出版社コード」の値が等しい行を選び出してその中から適当な項目を選んで新しいテーブルを作成するという操作が必要となります。


こうした操作をSQLでは「テーブルのジョイン」と呼びます。
「ジョイン」はテーブルからテーブルを作り出すという演算ですが、その元になっているのは複数のテーブルの積をつくり、その大きなテーブルから条件に合う行のみをselectするという操作です。
SQLでジョインを実現する為にはこうした操作を忠実に定式化すればいいのです。


まずSQLでのジョインの実例を見てみましょう。
次の例は「国語出版」という出版社が出版する本で500円未満のものを検索しています。


【例1:「国語出版」という出版社が出版する本で500円未満のものを検索(ジョイン)】

select substring(books.title, 1,10),books.publisher,  books2.price
 from books, books2 where publisher like '国語出版' and price < 500 and books.id=books2.id;



「select substring(books.title, 1,10),books.publisher,  books2.price  from books, books2 where publisher like &#x27;国語出版&#x27; and price &lt; 500 and books.id=books2.id;」
「select substring(books.title, 1,10),books.publisher, books2.price from books, books2 where publisher like '国語出版' and price < 500 and books.id=books2.id;」


substringというのは部分文字列を返す関数で、selectの項目名のリストの部分に置くことが出来ます。
この関数の三つの引き数は一番目がもとの文字列で、二番目、三番目の引き数がそれぞれ部分文字列が開始・終了される位置を表しています。
次の例を見れば、この関数の働きがわかると思います。


substring('abcdefghij',1,6) ===> 'abcdef'
substring('abcdefghij',1,5) ===> 'abcde'
substring('abcdefghij',1,4) ===> 'abcd'
substring('abcdefghij',2,6) ===> 'bcdef'
substring('abcdefghij',3,6) ===> 'cdef'
substring('abcdefghij',4,6) ===> 'def'


この例でのsubstringの使用は単に出力を一行に収めようとする為のもので、ジョインの操作とは何の関わりもありません。


ジョインとの関わりでより重要なことは、この例の中に現れている「books.publisher」とか「books2.price」といった項目名の指定の仕方です。


一般には「テーブル名.項目名」といった形式で用いられ、その項目がどのテーブル中の項目であるかを指定します。
例えば「books.publisher」とはテーブル「books」中の項目「publisher」という意味であり、「books2.price」というのはテーブル「books2」中の項目「price」という意味になります。
こうした項目名の指定の仕方を「項目名の修飾」と呼びます。


今までこの「項目名の修飾」という形式が現れてこなかったのは、selectで用いられるテーブルが一つしかなかったからです。


通常ジョインは二つのテーブルを両方に共通に含まれる項目の所でその項目の値が等しくなるように行を選んで張り合わせたものですから、項目名の修飾は必要です。


またもう一つ重要な事があります。
「books」テーブルのIDをと「books2」テーブルのIDをは共通のものになっています。
この二つのテーブルはそれぞれの本に一意的に付けられた「ID」というコードを共有しています。
この二つのテーブルのジョインはこの「ID」を通して行われることになりますので、二つのテーブルを結び付ける「books.id=books2.id」という記述が必要になります。


ジョインは二つのテーブルの結合に限られてはいません。
三つ以上のテーブルに対してもジョインという操作が考えられます。


今「keyword」テーブルの中のキーワードを含む著者の本で、「国語出版」という出版社が出版するで500円未満のものを検索するという問題を考えてみましょう。


【例2:著者がキーワードのどれかであり「国語出版」という出版社が出版するで500円未満のものを検索(ジョイン)】

select substring(books.title, 1,10),books.publisher,  books2.price, books.author
 from books, books2, keywords where publisher like '国語出版' and price < 500 and books.id=books2.id and books.author = keywords.keyword;



「select substring(books.title, 1,10),books.publisher,  books2.price, books.author  from books, books2, keywords where publisher like &#x27;国語出版&#x27; and price &lt; 500 and books.id=books2.id and books.author = keywords.keyword;」
「select substring(books.title, 1,10),books.publisher, books2.price, books.author from books, books2, keywords where publisher like '国語出版' and price < 500 and books.id=books2.id and books.author = keywords.keyword;」


あまり意味のない検索ですが「こういうことも出来ますよ」ということだけ覚えておいて下さい。


項目名のテーブル名での修飾はテーブルのジョインの際に多用されますが、SQLでは修飾を容易にするテーブル名への別名(エイリアス)付けと呼ばれる方法が準備されています。


次の例は先の例1をエイリアスを用いて書き換えたものです。


【例3:テーブル名のエイリアス(ジョイン)】

select substring(a.title, 1,10),a.publisher,  b.price from books a, books2 b where publisher like '国語出版' and price < 500 and a.id=b.id;



「select substring(a.title, 1,10),a.publisher,  b.price from books a, books2 b where publisher like &#x27;国語出版&#x27; and price &lt; 500 and a.id=b.id;」
「select substring(a.title, 1,10),a.publisher, b.price from books a, books2 b where publisher like '国語出版' and price < 500 and a.id=b.id;」


ここでfrom句に注目してください。
以前にselect文の項目リストに見出しを与える時に用いたのと同じ構文が見られます。
一般にfrom句にはテーブル名がカンマ(,)で区切って置かれるのですが、テーブルに別名を与える時にはテーブル名の後ろにスペースで区切って別名を置けばよいです。


テーブル名のエイリアスの機能を用いると一つのテーブルをあたかも二つのテーブルがあるかのように扱う検索が可能となります。
次の例は「books」テーブルから同じIDをもつ著者名を選び出しています。


【例4:同じIDの著者を検索(自己とのジョイン)】

select first.id, first.title, second.author from books first, books second where first.id=second.id;



「select first.id, first.title, second.author from books first, books second where first.id=second.id;」
「select first.id, first.title, second.author from books first, books second where first.id=second.id;」


自己とのジョインも出来るということを覚えておいて下さい。


<<前  [TOP]  次>>