Oracle上で外部結合でテーブル結合する際に条件指定を追加してみた

Oracle上でテーブルA, テーブルBを結合する際に、外部結合を利用すると、テーブルBにデータが含まれていない場合も、テーブルAのデータを取得することができる。

外部結合については、以下のサイトを参照のこと。
https://www.shift-the-oracle.com/sql/left-right-outer-join.html

ただし、テーブルAとテーブルBを外部結合する際のテーブルBの抽出条件の指定位置を、left (outer) join句でなくwhere句にしてしまうと、外部結合を利用しているにも関わらず、テーブルAのデータが絞られて取得される事象が発生してしまう。

今回は、テーブルA, テーブルBを結合する際に、テーブルBの条件指定を追加した上で外部結合を利用してみたので、その結果を共有する。

前提条件

Oracle XEのインストールが完了していること。

外部結合で利用するテーブルデータの作成

外部結合で利用するテーブルデータとして、テーブルA(employee)とテーブルB(dept)を作成する。その手順は、以下の通り。

1) テーブルB(dept)を作成する。

create table dept (
    dept_id varchar2(3) primary key not null
   , name varchar2(40) not null
   , start_dt date default sysdate not null
   , end_dt date default sysdate not null
   , del_flg char(1) default '0' 
)

create table dept (

dept_id varchar2(3) primary key not null

, name varchar2(40) not null

, start_dt date default sysdate not null

, end_dt date default sysdate not null

, del_flg char(1) default '0'

)

2) テーブルA(employee)を作成する。

create table employee (
   emp_id integer primary key not null
  , name varchar2(40) not null
  , dept_id varchar2(3)
)

create table employee (

emp_id integer primary key not null

, name varchar2(40) not null

, dept_id varchar2(3)

)

3) テーブルB(dept)に検証用データを追加する。

insert into dept values ('001', 'テスト部署'
    , to_date('2021/11/01', 'yyyy/MM/dd'), to_date('2099/12/31', 'yyyy/MM/dd'), '0') ;
insert into dept values ('002', 'テスト部署(期限切れ)'
    , to_date('2021/01/01', 'yyyy/MM/dd'), to_date('2021/10/31', 'yyyy/MM/dd'), '0') ;
insert into dept values ('003', 'テスト部署(無効)'
    , to_date('2021/11/01', 'yyyy/MM/dd'), to_date('2099/12/31', 'yyyy/MM/dd'), '1') ;
insert into dept values ('004', 'テスト部署(削除フラグ無し)'
    , to_date('2021/11/01', 'yyyy/MM/dd'), to_date('2099/12/31', 'yyyy/MM/dd'), null) ;
commit;

insert into dept values ('001', 'テスト部署'

, to_date('2021/11/01', 'yyyy/MM/dd'), to_date('2099/12/31', 'yyyy/MM/dd'), '0') ;

insert into dept values ('002', 'テスト部署(期限切れ)'

, to_date('2021/01/01', 'yyyy/MM/dd'), to_date('2021/10/31', 'yyyy/MM/dd'), '0') ;

insert into dept values ('003', 'テスト部署(無効)'

, to_date('2021/11/01', 'yyyy/MM/dd'), to_date('2099/12/31', 'yyyy/MM/dd'), '1') ;

insert into dept values ('004', 'テスト部署(削除フラグ無し)'

, to_date('2021/11/01', 'yyyy/MM/dd'), to_date('2099/12/31', 'yyyy/MM/dd'), null) ;

commit;

4) テーブルB(dept)のデータを全件取得した結果は、以下の通り。

select * from dept

1	select * from dept

5) テーブルA(employee)に検証用データを追加する。

insert into employee values (10001, 'テスト　プリン１', '001');
insert into employee values (10002, 'テスト　プリン２', '002');
insert into employee values (10003, 'テスト　プリン３', '003');
insert into employee values (10004, 'テスト　プリン４', '004');
insert into employee values (10005, 'テスト　プリン５', '005');
insert into employee values (10006, 'テスト　プリン６', null);
commit;

insert into employee values (10001, 'テスト　プリン１', '001');

insert into employee values (10002, 'テスト　プリン２', '002');

insert into employee values (10003, 'テスト　プリン３', '003');

insert into employee values (10004, 'テスト　プリン４', '004');

insert into employee values (10005, 'テスト　プリン５', '005');

insert into employee values (10006, 'テスト　プリン６', null);

commit;

6) テーブルA(employee)のデータを全件取得した結果は、以下の通り。

select * from employee

1	select * from employee

なお、上記手順では、テーブルB(dept)⇒テーブルA(employee)の順に作成しているが、逆の順番でもよい。

サラリーマン型フリーランスSEという働き方でお金の不安を解消しよう先日、「サラリーマン型フリーランスSE」という働き方を紹介するYouTube動画を視聴しましたので、その内容をご紹介します。「サ...

外部結合の実践

先ほど作成したデータを利用して、外部結合によるデータ抽出を行った結果は、以下の通り。

1) テーブルA(employee)とテーブルB(dept)を、条件指定せず外部結合してデータを取得した結果は以下の通りで、テーブルAの全データが取得されることが確認できる。

