[MySQL] 다른 테이블에서 데이터 참조하기 JOIN (vlookup)

다음과 같은 데이터가 있다고 하자.

어떤 유저가 언제 어떤 물건을 샀는지 기록해 놓은 자료다. user_data.xlsx

짐작하다시피, product는 이전에 등장했던 product_data 테이블의 product_id 열과 대응된다.

저 유저가 산 품목의 이름이 무엇인지 궁금하다면 product_data 테이블을 참조하여 이름을 가져와야 할 것이다.

말하자면 엑셀의 vlookup(), R의 merge() 에 해당하는 기능이 필요하다.

SQL에서는 이 역할을 JOIN이 담당한다.

일단 다음 쿼리를 보자.

SELECT

user_data.*

,

product_data.name

FROM

user_data

LEFT JOIN

product_data

ON

user_data.product = product_data.product_id

ORDER BY user_data.log_num

;

결과는 다음과 같다.

쿼리를 분석해 보자.

SELECT

user_data.*

,

product_data.name

FROM

user_data

LEFT JOIN

product_data

ON

user_data.product = product_data.product_id

ORDER BY user_data.log_num

;

먼저 눈에 뜨이는 것은 name이나 log_num 같은 열이름을 이야기할 때 그냥 이야기하지 않고 

product_data.name 이나 user_data.log_num 처럼 점(.)으로 구분하여 해당 열이 속한 테이블을 적어 준다는 점이다.

두 개 이상의 테이블을 사용할 때는 열 이름이 중복될 수 있으므로 이와 같이 명시해 주는 것이 좋다.

SELECT 에서는 user_data 테이블의 모든 열(*)과 product_data 테이블의 name 열을 보겠다고 이야기한다.

두 항목은 콤마(,)로 구분되어 있다.

해당 열들을 가져올 곳은(FROM) 말하자면 현재는 존재하지 않는 테이블인데, user_data와 product_data가 서로 연결된(LEFT JOIN) 가상의 테이블이다.

해당 테이블은 user_data 의 product 열과 product_data 의 product_id 열의 자료가 서로 일치하는 행들을 연결해 만들었다. (ON)

연결하는 방식에는 LEFT JOIN, RIGHT JOIN, INNER JOIN 세 가지가 있는데 (앞의 두 가지를 OUTER JOIN 이라고도 한다)

LEFT JOIN은 LEFT JOIN이 쓰인 곳 왼쪽의 테이블을 기준으로, 오른쪽 테이블을 참조하여 항목을 채워 넣는다.

만일 왼쪽 테이블에 있는 항목이 오른쪽 테이블에는 없다면, 해당 항목은 NULL로 표기된다. (왼쪽 테이블의 항목은 모두 출력)

RIGHT JOIN은 LEFT JOIN과 반대로 오른쪽 테이블을 기준으로 한다.

INNER JOIN은 왼쪽 테이블과 오른쪽 테이블 모두에 있는 항목만 표시한다.

R로 보자면 기본 merge()가 INNER JOIN에 해당하고, LEFT JOIN 은 [all.x=T] 옵션이 되겠다.

마지막으로 쿼리에 매 열마다 테이블 이름을 기재해 주는 것이 번거롭다면 AS 명령을 사용하자.

AS는 as soon as possible... 할때 그 as는 아니고 Aliases 의 약자라고 하지만, 개인적으로는 앞의 as로 생각해도 무방할 듯싶다.

SELECT

user_data.*

,

product_data.name

FROM

user_data AS u

LEFT JOIN

product_data AS p

ON

user_data.product = product_data.product_id

ORDER BY user_data.log_num

;

FROM 부분에 AS 명령만 두 개 추가하였다.

이제 이 쿼리 내에서 user_data 테이블은 u로, product_data 테이블은 p로 부르겠다는 이야기다.

그러므로 아래와 같이 쿼리를 간단하게 줄일 수 있다.

SELECT

u.*

,

p.name

FROM

user_data AS u

LEFT JOIN

product_data AS p

ON

u.product = p.product_id

ORDER BY u.log_num

;