タイトルの通りです。
SQLでテーブル結合する際にありがちなミスの一つだと思うので、情報展開します。
----------------------
例えば、以下のようなテーブルがあるとします。
・社員マスタテーブル
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)
----------------------
もし、ここで誤って結合条件に社員コードを含めないと、以下のように実際に保有している資格数よりも多く出力されてしまいます。
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文で全カラム指定すればよくわかると思います。
社員マスタテーブル.社員コードと保有資格管理テーブル.社員コードが一致しないレコードについても結合してしまうため、意図せずに直積結合が起こり、実際に保有している資格数よりも多く出力されてしまいます。
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)
