Chapter 2. JOIN을 이용한 데이터 조합 - (3) SELF JOIN

 

 

앞서서 INNER JOIN과 (LEFT/RIGHT) OUTER JOIN에 대해서 학습해 보았습니다. 다시 한번 내용을 되짚어보면, INNER JOIN의 경우 두 개 이상의 테이블에 공통되는 칼럼을 통해서 - 해당 테이블들의 데이터를 연결해 새로운 테이블을 또 하나 생성하는 경우였습니다. 이를테면 CUSTOMER 테이블의 PAYMENT ID를 확인하고, PAYMENT 테이블에 PAYMENT ID를 기반으로 결제 정보를 불러와서 고객 별 결제 정보를 리스트업 할 수 있는 것이죠.

 

LEFT/RIGHT OUTER JOIN의 경우, 두 개의 테이블을 매칭할 때 왼쪽 또는 오른쪽 테이블은 무조건 기준으로서 전부 출력하고, 주어진 조건에 매칭되는 데이터만 반대편에 보여주는 경우에 사용합니다. 만일 매칭되는 데이터가 없으면 NULL로 출력된다고 했습니다. 

 

자, 그럼 이번 아티클에서 다루는 SELF JOIN은 무엇일까요? 기본적으로 한 개의 테이블을 두 개의 인스턴스 개념으로 비교하여 새로운 데이터를 만들어내는 것입니다. 위의 INNER JOIN과 OUTER JOIN은 기본적으로 다른 테이블을 조합하는 경우를 의미했지만, SELF JOIN의 경우는 동일한 자신의 테이블을 하나 더 복사하여 비교합니다. 

 

특히 SELF JOIN이라고 해서 별도의 명령어를 사용하는 개념은 아니고, 위에서 언급한 INNER JOIN과 OUTER JOIN을 사용하되 - 언급했듯이 별개의 테이블이 아닌, 한 개의 테이블을 이용해 JOIN하여 비교하는 경우를 의미한다고 생각해 주시면 됩니다. 직관적으로 바로 이해가 가는 경우는 아닐 것입니다. 

 

일단 엑셀로 비교한다면, 하나의 표가 있는데 해당 표를 그대로 복사하여 하나 더 만들고 특정한 조건을 사용해 두 개의 표(테이블)을 조합하는 경우를 생각해 주시면 되겠습니다. 

 

 

 

---

 

 

아래와 같이 이번에는 [EMPLOYEE2]라는 새로운 테이블을 생성하여 실습을 진행해 보겠습니다.

 

create table employee2
(
	employee_id int primary key,
	first_name varchar (255) not null,
	last_name varchar (255) not null,
	manager_id int,
	foreign key (manager_id) references employee2 (employee_id) on delete cascade
);

insert into employee2 (
	employee_id,
	first_name,
	last_name,
	manager_id
)
values
(1, 'Windy', 'Hays', NULL),
(2, 'Ava', 'Christensen', 1),
(3, 'Hassan', 'Corner', 1),
(4, 'Anna', 'Reeves', 2),
(5, 'Sau', 'Norman', 2),
(6, 'Kelsie', 'Hays', 3),
(7, 'Tory', 'Goff', 3),
(8, 'Salley', 'Lester', 3);

 

위와 같이 테이블을 생성하고 나면 다음과 같이 테이블 내용을 확인할 수 있게 됩니다. 이제 이 테이블을 사용하여 SELF JOIN을 실습해 보도록 하겠습니다. 

 

 

 

---

 

 

우선 위의 테이블에 대해서 간단하게 개념을 정의하고 넘어가 보겠습니다. 일단, EMPLOYEE_ID는 우리가 흔히 얘기하는 '사번'을 뜻한다고 생각합시다. 더불어 MANAGER_ID는 '직속 상사의 사번'이라고 정의합니다. MANAGER_ID라는 칼럼이 별도로 있지만, 기본적으로는 EMPLOYEE_ID 즉 사번을 그대로 사용합니다. 예를 들어서 Ava Christensen과 Hassan Corner 두 사람은 모두 사번 1번인 Windy Hays가 직속 상사(팀장)입니다. 참고로 Windy Hays는 상사가 NULL입니다. 이는 곧 Windy Hays가 회사의 대표이사라 정도의 위치를 차지한다는 의미가 되겠죠. 4~8번까지의 직원들은 각자 상사가 존재하는데, 자연스럽게 조직도 형태가 그려지게 됩니다. 

 

