[SQLD 6화] NULL 개념의 이해와 데이터 누락을 방지하는 COALESCE 및 NVL 함수 활용법 (SQLD, 직장인 SQL)

작성자:

1. 데이터 집계의 가장 큰 복병: NULL이란 무엇인가?

5화에서 우리는 데이터의 시각적 가독성을 높이고 순위를 매기는 ORDER BY 구문의 정렬 기준과, 대용량 조회 시 자원 고갈 에러를 회피하기 위한 실무 최적화 방안을 살펴보았습니다.

필터링과 정렬을 거쳐 가공된 최종 데이터 집합을 가지고 본격적인 사내 보고서나 실적 장표를 만들다 보면, 예상치 못한 곳에서 수치가 맞지 않거나 합계 금액이 통째로 증발해 버리는 기이한 현상을 마주할 때가 있습니다. 이러한 현상을 일으키는 원인은 대개 데이터베이스 내에 존재하는 ‘NULL’이라는 특수한 값 때문인 경우가 많습니다.

많은 초보 분석가와 직장인들이 쿼리를 작성할 때 NULL을 숫자 0이나 공백 문자(Blank)와 동일한 개념으로 오해하곤 합니다. 그러나 SQL 문법 규격과 실무 데이터 생태계에서 NULL은 이들과 전혀 다른 메커니즘으로 작동합니다. NULL은 ‘알 수 없는 값(Unknown)’ 또는 ‘아직 입력되지 않은 결측치(Missing Value)’를 의미하는 일종의 상태 값에 가깝습니다.

이 개념을 명확히 인지하지 못하고 일반적인 사칙연산이나 집계 함수를 적용하면 데이터의 신뢰성이 무너지는 결과로 이어질 수 있습니다. 본 연작 시리즈는 대용량 데이터 처리에 자주 활용되는 테라데이터 SQL 환경을 기준으로 실무 예제와 함수 활용법을 설명합니다.

2. 현업 비즈니스 사례로 보는 NULL 연산의 치명적인 함정

실무 분석 환경에서 NULL 처리를 누락했을 때 얼마나 심각한 데이터 왜곡이나 누락이 발생하는지, 실제 커머스 기업의 정산 및 마케팅 실적 분석 부서에서 빈번하게 일어나는 가상의 시나리오를 통해 짚어보겠습니다.

📊 [실무 사례 예시: 정산 팀 박 대리의 매출 집계 누락]

온라인 종합 쇼핑몰의 정산 담당자인 박 대리는 이번 달 전사 실적 보고를 위해 ‘고객별 최종 결제 금액 합산 테이블’을 만들라는 지시를 받았습니다.

해당 테이블에는 고객이 물건을 구매한 원본 금액인 ‘주문 금액(ORDER_AMT)’과 변동성으로 지급되는 ‘할인 금액(DISCOUNT_AMT)’, 그리고 ‘포인트 사용 금액(POINT_AMT)’ 칼럼이 존재했습니다. 박 대리는 직관적으로 다음과 같이 사칙연산 쿼리를 작성하여 실행했습니다.

-- [주의] NULL 처리 누락으로 인해 최종 금액이 통째로 증발할 수 있는 위험한 쿼리
SELECT 
    CUST_NO
  , ORDER_AMT
  , DISCOUNT_AMT
  , POINT_AMT
  , (ORDER_AMT - DISCOUNT_AMT - POINT_AMT) AS NET_REVENUE -- 최종 순매출 계산
FROM 
    DW_MART.ORDER_SUMMARY_LOG
WHERE 
    PURCHASE_DT = '2026-07-12';

🚨 무엇이 문제였을까? (NULL의 사칙연산 메커니즘)

결과 화면을 확인한 박 대리는 당황했습니다. 주문 금액이 분명히 존재하는 일부 고객들의 NET_REVENUE(순매출) 칼럼 값이 숫자가 아닌 공백(NULL)으로 출력되었기 때문입니다.

이러한 현상이 발생하는 이유는 “NULL이 피연산자로 참여하는 일반적인 산술 연산의 결과는 NULL이 된다”는 SQL 표준에서 정의된 동작 방식입니다.

  • 만약 특정 고객이 할인을 전혀 받지 않아 DISCOUNT_AMT 칼럼에 숫자 0이 아닌 NULL이 적재되어 있었다면, 시스템은 100,000원 - NULL - 5,000원이라는 연산을 시도하게 됩니다.
  • 데이터베이스 엔진 입장에서 NULL은 ‘알 수 없는 값’이므로, 알 수 없는 값을 빼거나 더한 최종 결과 역시 ‘알 수 없는 상태’인 NULL로 처리해 버리는 것입니다.

