[SQL]非ユニークキーで INNER JOIN (DB H29 PM II)

[データベーススペシャリスト 平成29年 午後II](https://www.jitec.ipa.go.jp/1_04hanni_sukiru/mondai_kaitou_2017h29_1/2017h29h_db_pm2_qs.pdf) 問1 設問2(2) 

模範解答は

```sql
CREATE VIEW 会員 V2 AS
SELECT * FROM 会員 A INNER JOIN 従業員 B ON A.店舗コード = B.店舗コード AND B.従業員ID = CURRENT_USER
UNION
...
```
対して、私の答案は

```sql
CREATE VIEW 会員 V2 AS
SELECT * FROM 会員 A INNER JOIN (SELECT 店舗コード FROM 従業員 WHERE 従業員ID = CURRENT_USER) B ON A.店舗コード = B.店舗コード
UNION
...
```
長いので想定解ではないだろう、とは思いましたが、以下の疑問が湧いてきたので調べてみました。

1. 非ユニークキーで INNER JOIN するとどうなるのか？
  - INNER JOIN の使用例は主キーと比較するものが多く、元の表よりも小さくなる印象を持っていた。
2. 両者の結果の違いは？
  - 実行結果が同じなら、別解として認められるのでは。

なお、この検証には [DB Fiddle](https://www.db-fiddle.com/) MySQL 5.7 を使用します。

# 前提
設問は
> ... また、図 5 中の g ~ j に入れる適切な字句を答えよ。ここで， DB 接続に用いられたユーザ ID は CURRENT_USER で参照されるものとする。

問題文中の該当部分は
> ⑤ 図 5 の SQL 文で作成したビューでは，従業員が所属する店舗の会員情報だけにアクセスを限定する。...

つまり、 会員、従業員、店舗のテーブルがそれぞれ定義されており、 `CURRENT_USER` が所属する店舗の会員だけを参照するビューを作成せよ、という問題です。

# 準備
最低限のキーを設定したテーブルを用意します。

```sql
create table 会員(店舗コード integer, 店舗会員番号 integer, 氏名 text, primary key(店舗コード, 店舗会員番号));
create table 従業員(従業員ID integer primary key, 氏名 text, 店舗コード integer);
create table 店舗(店舗コード integer primary key, 店舗名 text);
```

検証データを生成します。

```sql
insert into 会員 values (1,1,'A'), (1,2,'B'), (1,3,'C'), (2,1,'D'), (2,2,'E');
insert into 従業員 values (1, 'X', 1), (2, 'Y', 2), (3, 'Z', 1);
insert into 店舗 values (1, 'TOKYO'), (2, 'OSAKA');
```
以下の表が得られます。

### 会員
| 店舗コード | 店舗会員番号 | 氏名  |
| ----- | ------ | --- |
| 1     | 1      | A   |
| 1     | 2      | B   |
| 1     | 3      | C   |
| 2     | 1      | D   |
| 2     | 2      | E   |

### 従業員
| 従業員ID | 氏名  | 店舗コード |
| ----- | --- | ----- |
| 1     | X   | 1     |
| 2     | Y   | 2     |
| 3     | Z   | 1     |
店舗コード 1 には 2 人の従業員を設定しています。

### 店舗
| 店舗コード | 店舗名   |
| ----- | ----- |
| 1     | TOKYO |
| 2     | OSAKA |

# 結果
## 非ユニークキーで INNER JOIN するとどうなるのか？
条件の一致する部分同士の直積が得られる。

```sql
select * from 会員 A inner join 従業員 B on A.店舗コード = B.店舗コード;
```

| 店舗コード | 店舗会員番号 | 氏名  | 従業員ID | 氏名  | 店舗コード |
| ----- | ------ | --- | ----- | --- | ----- |
| 1     | 1      | A   | 1     | X   | 1     |
| 1     | 1      | A   | 3     | Z   | 1     |
| 1     | 2      | B   | 1     | X   | 1     |
| 1     | 2      | B   | 3     | Z   | 1     |
| 1     | 3      | C   | 1     | X   | 1     |
| 1     | 3      | C   | 3     | Z   | 1     |
| 2     | 1      | D   | 2     | Y   | 2     |
| 2     | 2      | E   | 2     | Y   | 2     |

店舗コード 1 には X, Z の 2 人の従業員が所属しているので、会員テーブルのうち店舗コード 1 に一致する会員 A, B, C と、従業員 X, Z との直積が得られています。
なお、模範解答では、 ON 句の条件に `従業員ID = CURRENT_USER` を加えることで、この表から自身の `従業員ID` に合致する部分を抽出しています。

結局、内部結合は「直積の結果を ON 句の条件で抽出したもの」と理解しておけば良さそうです。

> MySQL では、JOIN、CROSS JOIN、および INNER JOIN は構文上同等です (互いに置き換えることができます)。標準 SQL では、それらは同等ではありません。INNER JOIN は ON 句とともに使用され、CROSS JOIN はそれ以外のときに使用されます。

[MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.9.2 JOIN 構文](https://dev.mysql.com/doc/refman/5.6/ja/join.html)

## 両者の結果の違いは？

模範解答

```sql
set @current_user = '1';
select * from 会員 A inner join 従業員 B on A.店舗コード = B.店舗コード and B.従業員ID = @current_user;
```

| 店舗コード | 店舗会員番号 | 氏名  | 従業員ID | 氏名  | 店舗コード |
| ----- | ------ | --- | ----- | --- | ----- |
| 1     | 1      | A   | 1     | X   | 1     |
| 1     | 2      | B   | 1     | X   | 1     |
| 1     | 3      | C   | 1     | X   | 1     |

私の答案

```sql
set @current_user = '1';
select * from 会員 A inner join (select 店舗コード from 従業員 where 従業員ID = @current_user) B on A.店舗コード = B.店舗コード;
```

| 店舗コード | 店舗会員番号 | 氏名  | 店舗コード |
| ----- | ------ | --- | ----- |
| 1     | 1      | A   | 1     |
| 1     | 2      | B   | 1     |
| 1     | 3      | C   | 1     |

従業員テーブルの `従業員ID` と `氏名` の列は得られませんが、同じ行を抽出できています。
別解にはないものの、出題の意図からすれば誤りではないように思われます。