이번 포스트에서는 Join에 대해 다룬다. Join은 두 개 이상의 테이블에서 나온 결과를 종합해 하나의 결과 집합으로 출력하는 키워드다. 정보를 여러 테이블에 분리해 보관하는 관계형 데이터베이스(RDBMS)와 두 테이블 이상의 접점 혹은 차이점에서 유의미한 인사이트를 도출해내는 집합은 뗄레야 뗄 수 없는 사이라 하겠다.

Join은 기본적으로 두 테이블을 갖다 놓고 기준점과 출력 범위를 어떻게 두냐에 따라 다양하게 세분화되어 있고 이는 집합의 개념과 거의 유사하다. 다만 교집합, 차집합 등 일부 집합 구현 기능은 mySQL에서 지원하지 않기 때문에, exists 구문으로 직접 만들어 구현해야 한다. 이번 포스트에서는 위에서 언급한 문법들을 아래 순서로 데이터를 통해 느슨하게 톺아볼 것이다.

1) Inner Join

2) (Left/ Right) Outer Join

3) intersect/ minus

추가로, 데이터는 금일자 기준 29cm의 실시간 베스트를 크롤링한 뒤, 관계형 데이터베이스를 최대한 구현할 수 있는 환경으로 가공해 올렸다. (혹시 궁금하신 분이 있으실까 해서 덧붙이면 크롤링에는 listly, 크롤링 파일의 sql식 가공에는 tableconvert.com를 사용했다.) 사용 데이터는 아래와 같다.

select * from lrank;  *상품코드, 제품의 정상가, 매출 순위 데이터를 보유하고 있다.
select * from linfo;  *카테고리, 브랜드, 상품명, 좋아요 수, 상품평 수 데이터를 보유하고 있다.

(상품코드와 순위는 분석을 위해 내가 임의로 부여한 값이다.)

1) Inner Join

Inner Join은 기준 테이블에 대한 교집합 개념이다. (쌍방 교집합은 아니다. 해당 개념 관련해서는 3) intersect 파트를 보면 된다.) on에 지정해 준 중심이 되는 컬럼(Primary Key, PK: 기본키) 기준으로 데이터를 합쳐서 보여 준다. 가장 최소값으로 Inner Join을 적용해 본다면 다음과 같은 쿼리가 나오고 출력값은 아래와 같다. 

select * from linfo li
inner join lrank lr on li.상품코드 = lr.상품코드;

데이터가 많이 줄었다. 두 테이블의 볼륨 차이 때문이다. linfo는 전체 등록 상품의 데이터를 가지고 있으니 볼륨이 크지만 lrank는 개중 매출 탑 식스만 뽑은 것이니 그렇다. 이렇게 두 테이블을 Inner Join 방식으로 정제하면 데이터를 집어넣지 못 한 null 값이 빠진 상태로 나온다. 여기서 정보값을 좀 줄여 준다면 직관적인 표가 될 수 있겠다.

select lr.순위, lr.상품코드, li.카테고리, li.브랜드명, li.상품명 from linfo li
inner join lrank lr on li.상품코드 = lr.상품코드
order by lr.순위;

이렇게 잡아 주면 뒤죽박죽이던 순위가 일렬로 정렬되고 상품코드, 카테고리, 브랜드명, 상품명까지 직관적으로 보인다. 

또 Inner Join은 (mySQL에서) 기본 조인이기 때문에 from에 추가 테이블을 넣어주고 where 절에 조건을 추가해주는 형식으로도 구현이 가능하다. 다음과 같이.

select lr.순위, lr.상품코드, li.브랜드명, li.상품명, li.좋아요, li.상품평 
from linfo li, lrank lr
where li.상품코드 = lr.상품코드
order by lr.순위;

이것도 위와 동일하게 출력되기 때문에 둘 중 편한 쪽을 취사선택해서 쓰면 되겠다. 또, 해당 방식은 조건을 여러 개 걸어줄 수 있다. 해당 데이터 중 상품평이 적은 것은 신상품인데 매출이 잘 나오는 것이니 프로모션 등으로 밀어볼 필요가 있을 것이다. 이럴 때는,

