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