외부 조인

OUTER JOIN

OUTER JOIN은 한쪽 테이블을 기준으로 설정하여 다른 테이블을 합치는 형태의 JOIN이다. 따라서 기준이 되는 테이블의 데이터는 모두 출력되며, 연결된 데이터가 없을 경우에는 null로 출력되는 특징을 가지게 된다.

샘플 데이터

OUTER JOIN을 살펴보기 위한 샘플 테이블과 데이터는 다음과 같다.

create table room(
room_no number primary key,
room_name varchar2(30) not null
);

create table reservation(
reservation_no number primary key,
person_name varchar2(30) not null,
start_date char(10) not null,
end_date char(10) not null,
room_no references room(room_no) on delete cascade
);

insert into room values(1, '스위트룸');
insert into room values(2, 'VIP룸');
insert into room values(3, '머시룸');
insert into room values(4, '골룸');

insert into reservation values(11, '세종대왕', '2020-12-01', '2020-12-03', 1);
insert into reservation values(12, '박혁거세', '2020-12-04', '2020-12-05', 1);
insert into reservation values(13, '백결선생', '2020-12-01', '2020-12-02', 2);
insert into reservation values(14, '의자왕', '2020-12-10', '2020-12-10', 1);
insert into reservation values(15, '김유신', '2020-12-04', '2020-12-07', 3);
insert into reservation values(16, '원효대사', '2020-12-09', '2020-12-10', 3);
insert into reservation values(17, '정몽주', '2020-12-01', '2020-12-02', 2);
insert into reservation values(18, '문익점', '2020-12-20', '2020-12-23', 1);
insert into reservation values(19, '강감찬', '2020-12-24', '2020-12-25', 1);
insert into reservation values(20, '대조영', '2020-12-15', '2020-12-16', 2);
commit;

table room

room_no	room_name
1	스위트룸
2	VIP룸
3	머시룸
4	골룸

table reservation

reservation_no	person_name	start_date	end_date	room_no
11	세종대왕	2020-12-01	2020-12-03	1
12	박혁거세	2020-12-04	2020-12-05	1
13	백결선생	2020-12-01	2020-12-02	2
14	의자왕	2020-12-10	2020-12-10	1
15	김유신	2020-12-04	2020-12-07	3
16	원효대사	2020-12-09	2020-12-10	3
17	정몽주	2020-12-01	2020-12-02	2
18	문익점	2020-12-20	2020-12-23	1
19	강감찬	2020-12-24	2020-12-25	1
20	대조영	2020-12-15	2020-12-16	2

기본적인 외부 조인

외부 조인(OUTER JOIN)은 기준이 있어야 하기 때문에 어느 테이블을 기준으로 하느냐에 따라서 구조가 달라진다.

• 방을 기준으로 예약 정보를 조회 : room ←─── reservation (예약이 없는 방이 존재하게됨)

• 예약 정보를 기준으로 방을 조회 : room ───→ reservation (사실상 EQUI JOIN과 같아짐)

select 
    room.room_no, room.room_name, 
    reservation.person_name, reservation.start_date, reservation.end_date
from room 
    left outer join reservation on room.room_no = reservation.room_no
order by room.room_no asc;

room_no	room_name	person_name	start_date	end_date
1	스위트룸	강감찬	2020-12-24	2020-12-25
1	스위트룸	박혁거세	2020-12-04	2020-12-05
1	스위트룸	의자왕	2020-12-10	2020-12-10
1	스위트룸	세종대왕	2020-12-01	2020-12-03
1	스위트룸	문익점	2020-12-20	2020-12-23
2	VIP룸	백결선생	2020-12-01	2020-12-02
2	VIP룸	대조영	2020-12-15	2020-12-16
2	VIP룸	정몽주	2020-12-01	2020-12-02
3	머시룸	김유신	2020-12-04	2020-12-07
3	머시룸	원효대사	2020-12-09	2020-12-10
4	골룸

만약 EQUI JOIN을 했다면 4번 room은 매칭되는 데이터가 존재하지 않기 때문에 나오지 않아야 하지만 OUTER JOIN에서는 출력됨을 확인할 수 있다. room이 기준이기 때문에 매칭되는 데이터가 없으면 비어있는 상태로 출력된다.

room left outer join reservation on room.room_no = reservation.room_no

OUTER JOIN은 기준이 있기 때문에 기준에 따라 방향이 정해지며, 지금은 room이 기준이고 왼쪽에 배치되어 LEFT OUTER JOIN으로 사용하였으나, 방향만 바꾸면 다음과 같이 표현도 가능하다.

reservation right outer join room on room.room_no = reservation.room_no

또한 각각의 테이블에 별칭을 부여하여 구문을 간소화 시킬 수도 있다.

select 
    rm.room_no, rm.room_name, 
    res.person_name, res.start_date, res.end_date
from room rm
    left outer join reservation res on rm.room_no = res.room_no
order by rm.room_no asc;

외부 조인과 그룹 함수

외부 조인을 그룹 함수와 사용하면 다음과 같은 데이터들을 조회할 수 있다.

• 각각의 방에 대한 예약 건수

• 각각의 방에 대한 가장 빠른 예약 일자 / 마지막 예약 일자

예약일자 알아보기

각각의 방에 대한 최초 입실 날짜와 최종 퇴실날짜를 알아보는 구문이다.

select 
    rm.room_no "방번호", rm.room_name "방이름", 
    min(rm.start_date) "최초입실",
    max(rm.end_date) "최종퇴실"
from room rm
    left outer join reservation res on rm.room_no = res.room_no
group by rm.room_no, rm.room_name
order by rm.room_no asc;

방번호	방이름	최초입실	최종퇴실
1	스위트룸	2020-12-01	2020-12-25
2	VIP룸	2020-12-01	2020-12-16
3	머시룸	2020-12-04	2020-12-10
4	골룸

예약건수 알아보기

예약 건수를 구할 때 주의할 점은 4번 room처럼 빈칸이 나와도 개수를 세기 때문에 미리 필터링을 한번 더 해야한다는 것이다.

select 
    rm.room_no "방번호", rm.room_name "방이름", nvl("예약건수", 0) "예약건수"
from
    room rm
        left outer join (
            select room_no, count(*) 예약건수 from reservation group by room_no
        ) res
        on rm.room_no = res.room_no
order by rm.room_no asc;

방번호	방이름	예약건수
1	스위트룸	5
2	VIP룸	3
3	머시룸	2
4	골룸	0

nvl 함수를 쓰지 않으면 4번이 null로 나오기 때문에 처리하기 위하여 nvl 함수를 사용하였다.