[MySQL] SELF JOIN 셀프 조인을 쓰는 이유

JOIN

1)

우리에게 다음과 같은 MONKEYS 테이블과 FRUITS 테이블이 주어졌습니다.

create table MONKEYS (
    id,
    tutor_id,
    favorite_fruit_id,
    type,
    monkey_nm,
    training_status
);

create table FRUITS (
    id,
    fruit_nm,
    origin,
    price
);

이로부터 원숭이의 이름과 원숭이가 가장 좋아하는 과일의 이름을 찾으려면 어떻게 해야 할까요?

여러 개의 테이블을 동시에 참조하려고 할 때, ON절에 주어진 조건으로 테이블을 결합할 수 있는 JOIN을 사용합니다.

m 테이블의 favorite_fruit_id와 f 테이블의 id가 일치하는 경우를 조건으로 걸어 JOIN 한 다음, 결합된 테이블로부터 검색을 하면 됩니다. 이렇게요.

SELECT m.monkey_nm, f.fruit_nm
FROM MONKEYS m JOIN FRUITS f
ON m.favorite_fruit_id = f.id

 

SELF JOIN

 

그런데 JOIN을 반드시 서로 다른 테이블을 결합할 때만 사용하는 것은 아닙니다.

관계형 데이터 베이스에서 관계란 테이블과 테이블 사이에 존재하는 것이 일반적이지만, 같은 테이블의 레코드 사이에서도 일정한 관련성이 존재할 수 있기 때문입니다. 이렇게 동일한 테이블을 조인하는 것을 셀프 조인이라고 합니다. 이때 양 테이블을 구분하기 위해서 alias 별칭을 반드시 지정해주어야 합니다.

설계의 관점에서는 이렇게 같은 테이블 내에 다수의 관련성이 존재하는 것은 바람직하지 않다고 볼 여지도 있습니다. 정규화를 통해 테이블을 분리해 주는 것이 필요한 경우도 있겠죠. 하지만 테이블을 무조건 쪼개는 것만이 정답은 아닐뿐더러, 이미 만들어져 있는 데이터베이스에서 원하는 결과를 얻기 위해서는 셀프 조인에 대해 이해할 필요가 있습니다.

2)

원숭이 마을에는 반드시 한 마리의 원숭이를 스승으로 삼아야 하는 전통적인 풍습이 있습니다. 

type이 제자인 모든 원숭이는 스승의 id를 tutor_id에 간직하고 살아갑니다.

훈련기간이 되면 제자들의 training_status가 진행 중으로 바뀌게 됩니다.

그런데 원숭이 마을의 개발자가 실수로 스승의 상태값을 바꾸는 것을 깜빡하고 말았습니다.

제자들은 온종일 모여서 스승을 기다렸지만 '진행 중' 상태가 아니었던 스승은 해가 질 때까지 결국 나타나지 않았습니다.

분노한 원숭이들은 개발자를 찾아 숲을 뒤지기 시작했습니다.

당신은 지금이라도 스승의 상태를 진행 중으로 바꿔 성난 원숭이들을 진정시켜야 합니다.

이럴 때 바로 셀프 조인을 사용하면 됩니다.

먼저 값을 수정하려면 UPDATE문을 써야겠죠?

MONKEYS를 셀프조인 하면서 별칭으로 각각 a, b를 줍니다.

JOIN 하는 조건은 a의 type이 제자일 것, 그리고 a의 tutor_id가 b의 id와 같을 것입니다.

만약 a의 training_status가 '진행 중'이라면 b의 training_status를 '진행 중'으로 바꿔줍니다.

UPDATE MONKEYS a JOIN MONKEYS b
ON a.type = ‘제자’ AND a.tutor_id = b.id
SET b.training_status = ‘진행중’
WHERE a.training_status = ‘진행중’

결과는 다음과 같이 확인할 수 있습니다.

SELECT a.monkey_nm, a.status, b.monkey_nm, b.status
FROM MONKEYS a JOIN MONKEYS b
ON a.type = ‘제자’ AND a.tutor_id = b.id

 

이렇게 동일한 테이블 내의 다른 레코드를 참조하는 필드가 존재하는 경우 셀프 조인을 써야 합니다.

또 각 레코드가 순서 등 테이블 내에서만 의미가 있는 필드를 가지고 있고, 이를 활용해야 할 경우에도 셀프 조인을 써야 합니다.