우선 간단하게 아래와 같이 출력해 보겠습니다. 

 

select *
  from employee2 e
 inner join employee2 m 
    on m.employee_id = e.manager_id

 

 

우선 EMPLOYEE2 테이블(E)에 또 하나의 EMPLOYEE2 테이블(M)을 내부 조인으로 매칭합니다. 단, 조건의 경우 매칭되는 (M) 테이블의 EMPLOYEE_ID가 (E) 테이블의 MANAGER_ID와 같은 경우입니다. 우선은 전체 칼럼을 붙여서 출력했지만 결과적으로는 각각의 직원 별 상사가 누구인지가 하나씩 매칭되었습니다. 이제 조금 이해가 가시나요? 

 

여기서 테이블이 보기 좋지 않게 나왔으니 정리해 보겠습니다. 

 

select e.employee_id,
       e.first_name || ' ' || e.last_name as employee_name,
       m.first_name || ' ' || m.last_name as manager_name
  from employee2 e
 inner join employee2 m 
    on m.employee_id = e.manager_id

 

 

 

(E) 테이블의 직원 이름과 (M) 테이블의 상사 이름을 각각 출력했습니다. 여기서 from / inner join / on에서의 테이블 순서를 잘 기억하시기 바랍니다. 예를 들어 on에서 (M)과 (E)의 순서를 바꾸어 작성하면 테이블이 반대로 상사 먼저 출력됩니다. 

 

또한, INNER JOIN을 사용하였기 때문에 특성상 사번이 1번인 Windy Hays는 임직원 리스트에서는 아예 출력되지 않았습니다. 그는 MANAGER_ID가 NULL이기 때문이죠. 

 

 

 

---

 

 

이제 NULL 여부와 상관 없이, 모든 임직원 리스트가 출력되도록 쿼리를 작성해 보겠습니다. 눈치채셨겠지만, OUTER JOIN을 사용하겠습니다. 

 

select e.employee_id,
	   e.first_name || ' ' || e.last_name as employee_name,
	   m.first_name || ' ' || m.last_name as manager_name
  from employee2 e
  left outer join employee2 m
    on m.employee_id = e.manager_id
 order by manager_name;

 

위와 같이 INNER JOIN 항목을 LEFT OUTER JOIN으로 바꾸어줬습니다. 그리고 이번에는 정렬을 매니저 이름으로 적용해 보았습니다. 

 

 

 

위에서 말했듯이 이번에는 직속 상사가 NULL인 Windy Hays까지 포함해서 모든 데이터가 리스트로 출력되었습니다. INNER JOIN과 OUTER JOIN의 특성은 그대로 활용하되, 테이블 1개를 중복해서 비교하는 과정입니다. 

 

 

 

---

 

 

 

마지막으로 우리가 기존에 활용하던 DVDRENTAL의 DB를 이용해서 문제를 해결해 보겠습니다. 아래의 문제를 해결해 봅시다. 

 

Q. FILM 테이블에서 '다른 영화'이지만, '상영 시간'은 동일한 영화를 찾아서 두 편을 찾아 매칭해 주세요.
- 하나의 행에는 각각의 영화 제목과 상영 시간을 표시합니다.
- 하나의 영화가 두 편 이상의 영화와 상영시간이 겹칠 수 있습니다. 이 경우에는 각각 다른 행으로 중복해서 출력합니다.

 

일단 가볍게 전제조건을 생각해 보면, 출력해야 하는 칼럼은 TITLE / TITLE / LENGTH가 될 것입니다. 그리고 FILM이라는 테이블에 해당 내용들은 모두 들어가 있으므로, SELF JOIN을 통해 FILM 테이블을 매칭해야 할 것 같습니다. 

 

select f1.title ,
       f2.title ,
       f1.length
  from film f1
 inner join film f2
    on f1.film_id <> f2.film_id 
 where f1.length = f2.length;