SQLite | 데이터 조인(Join) | 동일한 테이블을 이용한 자체 조인 (SELF JOIN)
내부 조인과 외부 조인은 보통 여러 테이블을 조인을 하는데, 자기 자신의 테이블으로도 조인을 할 수도 있다. 이러한 조인을 자체 조인(SELF JOIN)이라고 한다. 여기에서는 자체 조인을 하는 방법에 대해 설명한다.
자체 조인을 하는 방법
내부 조인과 외부 조인을 할 때에 자기 자신의 테이블과 조인하는 것을 자체 조인(SELF JOIN) 혹은 내부 조인이라고 한다. 자체 조인은 동일한 테이블을 사용하기 위해 각각 다른 이름으로 지정하여 SQL을 작성한다. 형식은 다음과 같다.
SELECT 별칭1.컬럼 명, ...
FROM 테이블명 별칭1 INNER JOIN 테이블명 별칭2
ON 별칭1.컬럼명 = 별칭2.컬럼명;
위와 같은 형식이 자체 결합이다. 다른 테이블을 조인하는 경우와 달리 FROM에 작성된 테이블과 JOIN 이후에 작성 테이블은 동일한 테이블이다. 단지, 동일 테이블이라면 조인 조건 및 조회하는 컬럼명이 겹치게 되므로, 각각 다른 이름으로 별칭을 붙여 구별할 수 있도록 한다. (별칭은 어느 하나의 테이블에만 붙여도 되는거 같다.)
–
간단한 예를 통해 어떻게 사용하는지에 대해 확인해 보자. 다음과 같이 테이블을 만든다.
create table employee (id integer, name text, boss_id integer);
sqlite> create table employee (id integer, name text, boss_id integer);
sqlite>
INSERT 문을 사용하여 테이블에 데이터를 추가한다.
insert into employee values (1, 'devkuma', 3);
insert into employee values (2, 'kimkc', 4);
insert into employee values (3, 'araikuma', 5);
insert into employee values (4, 'happykuma', 3);
insert into employee values (5, 'raccoon', 0);
sqlite> insert into employee values (1, 'devkuma', 3);
sqlite> insert into employee values (2, 'kimkc', 4);
sqlite> insert into employee values (3, 'araikuma', 5);
sqlite> insert into employee values (4, 'happykuma', 3);
sqlite> insert into employee values (5, 'raccoon', 0);
sqlite>
employee 테이블에 식별 번호를 나타내는 id 컬럼, 이름이 저장되는 name 컬럼 그리고 자신 상사의 식별 번호를 저장하는 boss_id 컬럼 이렇게 3개의 컬럼이 있다. 상사도 같은 employee 테이블에 저장되어 있으며, boss_id 컬럼의 값은 employee 테이블의 다른 데이터의 id 컬럼에 값이 저장되어 있다.
boss_id 값을 참조하고 있는 상사의 이름을 조인한 데이터를 얻고 싶다면, 조인을 하는 테이블도 동일한 employee 테이블이 된다. 이러한 경우에 자체 조인을 한다.
그럼 실제로 동일한 테이블을 외부 조인하여 데이터를 받아와 보자.
select staff.id, staff.name, boss.name from employee staff
left outer join employee boss
on staff.boss_id = boss.id;
sqlite> select staff.id, staff.name, boss.name from employee staff
...> left outer join employee boss
...> on staff.boss_id = boss.id;
id name name
---------- ---------- ----------
1 devkuma araikuma
2 kimkc happykuma
3 araikuma raccoon
4 happykuma araikuma
5 raccoon
sqlite>
동일한 테이블을 사용하여 외부 조인을 하였다. 여기서 조회하는 컬럼을 지정하거나 AS 절을 사용하여 컬럼 별칭을 붙일 수도 있다. (AS 절에 대해서는 조회한 데이터의 컬럼에 별칭 설정 (AS 절)를 참조하도록 한다.)
select staff.id, staff.name, boss.name as bossname
from employee staff
left outer join employee boss
on staff.boss_id = boss.id;
sqlite> select staff.id, staff.name, boss.name as bossname
...> from employee staff
...> left outer join employee boss
...> on staff.boss_id = boss.id;
id name bossname
---------- ---------- ----------
1 devkuma araikuma
2 kimkc happykuma
3 araikuma raccoon
4 happykuma araikuma
5 raccoon
sqlite>
여기서는 외부 조인의 경우만 시도했지만, 내부 조인의 경우에도 동일할 수 있다.