3개 이상의 테이블 LEFT JOIN 하기

우리는 SQL에서 2개의 테이블을 합칠 수 있다는 사실을 압니다. 바로 JOIN 이라는 개념을 사용해서요. 그렇다면, 세 개 혹은 그것보다 많은 테이블을 LEFT JOIN 할 수 있을까요? 네, 할 수 있습니다. 이 글은 여러 개의 테이블을 LEFT JOIN 하는 법에 관해 살펴볼 것입니다. 또한, 그 과정에서 우리가 우연히 놓칠 수 있는 개념에 대해서도 함께 다뤄보도록 하겠습니다.

 

LEFT JOIN이란?

LEFT JOIN이 무엇이었는지 되짚어보는 것부터 시작해보죠. 혹시 SQL의 조인 중에서 INNER JOIN을 기억하시나요? INNER JOIN은 조인하는 두 테이블에 공통으로 존재하는 값만 반환합니다. 반면에 LEFT JOIN의 경우, 왼쪽 테이블에서는 모든 값을 오른쪽 테이블에서는 왼쪽 테이블과 일치되는 값만 반환해줍니다. 그렇다면 어떠한 값이 왼쪽 테이블에는 존재하지만 오른쪽 테이블에는 존재하지 않는다면 두 테이블을 LEFT JOIN 한 결과는 어떠할까요? LEFT JOIN을 하는 이상 왼쪽 테이블이 주인공이기 때문에 왼쪽 테이블에서 가져온 행의 값들은 빠짐없이 전부다 반환될 것이지만, 오른쪽 테이블에서 가져오는 행의 값들은 존재하지 않으므로 NULL 값으로 반환됩니다. 그렇다면, 오른쪽 테이블에 있는 값이 왼쪽 테이블에는 존재하지 않는다면 두 테이블을 LEFT JOIN 한 결과는 어떠할까요? 결과 테이블에는 해당 값이 등장조차 하지 않을 것입니다. 왜냐, 주인공은 왼쪽 테이블이니까요.

아래는 LEFT JOIN의 가장 기본적인 구문입니다.

SELECT column names
FROM table1 AS t1
LEFT JOIN table2 AS t2
    ON t1.common_column = t2.common_column;

LEFT JOIN은 SQL을 통한 분석을 할 때 상당히 자주 사용되는 문법 중 하나입니다. 왜냐하면 이는 두 테이블 간의 차이를 파악할 때 상당히 용이하기 때문입니다. 즉, 특정 테이블에는 값이 존재하지만 다른 테이블에는 존재하지 않는 값을 분별해 낼 수 있기 때문입니다. 만약, 왼쪽 테이블에는 값이 존재하지만 오른쪽 테이블에는 존재하지 않는 값들만 따로 조회하고 싶다면 어떤 식으로 쿼리문을 구성해보면 좋을까요? LEFT JOIN을 한 후에 WHERE 절을 추가하여 오른쪽 테이블 열의 값이 NULL인 행을 추가로 필터링해달라고 요청하면 됩니다. 

실제 비즈니스 케이스를 통해 이야기해보도록 하겠습니다. 우리가 온라인 서점을 운영한다고 가정해 보겠습니다. 우리는 지난 6개월 간의 주문 내역을 통해 해당 기간 동안 구매 활동이 뜸해진 고객, 즉 비활성 고객이 누구인지 알고 싶습니다. 하지만 비활성 고객의 목록만 보는 것이 아니라, 최근에 구매 활동이 있었던 활성 고객의 목록도 같이 보았으면 합니다. 즉, 모든 고객의 정보를 나열하고, 그 고객이 구매를 했는지 안 했는지를 추가로 알 수 있게 결과 테이블이 구성되었으면 합니다. 지금 설명한 예시가 바로 LEFT JOIN를 사용하기에 아주 좋은 사례입니다.

우리 상점 내의 데이터베이스 테이블들을 살펴 보도록 합시다.

1. 고객 테이블 (customers)

