技術とか戦略とか

IT技術者が技術や戦略について書くブログです。

外部結合で結合条件をWHERE句に記述してしまうと片方のテーブルにしかないレコードが抽出されない

タイトルの通りですが、実務でちょっとうっかりしていたのでメモです。
 
----------------------
 
例えば、
・ユーザがログインした日時を管理するテーブルと、ユーザ名の履歴テーブルがある
・ユーザがログインした時、ユーザ名が存在しない場合がある
・ユーザがログインした時のユーザ名を知りたい(存在していない場合はその旨も)
という要件があった場合を考えます。
 
テーブルとテストデータは以下のようになります。
 
・ログイン日時管理テーブル
create table user_info
(
user_id CHAR(4) NOT NULL,
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,
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句で条件指定した場合、内部結合と同じようになる)