sql 셀프조인(self join) 쿼리 쓰는 이유와 간단한 예제

sql 쿼리를 짜다보면 다른 테이블을 병합해서 데이터간의 관계를 봐야하는 경우도 있지만 한 테이블 내에서 데이터간의 관계를 살펴봐야 하는 경우도 발생하게 된다. 즉 한 테이블에 존재하는 컬럼(column) 사이에 의미있는 관계가 존재할 때 셀프조인을 이용해서 효과적으로 보여줄 수 있게 된다. 

셀프조인 쿼리 작성방법

 

셀프조인에도 inner join, outer join, cross join 등 모든 조인쿼리(Join Query)가 적용 가능하다. 일반적인 조인 문과 다른점은 join하는 대상이 자기 자신이라는 점이다. 한가지 주의해야 할 점은 같은 테이블 이름이 2번 나오기 때문에 반드시 alias를 지정해줘야 한다는 점이다. 기본적인 셀프조인 쿼리 형태는 아래 예시와 같이 작성할 수 있다. 결과로는 중복되는 테이블을 병합한 테이블이 선택된다.

SELECT * FROM table t1
JOIN table t2
ON  t1.column1 = t2.column2

 

셀프조인 예시, 같은 값을 공유하는 instance가 있는지 보고싶을 때

아래 그림과 같은 테이블이 있다. 각 벤더의 정보가 하나의 instance이고 벤더가 소재하고 있는 city와 state를 알수 있다.

이때 자기 소재지에 경쟁하는 다른 벤더들이 존재하는 벤더들이 궁금하다면 어떻게 알아봐야 할까?

 

예시1

city와 state가 같고 name이 다른 조건으로 테이블을 셀프조인하면 같은 city와 state에 존재하는 다른 모든 벤더가 선택된다. 이때 한 지역에 벤더가 3개 이상일 경우 vendor_name이 중복되어 표시되기 때문에 DISTINCT를 이용해 중복 값을 제거해주면 아래 테이블과 같이 같은 city와 state에 경쟁사가 존재하는 벤더들이 선택된다.

SELECT DISTINCT v1.vendor_name, v1.vendor_city, v1.vendor_state
FROM vendors v1
JOIN vendors v2
ON v1.vendor_city = v2.vendor_city 
AND v1.vendor_state = v2.vendor_state
AND v1.vendor_name <> v2.vendor_name;

예시결과 1

 

셀프조인 예시, 같은 값을 공유하면서 관계가 있는 instance를 보고 싶을때

위 예시와 같은 테이블에서 American Expresss라는 벤더와 Office Depot이라는 벤더가 서로 경쟁하고 있는 City를 보려면 어떻게 해야할까? 위에서 셀프조인한 결과의 WHERE절에 조건을 추가해주면 쉽게 확인할 수 있다.

 

SELECT v1.vendor_city
FROM vendors v1
JOIN vendors v2
ON v1.vendor_city = v2.vendor_city 
AND v1.vendor_state = v2.vendor_state
AND v1.vendor_name <> v2.vendor_name
WHERE v1.vendor_name = 'American Express' AND v2.vendor_name='Office Depot';

 

예시결과 2

셀프조인 예시, instance간의 간접 연결을 보고싶을 때

아래 예시에서는 직접 연결되지 않았지만 간접적으로 연결된 instance를 확인한다는 점에서 중요하다. 서로 다른 지역의 벤더들간에 같은 경쟁사를 공유하는지 알아보고자 한다. 다른 두 지역에 존재하는 'Register of Copyrights'라는 벤더와 'pollstar'라는 벤더가 공통으로 경쟁사로 두고 있는 벤더를 알아보기 위해 셀프조인한 두 테이블을 다시 조인하고 조인의 조건으로 경쟁하는 벤더 이름이 같다는 조건을 걸었다. 여기서도 alias를 지정해주는 것을 잃어버리면 오류가 나니 주의

 

SELECT a.vendor_name FROM
(SELECT v1.vendor_city, v1.vendor_state, v2.vendor_name
FROM vendors v1
JOIN vendors v2
ON v1.vendor_city = v2.vendor_city 
AND v1.vendor_state = v2.vendor_state
AND v1.vendor_name <> v2.vendor_name
WHERE v1.vendor_name = 'Register of Copyrights') a
JOIN
(SELECT v1.vendor_city, v1.vendor_state, v1.vendor_name
FROM vendors v1
JOIN vendors v2
ON v1.vendor_city = v2.vendor_city 
AND v1.vendor_state = v2.vendor_state
AND v1.vendor_name <> v2.vendor_name
WHERE v2.vendor_name = 'pollstar') b
ON a.vendor_name = b.vendor_name;

 

셀프조인 정리

셀프조인은 컬럼간의 관계를 이용해서 데이터를 추출할때 매우 유용하게 쓰일 수 있다. 관계라는 것은 관념적인 개념이기 때문에 테이블의 컬럼의 값이 어떤 의미인지를 잘 추상화 한다면 셀프조인을 효율적으로 사용할 수 있다.

 

2021.07.20 - [Mysql query] - sql 서브쿼리 활용하기(쿼리 중첩해서 사용하는 방법)

sql 서브쿼리 활용하기(쿼리 중첩해서 사용하는 방법)