id	first_name	last_name	gender	age	customer_since
1	Daniel	Black	M	34	2014-10-13
2	Erik	Brown	M	25	2015-06-10
3	Diana	Trump	F	39	2015-10-25
4	Anna	Yao	F	19	2017-02-20
5	Christian	Sanders	M	42	2018-01-31

2. 매출 테이블 (sales)

id	date	book_id	customer_id	quantity	amount
1	2019-09-02	2	3	1	14.99
2	2019-10-01	1	2	1	12.99
3	2019-10-01	3	4	1	15.75

최근 6개월간의 비활성 고객과 활성 고객을 구하는 쿼리문을 작성하면 다음과 같습니다.

SELECT
 c.id,
 c.first_name,
 c.last_name,
 c.gender,
 c.age,
 c.customer_since,
 s.date AS sales_date,
 SUM(s.amount) AS total_spent
FROM customers AS c
LEFT JOIN sales AS s
    ON c.id = s.customer_id
GROUP BY c.id;

해당 쿼리는 customers 테이블에서 고객의 정보를 전부 추출할 뿐만 아니라 그들의 구매 날짜와 구매액을 계산합니다. 구매 날짜와 구매액은 sales 테이블에서 가져와야 할 열입니다. 해당 쿼리문을 실행해보면 아래와 같은 결과를 얻을 수 있는데요. 이중 볼드 처리된 2개의 행은 LEFT JOIN으로만 얻어낼 수 있는 결과입니다. 만약 위 쿼리문에서 LEFT JOIN 대신 INNER JOIN을 사용한다면 테이블 양쪽에 공통으로 존재하는 값만 반환할 것이므로 구매액 정보가 없는 아이디 1과 5는 조회되지 않을 것입니다.

id	first_name	last_name	gender	age	customer_since	sales_date	total_spent
1	Daniel	Black	M	34	2014-10-13	[NULL]	[NULL]
2	Erik	Brown	M	25	2015-06-10	2019-10-01	12.99
3	Diana	Trump	F	39	2015-10-25	2019-09-02	14.99
4	Anna	Yao	F	19	2017-02-20	2019-10-01	15.75
5	Christian	Sanders	M	42	2018-01-31	[NULL]	[NULL]

보시다시피, 해당 기간에 아무런 구매 활동이 없었던 고객의 경우 total_spent 열의 값이 존재하지 않습니다. 하지만, 그들의 고객 정보가 주인공 테이블인 왼쪽 테이블 customers 에 존재했기에 그들의 아이디, 이름, 성별, 나이 등의 정보가 조회될 수 있었습니다. 하지만, 오른쪽 테이블에서 가져오고자 했던 그들의 구매 날짜와 구매액에 관한 데이터는 존재하지 않으므로 전부 NULL값이 나왔습니다. 이게 LEFT JOIN의 모습입니다. 왼쪽 테이블과 공통되는 값이 없다고 해서 왼쪽 테이블 내의 행을 날려버리지 않습니다. 

이젠 좀더 복잡한 경우를 살펴보도록 하겠습니다.

 

하나의 쿼리 속에 여러 번의 LEFT JOIN

분석을 하다보면 2개의 테이블만으로는 분석이 힘들고 3개 이상의 테이블을 조인해야 하는 상황이 발생합니다. 그래서 이번에는 3개 이상의 테이블을 LEFT JOIN 하는 모습을 보여드리도록 하겠습니다. 아래 테이블을 보시죠. 

3. 프로모션 테이블 (promotions)

id	campaign	customer_id	date
1	SMS_discount10	2	2019-09-01
2	SMS_discount10	3	2019-09-01
3	SMS_discount10	5	2019-09-01

우리는 해당 테이블을 활용하여 최근에 진행한 프로모션이 고객의 구매에 영향을 끼쳤는지 알고 싶습니다. 이를 구하기 위해서는 고객(customers), 매출(sales), 프로모션(promotion) 총 3개의 테이블을 조인하면 됩니다. 우리가 원하는 정보가 세 개의 테이블에 흩어져 있으니까요.

