MySQL에서 CROSS JOIN, SELF JOIN 활용하기

게시일 : 2018년 04월 15일    
# MySQL # CROSS JOIN # SELF JOIN

MySQL에서 CROSS JOIN과 SELF JOIN에 대해 알아본다.

예제로 활용할 cities 테이블

id city latitude longitude
1 서울 37.5407 126.957
2 광주 35.126 126.831
3 대구 35.7988 128.583

CROSS JOIN

모든 경우의 수, 즉 A 테이블 row 개수 X B 테이블 row 개수 만큼의 row를 가진 테이블이 출력된다.

이 방식은 Cartesian Product, 곱집합이라는 용어로 정의되어 있다.

cities 테이블과 간단하게 만든 transport 테이블과의 CROSS JOIN을 시도했다.

id category
1 bus
2 subway
3 taxi

CROSS JOIN은 여러가지로 표현할 수 있는데, 아래 3가지 모두 같은 결과를 출력한다.

SELECT * FROM cities CROSS JOIN transport;
SELECT * FROM cities JOIN transport;
SELECT * FROM cities, transport;

3 X 3 = 9개의 row를 가진 테이블이 출력된다.

id city latitude longitude id category
1 서울 37.5407 126.957 1 bus
2 광주 35.126 126.831 1 bus
3 대구 35.7988 128.583 1 bus
1 서울 37.5407 126.957 2 subway
2 광주 35.126 126.831 2 subway
3 대구 35.7988 128.583 2 subway
1 서울 37.5407 126.957 3 taxi
2 광주 35.126 126.831 3 taxi
3 대구 35.7988 128.583 3 taxi

SELF JOIN

기본적인 SELF JOIN은 자기 자신과의 CROSS JOIN을 의미하고, 보통 자기 자신과의 조합은 제거한다.

SELECT * FROM cities a (CROSS) JOIN cities b WHERE a.city <> b.city;
SELECT * FROM cities a, cities b WHERE a.city <> b.city;
id city latitude longitude id city latitude longitude
2 광주 35.126 126.831 1 서울 37.5407 126.957
3 대구 35.7988 128.583 1 서울 37.5407 126.957
1 서울 37.5407 126.957 2 광주 35.126 126.831
3 대구 35.7988 128.583 2 광주 35.126 126.831
1 서울 37.5407 126.957 3 대구 35.7988 128.583
2 광주 35.126 126.831 3 대구 35.7988 128.583

SELF JOIN 활용

SELF JOIN은 한 테이블 내의 row끼리 어떤 계산을 할 때 유용하게 쓰인다.

도시의 위,경도를 활용해 거리를 계산하는 예제이다. (Codefights의 arcade 응용)

참고 : 계산공식은 제일 아래 링크를 통해 확인할 수 있고 Fomula 2를 사용했다.

SELECT a.city as city1, b.city as city2, ROUND(SQRT(POW(69.1 * (b.latitude-a.latitude),2) + 
POW(69.1 * (b.longitude-a.longitude) * COS(a.latitude/57.3),2)) * 1.6, 0) as distance  
    FROM cities a, cities b where a.city <> b.city;
city1 city2 distance
광주 서울 267
대구 서울 242
서울 광주 267
대구 광주 174
서울 대구 240
광주 대구 175

여기서, 문제점은 예를 들어, (광주, 서울)이나 (서울,광주)는 순서만 반대이고 같은 계산이기 때문에 불필요하다.

이를 제거하는 방법은 간단하다.

처음 QUERY와 다른 점은 WHERE 구문에 <> 대신 부등호를 사용한다. SQL에서 텍스트 값이 비교가 가능하기 때문에 반대 순서는 출력되지 않는다.

SELECT a.city as city1, b.city as city2, ROUND(SQRT(POW(69.1 * (b.latitude-a.latitude),2) + 
      POW(69.1 * (b.longitude-a.longitude) * COS(a.latitude/57.3),2)) * 1.6, 0) as distance  
      FROM cities a, cities b where a.city > b.city;
city1 city2 distance
서울 광주 267
대구 광주 174
서울 대구 240

서울은 사전 방식(lexicographically)으로 광주보다 큰 값이기 때문에 (광주,서울)은 출력되지 않는다.

References :