(2012/16 更新)
次の処理を行うSQLの作成方法について解説する。
・複数のテーブルの結合： 各テーブルのレコードで特定のフィールドの値が同一のものを結合する（結合条件の指定）
・サブクエリー： 複数のSQLを組合わせて処理する
■準備
レポートフォルダから、第６回の資料をコピーしておく
質問： 表の結合はなぜ必要なのか？
ＳＱＬを利用した表の結合の必要性を確認する為に、表の分割と正規化について復習しておくこと。
確認ポイント：
・テーブルに、レコードの追加・修正・削除などの操作を行ったときに、必要なデータを削除したり、不要なデータを削除し残したりしない様に工夫したい |
正規化の手順と利点の例）
所属部活名簿を作成し、複数の学生の複数のクラブへの所属を記録可能なようにテーブルを正規化する。
正規化する前の、所属部活名簿
| 学籍番号 | 氏名 | 学年 | クラブ名１ | クラブ名２ |
| １１３ | 小橋 | １ | ○○同好会 | △△部 |
| １１２ | 長谷川 | １ | ○○同好会 | |
１０９ |
横田 | ２ | △△部 | |
| １０２ | 松原 | ２ |
第1正規形：
| 学籍番号 | 氏名 | 学年 | クラブ名 |
| １１３ | 小橋 | １ | ○○同好会 |
| １１２ | 長谷川 | １ | ○○同好会 |
| １１３ | 小橋 | １ | △△部 |
| １０９ | 横田 | ２ | △△部 |
| １０２ | 松原 | ２ |
このテーブルの場合、 ある学生が、同じクラブに2回入部することは無いので、
つまり、学籍番号とクラブ名の組み合わせで、同じデータが2度記録されることは無いので、
（学籍番号， クラブ名） が 連結キーで主キー となる。
第２正規形：
上記のテーブルで、主キーに完全に依存する項目は、主キー自身の学籍番号とクラブ名であり、
氏名や学年は、主キーに完全に従属せず、その一部、学籍番号だけに従属する。
そこで、氏名と、学年を別のテーブルに分割する。
ついでに、クラブ名も繰返し現れると想定できるので、クラブ名にIDを割り振って別の表に分割する。
すると、以下の様になる。
|
|
|
正規化前のテーブルの問題点の確認：
・レコードの修正に関する問題： 学籍番号１０１の氏名、学年を修正した場合、どんなミスが起こり得るか？
・レコードの削除に関する問題： 学籍番号１０１と１０２が△△部を退部した場合、何が起こり得るか？
・レコードの追加に関する問題： 新設の部活を登録するにはどうすればよいか？
・クラブ名の修正に関する問題： ○○同好会が○○部に名称変更した場合、どんなミスが起こり得るか？
（応用）
上記のテーブルを、さらに第3正規形に修正可能か検討する。修正可能の場合の条件について考える。
データベースで 正規化する前の上の例の部活名簿 のような、一般的に表示や印刷で利用する為の表を利用するには、正規化された表をＳＱＬで結合することで作成する。
つまり、画面や紙に出力するための表は、出力するその時だけに毎回SQLで作成しなおすことになる。
これにより、ある表の一部のレコードが修正されても、必要に応じて最新のデータを用いて表示することが出来る。
表を結合するためのＳＱＬの文法はデータベースごとにさまざまなバリエーションがある。ここではＭＳ−ＡＣＣＥＳＳの場合について説明する。
2つの表の直積（無条件で、レコードの全ての組み合わせる）を求める。
SELECT * FROM A, B;
※キーワード FROM の後ろに、複数のテーブル名を書くだけでよい。
■SQLの文法について
・複数の表から、同じフィールド名の列を区別して指定する場合：
表Aと表Bのどちらにも、同じ名前のフィールド”NAME”がある場合、これを区別してＳＱＬで利用するには、
テーブル名 A
|
テーブル名 B
|
SELECT A.NAME, B.NAME FROM A, B;
と、表名．フィールド名 のように表名とフィールド名の間にピリオドを用いる書式を利用する。
・表の名前の付け替え：
フィールドの名前をＡＳで付け替えたのと同様にテーブル名をＡＳで変更可能
SELECT AAAAAA.NAME,AAAAAA.CODE,BBBBBB.NAME,BBBBBB.NAME, FROM AAAAAA, BBBBBB;
を
SELECT A.NAME,A.CODE,B.NAME,B.NAME, FROM AAAAAA AS A, BBBBBB AS B;
※長い名前を繰り返しSQL中に記述すると、SQLが読みにくくなる。その場合、名前を略記すると便利。
2つの表を条件を指定して結合する。
条件には、
「表Ａと表Ｂの、フィールドＣが同じ値のもの」
等と指定する。
正規化済みで分割された表は、外部キーを結合の条件に利用することになる。
SELECT * FROM A,B
WHERE A.CODE = B.CODE;
もしくは、
SELECT * FROM A
INNER JOIN B ON A.CODE = B.CODE;
等結合（内部結合）の例）
|
Ａ
|
Ｂ
|
SELECT * FROM A
INNER JOIN B ON A.CODE = B.CODE;
| Ａ．CODE | Ａ．ＮＡＭＥ | Ｂ．CODE | Ｂ．ＮＡＭＥ |
| １ | 小橋 | １ | 小橋 |
| ３ | 本多 | ３ | 本多 |
表の結合で結合条件がA.CODE = B.CODEのとき、表Ａまたは表ＢのどちらかのフィールドCODEに対応する値が存在しない場合は、結合した側のフィールドの値は空欄（ＮＵＬＬ値）として結合する。
SELECT * FROM A
LEFT JOIN B ON A.CODE = B.CODE;
SELECT * FROM A
RIGHT JOIN B ON A.CODE = B.CODE;
左外部結合の例)
SELECT * FROM A
LEFT JOIN B ON A.CODE = B.CODE;
| Ａ．CODE | Ａ．ＮＡＭＥ | Ｂ．ＣODE | Ｂ．ＮＡＭＥ |
| １ | 小橋 | １ | 小橋 |
| ２ | 長谷川 | NULL | NULL |
| ３ | 本多 | ３ | 本多 |
ＳＱＬでは、複数のSQLを組み合わせて、１つのSQLの実行結果を連鎖的に処理することが出来ます。
「SELECT文とSELECT文を結合」
「SQLで作成したテーブルを元にして、さらに別のSQLで別のテーブルを作成」
など。
SELECT 文で、FROM や WHERE などの後ろの、テーブル名や値を書くことが出来る場所には、別のＳＥＬＥＣＴ文を書くことが出来ます。このＳＥＬＥＣＴ文をサブクエリーと呼びます。
Ａ
|
Ｂ
|
SELECT *
FROM A
WHERE A.P =
(SELECT P FROM B WHERE Q = 3);
重要： 赤い部分のＳＱＬは、括弧の外側のＳＱＬを実行する以前に先に実行されます。 この時、実行した結果の表が作成されます。 しかし、この予め作成された表は、括弧の外側のSQLが利用する為に一時的に作成されるだけで、結果が表示されることはありません。
上記のＳＱＬの処理は サブクエリ―を使わずに、表の結合と、条件指定でも実現できる。
SELECT *
FROM A INNER JOIN B ON A.P = B.P
WHERE B.Q = 3;
WHERE で比較する値について
WHERE A.P = (SELECT P FROM B WHERE Q = 3)
重要： 上記のSQLでは、WHEREで、「＝」を利用している為、サブクエリーの返す値は、一意に定まる必要がある。
例えば、テーブルＢが以下のようであった場合、赤の部分のＳＱＬだけを実行すると、結果は、 {1, 5}と複数の値になる。
そうなると、
A.P = (1, 5)
の様に、複数の値と比較することになる。 ＝演算子では、１つの値と複数の値と一度に比較することは出来ないのでこのままでは 実行時にエラーが起きる。
複数の値と比較をする場合には特別な書き方が必要である。（下記参照）
Ｂ
| Ｐ | Ｑ |
| 1 | 3 |
| 2 | 4 |
| 5 | 3 |
A
| NAME | B |
| 小橋 | 3 |
| 田近 | 4 |
| 小橋 | 5 |
SELECT NAME FROM A
WHERE B > (SELECT AVG(B) FROM A)
グループ化と集計関数を利用する場合との差を考えること。上記と以下のSQLの差は？
SELECT NAME,ＡＶＧ（B） FROM A
GROUP BY NAME
HAVING AVG(B) > 3
サブクエリーは以下のようにいくらでも入れ子に記述できる。（ただし、データベースシステム上の上限回数はある）
構造：
FROM で、テーブル名を書く代わりに、Select文を利用してよい（実行結果がテーブルとして扱われる）
ＳＥＬＥＣＴ ＦＲＯＭ （ＳＥＬＥＣＴ ＦＲＯＭ （ＳＥＬＥＣＴ ＦＲＯＭ …））
WHERE で、比較の対象となるところで、 Select文を利用してよい。
ＳＥＬＥＣＴ ＦＲＯＭ ＷＨＥＲＥ （ＳＥＬＥＣＴ ＦＲＯＭ ＷＨＥＲＥ （ＳＥＬＥＣＴ ＦＲＯＭ ＷＨＥＲＥ…））
ただし、Selectの実行結果で、値が1つになる場合と、そうでない場合で扱いが異なるので注意する（下記 ALL ANY IN 等の解説参照）。
プログラムで考えると、For文の2重ループの様な処理を行うクエリーである。
通常のサブクエリーでは、サブクエリー単体での実行が可能である。
例えば、
SELECT * FROM ORDERS AS O WHERE O.PARTNUM = (SELECT PARTNUM FROM PART WHERE DESCRIPTION = 'ROAD BIKE');
サブクエリー部分の結果
| PARTNUM |
| 76 |
よって、上記クエリーでは、PARTNUM が76であるレコードが選択される。
一方、相関サブクエリーのサブクエリー部分は、それ自体では実行不可能である。
SELECT * FROM ORDERS AS O WHERE 'ROAD BIKE' = (SELECT DESCRIPTION FROM PART AS P WHERE P.PARTNUM = O.PARTNUM);
上記のサブクエリー部分だけでは、ＦＲＯＭ句に表Ｏの指定が無いため、WHERE句でO.PARTNUMを参照できない。
そこで、相関サブクエリーでは、外側のＳＥＬＥＣＴ分の表Ｏから、レコードが一行づつ渡されながらクエリーが実行される。
つまり、外側のSelect文が、Forの2重ループの外側のForの役割を果たし、変数、o.partnum に値をセットしながら、内側のSelect文が実行されることになる。
上記のサブクエリー部分を
SELECT DESCRIPTION
FROM ORDERS AS O, PART AS P
WHERE P.PARTNUM = O.PARTNUM
と、ＯＲＤＥＲＳ表を参照しながら実行するように変更して実行すると以下の出力を得る。
| DESCRIPTION |
| ROAD BIKE |
| ROAD BIKE |
| MOUNTAIN BIKE |
| ROAD BIKE |
| TANDEM |
| MOUNTAIN BIKE |
| TANDEM |
| TANDEM |
| PEDALS |
| SEATS |
| TIRES |
| ROAD BIKE |
| ROAD BIKE |
| TANDEM |
相関サブクエリーのWHERE句では、このＯＲＤＥＲＳ表からの値が順に評価される。
よって、ＰＡＲＴＮＵＭでＯＲＤＥＲＳとＰＡＲＴを結合した場合に’ＲＯＡＤ ＢＩＫＥ’に等しいＤＥＳＣＲＩＰＴＩＯＮに持つ注文データがＯＲＤＥＲＳ表から順に出力される。
サブクエリーが複数の値を返す場合の条件を指定する。
例）
WHERE EXISTS (SELECT ...)
サブクエリーの実行結果が１レコードでも存在する場合、条件として真
WHERE A = ANY (SELECT ...)
サブクエリーの実行結果のフィールドの値とどれか1つでも一致する場合 真
これは、以下のＳＱＬも同様の結果である
WHERE A ＩＮ (SELECT ...)
WHERE A ＞ ANY (SELECT ...)
サブクエリーの実行結果のフィールドの値うちとどれか1つよりも大きな場合 真
これは、以下のＳＱＬの結果に似ている
WHERE A ＞ (...) OR A ＞ (...) OR A ＞ (...) OR ...
WHERE A ＞ ALL (SELECT ...)
サブクエリーの実行結果のフィールドの値の全てと比べてよりも大きな場合 真
これは、以下のＳＱＬの結果に似ている
WHERE A ＞ (...) AND A ＞ (...) AND A ＞ (...) AND ...
select orders.ordered, orders.name, part.partnum, part.price, part.description from orders, part where orders.partnum = part.partnum and orders.ordered >= #1998/09/01# and orders.ordered <= #1998/09/30# order by part.partnum; |
| ORDERED | NAME | PARTNUM | QUANTITY |
| 1998/09/02 | TRUE WHEEL | 10 | 1 |
注意：ＡｃｃｅｓｓのＳＱＬの文法に注意する。
テーブル名 → 名前に特殊文字が含まれる場合、[ ]で囲む。例） [CHECK#]
文字の結合 → ＆ または ＋ を使用する
例） FIRSTNAME & LASTNAME
列名の名前の付け替え → 列名 ＡＳ 別名 のＡＳは省略できない。 付け替えた名前には、元の名前は利用できない
ワイルドカード → ＬＩＫＥによるパターン検索で、％→＊ ＿→？ を使用する。
ファイル名を「第6回#学籍番号」として、レポートフォルダに提出する。
※ファイル名で、#、数字、番号は全て半角文字を使用すること。