SELECT
 c.id,
 c.first_name,
 c.last_name,
 c.gender,
 c.age,
 c.customer_since,
 s.date AS sale,
 p.date AS promotion
FROM customers AS c
LEFT JOIN sales AS s
   ON c.id = s.customer_id
LEFT JOIN promotions AS p
    ON c.id = p.customer_id;

위 쿼리를 실행하면 아래와 같은 결과를 얻습니다.

id	first_name	last_name	gender	age	customer_since	sale	promotion
1	Daniel	Black	M	34	2014-10-13	[NULL]	[NULL]
2	Erik	Brown	M	25	2015-06-10	2019-10-01	2019-09-01
3	Diana	Trump	F	39	2015-10-25	2019-09-02	2019-09-01
4	Anna	Yao	F	19	2017-02-20	2019-10-01	[NULL]
5	Christian	Sanders	M	42	2018-01-31	[NULL]	2019-09-01

LEFT JOIN을 사용했기 때문에 고객의 구매 여부나 프로모션 참여 여부와는 상관없이 모든 고객의 목록을 조회할 수 있었습니다. 만약 3개의 테이블을 INNER JOIN으로 합쳤다면, 결과 테이블은 구매도 하고 프로모션에도 참여한 고객만 조회되었을 것입니다. LEFT JOIN은 한 테이블에는 값이 있지만 다른 테이블에는 그 값이 없어도 여전히 기존 행을 조회할 수 있습니다. 고객 1을 보시죠. 고객 1은 구매도 하지 않았고 프로모션 메시지를 받은 적도 없습니다. 그럼에도 불구하고 우리는 고객 1에 관한 기본적인 정보를 조회받지 않았습니까? 이게 바로 LEFT JOIN의 힘입니다. 고객 4도 살펴보시죠. 이 고객의 경우 구매 이력은 있지만 프로모션 메시지를 받은 적은 없습니다. 고객 5의 경우는 구매 이력은 없지만 프로모션 메시지는 받았습니다. 마지막으로, 구매 이력도 있고 프로모션 메시지도 받은 고객 2와 3도 결과 테이블에서 찾아볼 수 있습니다. 이 2명의 고객의 결과는 INNER JOIN을 사용했을 때도 얻을 수 있는 결과일 것입니다.

이 테이블 결과를 얻기 위해 작성한 쿼리문으로 돌아가보면, 우리는 두 번째 테이블(sales)와 세 번째 테이블(promotions)을 조인하기 위해 첫 번째 테이블(customers)과 겹치는 열을 사용하였습니다. 하지만, 항상 첫 번째 행과 겹치는 열을 찾아 조인해야 하는 것은 아닙니다. 세 번째의 테이블을 조인하고자 두 번째와의 공통된 열을 사용해도 괜찮습니다. 아래 예시에서 해당 케이스를 다뤄보도록 하겠습니다.

이번에는 고객에게 반응이 좋았던 책의 장르들을 알아보고 싶습니다. 이런 분석은 고객에게 책 추천과 같은 개인화된 경험을 제공할 수 있기 때문에 상당히 유용한 정보입니다. 해당 분석을 위해 총 3개의 테이블에서 각기 다른 데이터를 가져올 예정입니다. 테이블은 customers 테이블, sales 테이블, books 테이블이고요. 이미 앞에서 customers 와 sales 테이블을 조인한 적이 있으니, 3번째 테이블인 books 만 추가로 조인하는 과정을 보여드리도록 하겠습니다.

4. 책 테이블 (books)

id	name	author	genre	quantity	price
1	The Lord of the Rings	J. R. R. Tolkien	fantasy	7	12.99
2	Lolita	Vladimir Nabokov	novel	4	14.99
4	The Hobbit	J. R. R. Tolkien	fantasy	10	10.75
5	Death on the Nile	Agatha Christie	detective	8	9.75

쿼리문은 아래와 같습니다.

SELECT
 c.id,
 c.first_name,
 c.last_name,
 s.date AS sale,
 b.name AS book,
 b.genre
FROM customers AS c
LEFT JOIN sales AS s
   ON c.id = s.customer_id
