|

FULL OUTER JOIN과 COALESCE를 활용해 여러 개의 테이블을 깔끔하게 조인하기

Byautooffice


데이터 분석을 위해서는 SQL문법의 JOIN을 이해하는 것이 필수적입니다. INNER JOIN, LEFT JOIN, FULL OUTER JOIN 등 다양한 JOIN 문법을 활용하면 여러 개의 테이블에 흩어져 있는 데이터를 하나로 결합하여 더 폭넓은 분석을 가능하게 합니다. SQL문법의 꽃이라고도 할 수 있지요.

💡조인의 개념을 알고싶으시다면 아래의 링크에 들어가보세요.

[SQL] JOIN 한 방에 정리 – 개념부터 코드까지 이것만 보자


이번 포스트에서는 JOIN 중에서도 FULL OUTER JOIN 을 살펴보며, COALESCE 함수를 사용하는 방법에 대해 알아봅니다.

FULL OUTER JOIN 은 두 개의 테이블을 결합할 때 양쪽 테이블의 모든 데이터를 포함하지만, 조인 조건에 일치하지 않는 값은 NULL 로 표시하는 특징이 있습니다. 숫자 값의 경우에는 NULL 로 표시해도 무방하지만, 조인 조건에 들어있는 텍스트의 경우에는 NULL 이 아닌 값으로 합쳐서 보여주는 것이 필요할 때가 있습니다. 그런 경우, COALESCE 를 FULL OUTER JOIN 과 함께 사용해서 해결할 수 있습니다.

이 포스트에서는 예제를 통해 FULL OUTER JOIN 과 COALESCE 의 개념을 살펴보고, 두 구문을 함께 사용하여 NULL 을 효과적으로 처리하는 방법을 알아보겠습니다.

1. 두 테이블 간의 FULL OUTER JOIN


FULL OUTER JOIN 은 쉽게 말해 두 테이블에 존재하는 모든 데이터를 포함하는 조인 방식입니다. A테이블과 B테이블을 조인한다고 할 때 (1) A테이블에만 있는 데이터 (2) B테이블에만 있는 데이터 (3) A와 B 테이블 둘 다 있는 데이터를 모두 포함합니다.

💡FULL OUTER JOIN 문법에 대해서 자세히 알아보려면 아래의 레퍼런스에 접속해보세요.

SQL FULL OUTER JOIN Keyword

　

　

　

　　

　　

　


이제 예를 들어, 한 반의 학생들이 선택 과목 2개의 시험을 봤다고 생각해봅시다.

선택과목 1번을 본 학생은 철수, 영희, 민수이고, 선택과목 2번을 본 학생은 영희, 민지, 준호라고 합시다. 총 5명의 학생의 시험 점수를 한 테이블로 합쳐야 한다면 FULL OUTER JOIN 을 사용할 수 있습니다.


이름을 조인의 키 컬럼으로 사용해서 다음과 같이 쿼리를 작성하면 한 테이블로 합쳐서 볼 수 있습니다. 조인 키의 값이 없으면 항목들은 NULL 로 입력됩니다.


아래의 결과에서 알 수 있는 것처럼, 선택과목1시험에는 있지만 선택과목2시험에는 없는 철수와 민수는 B.이름과 선택과목2점수가 모두 NULL 로 표시됩니다.


한 눈에 이해하려면, A.이름, B.이름을 합쳐서 한 컬럼으로 보는 것이 좋겠지요? 이럴 때, COALESCE 를 사용합니다. COALESCE 는 두 컬럼을 하나로 통합하는 결과를 가져오는데, 다음과 같이 쿼리를 써보겠습니다.

💡COALESCE 함수는 NULL이 아닌 첫 번째 값을 반환하는 함수입니다. 기본 문법은 다음과 같습니다. 왼쪽부터 차례로 값을 검사해서 NULL이 아닌 첫 번째 값을 반환하는 방식이지요. 만약 모든 값이 NULL이라면 NULL을 반환합니다.

COALESCE(값1, 값2, 값3, …)



첫 번째 행에서 A.이름은 철수이고, B.이름은 NULL이기 때문에 철수로 표시되고, 4번째 행에서 A.이름은 NULL이고 B.이름은 민지이기에 NULL이 아닌 민지로 표시 됩니다.


⚠️빅쿼리용 테스트 코드를 사용해서 직접 실행해보세요.


2. 세 테이블 간의 FULL OUTER JOIN


여기서 선택과목3에 대한 점수도 다음과 같이 있다고 더 생각해봅시다. 이 테이블도 결과에 넣고 싶다면 어떻게 할 수 있을까요?


2가지 방법이 있습니다.

  1. 이미 조인된 선택과목1&2 테이블에 한 번 더 조인
  2. 처음부터 선택과목1, 2, 3을 한 번에 조인

2-1. 이미 조인된 테이블에 추가로 조인 하는 경우


이미 조인된 테이블을 CTE로 설정하고, 조인을 한 번 더 합니다.

💡CTE란? CTE(Common Table Expression)는 SQL에서 임시로 생성하는 테이블입니다. 이것은 쿼리의 가독성을 높이고 재사용성을 향상시키는 역할을 합니다. WITH문을 사용하여 정의합니다. 예시에서는 각 기간 별로 SELECT하는 구문이 괄호로 묶여있는데, 이 단위들을 CTE라고 합니다.



다음과 같은 통합된 결과를 얻을 수 있습니다.


⚠️ 빅쿼리용 테스트 코드를 사용해서 직접 실행해보세요.


2-2. 처음부터 선택과목 1, 2, 3을 한 번에 조인 하는 경우


