データベーススペシャリスト 平成29年 午後II 問1 設問2(2)
模範解答は
CREATE VIEW 会員 V2 AS
SELECT * FROM 会員 A INNER JOIN 従業員 B ON A.店舗コード = B.店舗コード AND B.従業員ID = CURRENT_USER
UNION
...
対して、私の答案は
CREATE VIEW 会員 V2 AS
SELECT * FROM 会員 A INNER JOIN (SELECT 店舗コード FROM 従業員 WHERE 従業員ID = CURRENT_USER) B ON A.店舗コード = B.店舗コード
UNION
...
長いので想定解ではないだろう、とは思いましたが、以下の疑問が湧いてきたので調べてみました。
- 非ユニークキーで INNER JOIN するとどうなるのか？
- INNER JOIN の使用例は主キーと比較するものが多く、元の表よりも小さくなる印象を持っていた。
- 両者の結果の違いは？
- 実行結果が同じなら、別解として認められるのでは。
なお、この検証には DB Fiddle MySQL 5.7 を使用します。
前提
設問は
... また、図 5 中の g ~ j に入れる適切な字句を答えよ。ここで， DB 接続に用いられたユーザ ID は CURRENT_USER で参照されるものとする。
問題文中の該当部分は
⑤ 図 5 の SQL 文で作成したビューでは，従業員が所属する店舗の会員情報だけにアクセスを限定する。...
つまり、 会員、従業員、店舗のテーブルがそれぞれ定義されており、 CURRENT_USER が所属する店舗の会員だけを参照するビューを作成せよ、という問題です。
準備
最低限のキーを設定したテーブルを用意します。
create table 会員(店舗コード integer, 店舗会員番号 integer, 氏名 text, primary key(店舗コード, 店舗会員番号));
create table 従業員(従業員ID integer primary key, 氏名 text, 店舗コード integer);
create table 店舗(店舗コード integer primary key, 店舗名 text);
検証データを生成します。
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 するとどうなるのか？
条件の一致する部分同士の直積が得られる。
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 構文
両者の結果の違いは？
模範解答
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 |
私の答案
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 と 氏名 の列は得られませんが、同じ行を抽出できています。
別解にはないものの、出題の意図からすれば誤りではないように思われます。