RDBMSでのSQLによる問い合わせにおいて、複数のテーブルを結合するパターンは比較的複雑な操作になります。
そのため、ミスも起こりやすいです。
今回は、注意喚起のため、私が実務で出くわしたミスを３つ挙げます。
１．外部結合の結合条件をwhere句に書いてしまい内部結合になってしまう
２．結合条件指定不足で意図せず直積結合してしまう
３．結合条件となる値のセットを誤り結合できなくなってしまう
１．外部結合の結合条件をwhere句に書いてしまい内部結合になってしまう
例えば、以下のようなテーブルがあるとします。
・ログイン日時管理テーブル
|
|
create table user_info ( user_id CHAR(4) NOT NULL, --ユーザID last_access_date CHAR(8) NOT NULL, --最終アクセス日 PRIMARY KEY (user_id) ); insert into user_info values("0001","20200301"); insert into user_info values("0002","20200301"); |
・ユーザ名履歴テーブル
|
|
create table user_name ( user_id CHAR(4) NOT NULL, --ユーザID start_date CHAR(8) NOT NULL, --有効開始日 end_date CHAR(8) NOT NULL, --有効終了日 user_name VARCHAR(10) NOT NULL, --ユーザ名 PRIMARY KEY (user_id,start_date) ); insert into user_name values("0001","20190101","20191231","hoge1"); insert into user_name values("0001","20200101","99991231","hoge2"); insert into user_name values("0002","20190101","20191231","piyo"); |
ここで、
・ユーザがログインした時のユーザ名を知りたい
・ただし、ユーザがログインした時、ユーザ名が存在しない場合がある
（この場合、ユーザ名が存在していない旨を知りたい）
という要件があった場合を考えます。
正しいselect文と問い合わせ結果は以下です。
|
|
select i.user_id, n.user_name from user_info i left join user_name n on (i.user_id = n.user_id) and (i.last_access_date ＞= n.start_date and i.last_access_date ＜= n.end_date); +---------+-----------+ | user_id | user_name | +---------+-----------+ | 0001 | hoge2 | | 0002 | NULL | +---------+-----------+ 2 rows in set (0.00 sec) |
もし、ここで、誤って結合条件の一部をwhere句に記述すると、下記のようになります。
左側外部結合なのに、左側のテーブルにしか存在しないレコードが抽出されません。
|
|
select i.user_id, n.user_name from user_info i left join user_name n on (i.user_id = n.user_id) where (i.last_access_date ＞= n.start_date and i.last_access_date ＜= n.end_date); +---------+-----------+ | user_id | user_name | +---------+-----------+ | 0001 | hoge2 | +---------+-----------+ 1 row in set (0.00 sec) |
誤ったSQL文については下記①②のような動きになるため、このような結果になります。
① on句に指定された条件で左側外部結合を行う
|
|
select i.*, n.* from user_info i left join user_name n on (i.user_id = n.user_id); +---------+------------------+---------+------------+----------+-----------+ | user_id | last_access_date | user_id | start_date | end_date | user_name | +---------+------------------+---------+------------+----------+-----------+ | 0001 | 20200301 | 0001 | 20190101 | 20191231 | hoge1 | | 0001 | 20200301 | 0001 | 20200101 | 99991231 | hoge2 | | 0002 | 20200301 | 0002 | 20190101 | 20191231 | piyo | +---------+------------------+---------+------------+----------+-----------+ 3 rows in set (0.00 sec) |
② ①の結果に対して、where句とselect句が評価される。
（where句で条件指定した場合、内部結合と同じようになる）
２．結合条件指定不足で意図せず直積結合してしまう
例えば、以下のようなテーブルがあるとします。
・社員マスタテーブル
|
|
create table employee_master ( company_id CHAR(3) NOT NULL, --会社コード employee_id CHAR(7) NOT NULL, --社員コード employee_name VARCHAR(20), --社員名 PRIMARY KEY (company_id,employee_id) ); insert into employee_master values("001","0000001","Employee_A"); insert into employee_master values("001","0000002","Employee_B"); insert into employee_master values("001","0000003","Employee_C"); insert into employee_master values("002","0000001","Employee_D"); |
・保有資格管理テーブル
|
|
create table owned_licence ( company_id CHAR(3) NOT NULL, --会社コード employee_id CHAR(7) NOT NULL, --社員コード licence_id CHAR(3) NOT NULL, --資格コード PRIMARY KEY (company_id,employee_id,licence_id) ); insert into owned_licence values("001","0000001","001"); insert into owned_licence values("001","0000002","001"); insert into owned_licence values("001","0000003","001"); insert into owned_licence values("001","0000003","002"); insert into owned_licence values("002","0000001","001"); |
ここで、会社コードが”001″の会社の全社員が保有している資格を取得したいとします。
正しいselect文と問い合わせ結果は以下です。
|
|
select ol.licence_id from employee_master em join owned_licence ol on em.company_id = ol.company_id and em.employee_id = ol.employee_id where em.company_id = "001"; +------------+ | licence_id | +------------+ | 001 | | 001 | | 001 | | 002 | +------------+ 4 rows in set (0.00 sec) |
もし、ここで誤って結合条件に社員コードを含めないと、以下のように実際に保有している資格数よりも多く出力されてしまいます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
select ol.licence_id from employee_master em join owned_licence ol on em.company_id = ol.company_id where em.company_id = "001"; +------------+ | licence_id | +------------+ | 001 | | 001 | | 001 | | 002 | | 001 | | 001 | | 001 | | 002 | | 001 | | 001 | | 001 | | 002 | +------------+ 12 rows in set (0.00 sec) |
なぜこうなるのかは、select文で全カラム指定すればよくわかると思います。
社員マスタテーブル.社員コードと保有資格管理テーブル.社員コードが一致しないレコードについても結合してしまうため、意図せずに直積結合が起こり、実際に保有している資格数よりも多く出力されてしまいます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
select * from employee_master em join owned_licence ol on em.company_id = ol.company_id where em.company_id = "001"; +----------+-----------+-------------+----------+-----------+----------+ |company_id|employee_id|employee_name|company_id|employee_id|licence_id| +----------+-----------+-------------+----------+-----------+----------+ |001 |0000001 |Employee_A |001 |0000001 |001 | |001 |0000001 |Employee_A |001 |0000002 |001 | |001 |0000001 |Employee_A |001 |0000003 |001 | |001 |0000001 |Employee_A |001 |0000003 |002 | |001 |0000002 |Employee_B |001 |0000001 |001 | |001 |0000002 |Employee_B |001 |0000002 |001 | |001 |0000002 |Employee_B |001 |0000003 |001 | |001 |0000002 |Employee_B |001 |0000003 |002 | |001 |0000003 |Employee_C |001 |0000001 |001 | |001 |0000003 |Employee_C |001 |0000002 |001 | |001 |0000003 |Employee_C |001 |0000003 |001 | |001 |0000003 |Employee_C |001 |0000003 |002 | +----------+-----------+-------------+----------+-----------+----------+ 12 rows in set (0.00 sec) |
３．結合条件となる値のセットを誤り結合できなくなってしまう
例えば、以下のようなテーブルが存在するとします。
・利用料金テーブル
|
|
create table usagefee ( usage_id CHAR(10) NOT NULL, --利用番号 use_date CHAR(8) NOT NULL, --利用日 shop_name CHAR(40) NOT NULL, --店名 fee DECIMAL(10) NOT NULL, --利用料金 PRIMARY KEY (usage_id) ); |
・利用料金明細テーブル
|
|
create table usagefee_detail ( usage_id CHAR(10) NOT NULL, --利用番号 detail_id CHAR(3) NOT NULL, --枝版 item_name CHAR(40) NOT NULL, --商品名 fee DECIMAL(10) NOT NULL, --利用料金 PRIMARY KEY (usage_id,detail_id) ); |
結合する時に指定するキーは利用番号になるため、以下のように利用番号には同一の値を使用する必要があります。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
insert into usagefee values("0000000001","20201201","Shop_A",10000); insert into usagefee_detail values("0000000001","001","Item_A",5000); insert into usagefee_detail values("0000000001","002","Item_B",5000); select * from usagefee p join usagefee_detail c on p.usage_id = c.usage_id; Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) +----------+--------+---------+-----+----------+---------+---------+----+ |usage_id |use_date|shop_name|fee |usage_id |detail_id|item_name|fee | +----------+--------+---------+-----+----------+---------+---------+----+ |0000000001|20201201|Shop_A |10000|0000000001|001 |Item_A |5000| |0000000001|20201201|Shop_A |10000|0000000001|002 |Item_B |5000| +----------+--------+---------+-----+----------+---------+---------+----+ 2 rows in set (0.00 sec) |
しかし、利用番号がプログラム中で生成する連番であり、かつプログラム中で利用料金テーブルと利用料金明細テーブルで別々に連番を付与していた場合、利用番号で結びつかなくなってしまいます。
|
|
insert into usagefee values("0000000001","20201201","Shop_A",10000); insert into usagefee_detail values("0000000002","001","Item_A",5000); insert into usagefee_detail values("0000000002","002","Item_B",5000); select * from usagefee p join usagefee_detail c on p.usage_id = c.usage_id; Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Empty set (0.00 sec) |
こうなると、利用料金テーブルと利用料金明細テーブルを結合するという使い方ができなくなり、後で困ることになります。
更に、既にサービスイン後であり過去データの補正が必要になった場合、その補正も困難になる場合があります。
いかがでしたでしょうか。
今回は失敗パターンの説明ということで、テーブル結合の失敗パターンを取り上げました。
失敗パターンは実務をこなす中で暗黙知的に知ることが多いと思いますので、文章に起こして共有できる形にしていきたいと思います。
