[SQL] COALESCE로 NULL값 처리하기

프로젝트에서 회사별 리워드 요약을 보여주는 테이블을 쿼리로 채우려고 하다가 월별 적립, 사용, 활성 트랙 수 같은 데이터를 LEFT JOIN으로 묶다 보니, 어떤 회사는 해당 월에 기록이 전혀 없는 경우 컬럼 값이 전부 NULL로 떨어져 계산식에서 에러가 나는 경우가 생겼다. 예를들어 SUM 결과가 NULL이면 0원 대신 비어버리고 사용률과 같은 계산이 꼬이는 경우이다.

이러한 경우 SQL에서 제공하는 COALESCE 함수를 사용하게되면 여러 인수 중 첫 번 째 NULL이 아닌 값을 반환하여 처리가 깔끔해 진다.

---

SQL에서 NULL 값의 문제

예를들어서,
music_plays 테이블에 기록이 없는 회사(company B)는 monthly_earned 값이 NULL로 나온다.

SELECT
  c.name,
  p.monthly_earned,
  (c.total_rewards_earned - c.total_rewards_used) AS total_tokens
FROM companies c
LEFT JOIN plays p ON p.company_id = c.id;

위 쿼리에서 데이터가 없는 최사는 null을 반환하여 비율을 계산같은 연산에 문제를 일으킬 수 있다.

name	monthly_earned	total_tokens
Company A	15000	20000
Company B	NULL	5000
Company C	30000	45000

이러한 연산 문제를 일으킬 수 있는 문제를 방지하고자 COALESCE를 사용한다.

COALESCE 개념

COALESCE의 동작 방식은 다음과 같다

• 입력으로 여러 인수를 받고, 그중 첫 번째 NULL이 아닌 값을 반환
• 모든 인수가 NULL이면 NULL을 반환

예를들어 COALESCE(NULL, NULL, 10, 20) → 10 반환.

문법

COALESCE(value1, value2, ..., default_value)

COALESCE로 문제 해결

앞서 봤던 쿼리를 COALESCE를 사용해 수정해보자

SELECT
  c.name,
  COALESCE(p.monthly_earned, 0) AS monthly_earned,
  (COALESCE(c.total_rewards_earned, 0) - COALESCE(c.total_rewards_used, 0)) AS total_tokens
FROM companies c
LEFT JOIN plays p ON p.company_id = c.id;

세번째 라인의 경우 monthly_earned가 NULL인 경우 0을 반환하게된다.

name	monthly_earned	total_tokens
Company A	15000	20000
Company B	0	5000
Company C	30000	45000