WHERE句で絞り込む
テーブルを結合した場合でも、WHERE句で抽出されるレコードを絞り込むことができます。
WHERE句の条件の書式は単一のテーブルの場合とまったく同じですが、絞り込みの条件式に使用するカラムはただ一つ特定できるように、必要があればテーブル名をつけて記述する必要があります。
例【registration_list】、【subject_id】、【teacher_list】、【student_list】を結合し、student_id=’1001’の行のみ抽出する場合
NATURAL JOIN句を使う場合
SQL
SELECT registration_list.subject_id, subject_name,
teacher_list.teacher_id, teacher_name, ex_number,
student_list.student_id, student_name
FROM registration_list
NATURAL JOIN subject_list
NATURAL JOIN student_list
NATURAL JOIN teacher_list
WHERE student_list.student_id='1001';WHERE句を使う場合
SQL
SELECT registration_list.subject_id, subject_name,
teacher_list.teacher_id, teacher_name, ex_number,
student_list.student_id, student_name
FROM registration_list,subject_list,student_list,teacher_list
WHERE registration_list.student_id=student_list.student_id AND
registration_list.subject_id=subject_list.subject_id AND
subject_list.teacher_id=teacher_list.teacher_id AND
student_list.student_id='1001';結果
練習
1.sales_detail、item_list を自然結合して、item_id=’S1’の情報のみ表示する
出力例：
| item_id | sales_id | item_count | item_name | price |
|---|---|---|---|---|
| S1 | 1 | 4 | AAA | 5000 |
| S1 | 3 | 7 | AAA | 5000 |
2.member_list、sales、sales_detail、item_listを自然結合して、member_id=’K3’の情報のみ表示する
| member_id | item_id | sales_id | item_count | item_name | price | member_name | member_address |
|---|---|---|---|---|---|---|---|
| K3 | S1 | 3 | 7 | AAA | 5000 | 佐藤 | 千葉県 |
| K3 | S4 | 3 | 2 | PPP | 1000 | 佐藤 | 千葉県 |
| K3 | S5 | 5 | 8 | QQQ | 1500 | 佐藤 | 千葉県 |
ORDER BY句で並べ替える
テーブルを結合した結果を ORDER BY句で並べ替えることができます。
ORDER BY句の書式は単一のテーブルの場合とまったく同じですが、並べ替えに使用するカラムはただ一つ特定できるように、必要があればテーブル名をつけて記述する必要があります。
例【registration_list】と【subject_list】と【student_list】と【teacher_list】の自然結合をstudent_idの昇順に整列する
NATURAL JOIN句を使用する場合
SQL
SELECT registration_list.subject_id, subject_name,
teacher_list.teacher_id, teacher_name, ex_number,
student_list.student_id, student_name
FROM registration_list
NATURAL JOIN subject_list
NATURAL JOIN student_list
NATURAL JOIN teacher_list
ORDER BY student_id;WHERE句を使用する場合
SQL
SELECT registration_list.subject_id, subject_name,
teacher_list.teacher_id, teacher_name, ex_number,
student_list.student_id, student_name
FROM registration_list,subject_list,student_list,teacher_list
WHERE registration_list.student_id=student_list.student_id AND
registration_list.subject_id=subject_list.subject_id AND
subject_list.teacher_id=teacher_list.teacher_id
ORDER BY student_id;結果
練習
member_list、sales、sales_detail、item_listを自然結合して、item_countの降順に表示する
結果例：
| item_id | sales_id | member_id | member_name | member_address | item_count | item_name | price |
|---|---|---|---|---|---|---|---|
| S2 | 6 | K5 | 鈴木 | 東京都 | 20 | BBB | 7000 |
| S2 | 2 | K2 | 吉田 | 埼玉県 | 10 | BBB | 7000 |
| S3 | 4 | K4 | 加藤 | 神奈川県 | 8 | CCC | 2000 |
| S5 | 5 | K3 | 佐藤 | 千葉県 | 8 | QQQ | 1500 |
| S1 | 3 | K3 | 佐藤 | 千葉県 | 7 | AAA | 5000 |
| S1 | 1 | K1 | 田中 | 東京都 | 4 | AAA | 5000 |
| S4 | 3 | K3 | 佐藤 | 千葉県 | 2 | PPP | 1000 |
結合したテーブルで計算する
結合したテーブル同士でも単一のテーブルの場合と同様、カラムの値を使って計算などの処理を行うことができます。
例【sales】、【sales_detail】、【item_list】、【member_list】を結合し、
item_list.price(単価) と sales_detail.item_count(個数) の積をsubtotal(小計)として表示
NATURAL JOIN句を使用する場合
SQL
SELECT sales.sales_id, sales.member_id, member_list.member_name,
sales_detail.item_id, item_list.item_name, item_list.price, sales_detail.item_count,
item_list.price * sales_detail.item_count AS subtotal
FROM sales
NATURAL JOIN sales_detail
NATURAL JOIN item_list
NATURAL JOIN member_list;WHERE句を使用する場合
SQL
SELECT sales.sales_id, sales.member_id, member_list.member_name,
sales_detail.item_id, item_list.item_name, item_list.price, sales_detail.item_count,
item_list.price * sales_detail.item_count AS subtotal
FROM sales, sales_detail, item_list, member_list
WHERE sales.sales_id=sales_detail.sales_id AND
sales.member_id=member_list.member_id AND
sales_detail.item_id=item_list.item_id;結果
グループ計算する
テーブルを結合した場合もGROUP BY句やグループ関数を利用できます。
例 各科目毎の履修人数を表示する（【registration_list】と【subject_list】を結合し、subject_id毎にグループ化して件数を数える）
NATURAL JOIN句を使用する場合
SQL
SELECT subject_list.subject_name, COUNT(*)
FROM registration_list
NATURAL JOIN subject_list
GROUP BY subject_list.subject_id;WHERE句を使用する場合
SQL
SELECT subject_list.subject_name, COUNT(*)
FROM registration_list, subject_list
WHERE registration_list.subject_id=subject_list.subject_id
GROUP BY subject_list.subject_id;実行結果
練習
sales、sales_detail、member_list、item_listのうち必要なテーブルを結合して、会員毎の売上金額合計を表示する
表示例
| member_id | member_name | goukei |
|---|---|---|
| K1 | 田中 | 20000 |
| K2 | 吉田 | 70000 |
| K3 | 佐藤 | 49000 |
| K4 | 加藤 | 16000 |
| K5 | 鈴木 | 140000 |
コメント