결과적으로 할인을 받지 않은 우량 고객들의 매장 기여 매출이 집계에서 대거 제외되어 전사 매출이 축소 보고되는 심각한 데이터 왜곡 현상을 야기할 리스크가 존재합니다.

3. 결측치를 구원하는 해결사: COALESCE 및 NVL 함수 활용법

이러한 연산 결과의 누락과 예기치 않은 계산 결과를 방지하기 위해, 현직 데이터 분석가들이 필수적으로 사용하는 함수가 바로 COALESCE와 NVL입니다. 이 함수들은 쿼리가 돌아가는 과정에서 NULL을 발견하면 사용자가 지정한 특정 대체값(예: 숫자 0 또는 문자 'N/A')으로 변환하여 연산이 끊기지 않도록 방어막 역할을 해줍니다.

1) NVL 함수의 특징과 활용

NVL 함수는 오라클에서 널리 사용되는 NULL 처리 함수이며, Teradata에서도 지원됩니다. 다만 SQL 표준 함수는 아니므로 데이터베이스에 따라 지원 여부가 다를 수 있습니다. NVL 함수는 오직 두 개의 표현식만 지정할 수 있습니다. 첫 번째 인자의 값이 NULL이 아니면 그 값을 그대로 반환하고, NULL일 경우에만 두 번째 지정된 값을 반환합니다.

-- NVL 함수를 적용하여 결측치를 0으로 치환하는 기본 예시
SELECT 
    NVL(DISCOUNT_AMT, 0) AS CLEAN_DISCOUNT
FROM 
    DW_MART.ORDER_SUMMARY_LOG;

2) 표준 함수 COALESCE의 강력한 확장성

COALESCE는 SQL 표준 함수이므로 데이터베이스 간 이식성이 높아 일반적으로 권장됩니다. COALESCE는 영어 단어 뜻 그대로 ‘합치다’, ‘하나로 아우르다’라는 의미를 지니며, NVL과 달리 3개 이상의 표현식을 지정할 수 있습니다.

COALESCE(인자1, 인자2, 인자3, ...) 구조로 작성하면, 시스템은 앞에서부터 차례대로 인자를 검사하다가 ‘최초로 NULL이 아닌 값’을 만나면 그 즉시 해당 값을 채택하고 해당 값을 반환합니다. 만약 모든 인자가 NULL이라면 최종적으로 NULL을 반환합니다.

📝 비즈니스 리팩토링 사례: COALESCE를 통한 안전한 매출 집계

앞서 박 대리가 실패했던 순매출 집계 쿼리를 COALESCE 함수를 활용하여 데이터 누락이 없도록 안전하게 리팩토링해 보겠습니다.

-- [올바른 예시] COALESCE를 활용해 결측치를 0으로 제어한 안정적인 매출 연산 쿼리
SELECT 
    CUST_NO
  , ORDER_AMT
  , COALESCE(DISCOUNT_AMT, 0) AS DISCOUNT_AMT -- NULL일 경우 0으로 대체
  , COALESCE(POINT_AMT, 0)    AS POINT_AMT    -- NULL일 경우 0으로 대체
  , (ORDER_AMT 
     - COALESCE(DISCOUNT_AMT, 0) 
     - COALESCE(POINT_AMT, 0)
    ) AS NET_REVENUE -- 이제 NULL을 만나도 0으로 연산되어 누락이 발생하지 않음
FROM 
    DW_MART.ORDER_SUMMARY_LOG
WHERE 
    PURCHASE_DT = '2026-07-12';

이처럼 COALESCE 함수를 적용하면, 할인 금액이나 포인트 금액이 입력되지 않은 결측치 상태의 행이라 할지라도 NULL을 0으로 대체하여 연산을 수행하므로, 최종 순매출액이 정상적인 숫자로 출력되는 신뢰성을 확보할 수 있게 됩니다.

4. 팩트 체크 및 데이터 분석가의 관점 점검

