等価結合の仲間たち
前回の勉強内容
勉強のきっかけになった問題
関係R，Sの等結合演算は，どの演算によって表すことができるか。
- 共通
- 差
- 直積と射影と差
- 直積と選択
出典 : 平成30年 春期 データベーススペシャリスト試験 午前Ⅱ 問9
等価結合は、同じ値のカラムで結合します。
2つのテーブルにおいて、結合に使うカラムが同じ値の場合に結合を行うのが「等価結合」です。
等価結合には、結合方法の違いでいくつか種類があります。
| 結合 | カラム指定 | 指定カラムの表示 |
|---|---|---|
| 等結合:ON | 必要 | まとめない |
| USING | 必要 | まとめる |
| 自然結合:NATURAL JOIN | 不要 | まとめる |
等結合:ON
等結合は、「=だけ」を使ってテーブルをくっつける方法です。
ON 結合は、すべてのテーブルのすべてのカラムを選択します。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.10.2 JOIN 句
結合に使われたカラムを2テーブル分2つ表示します。
"商品"表と"納品"表を商品番号で等結合した結果はどれか。
商品
商品番号 商品名 価格 S01 ボールペン 150 S02 消しゴム 80 S03 クリップ 200 納品
商品番号 顧客番号 納品数 S01 C01 10 S01 C02 30 S02 C02 20 S02 C03 40 S03 C03 60
ア. 商品番号 商品名 価格 顧客番号 納品数
----------------------------------------------
S01 ボールペン 150 C01 10
S02 消しゴム 80 C02 30
S03 クリップ 200 C03 20
イ. 商品番号 商品名 価格 商品番号 顧客番号 納品数
----------------------------------------------
S01 ボールペン 150 S01 C01 10
S02 消しゴム 80 S02 C02 30
S03 クリップ 200 S03 C03 20
ウ. 商品番号 商品名 価格 顧客番号 納品数
----------------------------------------------
S01 ボールペン 150 C01 10
S01 ボールペン 150 C02 30
S02 消しゴム 80 C02 20
S02 消しゴム 200 C03 40
S03 クリップ 200 C03 60
エ. 商品番号 商品名 価格 商品番号 顧客番号 納品数
----------------------------------------------
S01 ボールペン 150 S01 C01 10
S01 ボールペン 150 S01 C02 30
S02 消しゴム 80 S02 C02 20
S02 消しゴム 200 S02 C03 40
S03 クリップ 200 S03 C03 60
出典 : 平成27年 春期 データベーススペシャリスト試験 午前Ⅱ 問10
「"商品"表と"納品"表を商品番号で等結合」というわけで、「商品番号」を「=だけ」を使ってテーブルをくっつけています。
mysql> SELECT * FROM 商品 INNER JOIN 納品 ON 商品.商品番号=納品.商品番号; +--------------+-----------------+--------+--------------+--------------+-----------+ | 商品番号 | 商品名 | 価格 | 商品番号 | 顧客番号 | 納品数 | +--------------+-----------------+--------+--------------+--------------+-----------+ | S01 | ボールペン | 150 | S01 | C01 | 10 | | S01 | ボールペン | 150 | S01 | C02 | 30 | | S02 | 消しゴム | 80 | S02 | C02 | 20 | | S02 | 消しゴム | 80 | S02 | C03 | 40 | | S03 | クリップ | 200 | S03 | C03 | 60 | +--------------+-----------------+--------+--------------+--------------+-----------+ 5 rows in set (0.00 sec)
USING
ON句を使うと「ON table1.col=table2.col」とカラム名を2回指定しますが、
USING句を使うと「USING (col)」とカラム名の指定が1回ですみます。
ただし、指定できるのは「同じ名前のカラム」になります。
SELECT * の展開に対してどのカラムを表示するかの判定に関しては、この 2 つの結合は意味的に同一ではありません。 USING 結合が対応するカラムの合体した値を選択するのに対して、ON 結合は、すべてのテーブルのすべてのカラムを選択します。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.10.2 JOIN 句
結合に使われたカラムは冗長なので1つだけ表示します。
mysql> SELECT * FROM 商品 INNER JOIN 納品 USING (商品番号); +--------------+-----------------+--------+--------------+-----------+ | 商品番号 | 商品名 | 価格 | 顧客番号 | 納品数 | +--------------+-----------------+--------+--------------+-----------+ | S01 | ボールペン | 150 | C01 | 10 | | S01 | ボールペン | 150 | C02 | 30 | | S02 | 消しゴム | 80 | C02 | 20 | | S02 | 消しゴム | 80 | C03 | 40 | | S03 | クリップ | 200 | C03 | 60 | +--------------+-----------------+--------+--------------+-----------+ 5 rows in set (0.00 sec)
自然結合:NATURAL JOIN
自然結合は、結合条件に使うカラムを指定せず、「同じカラム名かつ同じ型」のカラムが自動で選ばれて結合される方法です。
NATURAL 結合の冗長カラムは表示されません。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.10.2 JOIN 句
結合に使われた「同じカラム名かつ同じ型」のカラムは、USING句と同じく1つだけ表示します。
関係“履修”と関係“担当”を自然結合した結果はどれか。
履修
学生 科目 山田太郎 情報処理 山田太郎 代数 加藤花子 情報処理 担当
科目 教官 情報処理 鈴木一郎 代数 斎藤正樹 ア.
学生 科目 教官 山田太郎 情報処理 鈴木一郎 山田太郎 代数 斎藤正樹 加藤花子 情報処理 鈴木一郎 イ.
履修.学生 履修.科目 担当.科目 担当.教官 山田太郎 情報処理 情報処理 鈴木一郎 山田太郎 代数 代数 斎藤正樹 加藤花子 情報処理 情報処理 鈴木一郎 ウ.
履修.学生 履修.科目 担当.科目 担当.教官 山田太郎 情報処理 代数 斎藤正樹 山田太郎 代数 情報処理 鈴木一郎 加藤花子 情報処理 代数 斎藤正樹 エ.
履修.学生 履修.科目 担当.科目 担当.教官 山田太郎 情報処理 情報処理 鈴木一郎 山田太郎 情報処理 代数 斎藤正樹 山田太郎 代数 情報処理 鈴木一郎 山田太郎 代数 代数 斎藤正樹 加藤花子 情報処理 情報処理 鈴木一郎 加藤花子 情報処理 代数 斎藤正樹 出典 : 平成22年 春期 データベーススペシャリスト試験午前Ⅱ 問13
mysql> select * from 履修 natural join 担当; +--------------+--------------+--------------+ | 科目 | 学生 | 教官 | +--------------+--------------+--------------+ | 情報処理 | 山田太郎 | 鈴木一郎 | | 代数 | 山田太郎 | 斎藤正樹 | | 情報処理 | 加藤花子 | 鈴木一郎 | +--------------+--------------+--------------+ 3 rows in set (0.00 sec)
演算
演算は、「+」「-」などは分かりやすいですが、日本語で書かれるととても分かりにくいです。
| 演算 | 意味 | SQLでは |
|---|---|---|
| 直積 | 全部の掛け合わせ | CROSS JOIN |
| 射影 | カラムを取り出す | SELECT句 |
| 選択 | レコードを取り出す | WHERE句/HAVING句 |
等結合演算は「直積」の結果を「選択」します。
「勉強のきっかけになった問題」を考えます。
「直積結合」の結果から・・・
mysql> select * from 履修 cross join 担当; +--------------+--------------+--------------+--------------+ | 学生 | 科目 | 科目 | 教官 | +--------------+--------------+--------------+--------------+ | 山田太郎 | 情報処理 | 代数 | 斎藤正樹 | | 山田太郎 | 情報処理 | 情報処理 | 鈴木一郎 | | 山田太郎 | 代数 | 代数 | 斎藤正樹 | | 山田太郎 | 代数 | 情報処理 | 鈴木一郎 | | 加藤花子 | 情報処理 | 代数 | 斎藤正樹 | | 加藤花子 | 情報処理 | 情報処理 | 鈴木一郎 | +--------------+--------------+--------------+--------------+ 6 rows in set (0.00 sec)
「選択」をすると・・・
mysql> select * from 履修 cross join 担当 where 履修.科目=担当.科目; +--------------+--------------+--------------+--------------+ | 学生 | 科目 | 科目 | 教官 | +--------------+--------------+--------------+--------------+ | 山田太郎 | 情報処理 | 情報処理 | 鈴木一郎 | | 山田太郎 | 代数 | 代数 | 斎藤正樹 | | 加藤花子 | 情報処理 | 情報処理 | 鈴木一郎 | +--------------+--------------+--------------+--------------+ 3 rows in set (0.01 sec)
「等結合」と同じになります。
mysql> select * from 履修 join 担当 on 履修.科目=担当.科目; +--------------+--------------+--------------+--------------+ | 学生 | 科目 | 科目 | 教官 | +--------------+--------------+--------------+--------------+ | 山田太郎 | 情報処理 | 情報処理 | 鈴木一郎 | | 山田太郎 | 代数 | 代数 | 斎藤正樹 | | 加藤花子 | 情報処理 | 情報処理 | 鈴木一郎 | +--------------+--------------+--------------+--------------+ 3 rows in set (0.00 sec)