select lr.순위, lr.상품코드, li.브랜드명, li.상품명, li.좋아요, li.상품평 
from linfo li, lrank lr
where li.상품코드 = lr.상품코드 and li.상품평 < 10
order by lr.순위;

해당 방식으로 상품평이 적은 걸 걸러서 유의미한 표를 만들어도 좋겠다.

2) (Left/ Right) Outer Join

Outer Join에는 세 가지가 있다. 각자 기준 테이블을 어디로 잡느냐에 따라 각각 Left/ Right/ Full Outer Join으로 나뉜다. 이 중 성능 상 거의 쓸 일이 없는 Full Outer Join을 제하고 여기서는 Left와 Right Join만 다룬다. 둘은 성능 차이 없이 완전히 동일해서, 동일한 데이터베이스에서 둘 이상이 쿼리 작업할 경우에는 왼쪽이든 오른쪽이든 한 쪽으로 통일하길 권한다고 한다. 가독성이 심히 안 좋다고......

Outer Join은 기준 테이블에 나머지 한 테이블이 합쳐지는 형태다. 만약 기준 테이블에 존재하지 않는 값이라면 합쳐지는 테이블에도 그 값은 지워진다. 온전한 기준 테이블에 나머지 한 테이블의 교집합까지만 살아남는 것이다.  

select lr.순위, li.상품명, lr.정상가 from linfo li
right outer join lrank lr on li.상품코드 = lr.상품코드;

select lr.순위, li.상품명, lr.정상가 from linfo li
left outer join lrank lr on li.상품코드 = lr.상품코드;

여기서 질문, 이 두 쿼리의 결과값은 차이가 있을까 없을까? 두 쿼리는 right와 left만 빼고는 다 동일하다.

차이가 별로 없을 것 같았지만 보다시피 차이가 크다.  두 Join 간의 기준 테이블 설정이 달라지기 때문에 생기는 현상이다. 위에서 언급한 바 있듯이, linfo는 전체 상품정보 테이블이다. lrank는 그 중 매출 상위권 상품만 랭킹에 올리면서 집계된 데이터이다. 데이터의 자체 크기는 뭐가 더 클까? 당연히 linfo이다. linfo가 lrank보다 데이터의 볼륨이 훨씬 크다. 상품등록이 되지 않은 상품이 랭킹에 오를 리 없으니 linfo가 lrank를 아예 포함한다고 말해도 좋겠다.

Outer Join은 기준 테이블에 나머지 한 테이블이 합쳐지는 형태라고 했다. lrank에 Left Join을 한다면 기준 테이블은 lrank가 되기 때문에 기존과 같이 출력되지만, lrank에 Right Join을 한다면 기준 테이블은 linfo가 되기 때문에 lrank에서 가지고 있지 않은 나머지 상품들까지 같이 뜬다. Outer Join 시에는 어떤 걸 써도 상관 없지만 기준 테이블이 어딘지를 명확히 하고 작업해야 한다.

추가로 덧붙이면, Outer Join으로 Inner Join을 구현할 수 있다.

select * from lrank lr 
left outer join linfo li on lr.상품코드 = li.상품코드 
where li.상품코드 is not null;

on구 뒤에 where절을 주고 is not null로 null값을 제거해 주면 된다. 하지만 이렇게 하느니 그냥 Inner Join을 거는 게 빠르고 깔끔하겠다.

3) intersect/ minus

위에서 Inner와 Outer Join을 다뤘다. 이쯤 되면 궁금하다. 아니, Join이 그게 끝이냐. 진심이야? 집합 설명하려면 한참 남았잖아. 그래. 나도 설명하고 싶다. 하지만 아쉽게도 mySQL의 경우에는 교집합과 차집합에 해당하는 기능을 지원하지 않는다. 그래서 우리가 별도로 만들어 써야 한다. 이렇게.