LEFT JOIN books AS b
    ON s.book_id = b.id;

아래는 customers, sales, books 테이블을 LEFT JOIN 한 결과입니다. 테이블의 구성을 보니, 최근 특정 고객이 주문한 책의 이름과 장르라는 정보가 고객의 정보에 합쳐진 것을 확인할 수 있습니다.

id	first_name	last_name	sale	book	genre
1	Daniel	Black	[NULL]	[NULL]	[NULL]
2	Erik	Brown	2019-10-01	The Lord of the Rings	fantasy
3	Diana	Trump	2019-09-02	Lolita	novel
4	Anna	Yao	2019-10-01	[NULL]	[NULL]
5	Christian	Sanders	[NULL]	[NULL]	[NULL]

결과 테이블을 보니, 최근에 구매 이력이 없기에 sales 테이블에서 가져온 sale 열의 값이 NULL인 고객이 2명 있습니다(고객 1과 5). 하지만, 우리가 INNER JOIN이 아닌 LEFT JOIN을 사용했기 때문에 이 둘의 결과를 조회받을 수 있었습니다. 이번에는 고객 4를 보시죠. 최근에 책을 구매하긴 했지만, 우리의 책 테이블 내에는 정보가 등록되어 있지 않은 책을 구매했나 봅니다. 그래서 sales 테이블에서 가져온 sale 열 값은 존재하지만 book 테이블에서 가져온 book 열과 genre 열의 값은 NULL 인 것을 확인할 수 있습니다. 계속해서 언급하는 부분이지만, LEFT JOIN을 사용했기 때문에 이렇게 한 쪽에는 값이 존재하지 않더라도 여전히 왼쪽 테이블의 결과가 조회되는 이런 구성의 결과를 얻을 수 있는 것입니다.

지금까지 다룬 내용을 보아, LEFT JOIN을 통해 3개 이상의 테이블을 조인 하는데는 큰 문제가 없습니다. 하지만, 내가 원하는 결과를 제대로 얻기 위해서는 아래 2개의 주의사항들만 기억해주세요.

 

LEFT JOIN을 여러번 할 때 주의할 점

SQL에서 2개가 아닌 3개 이상의 테이블을 조인하는 것은 헷갈리고 꽤나 까다로운 작업입니다. 그래서 3개 이상의 테이블을 LEFT JOIN을 할 경우 아래의 2가지를 기억해주세요.

첫째, INNER JOIN과는 달리 LEFT JOIN은 조인하는 테이블의 순서가 상당히 중요합니다. 여러분이 어떤 순서로 테이블을 조인하는지에 따라 결과 테이블에 조회되는 행의 개수며 구성 등이 달라질 수 있습니다. 따라서 JOIN 문을 작성하실 때, 만약 여러분이 LEFT JOIN을 하실 거라면 가장 첫 번째의 테이블로 SELECT문에 가장 많은 열을 가져와야 할 테이블을 우선으로 적어주세요. 

둘째, 조인을 여러 번 해야하는데 시작을 LEFT JOIN으로 했다면 보통의 경우는 나머지 조인도 LEFT JOIN을 합니다. 즉, LEFT JOIN을 쓰다가 갑자기 INNER JOIN 이나 다른 조인을 사용하지 않는다는 이야기입니다. 우리가 LEFT JOIN을 사용하는 이유가 무엇입니까? 어떤 값이 비록 조인하는 여러 개 테이블 내에는 공통적으로 존재하진 않더라도, 여전히 그 값을 결과 테이블에서 보고 싶기 때문입니다. 애초에 우리가 다양한 JOIN 종류 중에서 LEFT JOIN을 사용한 이유를 잊지 않으면서, 추가적인 LEFT JOIN을 구성해 나가야 합니다. 부디 갑자기 INNER JOIN을 사용함으로써 원하는 값이 조회되지 않는 불상사를 겪지 않으시길 바랍니다.

 

출처 : 3개 이상의 테이블 LEFT JOIN 하기 (tistory.com)