MSSQL - COALESCE()-ISNULL() 축약

COALESCE() 함수

 

구문 - COALESCE(expression1, …n)

지정한 표현식들 중에 NULL이 아니 첫 번째 값을 반환합니다. 즉 ISNULL()과 친척 관계입니다. 다중 ISNULL() 개념을 적용하고 싶을 때 이를 축약해서 사용할 수 있는 함수인 셈이죠.

온라인 설명서에는 이 함수가 다음과 같은 식이라고 소개합니다.

 

 

먼저 간단한 예제를 하나 보죠, 아래와 같이 3개의 변수값을 지정하면 NULL이 아닌 첫 번째 값, @C_Code 값을 반환하게 됩니다. 참고로 이걸 ISNULL()로 표현하면 어떻게 할 수 있을까도 생각해 보실 수 있겠죠.

DECLARE

        @A_Code int = NULL

,       @B_Code int = NULL

,       @C_Code int = 10248;

 

 

-- 다중 ISNULL()의 축약식

SELECT COALESCE(@A_Code, @B_Code, @C_Code);

 

 

특징

함수에 입력되는 표현식이 모두 NULL일 경우엔 결과도 NULL이 반환됩니다.

DECLARE

        @A_Code int = NULL

,       @B_Code int = NULL

 

SELECT COALESCE(@A_Code, @B_Code);

 

-----------
NULL

 

그리고 NULL 값 상수만 지정하면 아래와 같이 오류가 발생합니다

 

메시지 4127, 수준 16, 상태 1, 줄 1

COALESCE의 인수 중 적어도 하나는 NULL 상수가 아닌 식이어야 합니다.

 

 

적용 시나리오

그렇다면 이 함수는 어떤 경우에 적용할 수 있을까요? 물론 단순히 ISNULL()의 대체용으로 사용하는 경우도 있지만 아래와 같은 데이터 모델을 생각해 보겠습니다.

배타적 OR 관계 열

엔터티(테이블)에서 두 개 이상의 속성(열) 중 하나의 값만 가지는 데이터 일 경우입니다. 예를 들어 고객 유형(개인, 기업 등), 급여 유형(시급, 주급, 월급 등) 등을 생각해 볼 수 있습니다. 아래 간단히 표현한 Sales 테이블의 A_Code, B_Code 둘 중 하나에는 검색 대상 코드가 입력되어 있습니다. 물론 어떤 유형인지를 CodeGubun 열이 나타내지만 지금은 논외로 하겠습니다.

CREATE TABLE dbo.Sales

(

        CodeGubun      tinyint

,       A_Code         int

,       B_Code         int

);

 

INSERT dbo.Sales

VALUES

        (0, 91830, NULL)

,       (1, NULL, 87661)

;

 

-- 어디에 코드값이 존재하는지 상관없이 최종 결과를 확인할 수 있습니다.

SELECT Code = COALESCE(A_Code, B_Code)

FROM dbo.Sales;

 

Code

-----------

91830

87661

 

 

재미난 응용

이 함수를 알고 있으면 특별한 경우에 유용하게 활용할 수도 있습니다. 관련해서 재미난 예제 코드를 하나를 추가합니다. 아래는 두 자리 비트(0/1) 값에 대한 Bitmap을 만드는 코드로 T-SQL 책에 나오는 예제를 좀 더 의미 있게 살짝 수정했습니다. 테이블 행 생성자 VALUES(), GROUP BY CUBE, POWER() 함수, Bitmap 등등 아주 간단한 쿼리 안에 많은 기능과 개념들이 담겨져 있습니다. 모르는 내용이 있다면 각자 공부해 보세요.

 

SELECT

        A = COALESCE(a, 1)

,       B = COALESCE(b, 1)

,       R = (COALESCE(a, 1) * POWER(2, 1)) + COALESCE(b, 1)

FROM (VALUES (0, 0)) AS b(a, b)

GROUP BY CUBE (a, b)

ORDER BY A ASC;

 

A           B           R

----------- ----------- -----------

0           0           0

0           1           1

1           0           2

1           1           3

 

[출처] COALESCE() 함수|작성자 김정선