intersect (교집합) 구문
select * from linfo a
where exists (select 1 from lrank b where a.상품코드 = b.상품코드);

이때 select 뒤의 1은 단순히 exists(있는지 없는지만 찾으면 되는 함수)가 앞에 왔기 때문에 빠른 출력을 위해 써줬다. select 1 from 형식으로 쿼리를 치면 실제 값을 출력하지 않고 값이 있냐 없냐만 검증하고 있으면 1 쳐 준다. 전체 데이터를 출력하는 것보다 훨씬 빠르다. 

minus 구문
select * from linfo a
where not exists (select 1 from lrank b where a.상품코드 = b.상품코드);

교집합의 반대를 생각해 보자. 차집합이다. 많이 바꿀 필요 없이 exists 앞에 not 붙여서 교집합이 아닌 값만 불러와 주면 된다.

여기까지, 두 개 이상의 테이블을 불러와 한 눈에 보게 해 주는 Join에 대해 알아봤다. 쓰면서 느낀 게, 애초에 뭘 보여줄 수 있는 샘플 데이터를 가져와야 죽이 되든 밥이 되든 할 거 같다. 다음 포스팅에서는 서브쿼리에 대해 다뤄볼 건데, 예쁜 샘플 데이터 가져와서 잘 설명해 보도록 하겠다.

참고

Inpa dev 님 블로그

BK_Paul 님 블로그

데이터베이스/ 테이블 관련 키워드

데이터 타입

데이터베이스/ 테이블/ 컬럼 이름 정하기 규칙

1) 문자, 숫자, _ 를 사용한다.
2) 이름에 쓰이는 문자는 주로 영문 소문자를 쓴다. 한글도 가능하지만 인코딩 이슈 발생할 수 있음. 
보통 키워드나 함수명은 대문자, 사용자가 정의한 이름에는 소문자 사용.
3) 이미 키워드, 함수명 등 문법적으로 사용되고 있는 "예약어"는 이름으로 사용할 수 없다.
4) 단어와 단어 사이에는 빈칸 대신 _를 사용한다.
5) 문자로 시작해야 한다. 숫자, _ 불가.
6) 데이터베이스 이름은 중복될 수 없다. 테이블 이름 역시 하나의 데이터베이스 내에서 중복될 
수 없으며 컬럼 이름은 하나의 테이블 내에서 중복될 수 없다.

주요 쿼리 키워드

와일드카드

% / 0개 이상의 문자
_ / 1개의 문자

'%e' / e로 끝나는 문자열
e, ee, eevee, apple, pineapple

'e%' / e로 시작하는 문자열
e, ee, eevee, eric

'%e%' / e가 포함된 문자열
e, ee, eevee, apple, pineapple, aespa

'_e' / e로 끝나고 e 앞에 1개의 문자가 있는 문자열
ae, ee, ce

'%_e' / e로 끝나고 e 앞에 1개 이상의 문자가 있는 문자열
ee, eevee, apple, pineapple

'%_e_%' / e를 포함하고 e 앞뒤로 각각 1개 이상의 문자가 있는 문자열
eevee, aespa

순위 함수

[함수 이름] () OVER (ORDER BY [컬럼 이름])

그룹 함수

GROUP BY가 없는 쿼리에서 사용 시 전체 로우에 적용됨

주요 연산자

주요 문자형 데이터 함수

주요 숫자형 데이터 함수

주요 날짜형 데이터 함수

조건 만들기

테이블 합치기

여러 테이블 한 번에 다루기

함수 만들기 쿼리

SET GLOVAL log_bin_trust_function_creators=1; #사용자 계정에 function create 권한 생성

DELIMITER // # 함수의 시작 지정

CREATE FUNCTION [함수 이름] ([입력값 이름] [데이터 타입], ...)
RETURNS [결과값 데이터 타입]
BEGIN
DECLARE [임시값 이름] [데이터 타입];
SET [임시값 이름] = [입력값 이름];
쿼리;
RETURN 결과값
END

//
DELIMITER; #함수의 끝 지정