CTE를 만들지 않고, 한 번에 세 테이블을 조인합니다. 물론 조인은 순차적으로 이루어집니다.

두번째 조인 조건에서 COALESCE 를 사용해서 1번과 동일한 결과를 얻을 수 있습니다. 코드가 훨씬 간결해져서 보기 좋지요?


⚠️ 빅쿼리용 테스트 코드를 사용해서 직접 실행해보세요.


만약 두 번째 조인 조건에서 COALESCE 를 쓰지 않고 ON A.name = C.name 으로 하면 어떤 일이 벌어질까요?

첫번째 시험 테이블에 없지만 2, 3번째 시험테이블에 있는 준호가 올바르게 조인되지 않아서 다음과 같이 결과가 나옵니다.

준호의 2, 3번째 시험이 조인되지 않아서 개별적인 행으로 표시됩니다. 이건 우리가 원하는 것이 아니지요.


이 예제를 통해 FULL OUTER JOIN 으로 여러 테이블을 한 번에 조인할 때에는 조인 조건에 COALESCE 를 써야 한다는 것을 확인할 수 있었습니다. FULL OUTER JOIN 과 COALESCE 에 대한 기본 지식을 소개하였으니 이제 실제 사용 사례를 소개해보겠습니다.

3. 루커스튜디오 활용 예시


루커 스튜디오에는 기간 별로 다양한 수치를 비교하는 기능이 내재되어 있습니다. 그 중에 ‘맞춤 쿼리’ 또는 ‘커스텀 쿼리’ 라고 하는 기능은 SQL문을 루커 스튜디오에서 작성하고 빅쿼리로 보내어 실행한 결과를 받아오는 기능이지요.

💡지금부터 다룰 내용은 아래의 포스트에서 다루는 내용에 대해 소개되는 것으로, 아래의 포스트를 읽어보지 않았다면 링크를 참조해주세요.

맞춤 쿼리(커스텀 쿼리) 활용하기 – 기간 매개변수


예를 들어, 아래와 같이 일자별 매출이 담긴 테이블이 있을 때, FULL OUTER JOIN 과 COALESCE 을 사용하면 해당 기간의 1년 전, 1개월 전, 2개월 전, 3개월 전 매출도 한꺼번에 통합해서 보여줄 수 있습니다.

예제에서 사용할 테이블의 이름은 ‘LS_salesData’ 입니다.


참조 포스트에서 사용했던 쿼리를 다시 한번 살펴볼까요? 루커 스튜디오에서 시작 날짜(@DS_START_DATE)와 종료 날짜(@DS_END_DATE) 매개변수를 받고 있습니다.


쿼리의 결과 화면은 아래와 같습니다. 각각의 날짜에 대해서 비교 기간 별로 매출을 한 눈에 파악할 수 있도록 통합되었습니다. 어떻게 이런 결과가 나올 수 있었을까요?

예제에서 살펴본 ‘2-2 처음부터 선택과목 1, 2, 3을 한 번에 조인 하는 경우‘의 방법을 사용한 결과입니다.

선택 기간 매출 CTE, 1년 전 매출 CTE, 1개월 전 매출 CTE, 2개월 전 매출 CTE, 3개월 전 매출 CTE를 각각 생성한 뒤에 CTE들을 일자 기준으로 한 번에 FULL OUTER JOIN 하였습니다. 예제에서 살펴본 것처럼, 한 번에 여러 테이블을 조인하면서 salesDate에서 NULL이 없도록 통합하기 위해서 SELECT 와 조인 조건에 COALESCE 를 사용하였습니다.


처음 세 테이블 간의 조인을 도식화 하면 다음의 이미지와 같습니다.


조인 조건에서 COALESCE 구문을 사용했기 때문에 JOIN 할 때 누락되는 값 없이 JOIN할 수 있습니다. 각각의 JOIN 단계에서 일치하는 일자가 없을 때에, 다음 CTE의 일자와 대조하는 방식이기 때문입니다.

첫 번째 JOIN에서 선택 기간의 일별 매출 CTE에는 1년전 일별 매출 CTE에 있는 ‘2025-01-02’가 없습니다. 일자에는 COALESCE 가 적용되었기 때문에 ‘2025-01-02’가 표시되지만, 선택기간 일별 매출에는 NULL이 삽입됩니다. (이 부분도 COALESCE(c.currentRevenue, 0) 을 적용해서 NULL대신 0으로 표시되도록 만듭니다.)

두 번째 JOIN에서 선택기간의 일별 매출과 1개월전 일별 매출을 조인하려고 하는데, 선택기간의 일별 매출에는 ‘2025-01-02’와 일치하는 행이 없지요, 그래서 1년전 일별 매출에서 있는지 찾게 됩니다. 여기에는 있기 때문에 해당 행에 1개월 전 매출을 표시해줍니다.

총 5번의 조인도 동일한 방식으로 이루어지며, 모든 테이블의 날짜가 누락이 없으면서도 한 열로 통합이 되어서 표시되게 됩니다.


오늘의 포스트에서는 FULL OUTER JOIN 과 COALESCE 를 각각 소개하고, 적절히 결합하는 방법에 대해 소개하였습니다. 복잡한 요구 사항들을 수행하다보면 쿼리의 길이가 너무 길어질 때가 있지요. 그럴 때에 이러한 구문들을 적절히 활용하면 보다 깔끔한 쿼리로 목적을 달성할 수 있습니다. 오늘 살펴본 유용한 구문들도 실무에 활용해보시길 바랍니다!

Similar Posts