SQL はデータ定義言語とデータ操作言語に分かれる。データ操作のなかで大きな存在を占めるのが select である。
select の基本は「どのテーブルから」、「どんな条件を満たす行を」、「どの列から取り出すか」だ。 それを書いてみよう。
select 作曲家, 交響曲数 from 交響曲情報 where 交響曲数 >= 9
ここでテーブル"交響曲情報"は次のようなものだとする。
作曲家 | 交響曲数 |
---|---|
モーツァルト | 41 |
ベートーヴェン | 9 |
メンデルスゾーン | 5 |
シューマン | 4 |
ブラームス | 4 |
マーラー | 9 |
ショスタコーヴィチ | 15 |
次の出力が得られる
作曲家 | 交響曲数 |
---|---|
モーツァルト | 41 |
ベートーヴェン | 9 |
マーラー | 9 |
ショスタコーヴィチ | 15 |
ここで、どの列から取り出すか、については複数列を指定できた。では、テーブルは複数指定できるのだろうか。行の条件は複数指定できるのだろうか。 そこを見ていこう。
こんどは、次のテーブル"ピアノ協奏曲情報"があるとしよう。
作曲家 | ピアノ協奏曲数 |
---|---|
モーツァルト | 27 |
ベートーヴェン | 5 |
メンデルスゾーン | 2 |
シューマン | 1 |
ブラームス | 2 |
ショスタコーヴィチ | 2 |
ここで、次のような SQL を書いたとしたら、どのような結果が得られるだろうか。
select 作曲家, 交響曲数, ピアノ協奏曲数 from 交響曲情報, ピアノ協奏曲情報 where 交響曲数 >= 9
素人だったら(という素人の私がこうなってくれればいいな、という願望をこめれば)こんな表が出るのではないかと想像できる。
作曲家 | 交響曲数 | 協奏曲数 |
---|---|---|
モーツァルト | 41 | 27 |
ベートーヴェン | 9 | 5 |
マーラー | 9 | |
ショスタコーヴィチ | 15 | 2 |
ここで都合により、次のような SQL を書いたとする。列はすべて表示するので * を使う。where を使った交響曲数による制限は求めない
sqlite> select * from 交響曲情報, ピアノ協奏曲情報
作曲家 | 交響曲数 | 作曲家 | ピアノ協奏曲数 |
---|---|---|---|
モーツァルト | 41 | モーツァルト | 27 |
モーツァルト | 41 | ベートーヴェン | 9 |
モーツァルト | 41 | メンデルスゾーン | 2 |
(中略) | |||
ショスタコーヴィチ | 15 | モーツァルト | 27 |
ショスタコーヴィチ | 15 | ベートーヴェン | 9 |
ショスタコーヴィチ | 15 | メンデルスゾーン | 2 |
ショスタコーヴィチ | 15 | シューマン | 1 |
ショスタコーヴィチ | 15 | ブラームス | 2 |
ショスタコーヴィチ | 15 | ショスタコーヴィチ | 2 |
なんか変だ。2つのテーブルの作曲家の組み合わせが表示されてしまっている。このような結合をクロス結合という。 同じ作曲家は同じ作曲家として結合してほしい。それにはどうするか。
実際にはどうか。
sqlite> select * from 交響曲情報 INNER JOIN ピアノ協奏曲情報 on 交響曲情報.作曲家 = ピアノ協奏曲情報.作曲家
作曲家 | 交響曲数 | 作曲家 | ピアノ協奏曲数 |
---|---|---|---|
モーツァルト | 41 | モーツァルト | 27 |
ベートーヴェン | 9 | ベートーヴェン | 9 |
メンデルスゾーン | 5 | メンデルスゾーン | 2 |
シューマン | 4 | シューマン | 2 |
ブラームス | 4 | ブラームス | 2 |
ショスタコーヴィチ | 15 | ショスタコーヴィチ | 2 |
マーラーの名前がない。これは、ピアノ協奏曲情報にマーラーの名前がないからだ。共通する情報だけを結合するのが INNER JOIN の仕様だ。 ではマーラーの名前を出すにはどうするか。
マーラーの名前を出すには、左外部結合と呼ばれる結合を用いる。SQL では LEFT OUTER JOIN を使う。LEFT JOIN でもよい。
sqlite> select * from 交響曲情報 LEFT OUTER JOIN ピアノ協奏曲情報 on 交響曲情報.作曲家 = ピアノ協奏曲情報.作曲家
作曲家 | 交響曲数 | 作曲家 | ピアノ協奏曲数 |
---|---|---|---|
モーツァルト | 41 | モーツァルト | 27 |
ベートーヴェン | 9 | ベートーヴェン | 9 |
メンデルスゾーン | 5 | メンデルスゾーン | 2 |
シューマン | 4 | シューマン | 2 |
ブラームス | 4 | ブラームス | 2 |
マーラー | 9 | ||
ショスタコーヴィチ | 15 | ショスタコーヴィチ | 2 |
こんどはマーラーの名前が来ている。
では、ピアノ協奏曲情報が次のとおりだったらどうだろうか。
作曲家 | ピアノ協奏曲数 |
---|---|
モーツァルト | 27 |
ベートーヴェン | 5 |
メンデルスゾーン | 2 |
シューマン | 1 |
ブラームス | 2 |
ラヴェル | 2 |
ショスタコーヴィチ | 2 |
ラヴェルは交響曲を書いていない。だから、INNER JOIN でも、LEFT OUTER JOIN でも表せない。この場合は右外部結合を表す RIGHT OUTER JOIN を使う。 ただ、RIGHT OUT JOIN では、こんどはマーラーが出てこない。 どちらも出てくるようにするには、全外部結合を表す FULL OUTER JOIN を用いる。
ここまでで、二つの表を結合した結果がどうなるかがわかったので、交響曲が 9 曲以上ある作曲家の氏名と情報を見てみよう。
sqlite> select 作曲家, 交響曲数, ピアノ協奏曲数 from 交響曲情報 LEFT OUTER JOIN ピアノ協奏曲情報 ON 交響曲情報.作曲家=ピアノ協奏曲情報.作曲家 where 交響曲数 >= 9 ; Error: ambiguous column name: 作曲家
「作曲家」があいまいだというのだ。コンピュータに言わせれば、テーブル"交響曲情報"の作曲家なのか、 テーブル"ピアノ協奏曲情報"の作曲家なのかが区別がつかない、ということなのだ。そこで区別をしないといけない。
sqlite> select 交響曲情報.作曲家, 交響曲数, ピアノ協奏曲数 from 交響曲情報 LEFT OUTER JOIN ピアノ協奏曲情報 ON 交響曲情報.作曲家=ピアノ協奏曲情報.作曲家 where 交響曲数 >= 9 ;
作曲家 | 交響曲数 | ピアノ協奏曲数 |
---|---|---|
モーツァルト | 41 | 27 |
ベートーヴェン | 9 | 5 |
マーラー | 9 | |
ショスタコーヴィチ | 15 | 2 |
上記の場合は比較演算子 >= を使った。ほかにも比較演算子は多くある。注意すべきは、NULL との比較では比較演算子は使えない。 NULL と同じ場合は IS NULL を、NULL ではない場合は IS NOT NULL である。
グループごとに集計をすることができる。別の表で考えよう。作曲家情報というテーブルを下に示す。なお、ジャンルの選択は半ば無作為、半ば恣意的である。
作曲家 | ジャンル | 曲数 |
---|---|---|
モーツァルト | 歌劇 | 21 |
モーツァルト | 交響曲 | 41 |
モーツァルト | ピアノ協奏曲 | 27 |
モーツァルト | クラリネット協奏曲 | 1 |
ベートーヴェン | 歌劇 | 1 |
ベートーヴェン | 交響曲 | 9 |
ベートーヴェン | ピアノ協奏曲 | 5 |
マーラー | 交響曲 | 9 |
ショスタコーヴィチ | 歌劇 | 4 |
ショスタコーヴィチ | 交響曲 | 15 |
ショスタコーヴィチ | ピアノ協奏曲 | 2 |
ショスタコーヴィチ | チェロ協奏曲 | 2 |
ショスタコーヴィチ | ピアノ五重奏曲 | 1 |
ではそれぞれの作曲家が何種類のジャンルで作品を作っているかを知るにはどうしたらよいか。
sqlite> select 作曲家, COUNT(*) FROM 作曲家情報 GROUP BY 作曲家
作曲家 | COUNT(*) |
---|---|
モーツァルト | 4 |
ベートーヴェン | 3 |
マーラー | 1 |
ショスタコーヴィチ | 5 |
まりんきょ学問所 > コンピュータの部屋 > システムの部屋 > データベース > SQL > select