기술적 정확성을 지키기 위해, 실무에서 NULL과 대체 함수를 다룰 때 가장 많이 오해하거나 혼동하는 사실 관계들을 점검해 보겠습니다.

  • COALESCE 내에 나열하는 대체값들의 데이터 타입은 달라도 되는가?
    • 팩트 체크: 그렇지 않습니다. COALESCE(칼럼명, 0, '없음')과 같이 하나의 함수 식 내에 숫자형 타입과 문자형 타입을 혼용하여 나열하면, 데이터베이스 엔진은 데이터 타입 불일치(Data Type Mismatch) 에러를 발생시킵니다. COALESCE 함수 내에 배치되는 모든 표현식과 대체값들은 반드시 동일한 데이터 유형(또는 암시적 형변환이 가능한 호환되는 유형)이어야 합니다. 문자를 대체할 때는 문자를, 숫자를 대체할 때는 숫자를 매칭해야 문법적 에러를 회피할 수 있습니다.
  • 집계 함수(SUM, COUNT 등)를 사용할 때도 무조건 COALESCE로 묶어야 하는가?
    • 팩트 체크: 상황에 따라 다릅니다. SQL 문법 규칙상 SUM, AVG, MAX, MIN 같은 그룹 집계 함수들은 연산 과정에서 집계 대상 데이터 내에 존재하는 NULL 값을 자동으로 무시(Ignore)하고 나머지 숫자들만 가지고 연산을 수행하도록 기본 설계되어 있습니다. 따라서 단순 SUM(SALE_AMT)을 구하는 경우에는 일부 행이 NULL이어도 에러가 나지 않습니다. 단, 테이블 내에 데이터가 단 1건도 없거나 모든 행이 NULL인 대상을 SUM하면 최종 결과가 NULL로 출력되므로, 이를 방지하여 최종 화면에 숫자 0을 강제 노출하고 싶을 때는 COALESCE(SUM(SALE_AMT), 0) 형태로 집계 함수 바깥을 감싸주는 구조가 대안으로 활용됩니다.

5. 실무 결측치 예외 처리 가이드 및 체크리스트

쿼리를 최종 빌드하기 전, 내 데이터 파이프라인의 안전성을 검증할 수 있는 요약 가이드를 제공합니다.

상황 분류발생 가능한 문제점현직 분석가의 권장 대안 및 함수 적용
칼럼 간의 사칙연산 (+, -, *, /)연산 참여 칼럼 중 1개만 NULL이어도 결과가 NULL로 반환각 개별 칼럼을 COALESCE(칼럼명, 0)으로 감싸준 뒤 사칙연산 수행
고객 비상 연락처 조회 (핸드폰, 집전화)특정 연락처가 비어 있어 고객 연락망 공백 발생COALESCE(MOBILE_NO, HOME_NO, '연락처없음') 순차적 대안 추출
카테고리 코드 매칭 정보 없음보고서 상에 빈 칸으로 출력되어 시각적 불완전성 유발COALESCE(CATEGORY_NM, '기타/미분류') 형태로 문자열 대체 처리

6. 결론 및 다음 화 예고

이번 6화에서는 데이터베이스 연산의 가장 거대한 복병인 NULL 개념의 본질을 파악하고, NULL 전파로 인한 계산 결과 누락 문제를 제어하기 위한 COALESCE 및 NVL 함수의 문법 구조와 비즈니스 리팩토링 사례를 다각도로 점검해 보았습니다. 완벽해 보이는 논리를 가진 쿼리라 할지라도 결측치에 대한 예외 처리가 누락되면 데이터의 신뢰성을 담보할 수 없음을 인지하는 것이 중요합니다.

이어지는 7화에서는 데이터의 조건에 따라 흐름을 분기하고 쿼리 내에서 유연하게 데이터를 분류해 주는 CASE WHEN 조건문을 집중 분석합니다. 특히 실무 데이터 분석 과정에서 고객들의 구매 금액대를 기준으로 ‘VIP’, ‘골드’, ‘일반’ 등급의 세그먼트를 단 하나의 쿼리문 안에서 정밀하게 분류해 내는 방법과, 오늘 배운 NULL 개념을 CASE WHEN 문법과 유기적으로 결합하여 고급 비즈니스 로직을 빌드하는 치트키를 상세히 파악해 보겠습니다.

댓글 남기기