select emp.emp_id, emp.name as emp_name, emp.dept_id
    , dept.name as dept_name, dept.start_dt, dept.end_dt, dept.del_flg
from employee emp
left join dept on dept.dept_id = emp.dept_id
order by emp.emp_id

select emp.emp_id, emp.name as emp_name, emp.dept_id

, dept.name as dept_name, dept.start_dt, dept.end_dt, dept.del_flg

from employee emp

left join dept on dept.dept_id = emp.dept_id

order by emp.emp_id

2) テーブルB(dept)が有効期限内で削除フラグ≠’1’であるデータのみ、テーブルBのデータを表示しつつ、テーブルA(employee)の全件を取得できるようにした結果は、以下の通り。

select emp.emp_id, emp.name as emp_name, emp.dept_id
    , dept.name as dept_name, dept.start_dt, dept.end_dt, dept.del_flg
from employee emp
left join (
    select d.dept_id, d.name, d.start_dt, d.end_dt, d.del_flg
    from dept d
    where sysdate between d.start_dt and d.end_dt
    and ( d.del_flg = '0' or d.del_flg is null )
) dept on dept.dept_id = emp.dept_id
order by emp.emp_id

select emp.emp_id, emp.name as emp_name, emp.dept_id

, dept.name as dept_name, dept.start_dt, dept.end_dt, dept.del_flg

from employee emp

left join (

select d.dept_id, d.name, d.start_dt, d.end_dt, d.del_flg

from dept d

where sysdate between d.start_dt and d.end_dt

and ( d.del_flg = '0' or d.del_flg is null )

) dept on dept.dept_id = emp.dept_id

order by emp.emp_id

上記のように、有効期限内で削除フラグ≠’1’であるデータを抽出するという条件を、left join句に指定することで、テーブルA(employee)の全件が取得できることが確認できる。

3) 2)のように外部結合でテーブルA(employee)とテーブルB(dept)を結合しつつ、「テーブルB(dept)が削除フラグ≠’1’」という条件のみをwhere句に移動した結果は、以下の通り。

select emp.emp_id, emp.name as emp_name, emp.dept_id, dept.name as dept_name
    , dept.start_dt, dept.end_dt, dept.del_flg
from employee emp
left join (
    select d.dept_id, d.name, d.start_dt, d.end_dt, d.del_flg
    from dept d
    where sysdate between d.start_dt and d.end_dt
) dept on dept.dept_id = emp.dept_id
where ( dept.del_flg = '0' or dept.del_flg is null )
order by emp.emp_id

select emp.emp_id, emp.name as emp_name, emp.dept_id, dept.name as dept_name

, dept.start_dt, dept.end_dt, dept.del_flg

from employee emp

left join (

select d.dept_id, d.name, d.start_dt, d.end_dt, d.del_flg

from dept d

where sysdate between d.start_dt and d.end_dt

) dept on dept.dept_id = emp.dept_id

where ( dept.del_flg = '0' or dept.del_flg is null )

order by emp.emp_id

上記のように、テーブルA(employee)のデータのうち、削除フラグ=’1’であるdept_idをもつデータ(emp_id=10003)が、取得されないことが確認できる。

4) 2)のように外部結合でテーブルA(employee)とテーブルB(dept)を結合しつつ、「テーブルB(dept)が有効期限内」という条件のみをwhere句に移動した結果は、以下の通り。

select emp.emp_id, emp.name as emp_name, emp.dept_id, dept.name as dept_name
    , dept.start_dt, dept.end_dt, dept.del_flg
from employee emp
left join (
    select d.dept_id, d.name, d.start_dt, d.end_dt, d.del_flg
    from dept d
    where ( d.del_flg = '0' or d.del_flg is null )
) dept on dept.dept_id = emp.dept_id
where sysdate between dept.start_dt and dept.end_dt
order by emp.emp_id

select emp.emp_id, emp.name as emp_name, emp.dept_id, dept.name as dept_name

, dept.start_dt, dept.end_dt, dept.del_flg

from employee emp

left join (

select d.dept_id, d.name, d.start_dt, d.end_dt, d.del_flg

from dept d

where ( d.del_flg = '0' or d.del_flg is null )

) dept on dept.dept_id = emp.dept_id

where sysdate between dept.start_dt and dept.end_dt

order by emp.emp_id

上記のように、テーブルA(employee)のデータのうち、dept_idがテーブルB(dept)が有効期限内でないものや、対応するdept_idがleft join句で抽出した中に含まれないものは、取得されないことが確認できる。

5) 2)のように外部結合でテーブルA(employee)とテーブルB(dept)を結合しつつ、「有効期限内で削除フラグ≠’1’であるデータを抽出するという条件」という条件を全てwhere句に移動した結果は、以下の通り。

select emp.emp_id, emp.name as emp_name, emp.dept_id, dept.name as dept_name
    , dept.start_dt, dept.end_dt, dept.del_flg
from employee emp
left join dept on dept.dept_id = emp.dept_id
where sysdate between dept.start_dt and dept.end_dt
and ( dept.del_flg = '0' or dept.del_flg is null )
order by emp.emp_id